pg_lightool-postgres数据分布查看工具
一、前言
数据库在经过长时间的运行之后，有效数据不规则的分散在数据文件的各个地方。如何能得知每个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 -d
Start Datadis Analyse...
Datadis Analyse Success.
[lchch@yfslcentos71 ~]$
解析结果
某些解析会导致大量的输出，所以输出结果存储在-p指定的目录下，电影下载，目前格式不是很美观，各位看官不要介意。
[lchch@yfslcentos71 ~]$ cat datadis.txt
---------------------------------------------------
FILE:16904.0 use ratio:98%
Free Items Num: 0
Use Items Num: 20578304
Items Size: 164626432
Page Head Size: 3145728
Page Special Size: 0
Maybe Tuple Use Size: 0
Sure Tuple Use Size: 884867072
Free Size: 21102592
---------------------------------------------------
---------------------------------------------------
FILE:16904.1 use ratio:98%
Free Items Num: 0
Use Items Num: 20578304
Items Size: 164626432
Page Head Size: 3145728
Page Special Size: 0
Maybe Tuple Use Size: 0
Sure Tuple Use Size: 884867072
Free Size: 21102592
---------------------------------------------------
---------------------------------------------------
FILE:16904.2 use ratio:98%
Free Items Num: 0
Use Items Num: 157
Items Size: 1256
Page Head Size: 24
Page Special Size: 0
Maybe Tuple Use Size: 0
Sure Tuple Use Size: 6751
Free Size: 161
---------------------------------------------------
###################################################
改变表内数据分布
postgres=# delete from t2 where i % 2 = 1;
DELETE 20578383
postgres=# update t2 set k = 'https://www.oschina.net/' where i < 1000;
UPDATE 1076
postgres=# checkpoint;
CHECKPOINT
postgres=#
再次解析的结果
# 执行命令
[lchch@yfslcentos71 ~]$ pg_lightool datadis -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -g 1 -d
Start Datadis Analyse...
Datadis Analyse Success.
[lchch@yfslcentos71 ~]$
# 结果查询
[lchch@yfslcentos71 ~]$ cat datadis.txt
---------------------------------------------------
FILE:16904.0 use ratio:45%
Free Items Num: 10289230
Use Items Num: 10289432
Items Size: 82314648
Page Head Size: 3145728
Page Special Size: 0
Maybe Tuple Use Size: 18103
Sure Tuple Use Size: 442432485
Free Size: 545830860
---------------------------------------------------
---------------------------------------------------
FILE:16904.1 use ratio:45%
Free Items Num: 10289153
Use Items Num: 10289564
Items Size: 82314868
Page Head Size: 3145728
Page Special Size: 0
Maybe Tuple Use Size: 24811
Sure Tuple Use Size: 442432223
Free Size: 545824194
---------------------------------------------------
---------------------------------------------------
FILE:16904.2 use ratio:68%
Free Items Num: 78
Use Items Num: 384
Items Size: 1848
Page Head Size: 96
Page Special Size: 0
Maybe Tuple Use Size: 0
Sure Tuple Use Size: 20782
Free Size: 10042
---------------------------------------------------
###################################################
RELFILENODE:16904 Use Ratio:45%
Free Items Num: 20578461
Use Items Num: 20579380
Items Size: 164631364
Page Head Size: 6291552
Page Special Size: 0
Maybe Tuple Use Size: 42914
Sure Tuple Use Size: 884885490
Free Size: 1091665096
###################################################
[lchch@yfslcentos71 ~]$
其他的查询方式
# 命令执行
[lchch@yfslcentos71 ~]$ pg_lightool datadis -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -g 1
Start 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 2
Start 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 ~]$
注意：如下，执行的为pageinspect命令，这个命令对单独的page就行数据统计，站长站，并打印每个元组的详细情况，不再是datadis命令。
# 执行命令
[lchch@yfslcentos71 ~]$ pg_lightool pageinspect -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -b 0
Start Page Inspect...
Page Inspect Success.
[lchch@yfslcentos71 ~]$
# 结果解析
vi pageinspect.txt
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PAGE:0(0) OF FILE:16904 use ratio:41%
Free Items Num: 157
Use Items Num: 55
Items Size: 848
Page Head Size: 24
Page Special Size: 0
Maybe Tuple Use Size: 0
Sure Tuple Use Size: 3135
Free 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 postgres
Usage:
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 file
For 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）
文章转自:https://my.oschina.net/lcc1990/blog/1934261
