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
