Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries

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

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux