Re: Need help identifying a periodic performance issue.

On Nov 17, 2021, at 10:51 AM, Robert Creager <robertc(at)spectralogic(dot)com<mailto:robertc(at)spectralogic(dot)com>> wrote:

On Nov 15, 2021, at 10:50 PM, Thomas Munro <thomas(dot)munro(at)gmail(dot)com<mailto:thomas(dot)munro(at)gmail(dot)com>> wrote:

This message originated outside your organization.

On Tue, Nov 16, 2021 at 5:43 PM Robert Creager <robertc(at)spectralogic(dot)com<mailto:robertc(at)spectralogic(dot)com>> wrote:
One CPU is pegged, the data has been sent over STDIN, so Postgres is not waiting for more, there are no other queries running using this select:

So PostgreSQL is eating 100% CPU, with no value shown in
wait_event_type, and small numbers of system calls are counted. In
that case, is there an interesting user stack that jumps out with a
profiler during the slowdown (or the kernel version, stack())?

sudo dtrace -n 'profile-99 /arg0/ { @[ustack()] = count(); } tick-10s
{ exit(0); }

Ok, here is the logs around a dtrace included. I have dtaces every 1m10s, and the two I looked at, the last entry, were the same.

Also, the wall settings are different on this machine, had changed them on the original email submittal to see if it was related.

tapesystem=# SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');
name | current_setting | source
---------------------------------+---------------------------------+--------------------
application_name | psql | client
autovacuum_analyze_scale_factor | 0.05 | configuration file
autovacuum_analyze_threshold | 5000 | configuration file
autovacuum_max_workers | 8 | configuration file
autovacuum_vacuum_cost_delay | 5ms | configuration file
autovacuum_vacuum_scale_factor | 0.1 | configuration file
autovacuum_vacuum_threshold | 5000 | configuration file
checkpoint_completion_target | 0.9 | configuration file
checkpoint_timeout | 15min | configuration file
client_encoding | UTF8 | client
DateStyle | ISO, MDY | configuration file
default_text_search_config | pg_catalog.english | configuration file
dynamic_shared_memory_type | posix | configuration file
effective_cache_size | 6546MB | configuration file
effective_io_concurrency | 200 | configuration file
full_page_writes | off | configuration file
hot_standby | off | configuration file
lc_messages | C | configuration file
lc_monetary | C | configuration file
lc_numeric | C | configuration file
lc_time | C | configuration file
listen_addresses | * | configuration file
log_autovacuum_min_duration | 1s | configuration file
log_checkpoints | on | configuration file
log_connections | on | configuration file
log_destination | syslog | configuration file
log_disconnections | on | configuration file
log_duration | off | configuration file
log_line_prefix | db=%d,user=%u,app=%a,client=%h | configuration file
log_lock_waits | on | configuration file
log_min_duration_sample | 500ms | configuration file
log_min_duration_statement | 1s | configuration file
log_statement_sample_rate | 0.01 | configuration file
log_temp_files | 0 | configuration file
log_timezone | UTC | configuration file
maintenance_work_mem | 1964MB | configuration file
max_connections | 250 | configuration file
max_parallel_workers_per_gather | 8 | configuration file
max_replication_slots | 0 | configuration file
max_stack_depth | 32MB | configuration file
max_wal_senders | 0 | configuration file
max_wal_size | 10GB | configuration file
max_worker_processes | 8 | configuration file
random_page_cost | 2 | configuration file
shared_buffers | 22064MB | configuration file
synchronous_commit | off | configuration file
temp_buffers | 654MB | configuration file
TimeZone | UTC | configuration file
track_activities | on | configuration file
track_counts | on | configuration file
update_process_title | off | configuration file
vacuum_cost_delay | 1ms | configuration file
wal_init_zero | off | configuration file
wal_level | minimal | configuration file
wal_recycle | off | configuration file
work_mem | 654MB | configuration file