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.
This is the same positive change in behavior I saw when setting fastupdate=off in 9.0.2. 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) -Erik > 8.3 didn't have the fastupdate function at all, so I would imagine it is > possible that the fastupdate introduction in 8.4 was the problem, since > it is on by default in 8.4 and 9.0. > > I still have my 8.4 database, so I started it up and am re-creating the > GIN indexes with fastupdate=off, to see if that fixes the problem in 8.4 > also. > > It's on my SATA drive, so it will take about a day to complete. I'll let > you know on Monday. > > -Erik > > On Sun, 2011-01-23 at 18:13 +0100, Lennard wrote: > > On 23-1-2011 17:44, Tom Hughes wrote: > > > On 23/01/11 16:24, Erik Burrows wrote: > > > > > >> I was having great difficulty with applying the daily diffs to my > > >> PostgreSQL database (8.4 and 9.0). I would get good performance right > > [...] > > > I wonder if in fact this is the performance problem that we saw using an > > > 8.4 database. The general consensus was that it was somehow related to > > > the GIN indexes but we never managed to work out exactly what it was. > > > > Erik mentions encountering the same issue in 9.0. So either > > fastupdate=on is more intelligent in 9.0, or we're not on the right > > track to solve our 8.4 slowdown troubles. > > > > > > _______________________________________________ > dev mailing list > [email protected] > http://lists.openstreetmap.org/listinfo/dev > _______________________________________________ dev mailing list [email protected] http://lists.openstreetmap.org/listinfo/dev
