osm2pgsql append

dooley · 2016-11-24 22:38:42

Frage an die osm2pgsql-Spezialisten hier:

Der tägliche Update (--append) meiner Deutschland-Tabellen (Geofabrik-Extrakt) von http://download.geofabrik.de/europe/germany-updates/ dauert hier so im Schnitt um die 3 Stunden rum. Der Server hat 8 Cores, 48 GB RAM und 2 HDDs (RAID-1), Postgres ist 9.6 mit 8 Workern, OS Ubuntu 16.04 LTS.

Mir kommt das etwas sehr langsam vor für die paar 30-40 MB. pg_top zeigt allerdings fast permanent io-wait > 0 an, teilweise bis zu 40. Da sind dann wohl die Platten zu langsam oder gibts da mehr Schrauben, die ich verstellen kann?

osmupdate -v ${ZEITSTEMPEL} --keep-tempfiles --base-url=download.geofabrik.de/europe/germany-updates/ ${UPDATE_FILE}

/usr/local/bin/osm2pgsql \
    ...
    --append \
    --latlong \
    --proj 4326 \
    --slim \
    --hstore \
    --number-processes 6 \
    --disable-parallel-indexing \
    --prefix germany \
    --cache 4000 \
    --style ./auswertung.style \
    --flat-nodes germany_flat_nodes \
    ${UPDATE_FILE}
...
Maximum node in persistent node cache: 4517896191
node cache: stored: 91118(100.00%), storage efficiency: 48.24% (dense blocks: 1, sparse nodes: 90342), hit rate: 0.72%

Osm2pgsql took 11027s overall

wambacher · 2016-11-25 00:02:32

dooley wrote:

Frage an die osm2pgsql-Spezialisten hier:
Mir kommt das etwas sehr langsam vor für die paar 30-40 MB. pg_top zeigt allerdings fast permanent io-wait > 0 an, teilweise bis zu 40. Da sind dann wohl die Platten zu langsam oder gibts da mehr Schrauben, die ich verstellen kann?

Alte Falle: Man fängt mit einem Extrakt an (hier wohl DE) und mach danach Diff-Updates. Prinzipiell ok, nur der Diff importiert alles - auch POI in Papua Neuguinea So nach und nach wird deine DB immer ballasthaltiger und dadurch natürlich langsamer.
Schau dir mal in QGIS <prefix>_point an und staune.

Ein Re-Import alle paar Wochen sollte Wunder wirken.

was mir auffällt: --disable-parallel-indexing raus, sonst macht das am Ende doch nur 1 CPU.

In postgresql lassen sich natürlich noch einige Parameter einstellen, besonders, da du viel Memory hast.

Ansonsten: vacuum und analyze der Tabellen wird doch regelmässig gemacht, oder? wenn die autovacuum-dienste antiviert sind, wirkt das auch Wunder. Nur tunen sollte man die auch.

Welche Version von osm2pgsql verwendest du denn? da hat sich einiges getan. Dazu wäre für mich ein "\d <prefix>_ways" sehr informativ.

und eine kleine SSD (125-250 GB) für z.B. das Flat File und einige Indices würde auch was bringen.

so, das sollte erstmal für das Wochenende reichen

Gruss
walter

Habe bald eine 128 GB SSD übrig. Interesse? Please mail.

Last edited by wambacher (2016-11-25 00:27:42)

Nakaner · 2016-11-25 00:07:59

Hallo Walter,

wambacher wrote:

Alte Falle: Man fängt mit einem Extrakt an (hier wohl DE) und mach danach Diff-Updates. Prinzipiell ok, nur der Diff importiert alles - auch POI in Papua Neuguinea So nach und nach wird deine DB immer ballasthaltiger und dadurch natürlich langsamer.
Ein Re-Import alle paar Wochen sollte Wunder wirken.

