This is a static archive of our old OpenStreetMap Help Site. Please post any new questions and answers at community.osm.org.

Tuning Postgresql

Hi guys,

Id' like to import the France extract in my postgresql database with osm2pgsql. With the actual settings, it's really slow... but I saw that it's possible to tune Postgresql in order to speed up the process. But, I know almost nothing about hardware...

How would you tune my postgresql depending on my hard configuration (shared_buffers, work_mem, etc) ?. I'm using Ubuntu 12.04 server on a virtual machine, 4G of RAM, 100 GB HD and 4 cores.

Thanks! Lucas

nominatim postgresql osm2pgsql

asked 18 Jul '13, 11:42

Kalu06
140●7●8●15
accept rate: 0%

edited 18 Jul '13, 12:49

You have tagged this with nominatim, can we assume that your import uses osm2pgsql?

(18 Jul '13, 12:19) SK53 ♦

Yes. Sorry, I forgot to mention it.

(18 Jul '13, 12:39) Kalu06

2 Answers:

Tuning postgres is an art. For starters see the osm wiki, the postgres wiki, or the postgres doc. Some of the most important things you should try :

raise shared_buffers to 30-40% of available ram
raise work_mem until you stop seeing log_temp_file warnings in the logs, unless it gets so high that you'd run out of memory
raise maintenance_work_mem to twice that
raise checkpoint_segments a bit and checkpoint_completion_target to about 0.95
make sure you have the latest version of postgres (9.2 at the time of writing) and linux kernel
run postgres on the host instead of a virtual machine
give postgres an SSD drive and as much memory as you can afford (ideally enough to store the whole db, plus a bit to spare)
try formating the partition as XFS instead of EXT4; avoid btrfs for postres for now

You'll need to experiment a bit. Don't change all the settings at once, just change a few and measure the effect. Use a small (but not trivially smal) data extract (for example Auvergne instead of the whole of France) to iterate faster.

answered 18 Jul '13, 12:52

Vincent de P... ♦
17.3k●18●152●249
accept rate: 19%

Thank you very much for this comprehensive answer. I'm sure it wil be very helpful. I'm going to try that right away.

(18 Jul '13, 12:56) Kalu06

With only 4GB of ram and a single HDD, importing France will likely always be slow. In case this is an option, adding an SSD to put your database on, or increasing your ram to at least 8Gb or better 16Gb (and setting the cache parameter in osm2pgsql correctly) is probably the only way to really speed things up substantially.

(18 Jul '13, 21:39) apmon

0	As a first attempt to tune Postgres there's also a tool called pgtune: https://github.com/gregs1104/pgtune answered 20 Jul '13, 01:19 Geonick 106●2●3●8 accept rate: 0%