Re: UPDATEDs slowing SELECTs in a fully cached database

lars <lhofhansl(at)yahoo(dot)com> wrote:

> vacuum analyze;

I tried this out on a 16 core, 64 GB machine. It was a replication
target for a few dozen source databases into a couple 2 TB reporting
databases, and had some light testing going on, but it was only at
about 50% capacity, so that shouldn't throw this off by *too* much,
I hope. Since our data is long-lived enough to worry about
transaction ID freezing issues, I always follow a bulk load with
VACUUM FREEZE ANALYZE; so I did that here. I also just threw this
into the 2 TB database without changing our configuration. Among
other things, that means that autovacuum was on.

> prepare x as select count(*) from test where tenant = $1 and
> created_date = $2;
> prepare y as update test set created_by = $1 where tenant = $2 and
> created_date = $3;
>
> execute y('000000000000001', '000000000000001','2011-6-30');
> execute x('000000000000001','2011-6-30');

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

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.

> On the face of it, though, this looks like Postgres would not be
> that useful as database that resides (mostly) in the cache.

> autovacuum | off

Well, certainly not while under modification without running
autovacuum. That's disabling an integral part of what keeps
performance up. There are very few, if any, situations where
running PostgreSQL in production without autovacuum makes any sense,
and benchmarks which disable it don't give a very accurate picture
of typical performance. Now, if you're looking to artificially
create a worst-case scenario, then it makes sense, but I'm not clear
on the point of it.

I do understand the impulse, though. When we first started using
PostgreSQL there were certain very small tables which were updated
very frequently which got slow when autovacuum kicked in. We made
autovacuum less aggressive, and found that things go worse! Se we
went the other way and made autovacuum much more aggressive than the
defaults, and everything was fine.

-Kevin