一、前言
数据库在经过长时间的运行之后，有效数据不规则的分散在数据文件的各个地方。如何能得知每个page，每个segment到底存储了多少数据呢？作者DIY了这个功能'datadis'到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 exitFor 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 ~]$
五、工具用途
设想的使用场景（错勿怪）：
六、bug提交
如有bug可以在码云上提交，也可联系我（lchch1990@sina.cn）