postgres磁盘空间分析

1
2
3
4
5

CREATE TABLE asset(
    id integer,
    s char(100)
)
CREATE INDEX asset_s ON asset(s);

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

-------使用pageinspect扩展查看页头----------
CREATE EXTENSION pageinspect;

SELECT * FROM page_header(get_raw_page('asset',0));

SELECT * FROM heap_page_items(get_raw_page('asset', 0));

begin;
------ 查看当前事务id
SELECT pg_current_xact_id();
commit;

------- lp 指针被转换为元组 ID 的标准格式：页号，指针号。
------- lp_flags 的状态被详细展示出来。此处它被设为 normal，意味着确实指向一个元组。
------- 在所有信息位中，到目前为止我们只挑选出了两对。xmin_committed 和 xmin_aborted 表示 xmin 对应的事务已提交或者已中止。xmax_committed 和 xmax_aborted 提供了关于 xmax 事务的类似信息。
SELECT '(0,'||lp||')' AS ctid,
     CASE lp_flags
       WHEN 0 THEN 'unused'
       WHEN 1 THEN 'normal'
       WHEN 2 THEN 'redirect to '||lp_off
       WHEN 3 THEN 'dead'
     END AS state,
     t_xmin as xmin,
     t_xmax as xmax,
     (t_infomask & 256) > 0 AS xmin_committed,
     (t_infomask & 512) > 0 AS xmin_aborted,
     (t_infomask & 1024) > 0 AS xmax_committed,
     (t_infomask & 2048) > 0 AS xmax_aborted
FROM heap_page_items(get_raw_page('asset',0));

-------- 创建一个函数，方便多次使用 -----
CREATE OR REPLACE FUNCTION heap_page(relname text, pageno integer)
RETURNS TABLE(ctid tid, state text, xmin text, xmax text)
AS $$
SELECT (pageno,lp)::text::tid AS ctid,
     CASE lp_flags
       WHEN 0 THEN 'unused'
       WHEN 1 THEN 'normal'
       WHEN 2 THEN 'redirect to '||lp_off
       WHEN 3 THEN 'dead'
     END AS state,
     t_xmin || CASE
       WHEN (t_infomask & 256) > 0 THEN ' c'
       WHEN (t_infomask & 512) > 0 THEN ' a'
       ELSE ''
     END AS xmin,
     t_xmax || CASE
       WHEN (t_infomask & 1024) > 0 THEN ' c'
       WHEN (t_infomask & 2048) > 0 THEN ' a'
       ELSE ''
     END AS xmax
FROM heap_page_items(get_raw_page(relname,pageno))
ORDER BY lp;
$$ LANGUAGE sql;

SELECT * FROM heap_page('asset',0);

------ 重定义heap_page函数
DROP FUNCTION heap_page(text,integer);

CREATE OR REPLACE FUNCTION heap_page(relname text, pageno integer)
RETURNS TABLE(
  ctid tid, state text,
  xmin text, xmax text,
  hhu text, hot text, t_ctid tid
) AS $$
SELECT (pageno,lp)::text::tid AS ctid,
       CASE lp_flags
         WHEN 0 THEN 'unused'
         WHEN 1 THEN 'normal'
         WHEN 2 THEN 'redirect to '||lp_off
         WHEN 3 THEN 'dead'
       END AS state,
       t_xmin || CASE
         WHEN (t_infomask & 256) > 0 THEN ' c'
         WHEN (t_infomask & 512) > 0 THEN ' a'
         ELSE ''
       END AS xmin,
       t_xmax || CASE
         WHEN (t_infomask & 1024) > 0 THEN ' c'
         WHEN (t_infomask & 2048) > 0 THEN ' a'
         ELSE ''
       END AS xmax,
       CASE WHEN (t_infomask2 & 16384) > 0 THEN 't' END AS hhu,
       CASE WHEN (t_infomask2 & 32768) > 0 THEN 't' END AS hot,
       t_ctid
FROM heap_page_items(get_raw_page(relname,pageno))
ORDER BY lp;
$$ LANGUAGE sql;

