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.ribackina@yandex.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