On Mon, Jul 11, 2011 at 2:16 PM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote: > lars <lhofhansl@xxxxxxxxx> wrote: > >> Stopping the UPDATEs, waiting for any CHECKPOINTs to finish, >> and then running the SELECTs indeed shows a similar slowdown. >> >> Interestingly I see very heavy WAL traffic while executing the >> SELECTs. (So I was confused as to what caused the WAL traffic). > > Hint bit changes aren't logged, so if it was that you would be > seeing writes to the heap, but not to the WAL. Clean-up of dead > tuples is logged -- this is probably the result of pruning dead > tuples. You could probably reduce the impact on your SELECT > statements at least a little by making autovacuum more aggressive. yeah. In fact, I'd like to disable autovacuum completely just to confirm this. In particular I'd like to know if that removes wal traffic when only selects are going on. Another way to check is to throw some queries to pg_stat_activity during your select period and see if any non-select activity (like autovacum vacuum). Basically I'm suspicious there is more to this story. hint bit flusing causing i/o during SELECT is a typical complaint (especially on systems with underperformant i/o), but I'm suspicious if that's really the problem here. Since you are on a virtualized platform, I can't help but wonder if you are running into some bottleneck that you wouldn't see on native hardware. What's iowait during the slow period? merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
