Re: pg_stat_bgwriter - Mailing list pgsql-performance

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;
              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 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)

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



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html