Re: Combine Prune and Freeze records emitted by vacuum - Mailing list pgsql-hackers

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@iki.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