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
