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

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
memorypostgresql-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(at)gmail(dot)com> 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