Re: UPDATEDs slowing SELECTs in a fully cached database

On Mon, Jul 11, 2011 at 2:16 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> lars <lhofhansl(at)yahoo(dot)com> 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