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.
Monitoring shows following suspicious stuff:
asked 25 Apr '11, 13:12
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....
answered 14 Jan '12, 14:58
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).
answered 25 Apr '11, 16:08
Frederik Ramm ♦