The issue started a month ago. On Thu, Jul 21, 2022 at 3:59 PM bruno da silva <brunogi...@gmail.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 <pry...@telsasoft.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
