Re: Perplexing, regular decline in performance

Hi,

On 2019-07-18 16:01:46 -0400, Hugh Ranalli wrote:
> I've been going by a couple of articles I found about interpreting
> pg_buffercache (
> https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers),
> and so far shared buffers look okay. Our database is 486 GB, with shared
> buffers set to 32 GB. The article suggests a query that can provide a
> guideline for what shared buffers should be:
> 
> SELECT
>     pg_size_pretty(count(*) * 8192) as ideal_shared_buffers
> FROM
>     pg_class c
> INNER JOIN
> pg_buffercache b ON b.relfilenode = c.relfilenode
> INNER JOIN
>     pg_database d ON (b.reldatabase = d.oid AND d.datname =
> current_database())
> WHERE
>     usagecount >= 3;

IMO that's not a meaningful way to determine the ideal size of shared
buffers. Except for the case where shared buffers is bigger than the
entire working set (not just the hot working set), it's going to give
you completely bogus results.

Pretty much by definition it cannot give you a shared buffers size
bigger than what it's currently set to, given that it starts with the
number of shared buffers.

And there's plenty scenarios where you'll commonly see many frequently
(but not most frequently) used buffers with a usagecount < 3 even =
0. If you e.g. have a shared_buffers size that's just a few megabytes
too small, you'll need to throw some buffers out of shared buffers -
that means the buffer replacement search will go through all shared
buffers and decrement the usagecount by one, until it finds a buffer
with a count of 0 (before it has decremented the count). Which means
it's extremely likely that there's moments where a substantial number of
frequently used buffers have a lowered usagecount (perhaps even 0).

Therefore, the above query will commonly give you a lower number than
shared buffers, if your working set size is *bigger* than shared memory.


I think you can assume that shared buffers is too big if a substantial
portion of buffers have relfilenode IS NOT NULL (i.e. are unused); at
least if you don't continually also DROP/TRUNCATE relations.

If there's a large fluctuation about which parts of buffercache has a
high usagecount, then that's a good indication that very frequently new
buffers are needed (because that lowers a good portion of buffers to
usagecount 0).

I've had decent success in the past getting insights with a query like:

SELECT
    ceil(bufferid/(nr_buffers/subdivisions::float))::int AS part,
    to_char(SUM((relfilenode IS NOT NULL)::int) / count(*)::float * 100, '999D99')  AS pct_used,
    to_char(AVG(usagecount), '9D9') AS avg_usagecount,
    to_char(SUM((usagecount=0)::int) / SUM((relfilenode IS NOT NULL)::int)::float8 * 100, '999D99') AS pct_0
FROM
    pg_buffercache,
    (SELECT 10) AS x(subdivisions),
    (SELECT setting::int nr_buffers FROM pg_settings WHERE name = 'shared_buffers') s
GROUP BY 1 ORDER BY 1;

which basically subdivides pg_buffercache's output into 10 parts (or use
as much as fit comfortable in one screen / terminal).

Here's e.g. the output of a benchmark (pgbench) running against a
database that's considerably smaller than shared memory (15GB database,
1.5GB shared_buffers):

┌──────┬──────────┬────────────────┬─────────┐
│ part │ pct_used │ avg_usagecount │  pct_0  │
├──────┼──────────┼────────────────┼─────────┤
│    1 │  100.00  │  1.0           │   42.75 │
│    2 │  100.00  │   .6           │   47.85 │
│    3 │  100.00  │   .6           │   47.25 │
│    4 │  100.00  │   .6           │   47.52 │
│    5 │  100.00  │   .6           │   47.18 │
│    6 │  100.00  │   .5           │   48.47 │
│    7 │  100.00  │   .5           │   49.00 │
│    8 │  100.00  │   .5           │   48.52 │
│    9 │  100.00  │   .5           │   49.27 │
│   10 │  100.00  │   .5           │   49.58 │
│   11 │   99.98  │   .6           │   46.88 │
│   12 │  100.00  │   .6           │   45.23 │
│   13 │  100.00  │   .6           │   45.03 │
│   14 │  100.00  │   .6           │   44.90 │
│   15 │  100.00  │   .6           │   46.08 │
│   16 │  100.00  │   .6           │   44.84 │
│   17 │  100.00  │   .6           │   45.88 │
│   18 │  100.00  │   .6           │   46.46 │
│   19 │  100.00  │   .6           │   46.64 │
│   20 │  100.00  │   .6           │   47.05 │
└──────┴──────────┴────────────────┴─────────┘

As you can see usagecounts are pretty low overall. That's because the
buffer replacement rate is so high, that the usagecount is very
frequently reduced to 0 (to get new buffers).

