I have already imported the OSM Planet to the database. And I want to update the database regularly. The update command I use is osm2pgsql --append -slim with 6GB cache. However, the update process is still too slow.

Hardware: 32G memory, 1.7TB SSD.

Database config:

shared_buffers = 2GB
work_mem = 1000MB
effective_io_concurrency = 2
fsync = on
synchronous_commit = off
full_page_writes = off
checkpoint_segments = 100
checkpoint_completion_target = 0.9
autovacuum = on
effective_cache_size = 4GB
maintenance_work_mem = 2GB
max_worker_process = 12

On average it took 1 hour to process 6 hours worth of new OSM updates. (Screenshot is the log printed by the script openstreetmap-tiles-update-expire. Inside the script, it calls osm2pgsql --append -slim) alt text

I remember when bulk loading OSM Planet to the database, a lot of articles suggest modifying postgresql.conf

Such as:

fsync = off (import only!)

autovacuum = off (import only!)

Since the database is keep updating, should I change the postgresql config settings back to these import settings to increase performance?

asked 02 Dec '20, 20:38

Yingcai's gravatar image

accept rate: 0%

edited 04 Dec '20, 14:41


Could you indicate what hardware this is running on and your original import parameters?

(03 Dec '20, 06:47) SimonPoole ♦

(for completeness - that screenshot looks like output from openstreetmap-tiles-update-expire)

(03 Dec '20, 22:02) SomeoneElse ♦

Thanks @simonPoole and @SomeoneElse. The post has been updated.

(04 Dec '20, 14:42) Yingcai

I am not sure what screen shot is about, this doesn't look like osm2pgsql output!?

There is some advice on how to tune your database for osm2pgsql in the osm2pgsql manual at https://osm2pgsql.org/doc/manual.html#tuning-the-postgresql-server .

permanent link

answered 03 Dec '20, 09:27

Jochen%20Topf's gravatar image

Jochen Topf
accept rate: 31%

The screenshot is the log printed by the script openstreetmap-tiles-update-expire (mod_tile https://github.com/SomeoneElseOSM/mod_tile/tree/switch2osm ). Inside the script, it calls osm2pgsql --append -slim

(04 Dec '20, 19:53) Yingcai
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 02 Dec '20, 20:38

question was seen: 222 times

last updated: 04 Dec '20, 19:53

powered by OSQA