From: lars <lhofhansl@yahoo.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Ivan Voras <ivoras@freebsd.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
Date: Tue, 12 Jul 2011 15:36:20 -0700
Message-ID: <4E1CCC64.60700@yahoo.com> (raw)
In-Reply-To: <4E1C7B78020000250003F26E@gw.wicourts.gov>
References: <4E16479D.3050605@yahoo.com>
<4E1A0CBC.6090009@yahoo.com>
<4E1A31AB.9010201@postnewspapers.com.au>
<1310359653.80687.YahooMailRC@web121705.mail.ne1.yahoo.com>
<4E1AEDAB020000250003F1C3@gw.wicourts.gov>
<4E1B41EA.90104@yahoo.com>
<4E1B05CC020000250003F1E6@gw.wicourts.gov>
<CAHyXU0wPhZsQ_pMzfUWW0O=t_+2uwF-CJQNw-nuaxkjbi2zPEQ@mail.gmail.com>
<4E1B810D.4070404@yahoo.com>
<4E1B90AF.1020408@yahoo.com>
<ivho9v$6dp$1@dough.gmane.org>
<4E1C9A05.7060005@yahoo.com>
<4E1C5563020000250003F252@gw.wicourts.gov>
<4E1CA8E9.6090108@yahoo.com>
<4E1CB56E.7080801@yahoo.com>
<4E1C7B78020000250003F26E@gw.wicourts.gov>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lhofhansl@yahoo.com, Kevin.Grittner@wicourts.gov, ivoras@freebsd.org
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
In-Reply-To: <4E1CCC64.60700@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox