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 [[email protected] 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[[email protected] 13211]$

執行命令

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

解析結果

某些解析會導致大量的輸出,所以輸出結果存儲在-p指定的目錄下,目前格式不是很美觀,各位看官不要介意。

[[email protected] ~]$ 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=#  

再次解析的結果

# 執行命令 [[email protected] ~]$ pg_lightool datadis -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -g 1 -dStart Datadis Analyse...Datadis Analyse Success.[[email protected] ~]$ # 結果查詢 [[email protected] ~]$ 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 ################################################### [[email protected] ~]$

其他的查詢方式

# 命令執行 [[email protected] ~]$ pg_lightool datadis -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -g 1Start Datadis Analyse...Datadis Analyse Success.[[email protected] ~]$ # 解析結果 [[email protected] ~]$ 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% ################################################### [[email protected] ~]$
# 執行命令 [[email protected] ~]$ pg_lightool datadis -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -g 2Start Datadis Analyse...Datadis Analyse Success.[[email protected] ~]$ # 解析結果 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% ################################################### [[email protected] ~]$
# 執行命令 [[email protected] ~]$ 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.[[email protected] ~]$ # 解析結果 [[email protected] ~]$ cat datadis.txt PAGE:0(0) OF FILE:16904 use ratio:43% ################################################### RELFILENODE:16904 Use Ratio:49% ################################################### [[email protected] ~]$

 注意:如下,執行的為pageinspect命令,這個命令對單獨的page就行數據統計,并打印每個元組的詳細情況,不再是datadis命令。

# 執行命令 [[email protected] ~]$ pg_lightool pageinspect -f 0/13211/16904 -D /work/opt/postgres/20180728/data -p /home/lchch -b 0Start Page Inspect...Page Inspect Success.[[email protected] ~]$ # 結果解析 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[[email protected] ~]$

五、工具用途

設想的使用場景(錯勿怪):https://my.oschina.net/lcc1990/blog/1934262

六、bug提交

    如有bug可以在碼云上提交,也可聯系我([email protected]

44.1K
今天贵州十一选五走势图