Re: Low cache hit ratio

> On Jul 29, 2021, at 3:09 AM, Lucas <root(at)sud0(dot)nz> wrote:
>
> 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.

Hi Lucas,
Have you tried the pg_buffercache extension? It gives you some visibility into what’s in the cache so you can understand what’s staying resident and not leaving room for other things. I wrote a view atop pg_buffercache that I use for this purpose. It’s pasted below; I hope you find it helpful. My only caveat is that I run this under Postgres 11. I *think* I’ve used it under Postgres 9.6 but I’m not sure. It definitely hasn’t been tested on 9.2.

Hope this helps,
Philip

/* A view of pg_buffercache which shows what's in the Postgres cache.
Access to pg_buffercache requires membership in the group pg_monitor.

It's OK to query this ad hoc, but don't query it aggressively (e.g. in a polling loop). The
Postgres doc says --

> When the pg_buffercache view is accessed, internal buffer manager locks are taken for
> long enough to copy all the buffer state data that the view will display. This ensures
> that the view produces a consistent set of results, while not blocking normal buffer
> activity longer than necessary. Nonetheless there could be some impact on database
> performance if this view is read often.

https://www.postgresql.org/docs/11/pgbuffercache.html

*/

CREATE OR REPLACE VIEW
vw_postgres_cache
AS
SELECT
c.relname,
sum(usagecount) AS usage_count,
/* count(*) is the number of buffers in use. Each buffer is 8Kb. (8Kb is the page size
a.k.a. block size configured at compile time, confirm in psql with the command
`show block_size`).

I cast the count to float to introduce a float into calculations that are otherwise all int
which would produce a result rounded to the nearest int.
*/
-- cache_% shows the portion of the cache that this entity occupies
((count(*)::float / pg_settings.setting::int) * 100)::numeric(3, 1) AS "cache_%",
-- entity_% shows the portion of this entity that's in cache
-- pg_table_size() is in bytes, multiply buffer count by (8 * 1024) so units match
(((count(*)::float * 8192) / pg_table_size(c.relname::text)) * 100)::numeric(4,1)
AS "entity_%",
(count(*)::float * 8 / (1024 * 1024))::numeric(20, 1) AS Gb,
(count(*)::float * 8 / 1024 )::numeric(20, 1) AS Mb
FROM
pg_buffercache b
CROSS JOIN pg_settings
INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid)
AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
WHERE
pg_settings.name = 'shared_buffers'
-- If this is run on a system where shared_buffers is expressed in something other than 8kB
-- blocks, it will calculate incorrect kB/Mb/Gb values. Asserting the value of the unit here
-- ensures no results at all will be returned in that case.
AND pg_settings.unit = '8kB'
GROUP BY
c.relname, pg_settings.setting::int
HAVING
-- Only include entries that occupy at least 0.1% of the cache
((count(*)::float / pg_settings.setting::int) * 100) >= 0.1
ORDER BY 6 DESC
;