osm2pgsql tile expiry performance

classic Classic list List threaded Threaded
8 messages Options
j-2
Reply | Threaded
Open this post in threaded view
|

osm2pgsql tile expiry performance

j-2
I'm beginning to implement a tile expiration solution and have run into
some issues with the new tile expiry expansion.

I'm afraid I do not have precise timings, but I'm seeing what appears
to be at least an order of magnitude performance penalty, probably due
to memory exhaustion.

Test machine is Debian stretch 4CPUs, 26GB RAM, SSD Array. Initial osm
import was performed with osm2pgsql 0.92 and finished in under 48 hours.

Under 0.92 I was running multiple render chains while importing
changesets. Tests w/ 0.96 have been run against an otherwise idle
machine.

Database & flatnodes file were restored to initial state between each
round of testing.

WEEKLY changeset using 0.92:
-=-=-=-
time $OSM2PGSQL --append -s -C 3000 -G --hstore -d gis -H $PGHOST -U \
$PGUSER -r xml changes.osc \
--flat-nodes /database/postgresql/OSM-FLATNODES --slim \
--number-processes 4 --style openstreetmap-carto.style \
--tag-transform-script openstreetmap-carto.lua -e19 -o \
$WORKDIR_OSM/expired-tiles

With -e19, I was able to import a weekly changeset in roughly 24 hours.


Using git repository (5/27? pull):
-=-=-=-
time $OSM2PGSQL --append -s -C 3000 -G --hstore -d gis -H $PGHOST -U
$PGUSER -r xml changes.osc \
--flat-nodes /database/postgresql/OSM-FLATNODES --slim \
--number-processes 4 --style openstreetmap-carto.style \
--tag-transform-script openstreetmap-carto.lua -e10-16 -o \
$WORKDIR_OSM/expired-tiles

Consistently crashed w/ a bad alloc(). I didn't note any unusual output
in the compile. Crashes even with a 24 hour changeset.


DAILY changeset using Debian backport to stretch (0.96):
-=-=-=-
As above, using -e10-16.

22 hours spent processing a 24 hour changeset and still importing new
relations. It's 35GB into swap, with osm2pgsql claiming 89% of the
memory usage.

-------

I realize there is a large difference between zoom level 19 and 10-16,
but I assume it should take significantly less RAM/CPU for 10-16.

Please feel free to point out any stupidity I've generated here and/or
recommend a better way to generate a list of dirty tiles at lower zoom
levels based on the 0.92 output.

To be fair, I am not sure this is even related to tile expiration. I
have not tried 0.96 updates without tile expiration as a baseline.

j

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

Re: osm2pgsql tile expiry performance

j-2
> To be fair, I am not sure this is even related to tile expiration. I
> have not tried 0.96 updates without tile expiration as a baseline.

After 3 hours, the same update has reached the same state of progress.
osm2pgsql (debian backport) 0.96 maintains < 1GB of memory usage without
expiring tiles. While expiring tiles, it ballooned to >22GB before
being killed.

Seems the issue is specific to tile-expiration overhaul.

I note that initial testing of this code used an extract of Europe:
https://github.com/openstreetmap/osm2pgsql/pull/747

I did not note it earlier, but I am working with the planet. I doubt
these issues would present themselves on a modestly sized machine
working with Europe only.

j

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

Re: osm2pgsql tile expiry performance

Sven Geggus
In reply to this post by j-2
j <[hidden email]> wrote:

> I'm afraid I do not have precise timings, but I'm seeing what appears
> to be at least an order of magnitude performance penalty, probably due
> to memory exhaustion.

I can not confirm this. I have running the new tile Expiry code at least
since December 2017.

As I am about to setup two machines for this purpose right now I reworked
the scritps a bit. They are available here:
https://github.com/giggls/tileserver-scripts

Regards

Sven

--
Um Kontrolle Ihres Kontos wiederzugewinnen, klicken Sie bitte auf das
Verbindungsgebrüll. (aus einer Ebay fishing Mail)

/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: osm2pgsql tile expiry performance

Michael Reichert-2
In reply to this post by j-2
Hi,

Am 29.05.19 um 21:40 schrieb j:
> WEEKLY changeset using 0.92:
> -=-=-=-
> time $OSM2PGSQL --append -s -C 3000 -G --hstore -d gis -H $PGHOST -U \
> $PGUSER -r xml changes.osc \
> --flat-nodes /database/postgresql/OSM-FLATNODES --slim \
> --number-processes 4 --style openstreetmap-carto.style \
> --tag-transform-script openstreetmap-carto.lua -e19 -o \
> $WORKDIR_OSM/expired-tiles