You can infer from that, that unless you add a lot of shared buffers,
you're not likely going to make a huge difference (but if you set it
16GB, it'd obviously look much better).


In contrast to that, here's pgbench running on a smaller database, that
nearly fits into shared buffers (2GB DB, 1.5GB shared_buffers):

┌──────┬──────────┬────────────────┬─────────┐
│ part │ pct_used │ avg_usagecount │  pct_0  │
├──────┼──────────┼────────────────┼─────────┤
│    1 │  100.00  │  3.9           │    1.45 │
│    2 │  100.00  │  3.8           │    1.34 │
│    3 │  100.00  │  3.8           │    1.69 │
│    4 │  100.00  │  3.7           │    1.96 │
│    5 │  100.00  │  3.7           │    2.01 │
│    6 │  100.00  │  3.6           │    2.23 │
│    7 │  100.00  │  3.5           │    2.60 │
│    8 │  100.00  │  3.5           │    2.27 │
│    9 │  100.00  │  3.4           │    2.82 │
│   10 │  100.00  │  3.3           │    2.92 │
│   11 │  100.00  │  3.2           │    3.43 │
│   12 │  100.00  │  3.1           │    3.41 │
│   13 │  100.00  │  3.7           │    1.91 │
│   14 │  100.00  │  4.0           │    1.09 │
│   15 │  100.00  │  3.9           │    1.39 │
│   16 │  100.00  │  4.0           │    1.22 │
│   17 │  100.00  │  4.1           │    1.16 │
│   18 │  100.00  │  4.0           │    1.19 │
│   19 │  100.00  │  4.0           │    1.29 │
│   20 │  100.00  │  4.0           │    1.42 │
└──────┴──────────┴────────────────┴─────────┘

As you can see, there's many fewer buffers that have a usagecount of 0 -
that's because the buffer replacement rate is much lower (as most
buffers are in shared buffers), and thus the usagecount has time to
"increase" regularly.

Here you can guess that even just increasing shared buffers slightly,
would increase the cache hit ratio substantially. E.g. the same
workload, but with shared_buffes increased to 1.6GB:
┌──────┬──────────┬────────────────┬─────────┐
│ part │ pct_used │ avg_usagecount │  pct_0  │
├──────┼──────────┼────────────────┼─────────┤
│    1 │  100.00  │  5.0           │     .00 │
│    2 │  100.00  │  5.0           │     .00 │
│    3 │  100.00  │  5.0           │     .00 │
│    4 │  100.00  │  5.0           │     .00 │
│    5 │  100.00  │  5.0           │     .00 │
│    6 │  100.00  │  5.0           │     .00 │
│    7 │  100.00  │  5.0           │     .00 │
│    8 │  100.00  │  5.0           │     .00 │
│    9 │  100.00  │  5.0           │     .00 │
│   10 │  100.00  │  5.0           │     .00 │
│   11 │  100.00  │  5.0           │     .00 │
│   12 │  100.00  │  5.0           │     .00 │
│   13 │  100.00  │  5.0           │     .00 │
│   14 │  100.00  │  5.0           │     .00 │
│   15 │  100.00  │  5.0           │     .00 │
│   16 │  100.00  │  5.0           │     .00 │
│   17 │  100.00  │  5.0           │     .00 │
│   18 │  100.00  │  5.0           │     .00 │
│   19 │   93.27  │  5.0           │     .00 │
│   20 │     .00  │ (null)         │ (null)  │
└──────┴──────────┴────────────────┴─────────┘


Now, in reality things are rarely quite this neat - pgbench has a
uniform access pattern, which isn't that common in the real world.


I also suggest to monitor how the buffer hit ratio develops over
time. E.g. by doing a query like

SELECT datname, (blks_hit - blks_read)::float/NULLIF(blks_hit, 0)::float FROM pg_stat_database;

although that's not perfect, because it gives you the ratio since the
last time the stats have been reset, making it hard to see more recent
changes.  So you either need to reset the stats, or just compute the
difference to what the values where when you wanted to start observing.

E.g.

DROP TABLE IF EXISTS pg_stat_database_snap;CREATE TEMPORARY TABLE pg_stat_database_snap AS SELECT * FROM
pg_stat_database;

SELECT datname,
    (blks_hit - blks_read)::float/NULLIF(blks_hit, 0)::float
FROM (
    SELECT datname,
        pd.blks_read - ps.blks_read AS blks_read,
        pd.blks_hit - ps.blks_hit AS blks_hit
    FROM pg_stat_database pd JOIN pg_stat_database_snap ps USING (datname) ) pd_diff;


Greetings,

Andres Freund