Re: Fix parallel vacuum buffer usage reporting

Hi!

> The same script was run, but using vacuum verbose analyze, and I
> saw the difference again in the fifth step:
> with your patch: buffer usage: 32312 hits, 607 misses, 1566 dirtied
> master: buffer usage: 32346 hits, 573 misses, 1360 dirtied
>
> Isn't there a chance for the checkpointer to run during this time?
> That could make the conditions between the two runs slightly different
> and explain the change in buffer report.
>
> [0]
> https://github.com/postgres/postgres/blob/8a1b31e6e59631807a08a4e9465134c343bbdf5e/src/backend/access/heap/vacuumlazy.c#L2826-L2831
>
> Looking at the script, you won't trigger the problem.

Thank you for the link I accounted it in my next experiments.

I repeated the test without processing checkpoints with a single index,
and the number of pages in the buffer used almost matched:

master branch: buffer usage: 32315 hits, 606 misses, 4486 dirtied

with applied patch v4 version: buffer usage: 32315 hits, 606 misses,
4489 dirtied

I think you are right - the problem was interfering with the checkpoint
process, by the way I checked the first version patch. To cut a long
story short, everything is fine now with one index.

Just in case, I'll explain: I considered this case because your patch
could have impact influenced it too.

On 25.04.2024 10:17, Anthonin Bonnefoy wrote:
>
> On Wed, Apr 24, 2024 at 4:01 PM Alena Rybakina
> <lena(dot)ribackina(at)yandex(dot)ru> wrote:
>
> I tested the main postgres branch with and without your fix using
> a script that was written by me. It consists of five scenarios and
> I made a comparison in the logs between the original version of
> the master branch and the master branch with your patch:
>
>  Hi! Thanks for the tests.
>
> I have attached a test file (vacuum_check_logs.sql)
>
> The reporting issue will only happen if there's a parallel index
> vacuum and it will only happen if there's at least 2 indexes [0]. You
> will need to create an additional index.

Speaking of the problem, I added another index and repeated the test and
found a significant difference:

* I found it when I commited the transaction (3):

master: 2964hits, 0misses, 0dirtied

with applied patch v4 version: buffer usage: 33013hits, 0misses, 3dirtied

* When I deleted all the data from the table and later started vacuum
verbose again (4):

master: buffer usage: 51486hits, 0misses, 0dirtied

with applied patch v4 version:buffer usage: 77924hits, 0misses, 0dirtied

* when I inserted 1 million data into the table and updated it (5):

master:buffer usage: 27904hits, 5021misses, 1777dirtied

with applied patch v4 version:buffer usage: 41051hits, 9973misses,
2564dirtied

As I see, the number of pages is significantly more than it was in the
master branch and ,frankly, I couldn't fully figure out if it was a
mistake or not.

I attached a test script (vacuum_checks_logs.sql) with two indexes and
no checkpoints, I also attached log files: the first one (vacuum_test)
is the result of testing on the master branch, the second file with your
applied patch (vacuum_test_v4).

--
Regards,
Alena Rybakina
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company