Re: pg_stat_bgwriter - Mailing list pgsql-performance

On Mon, Oct 14, 2019 at 01:12:43PM -0700, dangal wrote:
>Thanks a lot, always helping
>I attached a snapshot that I take every 12 hours of the pg_stat_bgwriter
>
>select now,buffers_checkpoint,buffers_clean, buffers_backend from
>pg_stat_bgwriter_snapshot;

Please show us the deltas, i.e. subtract the preceding value (using a
window function, or something). FWIW 12 hours may be a bit too coarse,
but it's better than nothing.

>              now              | buffers_checkpoint | buffers_clean |
>buffers_backend
>-------------------------------+--------------------+---------------+-----------------
> 2019-10-07 12:00:01.312067-03 |             288343 |       1182944 |
>520101
> 2019-10-08 00:00:02.034129-03 |             475323 |       3890772 |
>975454
> 2019-10-08 12:00:01.500756-03 |             616154 |       4774924 |
>1205261
> 2019-10-09 00:00:01.520329-03 |             784840 |       7377771 |
>1601278
> 2019-10-09 12:00:01.388113-03 |            1149560 |       8395288 |
>2456249
> 2019-10-10 00:00:01.841054-03 |            1335747 |      11023014 |
>2824740
> 2019-10-10 12:00:01.354555-03 |            1486963 |      11919462 |
>2995211
> 2019-10-11 00:00:01.519538-03 |            1649066 |      14400593 |
>3360700
> 2019-10-11 12:00:01.468203-03 |            1979781 |      15332086 |
>4167663
> 2019-10-12 00:00:01.343714-03 |            2161116 |      17791871 |
>4525957
> 2019-10-12 12:00:01.991429-03 |            2323194 |      18324723 |
>5139418
> 2019-10-13 00:00:01.251191-03 |            2453939 |      19059149 |
>5306894
> 2019-10-13 12:00:01.677379-03 |            2782606 |      19391676 |
>5878981
> 2019-10-14 00:00:01.824249-03 |            2966021 |      19915346 |
>6040316
> 2019-10-14 12:00:01.869126-03 |            3117659 |      20675018 |
>6184214
>
>I tell you that we have a server with 24 gb of ram and 6gb of shared_buffers
>When you tell me that maybe I am running too low of shared_buffers, the
>query I run to see what is happening is the following:

The question is how that compared to database size, and size of the
active set (fraction of the database accessed by the application /
queries).

I suggest you also track & compute shared_buffers cache hit ratio.

>The first 10 are insert, update and an autovaccum
>
>select calls, shared_blks_hit, shared_blks_read, shared_blks_dirtied
>  from pg_stat_statements
>  where shared_blks_dirtied> 0 order by shared_blks_dirtied desc
>  limit 10
>
>
>  calls   | shared_blks_hit | shared_blks_read | shared_blks_dirtied
>-----------+-----------------+------------------+---------------------
>  41526844 |      1524091324 |         74477743 |            40568348
>  22707516 |      1317743612 |         33153916 |            28106071
>    517309 |       539285911 |         24583841 |            24408950
>        23 |        23135504 |        187638126 |            15301103
>  11287105 |       383864219 |         18369813 |            13879956
>   2247661 |       275357344 |          9252598 |             6084363
>  13070036 |       244904154 |          5557321 |             5871613
>  54158879 |       324425993 |          5054200 |             4676472
>  24955177 |       125421833 |          5775788 |             4517367
> 142807488 |     14401507751 |         81965894 |             2661358
>(10 filas)
>

Unfortunately, this has the same issue as the data you shared in the
first message - it's a snapshot with data accumulated since the database
was created. It's unclear whether the workload changed over time etc.
But I guess you can use this to identify queries producing the most
dirty buffers and maybe see if you can optimize that somehow (e.g. by
removing unnecessary indexes or something).

>Another query
>
>SELECT pg_size_pretty(count(*) * 8192) as buffered,
>       round(100.0 * count(*) /
>             (SELECT setting FROM pg_settings WHERE name = 'shared_buffers')
>             ::integer,
>             1) AS buffers_percent,
>       round(100.0 * count(*) * 8192 / pg_table_size(c.oid), 1) AS
>percent_of_relation
>  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())
> GROUP BY c.oid, c.relname
> ORDER BY 3 DESC LIMIT 10;
>
>buffered    buffers_percent      percent_of_relation
>3938 MB;       64.1;            53.2
>479 MB;        7.8;                21.3
>261 MB;        4.3;                99.3
>163 MB;        2.6;                0.1
>153 MB;        2.5;                6.7
>87 MB;        1.4;                1.2
>82 MB;        1.3;                81.6
>65 MB;        1.1;                100.0
>64 MB;        1.0;                0.1
>53 MB;        0.9;                73.5
>

It's generally a good idea to explain what a query is supposed to do,
instead of just leaving the users to figure that out. In any case, this
is a snapshot at a particular moment in time, it's unclear how how that
correlates to the activity. The fact that you've removed names of tables
and even queries is does not really help either.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services