Re: Need help identifying a periodic performance issue. - Mailing list pgsql-performance
From | Robert Creager |
---|---|
Subject | Re: Need help identifying a periodic performance issue. |
Date | |
Msg-id | BB5DB2FB-0F65-4723-A122-A477B2B006B6@spectralogic.com Whole thread Raw |
In response to | Need help identifying a periodic performance issue. (Robert Creager <robertc@spectralogic.com>) |
List | pgsql-performance |
On Nov 17, 2021, at 10:51 AM, Robert Creager <robertc@spectralogic.com> wrote:On Nov 15, 2021, at 10:50 PM, Thomas Munro <thomas.munro@gmail.com> wrote:This message originated outside your organization.
On Tue, Nov 16, 2021 at 5:43 PM Robert Creager <robertc@spectralogic.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.
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
pgsql-performance by date: