pg_lightool-postgres数据分布查看工具

服务器

浏览数:21

2020-5-31

AD:资源代下载服务

一、前言

    数据库在经过长时间的运行之后,有效数据不规则的分散在数据文件的各个地方。如何能得知每个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 ~]$ 
# 执行命令
[lchch@yfslcentos71 ~]$ pg_lightool datadis -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -g 2 -s 47
Start 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 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)

作者:movead