Hi Tomas, restart the statistics and take 24-hour samples to see if you can
help me
24 gb server memory 6 gb sharred buffers
# select now,
# pg_size_pretty(buffers_checkpoint*8192)AS buffers_checkpoint,
# pg_size_pretty(buffers_clean*8192)AS buffers_clean,
# pg_size_pretty(buffers_backend*8192)AS buffers_backend,
#
(buffers_checkpoint*100)/(buffers_checkpoint+buffers_clean+buffers_backend)AS
buffers_checkpoint_pct,
# (buffers_clean*100)/(buffers_checkpoint+buffers_clean+buffers_backend)AS
buffers_clean_pct,
# (buffers_backend*100)/(buffers_checkpoint+buffers_clean+buffers_backend)AS
buffers_backend_pct,
# pg_size_pretty(buffers_checkpoint * 8192 /(checkpoints_timed +
checkpoints_req)) AS avg_checkpoint_write,
# pg_size_pretty(8192 *(buffers_checkpoint + buffers_clean +
buffers_backend)) AS total_write
# from pg_stat_bgwriter_snapshot
# ;
now | buffers_checkpoint | buffers_clean |
buffers_backend | buffers_checkpoint_pct | buffers_clean_pct |
buffers_backend_pct | avg_checkpoint_write | total_write
-------------------------------+--------------------+---------------+-----------------+------------------------+-------------------+---------------------+----------------------+-------------
2019-10-15 15:00:02.070105-03 | 33 MB | 1190 MB | 144 MB
| 2 | 87 | 10 | 33 MB
| 1367 MB
2019-10-15 16:00:01.477785-03 | 109 MB | 3543 MB | 393 MB
| 2 | 87 | 9 | 36 MB
| 4045 MB
2019-10-15 17:00:01.960162-03 | 179 MB | 6031 MB | 703 MB
| 2 | 87 | 10 | 36 MB
| 6913 MB
2019-10-15 18:00:01.558404-03 | 252 MB | 8363 MB | 1000
MB | 2 | 86 |
10 | 36 MB | 9615 MB
2019-10-15 19:00:01.170866-03 | 327 MB | 10019 MB | 1232
MB | 2 | 86 |
10 | 36 MB | 11 GB
2019-10-15 20:00:01.397473-03 | 417 MB | 11 GB | 1407
MB | 3 | 85 |
10 | 38 MB | 13 GB
2019-10-15 21:00:01.211047-03 | 522 MB | 12 GB | 1528
MB | 3 | 85 |
11 | 40 MB | 14 GB
2019-10-15 22:00:01.164853-03 | 658 MB | 12 GB | 1691
MB | 4 | 83 |
11 | 44 MB | 14 GB
2019-10-15 23:00:01.116564-03 | 782 MB | 13 GB | 1797
MB | 5 | 83 |
11 | 46 MB | 15 GB
2019-10-16 00:00:01.19203-03 | 887 MB | 13 GB | 2016
MB | 5 | 82 |
12 | 47 MB | 16 GB
2019-10-16 01:00:01.329851-03 | 1003 MB | 14 GB | 2104
MB | 5 | 81 |
12 | 48 MB | 17 GB
2019-10-16 02:00:01.518606-03 | 1114 MB | 14 GB | 2222
MB | 6 | 81 |
12 | 48 MB | 17 GB
2019-10-16 03:00:01.673498-03 | 1227 MB | 14 GB | 2314
MB | 6 | 80 |
12 | 49 MB | 18 GB
2019-10-16 04:00:01.936604-03 | 1354 MB | 15 GB | 2468
MB | 7 | 79 |
12 | 50 MB | 19 GB
2019-10-16 05:00:01.854888-03 | 1465 MB | 15 GB | 2518
MB | 7 | 79 |
13 | 51 MB | 19 GB
2019-10-16 06:00:01.804182-03 | 1585 MB | 15 GB | 2581
MB | 8 | 78 |
13 | 51 MB | 19 GB
2019-10-16 07:00:01.889345-03 | 1677 MB | 15 GB | 2649
MB | 8 | 78 |
13 | 51 MB | 20 GB
2019-10-16 08:00:01.248247-03 | 1756 MB | 16 GB | 2707
MB | 8 | 78 |
13 | 50 MB | 20 GB
2019-10-16 09:00:01.258408-03 | 1826 MB | 16 GB | 2763
MB | 8 | 78 |
13 | 49 MB | 21 GB
2019-10-16 10:00:01.418323-03 | 1881 MB | 17 GB | 2872
MB | 8 | 78 |
13 | 48 MB | 21 GB
2019-10-16 11:00:02.077084-03 | 1951 MB | 18 GB | 3140
MB | 8 | 78 |
13 | 48 MB | 23 GB
2019-10-16 12:00:01.83188-03 | 2026 MB | 20 GB | 3322
MB | 7 | 79 |
12 | 47 MB | 25 GB
2019-10-16 13:00:01.628877-03 | 2109 MB | 22 GB | 3638
MB | 7 | 79 |
12 | 47 MB | 28 GB
2019-10-16 14:00:02.351529-03 | 2179 MB | 24 GB | 3934
MB | 6 | 80 |
12 | 46 MB | 30 GB
(24 filas)
# SELECT
# sum(heap_blks_read) as heap_read,
# sum(heap_blks_hit) as heap_hit,
# sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
# FROM
# pg_statio_user_tables;
heap_read | heap_hit | ratio
-------------+---------------+------------------------
80203672248 | 4689023850651 | 0.98318308953328194824
(1 fila)
# SELECT
# sum(idx_blks_read) as idx_read,
# sum(idx_blks_hit) as idx_hit,
# (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
# FROM
# pg_statio_user_indexes;
idx_read | idx_hit | ratio
------------+--------------+------------------------
3307622770 | 653969845259 | 0.99494223962468783241
(1 fila)
=# -- perform a "select pg_stat_reset();" when you want to reset counter
statistics
=# with
-# all_tables as
-# (
(# SELECT *
(# FROM (
(# SELECT 'all'::text as table_name,
(# sum( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) +
coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk,
(# sum( (coalesce(heap_blks_hit,0) + coalesce(idx_blks_hit,0) +
coalesce(toast_blks_hit,0) + coalesce(tidx_blks_hit,0)) ) as from_cache
(# FROM pg_statio_all_tables --> change to pg_statio_USER_tables if
you want to check only user tables (excluding postgres's own tables)
(# ) a
(# WHERE (from_disk + from_cache) > 0 -- discard tables without hits
(# ),
-# tables as
-# (
(# SELECT *
(# FROM (
(# SELECT relname as table_name,
(# ( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) +
coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk,
(# ( (coalesce(heap_blks_hit,0) + coalesce(idx_blks_hit,0) +
coalesce(toast_blks_hit,0) + coalesce(tidx_blks_hit,0)) ) as from_cache
(# FROM pg_statio_all_tables --> change to pg_statio_USER_tables if
you want to check only user tables (excluding postgres's own tables)
(# ) a
(# WHERE (from_disk + from_cache) > 0 -- discard tables without hits
(# )
-# SELECT table_name as "table name",
-# from_disk as "disk hits",
-# round((from_disk::numeric / (from_disk +
from_cache)::numeric)*100.0,2) as "% disk hits",
-# round((from_cache::numeric / (from_disk +
from_cache)::numeric)*100.0,2) as "% cache hits",
-# (from_disk + from_cache) as "total hits"
-# FROM (SELECT * FROM all_tables UNION ALL SELECT * FROM tables) a
-# ORDER BY (case when table_name = 'all' then 0 else 1 end), from_disk
desc
-# ;
table name | disk hits | % disk hits | %
cache hits | total hits
---------------------------------------------+-------------+-------------+--------------+---------------
all | 88000266877 | 1.60 |
98.40 | 5489558628019
b_e_i | 38269990257 | 2.88 |
97.12 | 1329542407426
n_c_r_o | 32839222402 | 1.44 | 98.56 |
2278801314997
b_e_i_a | 6372214550 | 4.76 |
95.24 | 133916822424
d_d | 2101245550 | 6.58 |
93.42 | 31936220932
pg_toast_550140 | 2055940284 | 32.63 |
67.37 | 6300424824
p_i | 1421254520 | 0.36 |
99.64 | 393348432350
n_c_e_s | 1164509701 | 27.85 | 72.15 |
4180714300
s_b_c_a | 1116814156 | 0.19 |
99.81 | 595617511928
b_e_i_l | 624945696 | 41.13 |
58.87 | 1519594743
p_e_i | 525580057 | 5.27 |
94.73 | 9968414493
=# select
-# s.relname,
-# pg_size_pretty(pg_relation_size(relid)),
-# coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
-# coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
-# (coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
(# then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
-# (select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\\d+)')
as
(# r(v) limit 1) AS fillfactor
-# from pg_stat_all_tables s
-# join pg_class c ON c.oid=relid
-# order by total_writes desc limit 50;
relname | pg_size_pretty | total_writes | hot_rate
| fillfactor
----------------------------------+----------------+--------------+----------+------------
pg_toast_550140 | 1637 GB | 820414234 | 0.00
|
b_e_i_a | 168 GB | 454229502 | 0.00 |
s_b_c_a | 26 MB | 419253909 | 96.94 |
b_e_i_a_l | 71 GB | 305584644 | 0.00 |
s_b_c_a_l | 965 MB | 203361185 | 0.00 |
b_e_i | 7452 MB | 194861425 | 62.88 |
b_e_i_l | 57 GB | 144929408 | 0.00 |
o_i_n | 3344 kB | 98435081 | 99.38 |
r_h | 1140 MB | 33209351 | 0.11 |
b_e | 5808 kB | 29608085 | 99.65 |
=# select
calls,shared_blks_hit,shared_blks_read,shared_blks_dirtied,query--,
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 |
query
43374691 | 1592513886 | 77060029 | 42096885 |
INSERT INTO b_e_i_a
23762881 | 1367338973 | 34351016 | 29131240 |
UPDATE b_e_i
541120 | 564550710 | 25726748 | 25551138 |
INSERT INTO d_d
23 | 23135504 | 187638126 | 15301103 |
VACUUM ANALYZE VERBOSE b_e_i;
11804481 | 401558460 | 19124307 | 14492182 |
UPDATE b_e_i_a
2352159 | 287732134 | 9462460 | 6250734 |
INSERT INTO b_e_i
13701688 | 256215340 | 5803881 | 6142119 |
INSERT into I_C_M
56582737 | 338943996 | 5272879 | 4882863 |
INSERT INTO b_e_i_a_l
26115040 | 131274217 | 6016404 | 4712060 |
INSERT INTO b_e_i_l
=# SELECT oid::REGCLASS::TEXT AS table_name,
-# pg_size_pretty(
(# pg_total_relation_size(oid)
(# ) AS total_size
-# FROM pg_class
-# WHERE relkind = 'r'
-# AND relpages > 0
-# ORDER BY pg_total_relation_size(oid) DESC
-# LIMIT 20;;
table_name | total_size
----------------------------------+------------
d_d | 1656 GB
b_e_i_a | 547 GB
b_e_i_a_l | 107 GB
b_e_i_l | 71 GB
b_e_i | 66 GB
n_c_e_s | 28 GB
p_e_i | 7807 MB
n_c_s | 7344 MB
e_i_n | 5971 MB
p_e_d_i | 3695 MB
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
