Re: pg_stat_bgwriter - Mailing list pgsql-performance

From dangal
Subject Re: pg_stat_bgwriter
Date
Msg-id 1571247457803-0.post@n3.nabble.com
Whole thread Raw
In response to Re: pg_stat_bgwriter  (Tomas Vondra <tomas.vondra@2ndquadrant.com>)
Responses Re: pg_stat_bgwriter
List pgsql-performance
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



pgsql-performance by date:

Previous
From: David Conlin
Date:
Subject: Change in CTE treatment in query plans?
Next
From: dangal
Date:
Subject: Re: pg_stat_bgwriter