We have decent hardware: 8-core AMD, 16G RAM, SATA drives. This meant for a tile server with full planet coverage. The problem is that running hourly diffs takes constantly more than 1 hour (1.5 hours or so), so database is constantly lacking behind. The same machine is expected to be able to generate mapnik tiles also: we expect most load for small number (region), so caching should be quite effective.

Config is:

  • ubuntu 10.4 64-bit
  • postgres 9.0
  • shared_buffers = 5GB
  • work_mem = 512MB
  • maintenance_work_mem = 128MB
  • autovacuum = off

  • osm2pgsql is run as: osm2pgsql --append --slim -d gis -S /var/lib/postgresql/estonia.style -C16000 changes.osc.gz

  • sysctl.conf: kernel.shmmax = 8589934592 kernel.shmmni = 18589934592

Monitoring shows following suspicious stuff:

  • only one core is used for postgres UPDATE transactions, can it be fixed?
  • iostat does shows idle for data disk for most of the time
  • 1G of memory is used (based on htop)

asked 25 Apr '11, 13:12

JaakL's gravatar image

accept rate: 0%

osm2pgsql is run as: osm2pgsql --number-processes=8 --append --slim -d gis -S /var/lib/postgresql/estonia.style -C16000 changes.osc.gz

Shold make you use all cores....

permanent link

answered 14 Jan '12, 14:58

Magnus%20L's gravatar image

Magnus L
accept rate: 0%

I am surprised to hear that your disks are idle; the usual recommendation in these cases is to increase disk performace by using fast hard disks, putting your PostgreSQL data on a RAID-0 or RAID-1 device (combining multiple SATA drives with Linux Software RAID), or even getting an SSD.

If you can get hold of a 250GB+ SSD for a week, connect that, copy your tablespace over and see how long the diff application takes then. I should be surprised if it were more than 15 minutes for a hourly diff.

Also, make sure you have FASTUPDATE set to OFF on your indexes (they will automatically have been created like that if you did your initial import with an osm2pgsql SVN version checked out after 31st January 2011). See this thread on the dev mailing list for details.

You could try a slight increase in maintenance_work_mem (say, to 512 MB), and you could try fsync=off. Note that if you intend to continue with autovacuum=off you will have to make sure your tables are vacuumed some other way (e.g. nightly).

permanent link

answered 25 Apr '11, 16:08

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
accept rate: 23%

Disks should be fast enough - these are 15000 rpm disks in stripe. There must be something in configuration, but we don't figure out what. Just to confirm: hourly global updates should work with our hardware, also without SSD?

(26 Apr '11, 16:05) JaakL

Let's put it this way - I have never heard of anybody who complained about not being able to apply hourly updates on 15krpm striped disks. Maybe check your disk subsystem independently of Postgres and if everything looks fine, you'll probably have to investige Postgres more thoroughly - maybe increase logging and see where all this disk-idle-but-machine-busy time is spent...

(26 Apr '11, 16:11) Frederik Ramm ♦
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: 25 Apr '11, 13:12

question was seen: 5,092 times

last updated: 14 Jan '12, 14:58

powered by OSQA