Re: UPDATEDs slowing SELECTs in a fully cached database - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: UPDATEDs slowing SELECTs in a fully cached database
Date
Msg-id 4E1B05CC020000250003F1E6@gw.wicourts.gov
Whole thread Raw
In response to Re: UPDATEDs slowing SELECTs in a fully cached database  (lars <lhofhansl@yahoo.com>)
Responses Re: UPDATEDs slowing SELECTs in a fully cached database  (Merlin Moncure <mmoncure@gmail.com>)
List pgsql-performance
lars <lhofhansl@yahoo.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.

At some point you could see the overhead of autovacuum having some
impact on your SELECT statements, so you may need to experiment to
find the "sweet spot" for your load.

-Kevin

pgsql-performance by date:

Previous
From: lars
Date:
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
Next
From: "Kevin Grittner"
Date:
Subject: Re: Statistics and Multi-Column indexes