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
Вложения
В списке pgsql-hackers по дате отправления: