0

We have two servers dedicated to PostgreSQL 11.3:

  1. master (128Mb RAM, 16 core CPU, SSD) - all write queries and 65% of read queries
  2. slave (64GB RAM, 12 core CPU, SSD) - 35% of read queries

For unknown reason, slave dies with giant CPU IO wait times (around 70%) as soon as we enable it, while master has 0.5% IO wait times and runs fine. iotop on slave is showing a lot of disk reads from postgresql processes with SELECT quries.

Configuration files:

master (works great)

max_connections = 100

# for replication 
wal_keep_segments = 100
wal_level = replica
max_wal_senders = 10
hot_standby = on
archive_mode = off

archive_command = 'cp %p /backups/pgsql-wal/%f'
max_standby_streaming_delay = 30s

# how much dedicated memory will be used by PostgreSQL for cache 
shared_buffers = 20GB

# estimate of the memory available for disk caching.
effective_cache_size = 80GB

# transaction commits very quickly because it will not wait for a WAL file to be flushed, but reliability is compromised
synchronous_commit = off

maintenance_work_mem = 2GB

checkpoint_completion_target = 0.7
checkpoint_timeout = 5min

wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 256MB
min_wal_size = 1GB
max_wal_size = 10GB
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_workers = 16

# vacuum
vacuum_cost_limit = 1000
autovacuum_vacuum_cost_delay = 10
autovacuum_max_workers = 6


# io performance
effective_io_concurrency = 2
random_page_cost = 1.5
seq_page_cost = 1.0

shared_preload_libraries = 'pg_stat_statements'

track_activity_query_size = 10000

slave (gets huge amount of disk read io)

max_connections = 100

# for replication 
wal_keep_segments = 100
wal_level = replica
max_wal_senders = 10
hot_standby = on
hot_standby_feedback = on
archive_mode = off
archive_command = 'cp %p /backups/pgsql-wal/%f'
max_standby_streaming_delay = 30s

# how much dedicated memory will be used by PostgreSQL for cache 
shared_buffers = 10GB

# estimate of the memory available for disk caching.
effective_cache_size = 40GB

# transaction commits very quickly because it will not wait for a WAL file to be flushed, but reliability is compromised
synchronous_commit = off

maintenance_work_mem = 2GB

checkpoint_completion_target = 0.7
checkpoint_timeout = 5min

wal_buffers = 16MB
default_statistics_target = 100
work_mem = 128MB
min_wal_size = 1GB
max_wal_size = 10GB
max_worker_processes = 16
max_parallel_workers_per_gather = 6
max_parallel_workers = 12


# io performance
effective_io_concurrency = 2
random_page_cost = 1.5
seq_page_cost = 1.0

shared_preload_libraries = 'pg_stat_statements'

# Increase the max size of the query strings Postgres records
track_activity_query_size = 10000

Any ideas what parameters are miconfigured on slave?

  • Could you give us a diff between the configuration files? What is the operating system on both systems? If Linux, which I/O scheduler are you using on each system? If Linux, did you disable "transparent hugepages" on both systems? Are the queries the same or similar on both systems? Can you isolate a single query that performs well on one and badly on the other system, or do you observe the behaviour only under load? – Laurenz Albe May 29 at 9:08
  • What do you mean "dies"? High read times are not lethal. Is replication staying caught up? Capture an EXPLAIN (ANALYZE, BUFFERS) of one of those SELECT queries, and do the same on master. – jjanes May 29 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.