Er nutzt die Diffs vom Geofabrik-Downloadserver (siehe die URL, die er gepostet hat). Diese entstehen durch Differenzbildung zwischen dem Extrakt und dem Extrakt des Vortages. Sie enthalten alle Änderungen in Deutschland, die man benötigt um vom alten auf den neuen Stand zu gelangen. (Wenn ein Objekt an einem Tag zwei neue Versionen bekommen hat, dürfte aber nur eine im Dieff enthalten sein. Aber das ist hier nicht relevant.)

Viele Grüße

Michael

wambacher · 2016-11-25 00:13:08

Nakaner wrote:

Hallo Walter,
Er nutzt die Diffs vom Geofabrik-Downloadserver

oops, ist mir leider nicht aufgefallen.

Nun denn, einige andere Vorschläge hab ich ja noch gemacht. Ich würde mit "Tabellenpflege" anfangen.

gruss
walter

Last edited by wambacher (2016-11-25 00:24:16)

dooley · 2016-11-25 00:57:22

wambacher wrote:

was mir auffällt: --disable-parallel-indexing raus, sonst macht das am Ende doch nur 1 CPU.
In postgresql lassen sich natürlich noch einige Parameter einstellen, besonders, da du viel Memory hast.
Ansonsten: vacuum und analyze der Tabellen wird doch regelmässig gemacht, oder? wenn die autovacuum-dienste antiviert sind, wirkt das auch Wunder. Nur tunen sollte man die auch.
Welche Version von osm2pgsql verwendest du denn? da hat sich einiges getan. Dazu wäre für mich ein "\d <prefix>_ways" sehr informativ.
und eine kleine SSD (125-250 GB) für z.B. das Flat File und einige Indices würde auch was bringen.

so, das sollte erstmal für das Wochenende reichen
Gruss
walter
Habe bald eine 128 GB SSD übrig. Interesse? Please mail.

disable-parallel-indexing, weil ansonsten die io-Wartezeit noch höher geht (RAID1 mit 2 HDD), da tritt sich das Parallel-Indexing gegenseitig auf die Füße?

vacuum und analyze wird täglich gemacht (cron-job vacuumdb). Autovacuum hab ich für die Tabellen ausgeschaltet. Shit, nach Neuimport mit --create vergessen, das Autovacuum auszuschalten. Ist jetzt im Script drin, mal schauen, obs was bringt.

osm2pgsql ist version 0.91.0-dev (64 bit id space)

gis=# \d germany_ways
  Table "public.germany_ways"
 Column |   Type   | Modifiers 
--------+----------+-----------
 id     | bigint   | not null
 nodes  | bigint[] | not null
 tags   | text[]   | 
Indexes:
    "germany_ways_pkey" PRIMARY KEY, btree (id)
    "germany_ways_nodes" gin (nodes) WITH (fastupdate=off)

An der Hardware kann ich leider nix ändern, ist ein gemieteter Root-Server, der im RZ steht... Aber Danke fürs Angebot.

Ich such morgen mal die Postgres-Konfig raus, also die Änderungen, die ich bisher gemacht habe,

Danke erstmal. Morgen mittag weiß ich zumindest, ob das nichtausgeschaltete Autovakuum da reingefunkt hat.

wambacher · 2016-11-25 11:41:04

dooley wrote:

disable-parallel-indexing, weil ansonsten die io-Wartezeit noch höher geht (RAID1 mit 2 HDD), da tritt sich das Parallel-Indexing gegenseitig auf die Füße?

Mag sein, ich würde es aber dennoch mal ausprobieren.

... vergessen, das Autovacuum auszuschalten. Ist jetzt im Script drin, mal schauen, obs was bringt.

Ich hoffe, du meintest ANzuschalten

osm2pgsql ist version 0.91.0-dev (64 bit id space)

gis=# \d germany_ways
  Table "public.germany_ways"
 Column |   Type   | Modifiers 
--------+----------+-----------
 id     | bigint   | not null
 nodes  | bigint[] | not null
 tags   | text[]   | 
