Re: [OSM-dev] PostgreSQL 11 - osm2pgsql performance problems during --append?
Hi, On Fri, Sep 13, 2019 at 01:07:39PM +0200, Michael Kussmaul wrote: > 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 Please have a look what exactly the SELECT is doing. My guess would be that you have lost an index during the upgrade to PG11 and it is now sequentially scanning the tables. Check for these indexes: planet_osm_nodes : planet_osm_nodes_pkey planet_osm_ways : planet_osm_ways_pkey, planet_osm_ways_nodes Sarah ___ dev mailing list dev@openstreetmap.org https://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] PostgreSQL 11 - osm2pgsql performance problems during --append?
Michael Kussmaul wrote: > - Or PostgreSQL 11 has some regressions? Unlikely. I'm using PostgreSQL 11 + Debian 10 on my new tile.openstreetmap.de machines without problems. Sven -- If we want hardware to work to its full potential, we need to claim to be a recent version of Windows. (Matthew Garrett) /me is giggls@ircnet, http://sven.gegg.us/ on the Web ___ dev mailing list dev@openstreetmap.org https://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] PostgreSQL 11 - osm2pgsql performance problems during --append?
> 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 ___ dev mailing list dev@openstreetmap.org https://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] PostgreSQL 11 - osm2pgsql performance problems during --append?
Hi, 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. Bye Frederik -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09" E008°23'33" ___ dev mailing list dev@openstreetmap.org https://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] PostgreSQL 11 - osm2pgsql performance problems during --append?
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 On Thu, 12 Sep 2019 17:59:06 +0200 Michael Kussmaul wrote: > Hi > > I'm running my osm2pgsql setup for a couple of years now by > processing the whole planet and doing "--append" updates on a weekly > basis. I have a Debian server and 1.5 TB SSD storage for the planet > and run with flat-nodes configuration. > > I keep my system up-to-date and recently switched to PostgreSQL 11 > and added a SSD disk to my SSD-RAID. Since this change I see a large > drop on nodes processing by approx a factor of 50x (initial import is > doing fine - just when I apply a diff with --append, I now see a > processing rate of about 0.1k/s, it was in the 5k/s before), way and > relation processing is still ok, faster even! > > Before: > Processing: Node(20644k 4.7k/s) Way(2943k 0.59k/s) Relation(65680 > 29.95/s) After: > Processing: Node(30299k 0.1k/s) Way(4609k 1.15k/s) Relation(81710 > 40.57/s) > > So now the whole update process takes longer as if I drop the > complete planet database and start a fresh import, which is > sub-optimal :-) > > It probably boils down to this: > > - Either my new SSD has some performance problem? > - Or PostgreSQL 11 has some regressions? > - osm2pgsql does not work well with PostgreSQL 11? > > I also don't see any I/O bottleneck (iowait is very low <1%), CPU > overall is steady at 25% (12% user, 12% system) > > I also updated osm2pgsql to the newest 1.0 release, but it did not > change anything. > > Now, before I downgrade the DB or replace the disk: Does anybody else > run PostgreSQL 11 and sees similar problems? > > kind regards > Michael > ___ > dev mailing list > dev@openstreetmap.org > https://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org https://lists.openstreetmap.org/listinfo/dev
[OSM-dev] PostgreSQL 11 - osm2pgsql performance problems during --append?
Hi I'm running my osm2pgsql setup for a couple of years now by processing the whole planet and doing "--append" updates on a weekly basis. I have a Debian server and 1.5 TB SSD storage for the planet and run with flat-nodes configuration. I keep my system up-to-date and recently switched to PostgreSQL 11 and added a SSD disk to my SSD-RAID. Since this change I see a large drop on nodes processing by approx a factor of 50x (initial import is doing fine - just when I apply a diff with --append, I now see a processing rate of about 0.1k/s, it was in the 5k/s before), way and relation processing is still ok, faster even! Before: Processing: Node(20644k 4.7k/s) Way(2943k 0.59k/s) Relation(65680 29.95/s) After: Processing: Node(30299k 0.1k/s) Way(4609k 1.15k/s) Relation(81710 40.57/s) So now the whole update process takes longer as if I drop the complete planet database and start a fresh import, which is sub-optimal :-) It probably boils down to this: - Either my new SSD has some performance problem? - Or PostgreSQL 11 has some regressions? - osm2pgsql does not work well with PostgreSQL 11? I also don't see any I/O bottleneck (iowait is very low <1%), CPU overall is steady at 25% (12% user, 12% system) I also updated osm2pgsql to the newest 1.0 release, but it did not change anything. Now, before I downgrade the DB or replace the disk: Does anybody else run PostgreSQL 11 and sees similar problems? kind regards Michael ___ dev mailing list dev@openstreetmap.org https://lists.openstreetmap.org/listinfo/dev
