Hi,
Application team is reporting performance is slow.
While looking at the log, I have the following information from the maintenance log.
Viewing Log: error/postgresql.log.2023-07-10-14 (5.9 kB):-
****************************************************
2023-07-10 14:00:01 UTC::@:[355]:LOG: checkpoint complete: wrote 426 buffers (0.1%); 0 WAL file(s) added, 0 removed, 1 recycled; write=42.847 s, sync=0.002 s, total=42.854 s; sync files=12, longest=0.002 s, average=0.001 s; distance=64874 kB, estimate=66875 kB
2023-07-10 14:04:19 UTC::@:[355]:LOG: checkpoint starting: time
2023-07-10 14:08:49 UTC::@:[355]:LOG: checkpoint complete: wrote 9197 buffers (1.9%); 0 WAL file(s) added, 0 removed, 2 recycled; write=269.779 s, sync=0.003 s, total=269.789 s; sync files=16, longest=0.003 s, average=0.001 s; distance=83868 kB, estimate=83868 kB
2023-07-10 14:09:19 UTC::@:[355]:LOG: checkpoint starting: time
2023-07-10 14:10:21 UTC::@:[355]:LOG: checkpoint complete: wrote 622 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=62.562 s, sync=0.004 s, total=62.569 s; sync files=26, longest=0.004 s, average=0.001 s; distance=48633 kB, estimate=80345 kB
2023-07-10 14:13:11 UTC:10.66.238.11(58986):postgres@bronxdb1:[27434]:ERROR: relation "bronxdev.tek_test_details_wafer" does not exist at character 15
2023-07-10 14:13:11 UTC:10.66.238.11(58986):postgres@bronxdb1:[27434]:STATEMENT: select * from bronxdb1.bronxdev.tek_test_details_wafer
2023-07-10 14:14:19 UTC::@:[355]:LOG: checkpoint starting: time
2023-07-10 14:15:38 UTC::@:[355]:LOG: checkpoint complete: wrote 784 buffers (0.2%); 0 WAL file(s) added, 0 removed, 1 recycled; write=78.655 s, sync=0.002 s, total=78.662 s; sync files=20, longest=0.002 s, average=0.001 s; distance=66114 kB, estimate=78922 kB
2023-07-10 14:19:19 UTC::@:[355]:LOG: checkpoint starting: time
2023-07-10 14:19:58 UTC::@:[355]:LOG: checkpoint complete: wrote 385 buffers (0.1%); 0 WAL file(s) added, 0 removed, 1 recycled; write=38.625 s, sync=0.002 s, total=38.632 s; sync files=15, longest=0.002 s, average=0.001 s; distance=63006 kB, estimate=77330 kB
2023-07-10 14:20:52 UTC:10.66.209.82(49945):postgres@bronxdb1:[27821]:ERROR: syntax error at or near "[" at character 17
2023-07-10 14:20:52 UTC:10.66.209.82(49945):postgres@bronxdb1:[27821]:STATEMENT: select distinct [source]
from bronx.errors
fetch first 100 rows only
2023-07-10 14:21:57 UTC:10.66.238.11(58986):postgres@bronxdb1:[27434]:ERROR: relation "bronx.weekly_operations_review_wafer" does not exist at character 15
2023-07-10 14:21:57 UTC:10.66.238.11(58986):postgres@bronxdb1:[27434]:STATEMENT: select * from bronxdb1.bronx.weekly_operations_review_wafer
LIMIT 10;
2023-07-10 14:24:19 UTC::@:[355]:LOG: checkpoint starting: time
I think, we need to manage the check points.
Any help on this would be appreciated.
I am providing current checkpoint configuration.
RDS / Parameter groups / default.postgres14 / default.postgres14:-
******************************************************************
Name
Values
Allowed values
Modifiable
Source
Apply type
Data type
Description
checkpoint_completion_target 0.9 0-1 true system dynamic float Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
checkpoint_flush_after 0-256 true engine-default dynamic integer (8kB) Number of pages after which previously performed writes are flushed to disk.
checkpoint_timeout 30-3600 true engine-default dynamic integer (s) Sets the maximum time between automatic WAL checkpoints.
checkpoint_warning 0-2147483647 true engine-default dynamic integer (s) Enables warnings if checkpoint segments are filled more frequently than this.
log_checkpoints 1 0, 1 true engine-default dynamic boolean Logs each checkpoint.
Recent events
Can you please help in making the changes suitably or point me to the documentation?
Thanks,
Sarwar
Hello Sarwar,
I don't think that your performance problem is related to checkpoint configuration. Checkpoints were triggered by time according to the log section you shared, which is good.
If I were you, I would focus on slow queries first and collect more information from your application team. Also, could you please share a little more back story about your performance issue?
You can start with setting log_min_duration_statement to a relevant value for your setup/application.
Best regards.
Samed YILDIRIM
On Mon, 10 Jul 2023 at 19:20, M Sarwar <sarwarmd02@xxxxxxxxxxx> wrote:
