[OSM-dev] Patch to osm2pgsql for faster updates

[Lennard]

On 25-1-2011 5:04, Erik Burrows wrote:
>
> In my 8.4 database, when I re-create the planet_osm_ways_nodes and
> planet_osm_rels_parts indexes with fastupdate=off, diff import times go
> from being progressively slower after a vacuum, to being consistent in
> terms of import time, accounting for the variation of diff size
> hour-to-hour.

Which would be expected, upon reading what fastupdate does. Slower 
overall, but consistent. The widely differing diff apply times with 
fastupdate=on would be due to both having to parse the regular index and 
the temporary one, and it possibly hitting autovacuum or the work_mem 
limit during diff processing, therefore triggering an immediate 
consolidation of the index.

> This is the same positive change in behavior I saw when setting
> fastupdate=off in 9.0.2.

So you imply that you saw the same gradual slowdown in 9.0.2 with 
fastupdate=on?

My experiences with 9.0.2 could very well be biased, since I switched 
from 8.4 to 9.0 the same time I started using more powerful hardware. 
A.k.a. a 'real server' with a 10k rpm scsi raid0 instead of a single 
disk sata desktop disk. :)

However, I've also switched our NL test server from 8.4 to 9.0.2 soon 
after, and that also feels a bit faster when processing diffs. That 
virtual hardware hasn't changed.

> My 8.4 db, on a SATA disk is now just better than 1:1 for applying
> hourly diffs, and my 9.0.2 db on SSD is about 4:1. (diff size to apply
> time)

With minutely diffs, even when processing an hours worth 
(maxInterval=3600), I think I never hit 1:1, even when on that SATA 
desktop disk I mentioned earlier. I could always catch up, meaning diffs 
would apply faster than the interval they represent.


For those interested to try fastupdate=off, the manual says you can do 
ALTER INDEX and then vacuum + rebuild the index. That saves you from 
having to do a reimport with osm2pgsql.

-- 
Lennard