[OSM-dev] PostgreSQL 11 - osm2pgsql performance problems during --append?

[Michael Kussmaul]

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