概要
ibdファイルの中身をパース、ダンプしてくれるツールが出ていたので、試して見たメモ。
こんな感じで見れるというサンプルとして残しておきます。
sysbenchで作った1000000レコードのテーブルsbtest1とmysql.ibdを見て見ています。
build
クローンしてmakeコマンド叩くだけ。
git clone https://github.com/KernelMaker/ibdNinja cd ibdNinja make
mysqldなど、いろいろビルドしている実験用サーバなので、パッケージは揃っている。
なければいろいろ必要かも？
help
$ ibdNinja --help
|--------------------------------------------------------------------------------------------------------------|
| _ _ _ _ _ _ _ |
| (_) (_) (_) (_) _ (_) (_) (_) |
| _ _ (_) _ _ _ _ _ _ (_) (_)(_)_ (_) _ _ _ _ _ _ _ _ _ _ _ |
|(_)(_) (_)(_)(_)(_)_ _(_)(_)(_)(_) (_) (_)_ (_) (_)(_) (_)(_)(_)(_)_ (_)(_) (_)(_)(_) _ |
| (_) (_) (_) (_) (_) (_) (_)_ (_) (_) (_) (_) (_) _ _ _ (_) |
| (_) (_) (_) (_) (_) (_) (_)(_) (_) (_) (_) (_) _(_)(_)(_)(_) |
| _ (_) _ (_) _ _ _(_) (_)_ _ _ (_) (_) (_) _ (_) _ (_) (_) (_) (_)_ _ _ (_)_ |
|(_)(_)(_) (_)(_)(_)(_) (_)(_)(_)(_) (_) (_) (_)(_)(_) (_) (_) _ _(_) (_)(_)(_) (_)|
| (_)_ _(_) |
| (_)(_) |
|--------------------------------------------------------------------------------------------------------------|
Usage: ibdNinja [OPTIONS]
Options:
--help, -h Display this help message
--file, -f Specify the path to the ibd file
--list-tables, -l List all *supported* tables and their supported indexes in the specified ibd file
--list-all-tables, -a List all tables and their indexes in the specified ibd file
--list-leafmost-pages, -e INDEX_ID Show the leftmost page number at each level of the specified index
--analyze-table, -t TABLE_ID Analyze the specified table
--analyze-index, -i INDEX_ID Analyze the specified index
--parse-page, -p PAGE_ID Parse the specified page
--no-print-record, -n Skip printing record details when parsing a page
--version, -v Display version information
各コマンド実行例
--versio n
$ ibdNinja --version |--------------------------------------------------------------------------------------------------------------| | _ _ _ _ _ _ _ | | (_) (_) (_) (_) _ (_) (_) (_) | | _ _ (_) _ _ _ _ _ _ (_) (_)(_)_ (_) _ _ _ _ _ _ _ _ _ _ _ | |(_)(_) (_)(_)(_)(_)_ _(_)(_)(_)(_) (_) (_)_ (_) (_)(_) (_)(_)(_)(_)_ (_)(_) (_)(_)(_) _ | | (_) (_) (_) (_) (_) (_) (_)_ (_) (_) (_) (_) (_) _ _ _ (_) | | (_) (_) (_) (_) (_) (_) (_)(_) (_) (_) (_) (_) _(_)(_)(_)(_) | | _ (_) _ (_) _ _ _(_) (_)_ _ _ (_) (_) (_) _ (_) _ (_) (_) (_) (_)_ _ _ (_)_ | |(_)(_)(_) (_)(_)(_)(_) (_)(_)(_)(_) (_) (_) (_)(_)(_) (_) (_) _ _(_) (_)(_)(_) (_)| | (_)_ _(_) | | (_)(_) | |--------------------------------------------------------------------------------------------------------------| Version: 1.0.0
--list-tables
ibdNinja --list-tables -f /mysql/data/d1/sbtest1.ibd
====================================================================================
| FILE INFORMATION |
------------------------------------------------------------------------------------
File name: /mysql/data/d1/sbtest1.ibd
File size: 251658240 B
Space id: 2
Page logical size: 16384 B
Page physical size: 16384 B
Total number of pages: 15360
Is compressed page? 0
First page number: 0
SDI root page number: 3
Post antelop: 1
Atomic blobs: 1
Has data dir: 0
Shared: 0
Temporary: 0
Encryption: 0
------------------------------------------------------------------------------------
[ibdNinja]: Successfully loaded 1 tables with 2 indexes.
====================================================================================
Listing all *supported* tables and indexes in the specified ibd file:
---------------------------------------
[Table] id: 1064 name: d1.sbtest1
[Index] id: 154 , root page no: 4 , name: PRIMARY
[Index] id: 155 , root page no: 49 , name: k_1
--list-all-tables
--list-tablesと同じかと思いきや、出力後半のテーブル、インデックス情報が違う。
こちらでは後述する--analyze-tableや--analyze-indexで指定するテーブルやインデックスのIDがわからない。
ibdNinja --list-all-tables -f /mysql/data/d1/sbtest1.ibd
====================================================================================
| FILE INFORMATION |
------------------------------------------------------------------------------------
File name: /mysql/data/d1/sbtest1.ibd
File size: 251658240 B
Space id: 2
Page logical size: 16384 B
Page physical size: 16384 B
Total number of pages: 15360
Is compressed page? 0
First page number: 0
SDI root page number: 3
Post antelop: 1
Atomic blobs: 1
Has data dir: 0
Shared: 0
Temporary: 0
Encryption: 0
------------------------------------------------------------------------------------
[ibdNinja]: Successfully loaded 1 tables with 2 indexes.
====================================================================================
Listing all tables and indexes in the specified ibd file:
---------------------------------------
[Table] name: d1.sbtest1
[Index] name: PRIMARY
[Index] name: k_1
--analyze-table
$ ibdNinja --analyze-table 1064 -f /mysql/data/d1/sbtest1.ibd
====================================================================================
| FILE INFORMATION |
------------------------------------------------------------------------------------
File name: /mysql/data/d1/sbtest1.ibd
File size: 251658240 B
Space id: 2
Page logical size: 16384 B
Page physical size: 16384 B
Total number of pages: 15360
Is compressed page? 0
First page number: 0
SDI root page number: 3
Post antelop: 1
Atomic blobs: 1
Has data dir: 0
Shared: 0
Temporary: 0
Encryption: 0
------------------------------------------------------------------------------------
[ibdNinja]: Successfully loaded 1 tables with 2 indexes.
====================================================================================
====================================================================================
| TABLE ANALYSIS RESULT |
------------------------------------------------------------------------------------
Table name: d1.sbtest1
Table id: 1064
Number of indexes: 2
Analyze each index:
Analyzing index PRIMARY at level 2...
Analyzing index PRIMARY at level 1...
Analyzing index PRIMARY at level 0...
====================================================================================
| INDEX ANALYSIS RESULT |
------------------------------------------------------------------------------------
Index name: PRIMARY
Index id: 154
Belongs to: d1.sbtest1
Root page no: 4
Num of fields(ALL): 6
Num of levels: 3
Num of pages: 13713
[Non leaf pages: 13]
[Leaf pages: 13700]
--------NON-LEAF-LEVELS--------
Total pages count: 13
Total pages size: 212992 B
Total valid records count: 13712
Total valid records size: 178256 B
[Headers: 68560 B]
[Bodies: 109696 B]
Valid records to non-leaf pages space ratio: 83.69141 %
Total delete-marked records count: 0
Total delete-marked records size: 0 B
Delete-marked recs to non-leaf pages space ratio: 0.00000 %
Total Innodb internal space used: 77092 B
InnoDB internals to non-leaf pages space ratio: 36.19479 %
Total free space: 26204 B
Free space ratio: 12.30281 %
--------LEAF-LEVEL---------------
Total pages count: 13700
Total pages size: 224460800 B
Total valid records count: 1000000
Total valid records size: 208000000 B
[Headers: 7000000 B]
[Bodies: 201000000 B]
Valid records to leaf pages space ratio: 92.66651 %
Total records with instant dropped columns count: 0
Total instant dropped columns size: 0 B
Dropped columns to leaf pages space ratio: 0.00000 %
Total delete-marked records count: 0
Total delete-marked records size: 0 B
Delete-marked records to leaf pages space ratio: 0.00000 %
Total Innodb internal space used: 9274152 B
InnoDB internal space to leaf pages space ratio: 4.13175 %
Total free space: 14186648 B
Free space ratio: 6.32032 %
Analyzing index k_1 at level 1...
Analyzing index k_1 at level 0...
====================================================================================
| INDEX ANALYSIS RESULT |
------------------------------------------------------------------------------------
Index name: k_1
Index id: 155
Belongs to: d1.sbtest1
Root page no: 49
Num of fields(ALL): 2
Num of levels: 2
Num of pages: 833
[Non leaf pages: 1]
[Leaf pages: 832]
--------NON-LEAF-LEVELS--------
Total pages count: 1
Total pages size: 16384 B
Total valid records count: 832
Total valid records size: 14144 B
[Headers: 4160 B]
[Bodies: 9984 B]
Valid records to non-leaf pages space ratio: 86.32812 %
Total delete-marked records count: 0
Total delete-marked records size: 0 B
Delete-marked recs to non-leaf pages space ratio: 0.00000 %
Total Innodb internal space used: 4706 B
InnoDB internals to non-leaf pages space ratio: 28.72314 %
Total free space: 1694 B
Free space ratio: 10.33936 %
--------LEAF-LEVEL---------------
Total pages count: 832
Total pages size: 13631488 B
Total valid records count: 1000000
Total valid records size: 13000000 B
[Headers: 5000000 B]
[Bodies: 8000000 B]
Valid records to leaf pages space ratio: 95.36743 %
Total records with instant dropped columns count: 0
Total instant dropped columns size: 0 B
Dropped columns to leaf pages space ratio: 0.00000 %
Total delete-marked records count: 0
Total delete-marked records size: 0 B
Delete-marked records to leaf pages space ratio: 0.00000 %
Total Innodb internal space used: 5606912 B
InnoDB internal space to leaf pages space ratio: 41.13206 %
Total free space: 24576 B
Free space ratio: 0.18029 %
--analyze-index
PK
$ ibdNinja --analyze-index 154 -f /mysql/data/d1/sbtest1.ibd
====================================================================================
| FILE INFORMATION |
------------------------------------------------------------------------------------
File name: /mysql/data/d1/sbtest1.ibd
File size: 251658240 B
Space id: 2
Page logical size: 16384 B
Page physical size: 16384 B
Total number of pages: 15360
Is compressed page? 0
First page number: 0
SDI root page number: 3
Post antelop: 1
Atomic blobs: 1
Has data dir: 0
Shared: 0
Temporary: 0
Encryption: 0
------------------------------------------------------------------------------------
[ibdNinja]: Successfully loaded 1 tables with 2 indexes.
====================================================================================
Analyzing index PRIMARY at level 2...
Analyzing index PRIMARY at level 1...
Analyzing index PRIMARY at level 0...
====================================================================================
| INDEX ANALYSIS RESULT |
------------------------------------------------------------------------------------
Index name: PRIMARY
Index id: 154
Belongs to: d1.sbtest1
Root page no: 4
Num of fields(ALL): 6
Num of levels: 3
Num of pages: 13713
[Non leaf pages: 13]
[Leaf pages: 13700]
--------NON-LEAF-LEVELS--------
Total pages count: 13
Total pages size: 212992 B
Total valid records count: 13712
Total valid records size: 178256 B
[Headers: 68560 B]
[Bodies: 109696 B]
Valid records to non-leaf pages space ratio: 83.69141 %
Total delete-marked records count: 0
Total delete-marked records size: 0 B
Delete-marked recs to non-leaf pages space ratio: 0.00000 %
Total Innodb internal space used: 77092 B
InnoDB internals to non-leaf pages space ratio: 36.19479 %
Total free space: 26204 B
Free space ratio: 12.30281 %
--------LEAF-LEVEL---------------
Total pages count: 13700
Total pages size: 224460800 B
Total valid records count: 1000000
Total valid records size: 208000000 B
[Headers: 7000000 B]
[Bodies: 201000000 B]
Valid records to leaf pages space ratio: 92.66651 %
Total records with instant dropped columns count: 0
Total instant dropped columns size: 0 B
Dropped columns to leaf pages space ratio: 0.00000 %
Total delete-marked records count: 0
Total delete-marked records size: 0 B
Delete-marked records to leaf pages space ratio: 0.00000 %
Total Innodb internal space used: 9274152 B
InnoDB internal space to leaf pages space ratio: 4.13175 %
Total free space: 14186648 B
Free space ratio: 6.32032 %
index k_1
$ ibdNinja --analyze-index 155 -f /mysql/data/d1/sbtest1.ibd
====================================================================================
| FILE INFORMATION |
------------------------------------------------------------------------------------
File name: /mysql/data/d1/sbtest1.ibd
File size: 251658240 B
Space id: 2
Page logical size: 16384 B
Page physical size: 16384 B
Total number of pages: 15360
Is compressed page? 0
First page number: 0
SDI root page number: 3
Post antelop: 1
Atomic blobs: 1
Has data dir: 0
Shared: 0
Temporary: 0
Encryption: 0
------------------------------------------------------------------------------------
[ibdNinja]: Successfully loaded 1 tables with 2 indexes.
====================================================================================
Analyzing index k_1 at level 1...
Analyzing index k_1 at level 0...
====================================================================================
| INDEX ANALYSIS RESULT |
------------------------------------------------------------------------------------
Index name: k_1
Index id: 155
Belongs to: d1.sbtest1
Root page no: 49
Num of fields(ALL): 2
Num of levels: 2
Num of pages: 833
[Non leaf pages: 1]
[Leaf pages: 832]
--------NON-LEAF-LEVELS--------
Total pages count: 1
Total pages size: 16384 B
Total valid records count: 832
Total valid records size: 14144 B
[Headers: 4160 B]
[Bodies: 9984 B]
Valid records to non-leaf pages space ratio: 86.32812 %
Total delete-marked records count: 0
Total delete-marked records size: 0 B
Delete-marked recs to non-leaf pages space ratio: 0.00000 %
Total Innodb internal space used: 4706 B
InnoDB internals to non-leaf pages space ratio: 28.72314 %
Total free space: 1694 B
Free space ratio: 10.33936 %
--------LEAF-LEVEL---------------
Total pages count: 832
Total pages size: 13631488 B
Total valid records count: 1000000
Total valid records size: 13000000 B
[Headers: 5000000 B]
[Bodies: 8000000 B]
Valid records to leaf pages space ratio: 95.36743 %
Total records with instant dropped columns count: 0
Total instant dropped columns size: 0 B
Dropped columns to leaf pages space ratio: 0.00000 %
Total delete-marked records count: 0
Total delete-marked records size: 0 B
Delete-marked records to leaf pages space ratio: 0.00000 %
Total Innodb internal space used: 5606912 B
InnoDB internal space to leaf pages space ratio: 41.13206 %
Total free space: 24576 B
Free space ratio: 0.18029 %
--parse-page
$ ibdNinja --parse-page 4 -f /mysql/data/d1/sbtest1.ibd
====================================================================================
| FILE INFORMATION |
------------------------------------------------------------------------------------
File name: /mysql/data/d1/sbtest1.ibd
File size: 251658240 B
Space id: 2
Page logical size: 16384 B
Page physical size: 16384 B
Total number of pages: 15360
Is compressed page? 0
First page number: 0
SDI root page number: 3
Post antelop: 1
Atomic blobs: 1
Has data dir: 0
Shared: 0
Temporary: 0
Encryption: 0
------------------------------------------------------------------------------------
[ibdNinja]: Successfully loaded 1 tables with 2 indexes.
====================================================================================
====================================================================================
| PAGE INFORMATION |
------------------------------------------------------------------------------------
Page no: 4
Slibling pages no: NULL [4] NULL
Space id: 2
Page type: INDEX
Lsn: 290607433
FLush lsn: 0
-------------------
Page level: 2
Page size: [logical: 16384 B], [physical: 16384 B]
Number of records: 12
Index id: 154
Belongs to: [table: d1.sbtest1], [index: PRIMARY]
Row format: DYNAMIC
Number dir slots: 4
Heap top: 276
Number of heap: 14
First free rec: 0
Garbage: 0 B
Last insert: 268
Direction: 2
Number direction: 11
Max trx id: 0
====================================================================================
| RECORDS INFORMATION |
------------------------------------------------------------------------------------
=======================================================================
[ROW 1] Length: 13 (5 | 8), Number of fields: 2
-----------------------------------------------------------------------
[HEADER ] 10 00 11 00 0d
[FIELD 1] Name : id
Length: 4
Type : int | LONG | DATA_INT
Value : 80 00 00 01
[FIELD 2] Name : *NODE_PTR(Child page no)
Length: 4
Value : 00 00 00 25
=======================================================================
[ROW 2] Length: 13 (5 | 8), Number of fields: 2
-----------------------------------------------------------------------
[HEADER ] 00 00 19 00 0d
[FIELD 1] Name : id
Length: 4
Type : int | LONG | DATA_INT
Value : 80 00 ab 3d
[FIELD 2] Name : *NODE_PTR(Child page no)
Length: 4
Value : 00 00 00 26
=======================================================================
[ROW 3] Length: 13 (5 | 8), Number of fields: 2
-----------------------------------------------------------------------
[HEADER ] 00 00 21 00 0d
[FIELD 1] Name : id
Length: 4
Type : int | LONG | DATA_INT
Value : 80 02 02 48
[FIELD 2] Name : *NODE_PTR(Child page no)
Length: 4
Value : 00 00 00 27
=======================================================================
[ROW 4] Length: 13 (5 | 8), Number of fields: 2
-----------------------------------------------------------------------
[HEADER ] 04 00 29 00 0d
[FIELD 1] Name : id
Length: 4
Type : int | LONG | DATA_INT
Value : 80 03 59 53
[FIELD 2] Name : *NODE_PTR(Child page no)
Length: 4
Value : 00 00 00 28
=======================================================================
[ROW 5] Length: 13 (5 | 8), Number of fields: 2
-----------------------------------------------------------------------
[HEADER ] 00 00 31 00 0d
[FIELD 1] Name : id
Length: 4
Type : int | LONG | DATA_INT
Value : 80 04 b0 5e
[FIELD 2] Name : *NODE_PTR(Child page no)
Length: 4
Value : 00 00 00 29
=======================================================================
[ROW 6] Length: 13 (5 | 8), Number of fields: 2
-----------------------------------------------------------------------
[HEADER ] 00 00 39 00 0d
[FIELD 1] Name : id
Length: 4
Type : int | LONG | DATA_INT
Value : 80 06 07 69
[FIELD 2] Name : *NODE_PTR(Child page no)
Length: 4
Value : 00 00 00 2a
=======================================================================
[ROW 7] Length: 13 (5 | 8), Number of fields: 2
-----------------------------------------------------------------------
[HEADER ] 00 00 41 00 0d
[FIELD 1] Name : id
Length: 4
Type : int | LONG | DATA_INT
Value : 80 07 5e 74
[FIELD 2] Name : *NODE_PTR(Child page no)
Length: 4
Value : 00 00 00 2b
=======================================================================
[ROW 8] Length: 13 (5 | 8), Number of fields: 2
-----------------------------------------------------------------------
[HEADER ] 04 00 49 00 0d
[FIELD 1] Name : id
Length: 4
Type : int | LONG | DATA_INT
Value : 80 08 b5 7f
[FIELD 2] Name : *NODE_PTR(Child page no)
Length: 4
Value : 00 00 00 2c
=======================================================================
[ROW 9] Length: 13 (5 | 8), Number of fields: 2
-----------------------------------------------------------------------
[HEADER ] 00 00 51 00 0d
[FIELD 1] Name : id
Length: 4
Type : int | LONG | DATA_INT
Value : 80 0a 0c 8a
[FIELD 2] Name : *NODE_PTR(Child page no)
Length: 4
Value : 00 00 00 2d
=======================================================================
[ROW 10] Length: 13 (5 | 8), Number of fields: 2
-----------------------------------------------------------------------
[HEADER ] 00 00 59 00 0d
[FIELD 1] Name : id
Length: 4
Type : int | LONG | DATA_INT
Value : 80 0b 63 95
[FIELD 2] Name : *NODE_PTR(Child page no)
Length: 4
Value : 00 00 00 2e
=======================================================================
[ROW 11] Length: 13 (5 | 8), Number of fields: 2
-----------------------------------------------------------------------
[HEADER ] 00 00 61 00 0d
[FIELD 1] Name : id
Length: 4
Type : int | LONG | DATA_INT
Value : 80 0c ba a0
[FIELD 2] Name : *NODE_PTR(Child page no)
Length: 4
Value : 00 00 00 2f
=======================================================================
[ROW 12] Length: 13 (5 | 8), Number of fields: 2
-----------------------------------------------------------------------
[HEADER ] 00 00 69 ff 64
[FIELD 1] Name : id
Length: 4
Type : int | LONG | DATA_INT
Value : 80 0e 11 ab
[FIELD 2] Name : *NODE_PTR(Child page no)
Length: 4
Value : 00 00 00 30
====================================================================================
| PAGE ANALYSIS RESULT |
------------------------------------------------------------------------------------
Total valid records count: 12
Total valid records size: 156 B
[Headers: 60 B]
[Bodies : 96 B)
Valid records to page space ratio: 0.95215 %
Total delete-marked records count: 0
Total delete-marked records size: 0 B
Delete-marked recs to page space ratio: 0.00000 %
Total innoDB internal space used: 196 B
[FIL HEADER 38 B]
[PAGE HEADER 36 B]
[FSEG HEADER 20 B]
[INFI + SUPRE 26 B]
[RECORD HEADERS 60 B]*
[PAGE DIRECTORY 8 B]
[FIL TRAILER 8 B]
InnoDB internals to page space ratio: 1.19629 %
Total free space: 16092 B
Free space ratio: 98.21777 %
.
