Re: Slow perormance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx> 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@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



 


[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux