PostgreSQL 11 - osm2pgsql performance problems during --append?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

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
_______________________________________________
dev mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/dev
j-2
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 11 - osm2pgsql performance problems during --append?

j-2
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 <[hidden email]> 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
> [hidden email]
> https://lists.openstreetmap.org/listinfo/dev


_______________________________________________
dev mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 11 - osm2pgsql performance problems during --append?

Frederik Ramm
In reply to this post by Michael Kussmaul
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 [hidden email]  ##  N49°00'09" E008°23'33"

_______________________________________________
dev mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 11 - osm2pgsql performance problems during --append?

Michael Kussmaul
In reply to this post by Michael Kussmaul
> 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
[hidden email]
https://lists.openstreetmap.org/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 11 - osm2pgsql performance problems during --append?

Sven Geggus
In reply to this post by Michael Kussmaul
Michael Kussmaul <[hidden email]> 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
[hidden email]
https://lists.openstreetmap.org/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 11 - osm2pgsql performance problems during --append?

Sarah Hoffmann
In reply to this post by Michael Kussmaul
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
[hidden email]
https://lists.openstreetmap.org/listinfo/dev