Re: PG17 optimizations to vacuum

[Melanie Plageman]

On Sun, Sep 1, 2024 at 6:00 PM Peter Geoghegan  wrote:
>
> On Sun, Sep 1, 2024 at 5:44 PM Pavel Luzanov  wrote:
> > I see a perfectly working TID-store optimization.
> > With reduced maintenance_work_mem it used only one 'vacuuming indexes'
> > phase instead of 21 in v16.
> > But I also expected to see a reduction in the number of WAL records
> > and the total size of the WAL. Instead, WAL numbers have significantly
> > degraded.
> >
> > What am I doing wrong?

I'll investigate more tomorrow, but based on my initial investigation,
there appears to be some interaction related to how much of the
relation is in shared buffers after creating the table and updating
it. If you set shared_buffers sufficiently high and prewarm the table
after the update, master has fewer WAL records reported by vacuum
verbose.

- Melanie

Re: PG17 optimizations to vacuum

[Melanie Plageman]

On Mon, Sep 2, 2024 at 1:47 PM Peter Geoghegan  wrote:
>
> On Mon, Sep 2, 2024 at 1:29 PM Melanie Plageman
>  wrote:
> > I'll investigate more tomorrow, but based on my initial investigation,
> > there appears to be some interaction related to how much of the
> > relation is in shared buffers after creating the table and updating
> > it. If you set shared_buffers sufficiently high and prewarm the table
> > after the update, master has fewer WAL records reported by vacuum
> > verbose.
>
> Fewer of what specific kind of WAL record?

I would have expected to see no freeze records (since they no longer
exist) and the same number of prune records. However, the overall
number of records that I get for 16 and master is pretty similar. For
some reason I stopped being able to reproduce Pavel's case. I'll work
more on it tomorrow.

This is roughly what I get for records by vacuum. Note that I prefixed
VACUUM with BTREE on master to indicate those records are from index
vacuuming. By default the headesc routine for records emitted by index
vacuuming prints just VACUUM -- perhaps it would be better to prefix
it.

Note that these add up to almost the same thing. I don't know yet why
the number PRUNE_VACUUM_SCAN is different than PRUNE on 16.
PRUNE_VACUUM_SCAN and PRUNE + FREEZE_PAGE on 16 are similar. So, there
must be pages that don't have items being pruned which are being
frozen. I'll need to investigate further.

master
--
 PRUNE_ON_ACCESS | 6
 PRUNE_VACUUM_SCAN| 30974
 PRUNE_VACUUM_CLEANUP | 14162
 BTREE_VACUUM| 19127


16
--
 PRUNE   | 15504
 FREEZE_PAGE  | 13257
 VACUUM | 34527


- Melanie