PostgresSQL 9.5.21 very slow to connect and perform basic queries
Hello. I'm investigating an issue on a PostgresSql 9.5.21 installation that becomes unusable in an intermittent way. Simple queries like "select now();" could take 20s. commits take 2s. and all gets fixed after an engine restart. I look into the pg logs and no signs of errors. and checkpoints are always timed. The machine is well provisioned, load isn't too high, and cpu io wait is under 1%. any suggestions on what I should check more? Thanks in advance. -- Bruno da Silva
Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
On Thu, Jul 21, 2022 at 02:37:35PM -0400, bruno da silva wrote: > I'm investigating an issue on a PostgresSql 9.5.21 installation that > becomes unusable in an intermittent way. Simple queries like "select > now();" could take 20s. commits take 2s. and all gets fixed after an engine > restart. > > I look into the pg logs and no signs of errors. and checkpoints are > always timed. The machine is well provisioned, load isn't too high, and cpu > io wait is under 1%. > > any suggestions on what I should check more? What OS/version is it ? What GUCs have you changed ? Is it a new issue ? https://wiki.postgresql.org/wiki/Slow_Query_Questions Operating system+version What OS / version ? At least for linux, you can get the distribution by running: tail /etc/*release GUC Settings What database configuration settings have you changed? What are their values? (These are things like "shared_buffers", "work_mem", "enable_seq_scan", "effective_io_concurrency", "effective_cache_size", etc). See Server Configuration for a useful query that will show all of your non-default database settings, in an easier to read format than posting pieces of your postgresql.conf file. -- Justin
Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
Thanks for the quick response. OS/version: CentOS release 6.9 (Final) Hardware(non dedicated to the db, other services and app run the same server): Xeon(R) CPU E5-2690 v4 @ 2.60GHz - 56 cores - 504 GB RAM logicaldrive 1 (1.5 TB, RAID 1, OK) physicaldrive 1I:3:1 (port 1I:box 3:bay 1, Solid State SAS, 1600.3 GB, OK) physicaldrive 1I:3:2 (port 1I:box 3:bay 2, Solid State SAS, 1600.3 GB, OK) GUC Settings: auto_explain.log_analyze 0 auto_explain.log_min_duration 1000 auto_explain.log_nested_statements 0 auto_explain.log_verbose 0 autovacuum_analyze_scale_factor0.1 autovacuum_analyze_threshold50 autovacuum_freeze_max_age2 autovacuum_max_workers 3 autovacuum_multixact_freeze_max_age 4 autovacuum_naptime 60 autovacuum_vacuum_cost_delay 2 autovacuum_vacuum_cost_limit 100 autovacuum_vacuum_scale_factor 0.1 autovacuum_vacuum_threshold 50 autovacuum_work_mem -1 checkpoint_timeout2700 effective_cache_size 4194304 enable_seqscan 0 log_autovacuum_min_duration250 log_checkpoints 1 log_connections 1 log_file_mode 600 log_lock_waits 1 log_min_duration_statement1000 log_rotation_age 1440 log_truncate_on_rotation 1 maintenance_work_mem262144 max_connections300 max_replication_slots 10 max_wal_senders 10 max_wal_size 1280 max_worker_processes15 min_wal_size 5 pg_stat_statements.max 1 standard_conforming_strings 1 track_commit_timestamp 1 wal_receiver_timeout 0 wal_sender_timeout 0 work_mem 8192 On Thu, Jul 21, 2022 at 3:33 PM Justin Pryzby wrote: > On Thu, Jul 21, 2022 at 02:37:35PM -0400, bruno da silva wrote: > > I'm investigating an issue on a PostgresSql 9.5.21 installation that > > becomes unusable in an intermittent way. Simple queries like "select > > now();" could take 20s. commits take 2s. and all gets fixed after an > engine > > restart. > > > > I look into the pg logs and no signs of errors. and checkpoints are > > always timed. The machine is well provisioned, load isn't too high, and > cpu > > io wait is under 1%. > > > > any suggestions on what I should check more? > > What OS/version is it ? > > What GUCs have you changed ? > > Is it a new issue ? > > https://wiki.postgresql.org/wiki/Slow_Query_Questions > > Operating system+version > What OS / version ? At least for linux, you can get the distribution by > running: tail /etc/*release > > GUC Settings > What database configuration settings have you changed? What are their > values? (These are things like "shared_buffers", "work_mem", > "enable_seq_scan", "effective_io_concurrency", "effective_cache_size", > etc). See Server Configuration for a useful query that will show all of > your non-default database settings, in an easier to read format than > posting pieces of your postgresql.conf file. > > -- > Justin > -- Bruno da Silva
Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
The issue started a month ago. On Thu, Jul 21, 2022 at 3:59 PM bruno da silva wrote: > Thanks for the quick response. > > OS/version: CentOS release 6.9 (Final) > > Hardware(non dedicated to the db, other services and app run the same > server): > > Xeon(R) CPU E5-2690 v4 @ 2.60GHz - 56 cores - 504 GB RAM > > logicaldrive 1 (1.5 TB, RAID 1, OK) > physicaldrive 1I:3:1 (port 1I:box 3:bay 1, Solid State SAS, 1600.3 GB, OK) > physicaldrive 1I:3:2 (port 1I:box 3:bay 2, Solid State SAS, 1600.3 GB, OK) > > > GUC Settings: > auto_explain.log_analyze 0 > auto_explain.log_min_duration 1000 > auto_explain.log_nested_statements 0 > auto_explain.log_verbose 0 > autovacuum_analyze_scale_factor0.1 > autovacuum_analyze_threshold50 > autovacuum_freeze_max_age2 > autovacuum_max_workers 3 > autovacuum_multixact_freeze_max_age 4 > autovacuum_naptime 60 > autovacuum_vacuum_cost_delay 2 > autovacuum_vacuum_cost_limit 100 > autovacuum_vacuum_scale_factor 0.1 > autovacuum_vacuum_threshold 50 > autovacuum_work_mem -1 > checkpoint_timeout2700 > effective_cache_size 4194304 > enable_seqscan 0 > log_autovacuum_min_duration250 > log_checkpoints 1 > log_connections 1 > log_file_mode 600 > log_lock_waits 1 > log_min_duration_statement1000 > log_rotation_age 1440 > log_truncate_on_rotation 1 > maintenance_work_mem262144 > max_connections300 > max_replication_slots 10 > max_wal_senders 10 > max_wal_size 1280 > max_worker_processes15 > min_wal_size 5 > pg_stat_statements.max 1 > standard_conforming_strings 1 > track_commit_timestamp 1 > wal_receiver_timeout 0 > wal_sender_timeout 0 > work_mem 8192 > > On Thu, Jul 21, 2022 at 3:33 PM Justin Pryzby > wrote: > >> On Thu, Jul 21, 2022 at 02:37:35PM -0400, bruno da silva wrote: >> > I'm investigating an issue on a PostgresSql 9.5.21 installation that >> > becomes unusable in an intermittent way. Simple queries like "select >> > now();" could take 20s. commits take 2s. and all gets fixed after an >> engine >> > restart. >> > >> > I look into the pg logs and no signs of errors. and checkpoints are >> > always timed. The machine is well provisioned, load isn't too high, and >> cpu >> > io wait is under 1%. >> > >> > any suggestions on what I should check more? >> >> What OS/version is it ? >> >> What GUCs have you changed ? >> >> Is it a new issue ? >> >> https://wiki.postgresql.org/wiki/Slow_Query_Questions >> >> Operating system+version >> What OS / version ? At least for linux, you can get the distribution by >> running: tail /etc/*release >> >> GUC Settings >> What database configuration settings have you changed? What are their >> values? (These are things like "shared_buffers", "work_mem", >> "enable_seq_scan", "effective_io_concurrency", "effective_cache_size", >> etc). See Server Configuration for a useful query that will show all of >> your non-default database settings, in an easier to read format than >> posting pieces of your postgresql.conf file. >> >> -- >> Justin >> > > > -- > Bruno da Silva > -- Bruno da Silva
Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
On 2022-07-21 Th 14:37, bruno da silva wrote: > Hello. > > I'm investigating an issue on a PostgresSql 9.5.21 installation that > becomes unusable in an intermittent way. Simple queries like "select > now();" could take 20s. commits take 2s. and all gets fixed after an > engine restart. > > I look into the pg logs and no signs of errors. and checkpoints are > always timed. The machine is well provisioned, load isn't too high, > and cpu io wait is under 1%. > > any suggestions on what I should check more? > > > 9.5 has been out of support for nearly 2 years. You should be looking to upgrade. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
On Thu, Jul 21, 2022 at 03:59:30PM -0400, bruno da silva wrote:
> OS/version: CentOS release 6.9 (Final)
How are these set ?
tail /sys/kernel/mm/ksm/run
/sys/kernel/mm/transparent_hugepage/{defrag,enabled,khugepaged/defrag}
/proc/sys/vm/zone_reclaim_mode
I suspect you may be suffering from issues with transparent huge pages.
I suggest to disable KSM and THP, or upgrade to a newer OS.
I've written before about these:
https://www.postgresql.org/message-id/[email protected]
https://www.postgresql.org/message-id/[email protected]
https://www.postgresql.org/message-id/[email protected]
https://www.postgresql.org/message-id/[email protected]
https://www.postgresql.org/message-id/[email protected]
https://www.postgresql.org/message-id/[email protected]
On Thu, Jul 21, 2022 at 04:01:10PM -0400, bruno da silva wrote:
> The issue started a month ago.
Ok .. but how long has the DB been running under this environment ?
--
Justin
Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
Thanks, I will check it out.
On Thu, Jul 21, 2022 at 4:21 PM Justin Pryzby wrote:
> On Thu, Jul 21, 2022 at 03:59:30PM -0400, bruno da silva wrote:
> > OS/version: CentOS release 6.9 (Final)
>
> How are these set ?
>
> tail /sys/kernel/mm/ksm/run
> /sys/kernel/mm/transparent_hugepage/{defrag,enabled,khugepaged/defrag}
> /proc/sys/vm/zone_reclaim_mode
>
> I suspect you may be suffering from issues with transparent huge pages.
>
> I suggest to disable KSM and THP, or upgrade to a newer OS.
>
> I've written before about these:
> https://www.postgresql.org/message-id/[email protected]
> https://www.postgresql.org/message-id/[email protected]
> https://www.postgresql.org/message-id/[email protected]
> https://www.postgresql.org/message-id/[email protected]
> https://www.postgresql.org/message-id/[email protected]
> https://www.postgresql.org/message-id/[email protected]
>
> On Thu, Jul 21, 2022 at 04:01:10PM -0400, bruno da silva wrote:
> > The issue started a month ago.
>
> Ok .. but how long has the DB been running under this environment ?
>
> --
> Justin
>
--
Bruno da Silva
Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
It has been running at least since 2018.
I got that from the tail
=> /sys/kernel/mm/ksm/run <==
0
==> /sys/kernel/mm/transparent_hugepage/defrag <==
[always] madvise never
==> /sys/kernel/mm/transparent_hugepage/enabled <==
always madvise [never]
==> /sys/kernel/mm/transparent_hugepage/khugepaged/defrag <==
[yes] no
==> /proc/sys/vm/zone_reclaim_mode <==
0
On Thu, Jul 21, 2022 at 4:32 PM bruno da silva wrote:
> Thanks, I will check it out.
>
> On Thu, Jul 21, 2022 at 4:21 PM Justin Pryzby
> wrote:
>
>> On Thu, Jul 21, 2022 at 03:59:30PM -0400, bruno da silva wrote:
>> > OS/version: CentOS release 6.9 (Final)
>>
>> How are these set ?
>>
>> tail /sys/kernel/mm/ksm/run
>> /sys/kernel/mm/transparent_hugepage/{defrag,enabled,khugepaged/defrag}
>> /proc/sys/vm/zone_reclaim_mode
>>
>> I suspect you may be suffering from issues with transparent huge pages.
>>
>> I suggest to disable KSM and THP, or upgrade to a newer OS.
>>
>> I've written before about these:
>> https://www.postgresql.org/message-id/[email protected]
>> https://www.postgresql.org/message-id/[email protected]
>> https://www.postgresql.org/message-id/[email protected]
>> https://www.postgresql.org/message-id/[email protected]
>> https://www.postgresql.org/message-id/[email protected]
>> https://www.postgresql.org/message-id/[email protected]
>>
>> On Thu, Jul 21, 2022 at 04:01:10PM -0400, bruno da silva wrote:
>> > The issue started a month ago.
>>
>> Ok .. but how long has the DB been running under this environment ?
>>
>> --
>> Justin
>>
>
>
> --
> Bruno da Silva
>
--
Bruno da Silva
