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 <brunogiovs(at)gmail(dot)com> 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_factor 0.1
> autovacuum_analyze_threshold 50
> autovacuum_freeze_max_age 200000000
> autovacuum_max_workers 3
> autovacuum_multixact_freeze_max_age 400000000
> 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_timeout 2700
> effective_cache_size 4194304
> enable_seqscan 0
> log_autovacuum_min_duration 250
> log_checkpoints 1
> log_connections 1
> log_file_mode 600
> log_lock_waits 1
> log_min_duration_statement 1000
> log_rotation_age 1440
> log_truncate_on_rotation 1
> maintenance_work_mem 262144
> max_connections 300
> max_replication_slots 10
> max_wal_senders 10
> max_wal_size 1280
> max_worker_processes 15
> min_wal_size 5
> pg_stat_statements.max 10000
> 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 <pryzby(at)telsasoft(dot)com>
> 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