Just to ensure that all important test conditions are known.

Do you mean the weekly changesets dump from
https://planet.openstreetmap.org/planet/changesets-latest.osm.bz2? Or
how did you create changes.osc or where did you download it from?
changesets-latest.osm.bz contains changeset metadata only.

Btw, "-H $PGHOST" forces connection via TCP instead of Unix sockets. If
you can use Unix sockets, -H 127.0.0.1 is not required.

Best regards

Michael

--
Michael Reichert      www.geofabrik.de
Geofabrik GmbH        Handelsregister: HRB Mannheim 703657
Amalienstr. 44        Geschaeftsfuehrung: C. Karch, F. Ramm
76133 Karlsruhe       Tel: 0721-1803560-3
[hidden email] Fax: 0721-1803560-9


_______________________________________________
dev mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/dev

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: osm2pgsql tile expiry performance

Michael Reichert-2
In reply to this post by j-2
Hi,

Am 30.05.19 um 00:09 schrieb j:
> I note that initial testing of this code used an extract of Europe:
> https://github.com/openstreetmap/osm2pgsql/pull/747
>
> I did not note it earlier, but I am working with the planet. I doubt
> these issues would present themselves on a modestly sized machine
> working with Europe only.

The tests were done with Europe only which is roughly 2/3 of the whole
planet. The main difference to your test is that my test applied the
diff of a single day from download.geofabrik.de. Diffs from
download.geofabrik.de are a pure diff of the old and the new file.

I will have a deeper look into this but my test machine is currently
busy doing other work.

Best regards

Michael

--
Michael Reichert      www.geofabrik.de
Geofabrik GmbH        Handelsregister: HRB Mannheim 703657
Amalienstr. 44        Geschaeftsfuehrung: C. Karch, F. Ramm
76133 Karlsruhe       Tel: 0721-1803560-3
[hidden email] Fax: 0721-1803560-9


_______________________________________________
dev mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/dev

signature.asc (836 bytes) Download Attachment
j-2
Reply | Threaded
Open this post in threaded view
|

Re: osm2pgsql tile expiry performance

j-2
In reply to this post by Michael Reichert-2
On Tue, 4 Jun 2019 14:28:38 +0200
Michael Reichert <[hidden email]> wrote:

> Just to ensure that all important test conditions are known.
>
> Do you mean the weekly changesets dump from
> https://planet.openstreetmap.org/planet/changesets-latest.osm.bz2? Or
> how did you create changes.osc or where did you download it from?
> changesets-latest.osm.bz contains changeset metadata only.

changes.osc was created via osmosis. 0.92 was running w/ a max
interval of 7 days. 0.96 was running w/ a max interval of 1 day.

For reference, this is the command used to create the changeset:
osmosis --read-replication-interval workingDirectory=$WORKDIR_OSM
--simplify-change --write-xml-change changes.osc.gz

Replication url from osmosis configuration.txt:
https://planet.openstreetmap.org/replication/day/

> The tests were done with Europe only which is roughly 2/3 of the whole
> planet.

I did not realize Europe accounted for such a large portion of the OSM
data! I did notice that your test reported a 10GB memory usage for the
import.

I did not pay attention to the memory usage for 0.92 imports, but tile
expiration seems to be requiring significant memory as of 0.96. My own
test hit 22GB before I ^C'd. 21GB more than required w/o expiration.

> I will have a deeper look into this but my test machine is currently
> busy doing other work.

My own dev box is very busy for the next couple of weeks, but I can
spin something up for testing purposes if needed.

j

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

Re: osm2pgsql tile expiry performance

j-2
> My own test hit 22GB before I ^C'd. 21GB more than required w/o
> expiration.

I have no idea where that 22GB number came from. My notes actually
indicate >50GB memory usage. Caffeine deficiency?

j

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

Re: osm2pgsql tile expiry performance

j-2
In reply to this post by Sven Geggus
On Tue, 4 Jun 2019 11:13:29 +0000 (UTC)
Sven Geggus <[hidden email]> wrote:

> As I am about to setup two machines for this purpose right now I
> reworked the scritps a bit. They are available here:
> https://github.com/giggls/tileserver-scripts

I'm afraid that these scripts will not work in our rendering
environment.

For more flexibility with render chains, I have created a small utility
that works with the 0.92 output and expands expiration to include
parent tiles.

It takes an osm2pgsql expiration file as input and streams the
expanded tile list to stdout in the same format.

Code is currently untested at any scale. Feel free to use in any manner
whatsoever. This is not meant as a long term fix, merely a kludge.

https://intergalacticdata.com/public_domain/expandExpire.c

j

_______________________________________________
dev mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/dev