---- 复杂查询，多次使用，定义为函数查询
CREATE OR REPLACE FUNCTION heap_page(
  relname text, pageno_from integer, pageno_to integer
)
RETURNS TABLE(
  ctid tid, state text,
  xmin text, xmin_age integer, xmax text
) AS $$
SELECT (pageno,lp)::text::tid AS ctid,
     CASE lp_flags
       WHEN 0 THEN 'unused'
       WHEN 1 THEN 'normal'
       WHEN 2 THEN 'redirect to '||lp_off
       WHEN 3 THEN 'dead'
     END AS state,
     t_xmin || CASE
       WHEN (t_infomask & 256+512) = 256+512 THEN ' f'
       WHEN (t_infomask & 256) > 0 THEN ' c'
       WHEN (t_infomask & 512) > 0 THEN ' a'
       ELSE ''
     END AS xmin,
     age(t_xmin) AS xmin_age,
     t_xmax || CASE
       WHEN (t_infomask & 1024) > 0 THEN ' c'
       WHEN (t_infomask & 2048) > 0 THEN ' a'
       ELSE ''
     END AS xmax
FROM generate_series(pageno_from, pageno_to) p(pageno),
     heap_page_items(get_raw_page(relname, pageno))
ORDER BY pageno, lp;
$$ LANGUAGE sql;

SELECT * FROM heap_page('asset',0,0) LIMIT 5;


-------- 使用 pageinspect 显示页面中的所有索引条目 (B 树索引页将它们存储为一个扁平列表)
CREATE OR REPLACE FUNCTION index_page(relname text, pageno integer)
RETURNS TABLE(itemoffset smallint, htid tid)
AS $$
SELECT itemoffset,
       htid -- ctid before v.13
FROM bt_page_items(relname,pageno);
$$ LANGUAGE sql;

SELECT * FROM index_page('asset_timestamp',1);


--------- 扩展索引页面函数
DROP FUNCTION index_page(text, integer);
CREATE OR REPLACE FUNCTION index_page(relname text, pageno integer)
RETURNS TABLE(itemoffset smallint, htid tid, dead boolean)
AS $$
SELECT itemoffset,
       htid,
       dead -- starting from v.13
FROM bt_page_items(relname,pageno);
$$ LANGUAGE sql;

1
2
3

SELECT pg_current_snapshot();
--------- pg_export_snapshot 函数返回一个快照 ID
SELECT pg_export_snapshot();

1
2
3
4
5
6
7
8
9

--------- pg_stat_activity 表中看到它们自己的视界
SELECT backend_xmin FROM pg_stat_activity WHERE pid = pg_backend_pid();

--------- 使用 pg_visibility 扩展来检查可见性映射
CREATE EXTENSION pg_visibility;

SELECT all_visible FROM pg_visibility_map('asset',0);
--------- 页头中的属性也进行了更新，表明其所有元组在所有快照中都是可见的
SELECT flags & 4 > 0 AS all_visible FROM page_header(get_raw_page('asset',0));

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

--------- 调用 VACUUM 时使用 verbose 子句来观察发生了什么
---- VACUUM 的输出显示了以下信息：
---- VACUUM 没有检测到可以移除的元组 (0 REMOVABLE)。
---- 两个元组不能被移除 (2 NONREMOVABLE)。
---- 其中一个不可移除的元组状态是 dead (1 DEAD)，其他的正在使用。
---- VACUUM 当前所遵循的视界 (OLDEST XMIN) 是活跃事务的视界。
VACUUM VERBOSE asset_list_summary_day;

---------- 创建两个视图以显示当前哪些表需要被清理和分析
CREATE OR REPLACE FUNCTION p(param text, c pg_class) RETURNS float
AS $$
  SELECT coalesce(
    -- use storage parameter if set
    (SELECT option_value
     FROM pg_options_to_table(c.reloptions)
     WHERE option_name = CASE
             -- for TOAST tables the parameter name is different
             WHEN c.relkind = 't' THEN 'toast.' ELSE ''
            END || param
    ),
    -- else take the configuration parameter value
    current_setting(param)
  )::float;
