[OSM-dev] PostgreSQL 11 - osm2pgsql performance problems during --append?
Michael Kussmaul
kussmaul.list at nix.ch
Fri Sep 13 11:07:39 UTC 2019
> Sounds similar to this issue. How is your RAM usage? Perhaps the new -C0 flag will help, I dunno.
> https://github.com/openstreetmap/osm2pgsql/issues/946
> j
I had memory problems first after upgrading to PostgreSQL 11, but I disabled JIT on PostgreSQL 11 and then it worked fine again - not sure if it was the main culprit. I only have a 16GB machine, but during append I don't see any memory problems. SWAP is empty, and I'm using 2GB memory at the moment (14% memory consumption) - so I don't think it is a memory problem. Nevertheless I will try the -C0 flag on the next round/week
> if you are using the flatnodes option (which you should for a world-wide import) then the node import step will mainly hit the flatnodes file and only have relatively limited PostgreSQL interaction. It therefore sounds unlikely that the PostgreSQL upgrade could be at fault.
Yes, I use flatnodes (also on SSD) - the strange thing is: I don't see many IO during node processing - I have near constant resource consumption (CPU/Memory/IOwait...):
Overall CPU: 25% (12% user, 12% system), system seems a bit high..
Overall Memory: 14%
IOwait: 0.2% or most of the time less
local network: constant 17.8Mb in and 17.8Mb out. It looks like those are postgres UDP packets sent/received (according to lsof), googling it seems those are from the stats collector. Perhaps this is my problem - I will disable stats on the next run.
I then have mostly two postgres processes consuming CPU
19% CPU on "main: gis planet [local] SELECT" the queries on the DB
13% CPU on "/usr/lib/postgresql/11/bin/postgres" the binary itself
1-4% CPU on osm2pgsql
Not sure, where my bottleneck is. Neither CPU, Memory or IO seem saturated... and initial import was processing fast/as-expected...
But according to your feedback, no PostgreSQL 11 problems are "known", this is good to know. So it probably has something todo with my setup :-)
kind regards and many thanks!
Michael
More information about the dev
mailing list