pg_lightool-postgres数据分布查看工具

Download

2019/12/05

李传成Contributor

瀚高乐虎国际官网登录内核研发工程师,主要的研究wal日志,并基于对wal日志的理解开发了wal相关的开源项目walminer和pg_lightool

在大象的世界里,砥砺前行,move ahead。

博客:https://my.oschina.net/lcc1990

一、前言

    数据库在经过长时间的运行之后,有效数据不规则的分散在数据文件的各个地方。如何能得知每个page,每个segment到底存储了多少数据呢?作者DIY了这个功能'datadis'到pg_lightool工具中。

二、源码

下载地址:http://gitee.com/movead/pg_lightool

目前只有源码编译安装版本。

三、代码编译安装

1.首先需要配置postgres的PATH路径

2.进入下载的代码的pg_lightool目录

3.make;make install;

这样就pg_lightool工具就成功安装到postgres的bin目录下了

四、工具使用

现有表

postgres=# select relfilenode from pg_class where relname ='t2'; relfilenode ------------- 16904 (1 row)postgres=# \d t2 \                      Table "public.t2" Column |       Type        | Collation | Nullable | Default --------+-------------------+-----------+----------+--------- i | integer           | |          | j | integer           | |          | k | character varying | |          | postgres=#\q [lchch@yfslcentos71 13211]$ ll 16904*-rw------- 1 lchch lchch 1073741824 Aug 26 19:39 16904 -rw------- 1 lchch lchch 1073741824 Aug 26 20:24 16904.1 -rw------- 1 lchch lchch 8192 Aug 26 20:22 16904.2 -rw------- 1 lchch lchch 548864 Aug 26 20:22 16904_fsm-rw------- 1 lchch lchch 40960 Aug 26 19:50 16904_vm[lchch@yfslcentos71 13211]$

执行命令

[lchch@yfslcentos71 ~]$ pg_lightool datadis -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -g 1 -dStart Datadis Analyse...Datadis Analyse Success.[lchch@yfslcentos71 ~]$

解析结果

某些解析会导致大量的输出,所以输出结果存储在-p指定的目录下,目前格式不是很美观,各位看官不要介意。

[lchch@yfslcentos71 ~]$ cat datadis.txt ---------------------------------------------------FILE:16904.0 use ratio:98%Free Items Num:       0Use Items Num:        20578304Items Size:           164626432Page Head Size:       3145728Page Special Size:    0Maybe Tuple Use Size: 0Sure Tuple Use Size:  884867072Free Size:            21102592------------------------------------------------------------------------------------------------------FILE:16904.1 use ratio:98%Free Items Num:       0Use Items Num:        20578304Items Size:           164626432Page Head Size:       3145728Page Special Size:    0Maybe Tuple Use Size: 0Sure Tuple Use Size:  884867072Free Size:            21102592------------------------------------------------------------------------------------------------------FILE:16904.2 use ratio:98%Free Items Num:       0Use Items Num:        157Items Size:           1256Page Head Size:       24Page Special Size:    0Maybe Tuple Use Size: 0Sure Tuple Use Size:  6751Free Size:            161--------------------------------------------------- ###################################################

改变表内数据分布

postgres=# delete from t2 where i % 2 = 1; DELETE 20578383postgres=# update t2 set k = 'https://www.oschina.net/' where i < 1000; UPDATE 1076postgres=# checkpoint; CHECKPOINTpostgres=#  

再次解析的结果

# 执行命令 [lchch@yfslcentos71 ~]$ pg_lightool datadis -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -g 1 -dStart Datadis Analyse...Datadis Analyse Success.[lchch@yfslcentos71 ~]$ # 结果查询 [lchch@yfslcentos71 ~]$ cat datadis.txt ---------------------------------------------------FILE:16904.0 use ratio:45%Free Items Num:       10289230Use Items Num:        10289432Items Size:           82314648Page Head Size:       3145728Page Special Size:    0Maybe Tuple Use Size: 18103Sure Tuple Use Size:  442432485Free Size:            545830860------------------------------------------------------------------------------------------------------FILE:16904.1 use ratio:45%Free Items Num:       10289153Use Items Num:        10289564Items Size:           82314868Page Head Size:       3145728Page Special Size:    0Maybe Tuple Use Size: 24811Sure Tuple Use Size:  442432223Free Size:            545824194------------------------------------------------------------------------------------------------------FILE:16904.2 use ratio:68%Free Items Num:       78Use Items Num:        384Items Size:           1848Page Head Size:       96Page Special Size:    0Maybe Tuple Use Size: 0Sure Tuple Use Size:  20782Free Size:            10042--------------------------------------------------- ################################################### RELFILENODE:16904 Use Ratio:45%Free Items Num:       20578461Use Items Num:        20579380Items Size:           164631364Page Head Size:       6291552Page Special Size:    0Maybe Tuple Use Size: 42914Sure Tuple Use Size:  884885490Free Size:            1091665096 ################################################### [lchch@yfslcentos71 ~]$

其他的查询方式

