Hello,
I have recently deployed a new Slave (streaming replication) and have been monitoring its cache hit ratio.
At the moment, read-only queries are being sent to this slave but only 10% of the traffic.
The cache hit ratio is now at 82%. This database is around 1.4TB and is running on EC2 instances in AWS. PostgreSQL version is 9.2.24.
The instance is a r4.2xlarge, so it's got 8 vCPUs and 61GB of RAM. The postgresql.conf file can be seen below (will show only relevant parameters for the cache/performance):
hot_standby = on
random_page_cost = 1.1
max_connections = 500
shared_buffers = 15GB
statement_timeout = 0
work_mem = 31457kB
maintenance_work_mem = 2GB
wal_level = hot_standby
fsync = on
synchronous_commit = on
wal_buffers = 16MB
checkpoint_segments = 32
checkpoint_completion_target = 0.9
max_wal_senders = 20
wal_keep_segments = 1024
effective_cache_size = 45GB
logging_collector = on
autovacuum = on
log_autovacuum_min_duration = 1000
autovacuum_max_workers = 5
autovacuum_naptime = 40s
autovacuum_vacuum_threshold = 200
autovacuum_analyze_threshold = 150
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.005
deadlock_timeout = 2s
max_files_per_process = 4096
effective_io_concurrency = 200
hot_standby_feedback = on
max_standby_streaming_delay = 120s
default_statistics_target = 100
I have tried reducing the shared_buffers parameter from 15GB to 8GB, but the cache hit ratio went down to 60%.
Do you guys have any suggestions, on what I could try to get this cache more hits?
Thanks in advance!
---
Regards,
Lucas
This message is encrypted. Both the Public Key and the GPG encrypted message are included in this email so that you can verify its origin.