Indexes:
    "germany_ways_pkey" PRIMARY KEY, btree (id)
    "germany_ways_nodes" gin (nodes) WITH (fastupdate=off)

Prima, das ist die Version, die keine "pending ways" mehr verwendet. Damit habe ich bei mir derzeit riesige Probleme.

Ich such morgen mal die Postgres-Konfig raus, also die Änderungen, die ich bisher gemacht habe,

kleiner trick - obwohl der keine performance bringt:

mach die Änderungen für postgresql mit "ALTER SYSTEM ...".
dann stehen alle mods in postgresql.auto.conf und das postgresql.conf bleibt unangetastet. Ist einfach übersichtlicher.

maintenance_work_mem = '8GB'
checkpoint_segments = '256'
log_line_prefix = '%t '
max_connections = '200'
shared_buffers = '8GB'
autovacuum_max_workers = '3'
work_mem = '1024MB'
log_temp_files = '1024'
log_statement = 'none'
logging_collector = 'ON'
log_autovacuum_min_duration = '0'
autovacuum = 'on'
max_locks_per_transaction = '128'

Danke erstmal. Morgen mittag weiß ich zumindest, ob das nichtausgeschaltete Autovakuum da reingefunkt hat.

Du kannst Vacuum/Analyze auch jederzeit online machen. So ist das garantiert vor dem nächsten Import durch.
Zumindest den Analyze würde ich schnell machen.

Gruss
walter

Last edited by wambacher (2016-11-25 11:41:25)

dooley · 2016-11-25 12:07:12

Also das Autovakuumieren hat offensichtlich nicht dazwischengefunkt, ist heute wieder ~ 3h mit ca. der gleichen Datenmenge gelaufen.
Dann werde ich für den morgigen Lauf mal das --disable-parallel-indexing rauswerfen, vielleicht ... Große Hoffung hab ich da aber nicht, weil die io-wait permanent zwischen seltenen 0 und ziemlch oft von 2 bis hoch zu 40, 45% schwankt.

Meine Änderungen in der postgresql.conf:

max_worker_processes = 8
max_parallel_workers_per_gather = 8
max_connections = 100
shared_buffers = 12GB
effective_cache_size = 36GB
work_mem = 307MB
maintenance_work_mem = 4915MB
min_wal_size = 1GB
max_wal_size = 2GB
wal_buffers = 16MB
default_statistics_target = 1000

ngt · 2016-11-25 12:13:36

Hi, öhm, es betrifft nicht direkt das Problem von dooley, aber zumindest indirekt werde ich vielleicht auch dieses Problem haben.

Ich habe einen kleineren vServer (ubuntu 16.04, 2CPU, 2GB RAM) und darauf laufen renderd mit mod_tile für 5 Layer mit relativ wenigen Daten aus BaWü. Jetzt möchte ich die Daten zumindest einmal am Tag aktualisieren und bin mir nicht sicher, wie ich das am besten anstelle.
Momentan starte ich händisch alle paar Tage ein Script, das mir eine zweite DB erstellt, dort den Import macht und anschließend die alte db löscht und die neue umbenennt. Das könnte ich über ein cronjob laufen lassen. Nachteil ist dabei allerdings, dass ich eben immer genügend Speicherplatz für die doppelte DB haben muss. Wenn ich demnächst einen größeren Ausschnitt haben möchte, wird es eng. osmupdate scheint ja da eine gute Alternative zu sein.
Was ich aber noch nicht so richtig blicke, sind die Zeitstempel. Wie muss mein Aufruf aussehen?

Wenn ich das Wiki richtig interpretiere, würde er so den Zeitstempel der letzten Updatedatei nehmen. Für den ersten Aufruf muss ich aber dann manuell den Aufruf mit Zeitstempel machen. Kann ich da auch die osm.pbf angeben oder wie verhindere ich da eine Lücke? Woher bekommst du den Zeitstempel in deinem Script?