# 命令执行 [lchch@yfslcentos71 ~]$ pg_lightool datadis -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -g 1Start Datadis Analyse...Datadis Analyse Success.[lchch@yfslcentos71 ~]$ # 解析结果 [lchch@yfslcentos71 ~]$ cat datadis.txt FILE:16904.0 use ratio:45%FILE:16904.1 use ratio:45%FILE:16904.2 use ratio:68% ################################################### RELFILENODE:16904 Use Ratio:45% ################################################### [lchch@yfslcentos71 ~]$
# 执行命令 [lchch@yfslcentos71 ~]$ pg_lightool datadis -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -g 2Start Datadis Analyse...Datadis Analyse Success.[lchch@yfslcentos71 ~]$ # 解析结果 vi datadis.txt PAGE:0(0) OF FILE:16904 use ratio:41%PAGE:1(1) OF FILE:16904 use ratio:45%PAGE:2(2) OF FILE:16904 use ratio:45%PAGE:3(3) OF FILE:16904 use ratio:45%...PAGE:131069(262141) OF FILE:16904.1 use ratio:45%PAGE:131070(262142) OF FILE:16904.1 use ratio:45%PAGE:131071(262143) OF FILE:16904.1 use ratio:45%PAGE:0(262144) OF FILE:16904.2 use ratio:45%PAGE:1(262145) OF FILE:16904.2 use ratio:89%PAGE:2(262146) OF FILE:16904.2 use ratio:89%PAGE:3(262147) OF FILE:16904.2 use ratio:48% ################################################### RELFILENODE:16904 Use Ratio:45% ################################################### [lchch@yfslcentos71 ~]$
# 执行命令 [lchch@yfslcentos71 ~]$ pg_lightool datadis -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -g 2 -s 47Start Datadis Analyse...Datadis Analyse Success.[lchch@yfslcentos71 ~]$ # 解析结果 [lchch@yfslcentos71 ~]$ cat datadis.txt PAGE:0(0) OF FILE:16904 use ratio:43% ################################################### RELFILENODE:16904 Use Ratio:49% ################################################### [lchch@yfslcentos71 ~]$

 注意:如下,执行的为pageinspect命令,这个命令对单独的page就行数据统计,并打印每个元组的详细情况,不再是datadis命令。

# 执行命令 [lchch@yfslcentos71 ~]$ pg_lightool pageinspect -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -b 0Start Page Inspect...Page Inspect Success.[lchch@yfslcentos71 ~]$ # 结果解析 vi pageinspect.txt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PAGE:0(0) OF FILE:16904 use ratio:41%Free Items Num:       157Use Items Num:        55Items Size:           848Page Head Size:       24Page Special Size:    0Maybe Tuple Use Size: 0Sure Tuple Use Size:  3135Free Size:            4185----------------------------------------------------------------------------LP   STATE       XMIN      XMAX      TUPLELEN  HOFF      OFFSET RLP----------------------------------------------------------------------------0    INVALID     0         0         0         0         0      (0,0)1    INVALID     0         0         0         0         158    (0,0)2    INVALID     0         0         0         0         0      (0,0)3    INVALID     0         0         0         0         159    (0,0)4    INVALID     0         0         0         0         0      (0,0)5    INVALID     0         0         0         0         160    (0,0)6    INVALID     0         0         0         0         0      (0,0)7    INVALID     0         0         0         0         161    (0,0)...202  NOMAL       757       0         57        24        5248   (0,202)203  NOMAL       757       0         57        24        5184   (0,203)204  NOMAL       757       0         57        24        5120   (0,204)205  NOMAL       757       0         57        24        5056   (0,205)206  NOMAL       757       0         57        24        4992   (0,206)207  NOMAL       757       0         57        24        4928   (0,207)208  NOMAL       757       0         57        24        4864   (0,208)209  NOMAL       757       0         57        24        4800   (0,209)210  NOMAL       757       0         57        24        4736   (0,210)211  NOMAL       757       0         57        24        4672   (0,211)

-g -d -s各种不同的参数组合含有更多不同的输出结果。

参数说明

pg_lightool -?pg_lightool is a light tool of postgresUsage:  pg_lightool OPTION blockrecover  pg_lightool OPTION walshow  pg_lightool OPTION datadis      //数据分布情况查询  pg_lightool OPTION pageinspect  //page详情查询Common Options:  -V, --version                         output version information, then exit For blockrecover:  -l, --log whether to write a debug info  -f, --relnode=spcid/dbid/relfilenode specify files to repair  -b, --block=n1[,n2,n3]                specify blocks to repair(10 limit)  -w, --walpath=walpath                 wallog read from  -D, --pgdata=datapath                 data dir of database  -i, --immediate            does not do a backup for old fileFor datadis:  -f, --relnode=spcid/dbid/relfilenode  /指定要查询分布的表  -D, --pgdata=datapath                 //数据文件路径  -p, --place=outPtah                   //存放解析结果的路径  -g, --grade=level                     1 显示segfile级别的使用率(默认);                                        2 显示page级别的使用率;                                        3 全部显示;  -d, --detail                    是否显示详细使用信息  -s, --small                    显示使用率比此项小的结果For pageinspect:  -f, --relnode=spcid/dbid/relfilenode  //page存在的表  -D, --pgdata=datapath                 //数据文件路径  -p, --place=outPtah                   //存放解析结果的路径  -b, --block=blkno                     //指定要查询的page[lchch@yfslcentos71 ~]$

五、工具用途

设想的使用场景(错勿怪):https://my.oschina.net/lcc1990/blog/1934262

六、bug提交

    如有bug可以在码云上提交,也可联系我(lchch1990@sina.cn)

44.1K