Low cache hit ratio

Поиск
Список
Период
Сортировка
От Lucas
Тема Low cache hit ratio
Дата
Msg-id B5H6NmZ9UI_bsBogtc-tkaAkxFGcBuqFW6Su60HvbRHHjkh6rBb9BxKp0SRxAE69WzxrCMRBfQKKSe9MjR7AcVQpXbiBUPs9eVcT5mRpgQY=@sud0.nz
обсуждение исходный текст
Ответы Re: Low cache hit ratio
Список pgsql-general
Дерево обсуждения
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.


Вложения

В списке pgsql-general по дате отправления: