Re: Combine Prune and Freeze records emitted by vacuum

On Tue, Apr 02, 2024 at 01:24:27PM -0400, Melanie Plageman wrote:
> On Tue, Apr 2, 2024 at 9:11 AM Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
> >
> > On 01/04/2024 20:22, Melanie Plageman wrote:
> > > Review for 0003-0006 (I didn't have any new thoughts on 0002). I know
> > > you didn't modify them much/at all, but I noticed some things in my code
> > > that could be better.
> >
> > Ok, here's what I have now. I made a lot of small comment changes here
> > and there, and some minor local refactorings, but nothing major. I lost
> > track of all the individual changes I'm afraid, so I'm afraid you'll
> > have to just diff against the previous version if you want to see what's
> > changed. I hope I didn't break anything.
> >
> > I'm pretty happy with this now. I will skim through it one more time
> > later today or tomorrow, and commit. Please review once more if you have
> > a chance.
>
> Thanks!
>
> 0001 looks good. Attached are some comment updates and such on top of
> 0001 and 0002.
>
> I started some performance testing of 0002 but haven't finished yet. I
> wanted to provide my other review first.

I tried to do some performance tests of just on-access HOT pruning with
the patches in this thread applied. I'm not sure if I succeeded in being
targeted enough to have usable results.

Off-list Andres gave me some suggestions of how to improve my test case
and setup and this is what I ended up doing:

----------------------------------------
On-access pruning during a SELECT query:
----------------------------------------

# Make a table with a single not NULL column of a small datatype to fit
# as many tuples as possible on the page so each page we prune exercises
# those loops in heap_page_prune_and_freeze() and heap_prune_chain() as
# much as possible

psql -c "create table small(col smallint not null)"

# Insert data that is the same except for ~1 row per page with a different value

for i in $(seq 1000)
do
psql -c "INSERT INTO small VALUES(2);" -c "INSERT INTO small SELECT 1 FROM (SELECT generate_series(1,220));"
done

# COPY this data to a file
psql -c "COPY small TO '/tmp/small.data';"

# Start postgres bound to a single CPU core

# Run the following script with pgbench

# Make the table unlogged table so we don't see the effects of WAL writes in
# results
#
# Make sure autovacuum doesn't run on the table
drop table if exists small;
create unlogged table small(col smallint not null) with (autovacuum_enabled = false);
copy small from '/tmp/small.data';
update small set col = 9 where col = 2;
select * from small where col = 0;

pgbench -n -f query.sql -t 100 -M prepared -r

# (I made sure that HOT pruning was actually happening for the SELECT
# query before running this with pgbench)

# There seemed to be no meaningful difference for this example with the
# patches:

on current master:
statement latencies in milliseconds and failures:
12.387 0 drop table if exists small;
1.914 0 create unlogged table small(col smallint not null) with (autovacuum_enabled = false);
100.152 0 copy small from '/tmp/small.data';
49.480 0 update small set col = 9 where col = 2;
46.835 0 select * from small where col = 0;

with the patches applied:
statement latencies in milliseconds and failures:
13.281 0 drop table if exists small;
1.952 0 create unlogged table small(col smallint not null) with (autovacuum_enabled = false);
99.418 0 copy small from '/tmp/small.data';
47.397 0 update small set col = 9 where col = 2;
46.887 0 select * from small where col = 0;

--------------------------------
On-access pruning during UPDATE:
--------------------------------

# The idea is to test a query which would be calling the new
# heap_prune_record_unchanged_lp_normal() function a lot

# I made the same table but filled entirely with the same value

psql -c "create table small(col smallint not null)" \
-c "INSERT INTO small SELECT 1 FROM (SELECT generate_series(1, 221000));"

# COPY this data to a file
psql -c "COPY small TO '/tmp/small_univalue.data';"

# Start postgres bound to a single CPU core

# Run the following script with pgbench

# Pick a low fillfactor so we have room for the HOT updates
drop table if exists small;
create unlogged table small(col smallint not null) with (autovacuum_enabled = false, fillfactor=25);
copy small from '/tmp/small_univalue.data';
update small set col = 3;
update small set col = 4;
update small set col = 5;
update small set col = 6;

pgbench -n -f update.sql -t 20 -M prepared -r

# There again seems to be no meaningful difference with the patches
# applied

on master:
statement latencies in milliseconds and failures:
19.880 0 drop table if exists small;
2.099 0 create unlogged table small(col smallint not null) with (autovacuum_enabled = false, fillfactor=25);
130.793 0 copy small from '/tmp/small_univalue.data';
377.707 0 update small set col = 3;
417.644 0 update small set col = 4;
483.974 0 update small set col = 5;
422.956 0 update small set col = 6;

with patches applied:
statement latencies in milliseconds and failures:
19.995 0 drop table if exists small;
2.034 0 create unlogged table small(col smallint not null) with (autovacuum_enabled = false, fillfactor=25);
124.270 0 copy small from '/tmp/small_univalue.data';
375.327 0 update small set col = 3;
419.336 0 update small set col = 4;
483.750 0 update small set col = 5;
420.451 0 update small set col = 6;

- Melanie