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 _______________________________________________ dev mailing list [email protected] http://lists.openstreetmap.org/listinfo/dev
