Re: Low cache hit ratio

[Philip Semanchuk <philip@xxxxxxxxxxxxxxxxxxxxx>]

> On Jul 29, 2021, at 3:09 AM, Lucas <root@xxxxxxx> 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
;