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
