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