Low cache hit ratio

[Lucas <root@xxxxxxx>]

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.

Attachment: publickey - root@sud0.nz - 0xC5E964A1.asc
Description: application/pgp-keys

Attachment: signature.asc
Description: OpenPGP digital signature