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
