Date: Mon, 14 Oct 2019 13:12:43 -0700 (MST)
From: dangal <danielito.gallo@gmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1571083963570-0.post@n3.nabble.com>
In-Reply-To: <20191014181847.6lf5s3fwuh7ui25r@development>
References: <20191014181847.6lf5s3fwuh7ui25r@development>
Subject: Re: pg_stat_bgwriter
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

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=20
-------------------------------+--------------------+---------------+------=
-----------
 2019-10-07 12:00:01.312067-03 |             288343 |       1182944 |      =
  =20
520101
 2019-10-08 00:00:02.034129-03 |             475323 |       3890772 |      =
  =20
975454
 2019-10-08 12:00:01.500756-03 |             616154 |       4774924 |      =
 =20
1205261
 2019-10-09 00:00:01.520329-03 |             784840 |       7377771 |      =
 =20
1601278
 2019-10-09 12:00:01.388113-03 |            1149560 |       8395288 |      =
 =20
2456249
 2019-10-10 00:00:01.841054-03 |            1335747 |      11023014 |      =
 =20
2824740
 2019-10-10 12:00:01.354555-03 |            1486963 |      11919462 |      =
 =20
2995211
 2019-10-11 00:00:01.519538-03 |            1649066 |      14400593 |      =
 =20
3360700
 2019-10-11 12:00:01.468203-03 |            1979781 |      15332086 |      =
 =20
4167663
 2019-10-12 00:00:01.343714-03 |            2161116 |      17791871 |      =
 =20
4525957
 2019-10-12 12:00:01.991429-03 |            2323194 |      18324723 |      =
 =20
5139418
 2019-10-13 00:00:01.251191-03 |            2453939 |      19059149 |      =
 =20
5306894
 2019-10-13 12:00:01.677379-03 |            2782606 |      19391676 |      =
 =20
5878981
 2019-10-14 00:00:01.824249-03 |            2966021 |      19915346 |      =
 =20
6040316
 2019-10-14 12:00:01.869126-03 |            3117659 |      20675018 |      =
 =20
6184214
=20
I tell you that we have a server with 24 gb of ram and 6gb of shared_buffer=
s
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
=C2=A0 from pg_stat_statements
=C2=A0 where shared_blks_dirtied> 0 order by shared_blks_dirtied desc
=C2=A0 limit 10
=20

  calls   | shared_blks_hit | shared_blks_read | shared_blks_dirtied=20
-----------+-----------------+------------------+---------------------
  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 =3D 'shared_buffer=
s')
             ::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 =3D c.relfilenode
 INNER JOIN pg_database d
    ON (b.reldatabase =3D d.oid AND d.datname =3D current_database())
 GROUP BY c.oid, c.relname
 ORDER BY 3 DESC LIMIT 10;

buffered=09buffers_percent=09  percent_of_relation
3938 MB;   =0964.1;=09=09=0953.2
479 MB;=09=097.8;=09=09=09=0921.3
261 MB;=09=094.3;=09=09=09=0999.3
163 MB;=09=092.6;=09=09=09=090.1
153 MB;=09=092.5;=09=09=09=096.7
87 MB;=09=091.4;=09=09=09=091.2
82 MB;=09=091.3;=09=09=09=0981.6
65 MB;=09=091.1;=09=09=09=09100.0
64 MB;=09=091.0;=09=09=09=090.1
53 MB;=09=090.9;=09=09=09=0973.5


--
Sent from: https://www.postgresql-archive.org/PostgreSQL-performance-f20500=
81.html