osmupdate -v --day --base-url=http://download.geofabrik.de/europe/germany/baden-wuerttemberg-updates/ /osm/planet/bw_update.osc /osm/planet/bw_update_new.osc
rm -f /osm/planet/bw_update.osc
mv /osm/planet/bw_update_new.osc /osm/planet/bw_update.osc
osm2pgsql --append ...

Das tunen von pgsql steckt bei mir auch erst in den Kinderschuhen. Muss mich da mal einarbeiten, wie ich mit scripten dann das ganze Zeug ändern kann. Zumindest grob ist die .conf mal an RAM und CPUs angepasst.

dooley · 2016-11-25 12:34:05

Hallo Walter,

da haben wir ja fast gleichzeitig gepostet. Sorry hatte ich nicht gesehen.

wambacher wrote:

... vergessen, das Autovacuum auszuschalten. Ist jetzt im Script drin, mal schauen, obs was bringt.
Ich hoffe, du meintest ANzuschalten

Hu? Wieso einschalten? Das Autovakuum kann doch dazwischenfunken, während der Update läuft?

kleiner trick - obwohl der keine performance bringt:
mach die Änderungen für postgresql mit "ALTER SYSTEM ...".
dann stehen alle mods in postgresql.auto.conf und das postgresql.conf bleibt unangetastet. Ist einfach übersichtlicher.

Stimmt, danke. Hatte ich nicht mehr auf dem Schirm, damit sieht man viel besser, was man selbst geändert hat.

Du kannst Vacuum/Analyze auch jederzeit online machen. So ist das garantiert vor dem nächsten Import durch.
Zumindest den Analyze würde ich schnell machen.

Das läuft täglich um 3 Uhr nachts und braucht ca. 30 Minuten, ist also mit Sicherheit vor der nächsten Änderung an den Tabellen (Start ca. 7:00) fertig. Diese Tabellen werden veränderlich nur von osm2pgsql angelangt, danach wird nur lesend drauf zugegriffen.

Wie gesagt, ich probier das mal morgen mit dem parallelen Indizieren.

Gruß, Frank

dooley · 2016-11-25 12:47:28

ngt wrote:

Woher bekommst du den Zeitstempel in deinem Script?

Also ich erstelle mir erstmalig eine Datei mit dem Zeitstempel mittels osmconvert:

osmconvert --out-timestamp ${UPDATE_FILE} > osm_timestamp

Diesen Zeitstempel verwende ich dann in meinem Update-Script für die folgenden Durchläufe so:

TIMESTAMP_FILE="./osm_timestamp"
ZEITSTEMPEL=$(cat ${TIMESTAMP_FILE})
UPDATE_FILE="./germany_updates.osc"
...
# Updates holen
osmupdate -v \
  ${ZEITSTEMPEL} \
  --keep-tempfiles \
  --base-url=download.geofabrik.de/europe/germany-updates/ \
  ${UPDATE_FILE}
...
[Verarbeitung]
...
osmconvert --out-timestamp ${UPDATE_FILE} > ${TIMESTAMP_FILE}

Damit hab ich den immer aktuell.

Announcement

#1 2016-11-24 22:38:42

osm2pgsql append

#2 2016-11-25 00:02:32

Re: osm2pgsql append

#3 2016-11-25 00:07:59

Re: osm2pgsql append

#4 2016-11-25 00:13:08

Re: osm2pgsql append

#5 2016-11-25 00:57:22

Re: osm2pgsql append

#6 2016-11-25 11:41:04

Re: osm2pgsql append

#7 2016-11-25 12:07:12

Re: osm2pgsql append

#8 2016-11-25 12:13:36

Re: osm2pgsql append

#9 2016-11-25 12:34:05

Re: osm2pgsql append

#10 2016-11-25 12:47:28

Re: osm2pgsql append

Board footer