[Tile-serving] [openstreetmap/osm2pgsql] How to update version 4.x database? (#762)

[Hartmut Holzgraefe]

On 09.06.2017 03:06, Klaus Tockloth wrote:

> My estimation is that the systems IO performance (PostgreSQL) is at it's
> limits and the harddisk based system is not sufficient to achieve the
> aim to update the europe data within 4 hours.
> 
> Secondary questions:
> 
>   * What is your assessment?
>   * What is the expected impacted on a SSD based system?

I'm running a full planet database, and can confirm that keeping that up
to date on a pure HDD system was next to impossible.

Currently I'm running it on a machine with both HDDs (2TB) and SSDs
(480GB), with only the flat nodes file and database indexes on SSD,
actual table data still on HDD.

This works well enough for now, but as I also have a copy of the
WayMarkedTrails database on the same server, purely on HDD, the HDDs
are already pretty well loaded again, and at times the WayMarked diff
import falls a bit behind, but so far it still catches up again as on
average the diff import is still able to apply diffs twice as fast
as they come in.

So in general:

* if you can -> put everything on SSD

* if that's not an option -> try to at least have the indexes on SSD
  (e.g. using "osm2pgsql --tablespace-index=...")

All of the above refers to "full planet" experience, with "europe only"
load and requirements are a bit lower, but not that much as Europe
currently accounts for about 1/3 of total planet size. So you're
still playing in the same order of magnitude ...

-- 
hartmut