On Nov 17, 2021, at 10:51 AM, Robert Creager <robe...@spectralogic.com<mailto:robe...@spectralogic.com>> wrote:
On Nov 15, 2021, at 10:50 PM, Thomas Munro <thomas.mu...@gmail.com<mailto:thomas.mu...@gmail.com>> wrote: This message originated outside your organization. On Tue, Nov 16, 2021 at 5:43 PM Robert Creager <robe...@spectralogic.com<mailto:robe...@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. 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
