Re: core system is getting unresponsive because over 300 cpu load

Victor Yegorov wrote
> Looks like `sdg` and `sdm` are the ones used most.
> Can you describe what's on those devices? Do you have WAL and DB sitting
> together?
> Where DB log files are stored?

it's multipath with the same LUN for PGDATA and pg_log, but separate one for
xlogs and archives.

mpatha dm-4 IBM ,2145

size=2.0T features='0' hwhandler='0' wp=rw

|-+- policy='round-robin 0' prio=0 status=active

| |- 7:0:1:2 sdg 8:96 active undef running

| `- 8:0:1:2 sdm 8:192 active undef running

`-+- policy='round-robin 0' prio=0 status=enabled

|- 7:0:0:2 sdd 8:48 active undef running

`- 8:0:0:2 sdj 8:144 active undef running

Victor Yegorov wrote
> Can you, please, provide the output of this query (linked from the article
> mentioned):
> https://gist.github.com/lesovsky/4587d70f169739c01d4525027c087d14

00:26:51.226024|120 days
03:05:37.987175|0.6|7.99|300.63|0.46|12673500.4|162.00|0.34|0.51|0.37|1.22|26.721|27.7|41.8|30.6|4.47|34.27|--------------------------------------|21532|124|6510377185|9920323|449049896|677360078|2321057|495798075|0

Victor Yegorov wrote
> And also this query:
> SELECT name,version,source FROM pg_settings WHERE source NOT IN
> ('default','override');

application_name | client | psql

archive_command | configuration file | <deleted>

archive_mode | configuration file | on

autovacuum | configuration file | on

autovacuum_max_workers | configuration file | 10

checkpoint_completion_target | configuration file | 0.9

checkpoint_timeout | configuration file | 480

client_encoding | client | UTF8

DateStyle | configuration file | ISO, MDY

default_statistics_target | configuration file | 350

default_text_search_config | configuration file | pg_catalog.english

effective_cache_size | configuration file | 52428800

enable_indexscan | configuration file | on

huge_pages | configuration file | on

lc_messages | configuration file | en_US.UTF-8

lc_monetary | configuration file | en_US.UTF-8

lc_numeric | configuration file | en_US.UTF-8

lc_time | configuration file | en_US.UTF-8

listen_addresses | configuration file | *

log_autovacuum_min_duration | configuration file | 0

log_checkpoints | configuration file | on

log_connections | configuration file | on

log_destination | configuration file | stderr

log_directory | configuration file | pg_log

log_disconnections | configuration file | on

log_duration | configuration file | off

log_filename | configuration file | postgresql-%a.log

log_line_prefix | configuration file | %t [%p]: [%l-1]
user=%u,db=%d

log_lock_waits | configuration file | on

log_min_duration_statement | configuration file | 0

log_rotation_age | configuration file | 1440

log_rotation_size | configuration file | 0

log_temp_files | configuration file | 0

log_timezone | configuration file | Poland

log_truncate_on_rotation | configuration file | on

logging_collector | configuration file | on

maintenance_work_mem | configuration file | 2097152

max_connections | configuration file | 1000

max_stack_depth | environment variable | 2048

max_wal_senders | configuration file | 10

max_wal_size | configuration file | 640

random_page_cost | configuration file | 1

shared_buffers | configuration file | 2097152

temp_buffers | configuration file | 16384

TimeZone | configuration file | Poland

track_functions | configuration file | all

track_io_timing | configuration file | off

wal_buffers | configuration file | 2048

wal_keep_segments | configuration file | 150

wal_level | configuration file | hot_standby

work_mem | configuration file | 393216+

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html