Re: UPDATEDs slowing SELECTs in a fully cached database

On 07/12/2011 02:51 PM, Kevin Grittner wrote:
> I ran x a bunch of times to get a baseline, then y once, then x a
> bunch more times. The results were a bit surprising:
>
> cir=> \timing
> Timing is on.
> cir=> execute x('000000000000001','2011-6-30');
> count
> -------
> 3456
> (1 row)
>
> Time: 9.823 ms
> cir=> execute x('000000000000001','2011-6-30');
> count
> -------
> 3456
> (1 row)
>
> Time: 8.481 ms
> cir=> execute x('000000000000001','2011-6-30');
> count
> -------
> 3456
> (1 row)
>
> Time: 14.054 ms
> cir=> execute x('000000000000001','2011-6-30');
> count
> -------
> 3456
> (1 row)
>
> Time: 10.169 ms
> cir=> execute y('000000000000001', '000000000000001','2011-6-30');
> UPDATE 3456
> Time: 404.244 ms
> cir=> execute x('000000000000001','2011-6-30');
> count
> -------
> 3456
> (1 row)
>
> Time: 128.643 ms
> cir=> execute x('000000000000001','2011-6-30');
> count
> -------
> 3456
> (1 row)
>
> Time: 2.657 ms
> cir=> execute x('000000000000001','2011-6-30');
> count
> -------
> 3456
> (1 row)
>
> Time: 5.883 ms
> cir=> execute x('000000000000001','2011-6-30');
> count
> -------
> 3456
> (1 row)
>
> Time: 2.645 ms
> cir=> execute x('000000000000001','2011-6-30');
> count
> -------
> 3456
> (1 row)
>
> Time: 2.753 ms
> cir=> execute x('000000000000001','2011-6-30');
> count
> -------
> 3456
> (1 row)
>
> Time: 2.253 ms
>
Interesting. When you did you test, did you also find WAL write activity
when running x the first time after y?
(It's very hard to catch in only a single query, though).

> Running the update made the next SELECT slow, then it was much
> *faster*. My best guess is that the data landed in a more
> concentrated set of pages after the update, and once autovacuum
> kicked in and cleaned things up it was able to get to that set of
> data faster.
>
>> autovacuum | off
> Well, certainly not while under modification without running
> autovacuum. That's disabling an integral part of what keeps
> performance up.
Oh, it's just switched off for testing, so that I can control when
vacuum runs and make sure that it's not
skewing the results while I am measuring something.
In a real database I would probably err on vacuuming more than less.

For a fully cached database I would probably want to switch off HOT
pruning and compaction (which from what we see
is done synchronously with the select) and leave it up to the
asynchronous auto vacuum to do that. But maybe I am
still not quite understanding the performance implications.