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.
publickey - root@sud0.nz - 0xC5E964A1.asc
Description: application/pgp-keys
signature.asc
Description: OpenPGP digital signature
