Re: Perplexing, regular decline in performance

On Tue, 25 Jun 2019 at 12:23, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> It's possible that the "administrative" queries are using up lots of your
> shared_buffers, which are (also/more) needed by the customer-facing
> queries. I
> would install pg_buffercache to investigate. Or, just pause the admin
> queries
> and see if that the issue goes away during that interval ?
>
> SELECT 1.0*COUNT(1)/sum(count(1))OVER(), COUNT(1),
> COUNT(nullif(isdirty,'f')), datname, COALESCE(c.relname,
> b.relfilenode::text), d.relname TOAST,
> 1.0*COUNT(nullif(isdirty,'f'))/count(1) dirtyfrac, avg(usagecount) FROM
> pg_buffercache b JOIN pg_database db ON b.reldatabase=db.oid LEFT JOIN
> pg_class c ON b.relfilenode=pg_relation_filenode(c.oid) LEFT JOIN pg_class
> d ON c.oid=d.reltoastrelid GROUP BY 4,5,6 ORDER BY 1 DESC LIMIT 9;
>

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;

This comes out to 25 GB, and even dropping the usage count to 1 only raises
it to 30 GB. I realise this is only a guideline, and I may bump it to 36
GB, to give a bit more space.

I did run some further queries to look at usage (based on the same
article), and most of the tables that have very high usage on all the
buffered data are 100% buffered, so, if I understand it correctly, there
should be little churn there. The others seem to have sufficient
less-accessed space to make room for data that they need to buffer:

relname | buffered | buffers_percent | percent_of_relation
-------------------------+----------+-----------------+---------------------
position | 8301 MB | 25.3 | 99.2
stat_position_click | 7359 MB | 22.5 | 76.5
url | 2309 MB | 7.0 | 100.0
pg_toast_19788 | 1954 MB | 6.0 | 49.3
(harvested_job)
stat_sponsored_position | 1585 MB | 4.8 | 92.3
location | 927 MB | 2.8 | 98.7
pg_toast_20174 | 866 MB | 2.6 | 0.3
(page)
pg_toast_20257 | 678 MB | 2.1 | 92.9
(position_index)
harvested_job | 656 MB | 2.0 | 100.0
stat_employer_click | 605 MB | 1.8 | 100.0

usagecount >= 5
relname | pg_size_pretty
-------------------------+----------------
harvested_job | 655 MB
location | 924 MB
pg_toast_19788 | 502 MB
pg_toast_20174 | 215 MB
pg_toast_20257 | 677 MB
position | 8203 MB
stat_employer_click | 605 MB
stat_position_click | 79 MB
stat_sponsored_position | 304 kB
url | 2307 MB

usagecount >= 3
relname | pg_size_pretty
-------------------------+----------------
harvested_job | 656 MB
location | 927 MB
pg_toast_19788 | 1809 MB
pg_toast_20174 | 589 MB
pg_toast_20257 | 679 MB
position | 8258 MB
stat_employer_click | 605 MB
stat_position_click | 716 MB
stat_sponsored_position | 2608 kB
url | 2309 MB

usagecount >= 1
relname | pg_size_pretty
-------------------------+----------------
harvested_job | 656 MB
location | 928 MB
pg_toast_19788 | 3439 MB
pg_toast_20174 | 842 MB
pg_toast_20257 | 680 MB
position | 8344 MB
stat_employer_click | 605 MB
stat_position_click | 4557 MB
stat_sponsored_position | 86 MB
url | 2309 MB

If I'm misreading this, please let me know. I know people also asked about
query plans and schema, which I'm going to look at next; I've just been
knocking off one thing at at time.

Thanks,
Hugh