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

asked 18 Jul '13, 11:42

Kalu06's gravatar image

Kalu06
1407815
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

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.

permanent link

answered 18 Jul '13, 12:52

Vincent%20de%20Phily's gravatar image

Vincent de P... ♦
17.1k17148245
accept rate: 19%

1

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
2

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

As a first attempt to tune Postgres there's also a tool called pgtune: https://github.com/gregs1104/pgtune

permanent link

answered 20 Jul '13, 01:19

Geonick's gravatar image

Geonick
106238
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

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:

×617
×230
×155

question asked: 18 Jul '13, 11:42

question was seen: 11,396 times

last updated: 20 Jul '13, 01:19

powered by OSQA