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

[bruno da silva <brunogiovs@xxxxxxxxx>]

Hello.

After more investigation, we found that pgss_query_texts.stat of a size of 2.2GB. and this deployment has a 32bit pg.

and this errors:

postgresql-2022-07-12-20:07:15.log.gz:[2022-07-14 11:17:06.713 EDT] 207.89.58.230(46964) {62c87db0.8eb2} xxxx LOG:  out of memory
postgresql-2022-07-12-20:07:15.log.gz:[2022-07-14 11:17:06.713 EDT] 207.89.58.230(46964) {62c87db0.8eb2} xxxx DETAIL:  Could not allocate enough memory to read pg_stat_statement file "pg_stat_tmp/pgss_query_texts.stat".

So, my question is if pgss_query_texts.stat increases in size gradually due to too many distincts large sql statements could it cause an overall slowness on the engine? this slowness could cause simple statements to be super slow to return like
"select now()" taking 20s? 

Thanks in advance

Environment:

  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)

  PostgresSQL 9.5.21 32bit

 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 2:37 PM bruno da silva <brunogiovs@xxxxxxxxx> 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?

Thanks in advance.

--

Bruno da Silva

--

Bruno da Silva