Re: Report checkpoint progress with pg_stat_progress_checkpoint (was: Report checkpoint progress in server logs)

Hi,

On 7/28/22 11:38 AM, Nitin Jadhav wrote:
>>> To understand the performance effects of the above, I have taken the
>>> average of five checkpoints with the patch and without the patch in my
>>> environment. Here are the results.
>>> With patch: 269.65 s
>>> Without patch: 269.60 s
>>
>> Those look like timed checkpoints - if the checkpoints are sleeping a
>> part of the time, you're not going to see any potential overhead.
>
> Yes. The above data is collected from timed checkpoints.
>
> create table t1(a int);
> insert into t1 select * from generate_series(1,10000000);
>
> I generated a lot of data by using the above queries which would in
> turn trigger the checkpoint (wal).
> ---
>
>> To see whether this has an effect you'd have to make sure there's a
>> certain number of dirty buffers (e.g. by doing CREATE TABLE AS
>> some_query) and then do a manual checkpoint and time how long that
>> times.
>
> For this case I have generated data by using below queries.
>
> create table t1(a int);
> insert into t1 select * from generate_series(1,8000000);
>
> This does not trigger the checkpoint automatically. I have issued the
> CHECKPOINT manually and measured the performance by considering an
> average of 5 checkpoints. Here are the details.
>
> With patch: 2.457 s
> Without patch: 2.334 s
>
> Please share your thoughts.
>

v6 was not applying anymore, due to a change in
doc/src/sgml/ref/checkpoint.sgml done by b9eb0ff09e (Rename
pg_checkpointer predefined role to pg_checkpoint).

Please find attached a rebase in v7.

While working on this rebase, I also noticed that "pg_checkpointer" is
still mentioned in some translation files:
"
$ git grep pg_checkpointer
src/backend/po/de.po:msgid "must be superuser or have privileges of
pg_checkpointer to do CHECKPOINT"
src/backend/po/ja.po:msgid "must be superuser or have privileges of
pg_checkpointer to do CHECKPOINT"
src/backend/po/ja.po:msgstr
"CHECKPOINTを実行するにはスーパーユーザーであるか、またはpg_checkpointerの権限を持つ必要があります"
src/backend/po/sv.po:msgid "must be superuser or have privileges of
pg_checkpointer to do CHECKPOINT"
"

I'm not familiar with how the translation files are handled (looks like
they have their own set of commits, see 3c0bcdbc66 for example) but
wanted to mention that "pg_checkpointer" is still mentioned (even if
that may be expected as the last commit related to translation files
(aka 3c0bcdbc66) is older than the one that renamed pg_checkpointer to
pg_checkpoint (aka b9eb0ff09e)).

That said, back to this patch: I did not look closely but noticed that
the buffers_total reported by pg_stat_progress_checkpoint:

postgres=# select type,flags,start_lsn,phase,buffers_total,new_requests
from pg_stat_progress_checkpoint;
type | flags | start_lsn | phase
| buffers_total | new_requests
------------+-----------------------+------------+-----------------------+---------------+--------------
checkpoint | immediate force wait | 1/E6C523A8 | checkpointing
buffers | 1024275 | false
(1 row)

is a little bit different from what is logged once completed:

2022-11-04 08:18:50.806 UTC [3488442] LOG: checkpoint complete: wrote
1024278 buffers (97.7%);

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com