$$ LANGUAGE sql;

CREATE OR REPLACE VIEW need_vacuum AS
WITH c AS (
  SELECT c.oid,
    greatest(c.reltuples, 0) reltuples,
    p('autovacuum_vacuum_threshold', c) threshold,
    p('autovacuum_vacuum_scale_factor', c) scale_factor,
    p('autovacuum_vacuum_insert_threshold', c) ins_threshold,
    p('autovacuum_vacuum_insert_scale_factor', c) ins_scale_factor
  FROM pg_class c
WHERE c.relkind IN ('r','m','t')
)
SELECT st.schemaname || '.' || st.relname AS tablename,
  st.n_dead_tup AS dead_tup,
  c.threshold + c.scale_factor * c.reltuples AS max_dead_tup,
  st.n_ins_since_vacuum AS ins_tup,
  c.ins_threshold + c.ins_scale_factor * c.reltuples AS max_ins_tup,
  st.last_autovacuum
FROM pg_stat_all_tables st
  JOIN c ON c.oid = st.relid;

CREATE OR REPLACE VIEW need_analyze AS
WITH c AS (
  SELECT c.oid,
    greatest(c.reltuples, 0) reltuples,
    p('autovacuum_analyze_threshold', c) threshold,
    p('autovacuum_analyze_scale_factor', c) scale_factor
  FROM pg_class c
  WHERE c.relkind IN ('r','m')
)
SELECT st.schemaname || '.' || st.relname AS tablename,
  st.n_mod_since_analyze AS mod_tup,
  c.threshold + c.scale_factor * c.reltuples AS max_mod_tup,
  st.last_autoanalyze
FROM pg_stat_all_tables st
  JOIN c ON c.oid = st.relid;


--------- VACUUM 运行时多次查询 pg_stat_progress_vacuum 视图
VACUUM FULL verbose asset;
---- 该视图主要显示了：
---- phase — 当前清理阶段的名称 (我描述了主要的几个阶段，但实际上还有更多 19)
---- heap_blks_total — 表中的页面总数
---- heap_blks_scanned — 已扫描的页面数量
---- heap_blks_vacuumed —已清理的页面数量
---- index_vacuum_count — 索引扫描的次数
SELECT * FROM pg_stat_progress_vacuum

1
2
3

---- 重新加载配置文件
SELECT pg_reload_conf();

1
2
3
4
5
6
7
8

----- 创建一张测试表
CREATE TABLE vac(
    id integer,
    s char(100)
)
WITH (autovacuum_enabled = on);

CREATE INDEX vac_s ON vac(s);

1
2

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('vac'); -- 分析数据密度

1

SELECT * FROM pgstatindex('vac_s'); -- 分析索引

1
2
3

----- 表及其索引的当前大小
SELECT pg_size_pretty(pg_table_size('vac')) AS table_size,
        pg_size_pretty(pg_indexes_size('vac')) AS index_size;

1
2
3

----- 插入50w数据
INSERT INTO vac(id,s)
SELECT id, id::text FROM generate_series(1,50000) id;

1
2
3
4
5

----- 删除 90% 的行 共删除450000行
DELETE FROM vac WHERE id % 10 != 0;
---- 数据密度下降了大约 10 倍
SELECT vac.tuple_percent, vac_s.avg_leaf_density
FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s;

1
2
3

---- 表和索引当前位于以下文件中
SELECT pg_relation_filepath('vac') AS vac_filepath,
        pg_relation_filepath('vac_s') AS vac_s_filepath;

1
2

---- 完全清理
VACUUM FULL vac;

1

update vac set s = id::text

1
2
3
4
5
6

WITH batch AS (
    SELECT id FROM vac WHERE NOT processed offset 0 LIMIT 10000
    FOR UPDATE SKIP LOCKED
)
UPDATE vac SET s = id::text
WHERE id IN (SELECT id FROM batch);