From: | Melanie Plageman <melanieplageman(at)gmail(dot)com> |
---|---|
To: | Peter Geoghegan <pg(at)bowt(dot)ie> |
Cc: | Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> |
Subject: | Re: PG17 optimizations to vacuum |
Date: | 2024-09-02 19:23:13 |
Message-ID: | CAAKRu_YF9UeFnfsgDiFybAss2YQtmEHU+xjngqwmwwyQjCDvxQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Sep 2, 2024 at 1:47 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>
> On Mon, Sep 2, 2024 at 1:29 PM Melanie Plageman
> <melanieplageman(at)gmail(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2024-09-02 19:42:00 | Re: PG17 optimizations to vacuum |
Previous Message | Peter Geoghegan | 2024-09-02 17:47:28 | Re: PG17 optimizations to vacuum |