User:Mmd/Overpass API/Performance Project 2016

From OpenStreetMap Wiki
Jump to: navigation, search

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.


Peak time CPU utilization - almost 100% CPU utilization for extended periods of time

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

File size comparison

Relevant changes:

  • 0.7.52 uses zlib compression, while 0.7.54_mmd uses lz4 compression. zlib has a higher compression rate at the expense of higher cpu consumption.
  • 0.7.54_mmd also compresses .map files. This feature is currently in the backend_compression_map branch and not yet integrated into master (https://github.com/drolbr/Overpass-API/tree/backend_compression_map)
  • 0.7.54_mmd uses a prototype to reduce way size via delta encoding. This is exactly the same approach as in .pbf and .o5m file formats (https://github.com/drolbr/Overpass-API/issues/248). This affects primarily the ways.bin file.
Filename Version 0.7.52 Version 0.7.54_mmd Size Increase (in %)
node_keys.bin
786.432
1.376.256
75,00%
node_keys.bin.idx
40
40
0,00%
node_tags_global.bin
2.003.697.664
2.662.727.680
32,89%
node_tags_global.bin.idx
415.837
415.837
0,00%
node_tags_local.bin
1.967.521.792
2.279.473.152
15,86%
node_tags_local.bin.idx
401.360
401.360
0,00%
nodes.bin
28.262.662.144
30.511.136.768
7,96%
nodes.bin.idx
1.220.376
1.220.376
0,00%
nodes.map
15.924.461.568
7.620.689.920
-52,14%
nodes.map.idx
242.988
1.943.888
699,99%
nodes_meta.bin
21.574.320.128
26.095.386.624
20,96%
nodes_meta.bin.idx
2.544.632
2.544.632
0,00%
relation_keys.bin
262.144
458.752
75,00%
relation_keys.bin.idx
24
24
0,00%
relation_roles.bin
262.144
327.680
25,00%
relation_roles.bin.idx
24
24
0,00%
relation_tags_global.bin
225.968.128
249.102.336
10,24%
relation_tags_global.bin.idx
26.223
26.223
0,00%
relation_tags_local.bin
239.075.328
276.365.312
15,60%
relation_tags_local.bin.idx
33.339
33.339
0,00%
relations.bin
566.493.184
653.524.992
15,36%
relations.bin.idx
16.312
16.312
0,00%
relations.map
24.117.248
15.204.352
-36,96%
relations.map.idx
364
2.912
700,00%
relations_meta.bin
104.333.312
107.216.896
2,76%
relations_meta.bin.idx
4.248
4.248
0,00%
user_data.bin
11.927.552
13.762.560
15,38%
user_data.bin.idx
312
312
0,00%
user_indices.bin
91.226.112
120.913.920
32,54%
user_indices.bin.idx
2.664
2.664
0,00%
way_keys.bin
1.310.720
1.441.792
10,00%
way_keys.bin.idx
56
56
0,00%
way_tags_global.bin
4.230.610.944
5.469.175.808
29,28%
way_tags_global.bin.idx
639.989
639.989
0,00%
way_tags_local.bin
3.432.382.464
4.633.133.056
34,98%
way_tags_local.bin.idx
557.537
557.537
0,00%
ways.bin
20.921.188.352
16.066.215.936
-23,21%
ways.bin.idx
1.251.944
542.168
-56,69%
ways.map
1.581.252.608
983.719.936
-37,79%
ways.map.idx
24.124
192.976
699,93%
ways_meta.bin
3.080.978.432
3.349.151.744
8,70%
ways_meta.bin.idx
224.584
224.584
0,00%
area_blocks.bin
3.228.303.360
2.965.372.928
-8,14%
area_blocks.bin.idx
107.576
107.576
0,00%
area_tags_global.bin
2.623.799.296
2.809.856.000
7,09%
area_tags_global.bin.idx
46.400
46.400
0,00%
area_tags_local.bin
381.157.376
430.800.896
13,02%
area_tags_local.bin.idx
116.052
116.052
0,00%
areas.bin
125.829.120
184.025.088
46,25%
areas.bin.idx
1.192
1.192
0,00%
Total
110.611.805.749
107.509.601.105
-2,80%


Setup

Overpass API versions

The following two versions were used for runtime measurements:

  1. Version 0.7.52: Baseline: master branch (0.7.52), minor issues branch merged on top, HEAD: commit 291bf9599facb4b6567446b02b8a9c6c9a4787ba
  2. 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

Related pages

Full Attic DB SetupFull Day SimulationTestrun 1
Testrun 2Testrun 3Testrun 4