User:Mmd/Overpass API/Performance Project 2016
Introduction
The aim of the Overpass performance project is to identify bottlenecks in the current version 0.7.52, and provide concrete suggestions on how the overall performance can be improved. Those improvements are included in a branch called 0.7.54_mmd (see details below) and are published as pull requests and/or prototypes for inclusion into the master Overpass branch after further review and cleanups.
While some improvements target very specific use cases, the focus of this project phase is to measure actual improvements – and of course potential regressions – based on a set of queries taken from productive logfiles on overpass-api.de. Each query needs to have at least a runtime of 5 seconds to qualify. In total this amounts to about 3000 relevant queries with identical queries being filtered out beforehand.
For each version, the database was populated with the same OSM planet before starting measurements. No further database updates were run afterwards to have exactly the same database contents for all measurements. Also, attic data (“previous OSM object versions”) was out of scope due to the long time to prepare the database. This is planned for the next project phase where fast SSDs need to be deployed on the Dev Instance.
All measurements are automatically executed three times in a row in sequential mode. Statistics for each run are also automatically collected for later upload to this wiki. It is important to note that only the third run for each query counts towards statistics.
Results
(text... tbd)
CPU utilization
-
Before: CPU utilization on main instance w/ 0.7.52 - almost 100% CPU utilization for extended periods of time -
After: CPU utilization on main instance w/ 0.7.53, not all recommendations in production yet
Runtime measurements
Initial DB Import
| Version 0.7.52 | Version 0.7.54_mmd | Difference (in %) |
63.9 hours
|
49.5 hours
|
-22%
|
Area creation
Full area creation (no delta mode) using areas.osm3s rules file.
| Version 0.7.52 | Version 0.7.54_mmd | Difference (in %) |
28.9 hours
|
10.75 hours
|
-63%
|
Remark: Process step is mostly CPU bound. Actual runtime for 0.7.52 version on dev instance about 15-17 hours according to previous measurements by Roland, though running at 1.75x CPU speed. Area creation using version 0.7.54_mmd is estimated to run for about 6 hours on dev instance.
Long running queries on production
The following list contains measurements for about 2500 queries, which took longer than 5 seconds on production. They originate from a log file dated 2015-12-17.
Each query was run 3 times and only the last measurement is included in the spreadsheet. Columns cover the number of the query (Ctrl-click follows a hyperlink to the actual query), user time, system time, combined user/system time, Elapsed (wall clock) time, return code (rc), as well as the percentage of time spent on CPU. All values were provided by the time command line tool.
Slower queries are most likely due to UTF-8 handling in PCRE, which is used in Version 0.7.54_mmd, while 0.7.52 uses POSIX regular expressions instead.
Testrun 1
See results on page Testrun 1
Misc queries
Comparison of miscellaneous production queries, log file from Jan 30th 2016.
Testrun 2
See results on page Testrun 2
Testrun 3
See results on page Testrun 3
Testrun 4
See results on page Testrun 4
Setup
Overpass API versions
The following two versions were used for runtime measurements:
- Version 0.7.52: Baseline: master branch (0.7.52), minor issues branch merged on top, HEAD: commit 291bf9599facb4b6567446b02b8a9c6c9a4787ba
- Version 0.7.54_mmd: Branch test_way_diffs, HEAD: commit 43a2b6e404130f4384f232a78335e88bb679ebaf
Compiled with CXXFLAGS=-O3
Initial Database Import
../tools/osmconvert ~/download/planet-latest.osm.pbf --out-osm | buffer -m 20000000 | /srv/overpass0752/bin/update_database --db-dir=/home/mmd/db0752/ --meta 2> proc0752.log
../tools/osmconvert ~/download/planet-latest.osm.pbf --out-osm | buffer -m 20000000 | /srv/overpass/bin/update_database --db-dir=/home/mmd/db/ --meta 2> proc0754.log
NB: Buffer command line tool is used to maximize CPU utilization of the update_database process.
Area creation
Changes to rules file: timeout and element limited increased to avoid timeout/out of memory:
<osm-script timeout="186400" element-limit="2073741824">
Miscellaneous
OSM Planet file
../tools/osmconvert --out-statistics ~/download/planet-latest.osm.pbf
timestamp min: 2005-05-01T14:56:35Z timestamp max: 2016-02-01T01:59:58Z lon min: -180.0000000 lon max: 180.0000000 lat min: -90.0000000 lat max: 90.0000000 nodes: 3200981527 ways: 328627638 relations: 3981413 node id min: 1 node id max: 3981056411 way id min: 37 way id max: 395196498 relation id min: 11 relation id max: 5933210 keyval pairs max: 432 keyval pairs max object: relation 87565 noderefs max: 2000 noderefs max object: way 12469343 relrefs max: 12775 relrefs max object: relation 1205151
System specs
Test server
processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 42 model name : Intel(R) Xeon(R) CPU E5-26xx (Sandy Bridge) stepping : 1 microcode : 0x1 cpu MHz : 1999.999 cache size : 4096 KB physical id : 0 siblings : 4 core id : 0 cpu cores : 4 apicid : 0 initial apicid : 0 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good nopl eagerfpu pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx hypervisor lahf_lm arat xsaveopt bugs : bogomips : 3999.99 clflush size : 64 cache_alignment : 64 address sizes : 40 bits physical, 48 bits virtual power management: (4 procs) SSD, 16GB main memory
Overpass Dev instance
(only listed here for reference purposes)
processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 60 model name : Intel(R) Xeon(R) CPU E3-1271 v3 @ 3.60GHz stepping : 3 microcode : 0x17 cpu MHz : 3636.000 cache size : 8192 KB physical id : 0 siblings : 8 core id : 0 cpu cores : 4 apicid : 0 initial apicid : 0 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 fma cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm ida arat epb xsaveopt pln pts dtherm tpr_shadow vnmi flexpriority ept vpid fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm bogomips : 7183.39 clflush size : 64 cache_alignment : 64 address sizes : 39 bits physical, 48 bits virtual power management: (8 procs) Hard disk, 32GB main memory