Обсуждение: Slow perormance
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
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
Sent: Monday, July 10, 2023 12:28 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Slow perormance
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