Re: Slow perormance

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.

https://www.postgresql.org/docs/15/runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT

Best regards.
Samed YILDIRIM

On Mon, 10 Jul 2023 at 19:20, M Sarwar <sarwarmd02(at)outlook(dot)com> wrote:

>
> 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(at)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(at)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(at)bronxdb1:[27821]:ERROR:
> syntax error at or near "[" at character 17
>
> 2023-07-10 14:20:52 UTC:10.66.209.82(49945):postgres(at)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(at)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(at)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
>
>
>
>
>