Any reasons why osm2pgsql doesn't leverage parallelization during huge maps import (eg: whole world)?

[ghevge]

Hi,

I'm new to osm2pgsql realm, but still it is impossible not to notice how slow the tool is when trying to import the whole worls map.

Monitoring the hardware resources, the only resource that seems to be utilized at maximum is the RAM. The rest: CPU and Disks are berely used.

For example when the command below is started, I'm reading in the logs processing rates of:
Processing: Node(8646182k 1100.0k/s) Way(25045k 11.64k/s) Relation(0 0.0/s)

command executed:
sudo -u renderer osm2pgsql -d gis --create --slim -G --hstore --tag-transform- script /data/style/openstreetmap-carto.lua --number-processes 22 -S /data/style/ openstreetmap-carto.style /data/region.osm.pbf -C 8192 --flat-nodes /data/databa se/flat_nodes.bin

At these rates, there is no wonder it takes days to set up an openstreetmap system, if you don't own a last gen server.

My CPU load doesn't go beyond 7% and Disks IOs don't exceed 15 MB/s. Only the RAM is maxed out.

To me this looks like some improper use (or better say: no use of ) parallelization .

Any particular reason(s) why the resources are not utilized at their max pottential ?

Thanks!

PS: the system I'm running on is a 22 cores 120GB RAM VM running on a server with 2 x Xeon v2 (24 cores total) with 128GB of ram and 2 x 1TB SATA SSD in RAID 1.
There is only this particular VM running on this system

[joto]

First, see this FAQ entry.

Second: Parallelization isn't some magic thing that you just switch on and then everything is fast. Osm2pgsql is a complex piece of software and there are many things developers are doing in the limited time they have to make it faster and make it better. There are many places where it can be made faster (some of which involve parallelization), but that's not always the highest priority.
In some parts we are limited by the performance we get from the PostgreSQL database, a lot of the time is spent in there, so making our code better will not always result in much improvement overall. (But we do have a plan to circumvent PostgreSQL for some parts of the work to improve that also.)

And please remember you are getting this software for free. I get that you are frustrated that osm2pgsql isn't as fast or as good as you would want it to be. But imagine how frustrated developers are if you basically tell them that they are stupid and don't know what they are doing. Your question isn't a question at all, it is a complaint. And that is not the best way to motivate developers to work for you for free.

[ghevge]

@joto thanks for the explanations! I'm sorry if I pissed you or anyone else off ! I haven't wrote this post to blame anyone. I am just trying to understant what can make this process so slow, and make it better by offering some ideas. I very much respect and appreciate the work done by the developers behind this project. But keep in mind, as everywhere in a healthy society, you can not get only good feedback, no matter how much you will try. The alternative will be to move to a country like North Korea and from inside, there, everything will be rosy, at least on TV.

To get back to the topic of this discussion, I'm not sure how osm2pgsql prioritizes the issues, but maybe for the next year, instead of implementing 10 new features that maybe 0.1% of users will end up using, better invest that time in fixing this, or any other performance issue that is known by the team. The faster and less resource demanding these processes are, the more accessible they will become, especcialy to the small folks, thus increasing the pool of people that could end up working on this project. As right now, a lot of those people will probably give up and move towards paying a 3rd party entity to deal with the maps services.

Sorry again if something will sound offensive! I'm just rying to be constructive here!

Thanks

[ImreSamu]

@ghevge > "I'm not sure how osm2pgsql prioritizes the issues, "

Just as one shouldn't judge a book by its cover, it's important not to judge osm2pgsql based on first impressions. If you're interested in the project ideas, they can be found at: https://osm2pgsql.org/contribute/project-ideas.html ( search for : "parallel" )
If you wish to prioritize a particular issue, that's certainly possible. If you're in a leadership position within a company or organization and need a specific feature, you have the option to expedite its development by financially supporting someone to work on it.

[ghevge]

@ImreSamu thanks for your feedback. Unfortunately what I need OSM for, is a personal project. I have no budget for it other than my personal time.

I just raised a concern I had about this software, based on the observations I've gathered so far. In the end it will be you guys who will decide what will get fixed or not. Even If I will try to start improvind this functionality in osm2pgsql today, it will probably just take me a couple of month to dig into the code and understand it properly before being able to actually do something relevant.

[mboeringa]

Any particular reason(s) why the resources are not utilized at their max pottential ?

Thanks!

PS: the system I'm running on is a 22 cores 120GB RAM VM running on a server with 2 x Xeon v2 (24 cores total) with 128GB of ram and 2 x 1TB SATA SSD in RAID 1.

Apart from possible other reasons within osm2pgsql, I strongly recommend you to go with at least a Xeon E5 v4 processor that supports PCIe 3.0 if you want to be on a budget second hand server instead of v2, and get off SATA for your disks. Disk IO is probably one of the main bottlenecks in such import processes.

I import the entire Planet using a complex flex style in about 8 hours on my 2016 age HP Z840, using regular PCIe NVMe disks and a cheap PCIe 3.0 NVMe card with bifurcation set in the system's BIOS. Runs highly reliable.

Sure, the switch to NVMe will not suddenly make your system go 100% on CPU and IO all the time, but it does help with IO bottlenecks.

[ghevge]

Thanks for the suggestion! I am already aware that a newer system will be faster. I've saw the benchmarks here:
https://wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks#What_affects_import_time?

Unfortunately for me, I don't have the budget for a more powerfull server at this moment. Maybe in the future. So I'll be stuck with the configuration I've mentioned above, for a while at least.

[mboeringa]

At a minimum, if you can backup to an external disk instead of relying on RAID 1, switch to RAID 0, this should alleviate some of the possible IO bottlenecks on writes. osm2pgsql needs fast random read and write.

I now also see on the Intel site that v2 Xeon's actually already support PCIe 3.0, I always assumed they were PCIe 2.0:

https://ark.intel.com/content/www/us/en/ark/products/75281/intel-xeon-processor-e52695-v2-30m-cache-2-40-ghz.html
https://ark.intel.com/content/www/us/en/ark/products/75283/intel-xeon-processor-e52697-v2-30m-cache-2-70-ghz.html

If your motherboard has PCIe 3.0 as well, then the advice to plug in a PCIe card for NVMe is certainly an option (even PCIe 2.0 bifurcated should give you better IO if (software) RAID 0). One or two good NVMe drives of 1 or 2TB don't break the bank nowadays...

[ghevge]

But I'm not seeing any bottlenecks at the Disks level. It max out at about 17 MB/s when loading the planet data. Where the theoretical SSD max limit is about 600 MB/s

The CPU is the suspect here as it only goes up to about 6 % load.

[ghevge]

And the RAM is always maxed out. I've limited it to 100 GB else it would have taken everything available, killing the VM in the process.

[pnorman]

You want all the memory to be used. Most of it is used as cache by the OS.

The relevant numbers for disk performance are iops, queue depth, and %util. MB/s is not a useful measurement for a random workload.

[mboeringa]

SSD max limit is about 600 MB/s

The random IO needed by osm2pgsql is not so much about maximum throughput, but more about latency and IOPS, where NVMe wins over SATA, e.g see below link for an explanation:

https://www.techtarget.com/searchstorage/feature/NVMe-SSD-speeds-explained

I also don't see anywhere near maximum throughput in GBs of my NVMe drives when osm2pgsql runs, but it still is a lot faster due to high random read/write. This is more general an issue with databases, they often need good low queue depth random read/write, which even with NVMe is much lower than the GBs/s maximum sequential read/write.

E.g. even my raided NVMe Samsung drives have only about 42 MB/s random read at queue depth 1 and with 1 thread (Q1T1), and 78 MB/s random write.

This ameliorates much with higher queue depth and more threads: e.g. 1774 MB/s random read and 1390 MB/s random write at Q32T16.

That is however still far below the maximum 11GB/s sequential read/write of the same drives as measured in CrystalDiskMark at Q8T1.

[ghevge]

Is not a disk problem. If you look at the benchmark for Planet (PBF) on Ubuntu 20.04, Xeon E3-1245 v2, 32GB + 72GB swap, 4C/8T, 2x 480GB SSD RAID 0 ~25h which is a similar system with what I have, except for th RAID 0 ( so better HDD performance) and the CPU. But the speeds reported there are about 30% higher than what I see on my side. On my side i have 2 x XEON E5-2630 v2. The 1 thread score difference between my CPU and E3-1245 v2 is roughly 30%, where E3-1245 v2 is the fastest. So you see, it is strictly related to the single core CPU performance.

E3-1245 v2:
Processing: Node(7086449k 1608.7k/s) Way(787322k 16.38k/s) Relation(9109670 473.55/s) parse time: 71718s

2 x XEON E5-2630 v2:
Processing: Node(8646182k 1100.0k/s) Way(25045k 11.64k/s) Relation(0 0.0/s)

[mboeringa]

PS: the system I'm running on is a 22 cores 120GB RAM VM running on a server with 2 x Xeon v2 (24 cores total)

On my side i have 2 x XEON E5-2630 v2.

You first suggested you had 22/24 real cores, but that processor only has 6 real cores, 12 threads. It also has pretty low single thread performance, which willl hurt, and multi-threaded performance is also nowhere near the top of its generation of processors:

https://www.cpubenchmark.net/cpu.php?cpu=Intel+Xeon+E5-2630+v2+%40+2.60GHz&id=2052

Upgrading your processors - these v2 processors should be dirt cheap nowadays on the second hand market - to something like the Xeon E5-2697 v2 (12C/24T), should give you a considerable boost and cost little, assuming you switch to NVMe drives as well to alleviate the random IO bottleneck, or switch to RAID 0 with the SATA SSDs. That processor has both higher single threaded and considerably higher multi-threaded performance (2x over your current CPUs):

https://www.cpubenchmark.net/cpu.php?cpu=Intel+Xeon+E5-2697+v2+%40+2.70GHz&id=2009

It is not that difficult to replace CPUs if you have any experience with hardware. I swapped my dual Xeon E5-2680 v4 14C/28T processors for two Xeon E5-2699 v4 22C/44T processors and got about a 40%-50% boost in multi-threaded processing, in line with the difference in benchmark results.

[pnorman]

I don't see an issue here. I did a --slim --drop import on a 2017 consumer machine with NVMe (i7-8700, 64G ram, 2x 1TB NVMe SSDs, untuned PostgreSQL) osm2pgsql took 10h32m to import the planet. 9h of this was processing, with most of the remainder being clustering and index building. It'd be nice to get these times down, but half a day on a 5 year old machine is reasonable for the planet. With a ten year old system and low-end CPU I'd expect yours to take longer, of course.

You haven't provided enough information to show what the bottleneck is, but it's almost certainly the disks, regardless of what you've said. Disk measurements in MB/s are not useful and indicate a misunderstanding of random IO workloads.

A system running osm2pgsql will benefit from being able to use lots of RAM. This RAM is not directly used by osm2pgsql, but is used by the OS to cache.

You also haven't indicated how you adjusted your PostgreSQL settings. This can make a significant difference in performance in the postprocessing stage.

[pnorman]

For reference, in the processing stage my system is at 98.79% disk util, 12974 reads/s, 222 writes/sec.