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

2019-09-13 Thread Sarah Hoffmann
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?

2019-09-13 Thread Sven Geggus
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?

2019-09-13 Thread 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
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev


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

2019-09-12 Thread Frederik Ramm
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?

2019-09-12 Thread j
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