【Mysql】innodb_space usage introduction

Time:2019-10-24

Innodb_space git website: https://github.com/jeremycole…

1, install,

# yum -y install ruby
# gem install innodb_ruby

After the installation is complete, verify that innodb_ruby was installed successfully by executing the following command: # innodb_space –help

2. Mysql environment

innodb_file_per_table=ON,innodb_file_format=Barracuda, innodb_file_format_max=Barracuda

3. Basic usage

Against a single space file (ibdata or .ibd):
Option Parameters Description
-f <filename> Load the tablespace file (system or table)
Against a system tablespace which will auto-load file-per-table tablespace files:
Option Parameters Description
-s <filename> Load the system tablespace file (e.g. ibdata1)
-T <table name> Use the given table name.
-I <index name> Use the given index name

Common usage

Although every innodb table has a FRM file, innodb also maintains the innodb table metadata in the system table space, so you can directly analyze the ibdata1 file to understand the storage information of a table
Table structure:

CREATE TABLE `aa` (
  'id' int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'locale id ',
  'name' varchar(20) NOT NULL DEFAULT 'COMMENT' name,
  'rel_id' varchar(50) NOT NULL DEFAULT 'COMMENT' relationship ID',
  'pid' int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'parent ID',
  'level' int(11) NOT NULL DEFAULT '0' COMMENT 'category, 1, province 2, city 3, district 4, county ',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNQ_RID` (`rel_id`) USING BTREE,
  KEY `IDX_PID` (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=' locale table '
1 row in set (0.00 sec)

Table information:

【Mysql】innodb_space usage introduction

4.1 space related

4.1.1 system-spaces

Innodb_space-s ibdata1 system-spaces // lists all table Spaces available in the system, including some basic statistics. This is basically a list of tables:
innodb_space -s ibdata1 system-spaces

[[email protected] ztj]#  innodb_space -s ../ibdata1 system-spaces
name                            pages       indexes     
(system)                        4864        6           
monitor/monitor                 6           1           
monitor/db_monitor              6           1           
monitor/monitor                 6           1           
mysql/innodb_index_stats        6           1           
mysql/innodb_table_stats        6           1           
mysql/slave_master_info         6           1           
mysql/slave_relay_log_info      6           1           
mysql/slave_worker_info         6           1           
opsdb/myapp_db_instance         8           1           
test/aaa                        6           1           
test/area                       8           3           
ztj/aa                          9           3           
ztj/aaa                         8           3           
ztj/dim_library                 4608        4           

4.1.2 space-page-type-regions

// view data distribution
innodb_space -s ibdata1 -T ztj/aa space-page-type-regions

[[email protected] data]#  innodb_space -s ibdata1 -T ztj/aa space-page-type-regions
start       end         count       type                
0           0           1           FSP_HDR             
1           1           1           IBUF_BITMAP         
2           2           1           INODE               
3 5 3 INDEX // INDEX is distributed in page: 3/4/5, where 3 stores Primary information, 4 stores UNQ_RID information and 5 stores IDX_PID information
6           7           2           FREE (ALLOCATED)

For the index name of the table, you can also check the information_schema database table innodb_sys_indexes, innodb_sys_tables:

mysql> SELECT
    -> b.name, a.name, index_id, type, a.space, a.PAGE_NO
    -> FROM
    -> information_schema.INNODB_SYS_INDEXES a,
    -> information_schema.INNODB_SYS_TABLES b
    -> WHERE
    -> a.table_id = b.table_id AND a.space <> 0;
+------------------------------+-------------------------------------------+----------+------+-------+---------+
| name                         | name                                      | index_id | type | space | PAGE_NO |
+------------------------------+-------------------------------------------+----------+------+-------+---------+
| monitor/ll_monitor          | GEN_CLUST_INDEX                           |       21 |    1 |     5 |       3 |
| monitor/db_monitor           | GEN_CLUST_INDEX                           |       22 |    1 |     6 |       3 |
| monitor/monitor              | GEN_CLUST_INDEX                           |       20 |    1 |     4 |       3 |
| mysql/innodb_index_stats     | PRIMARY                                   |       23 |    3 |     7 |       3 |
| mysql/innodb_table_stats     | PRIMARY                                   |       24 |    3 |     8 |       3 |
| mysql/slave_master_info      | PRIMARY                                   |       25 |    3 |     9 |       3 |
| mysql/slave_relay_log_info   | PRIMARY                                   |       26 |    3 |    10 |       3 |
| mysql/slave_worker_info      | PRIMARY                                                                  |       47 |    3 |    18 |       3 |
| ztj/aa                      | UNQ_RID                                   |       51 |    2 |    19 |       4 |
| ztj/aa                      | IDX_PID                                   |       62 |    0 |    19 |       6 |
| ztj/aaa                     | PRIMARY                                   |       63 |    3 |    25 |       3 |
| ztj/aaa                     | UNQ_RID                                   |       64 |    2 |    25 |       4 |
| ztj/aaa                     | IDX_PID                                   |       65 |    0 |    25 |       5 |
+------------------------------+-------------------------------------------+----------+------+-------+---------+

4.1.3 space-indexes

// index structure, data allocation
innodb_space -s ibdata1 -T ztj/aa space-indexes

[[email protected] data]# innodb_space -s ibdata1 -T ztj/aa space-indexes
id          name                            root        fseg        used        allocated   fill_factor 
50          PRIMARY                         3           internal    1           1           100.00%     
50          PRIMARY                         3           leaf        0           0           0.00%       
51          UNQ_RID                         4           internal    1           1           100.00%     
51          UNQ_RID                         4           leaf        0           0           0.00%       
52          IDX_PID                         5           internal    1           1           100.00%     
52          IDX_PID                         5           leaf        0           0           0.00% 

4.1.4 space-page-type-summary

// view the proportion of the table pages
innodb_space -s ../ibdata1 -T ztj/aa space-page-type-summary

[[email protected] ztj]#  innodb_space -s ../ibdata1 -T ztj/aa space-page-type-summary
type                count       percent     description         
INDEX               3           37.50       B+Tree index        
ALLOCATED           2           25.00       Freshly allocated   
INODE               1           12.50       File segment inode  
IBUF_BITMAP         1           12.50       Insert buffer bitmap
FSP_HDR             1           12.50       File space header

4.1.5 space-index-pages-summary

// see how the index of the table is distributed in pages
innodb_space -s ibdata1 -T ztj/aaa space-index-pages-summar |head -n 10
innodb_space -f ztj/aaa.ibd space-index-pages-summary |head -n 10

[[email protected] data]#  innodb_space -s ibdata1  -T ztj/aaa space-index-pages-summary  |head -n 10
page        index   level   data    free    records 
16 // height of primary key B+ tree is page level+1=1
4           64      0       380     15866   16      
5           65      0       208     16040   16      
6           0       0       0       16384   0       
7           0       0       0       16384   0 

[[email protected] ztj]#  innodb_space -f dim_library.ibd  space-index-pages-summary |head -n 10
page        index   level   data    free    records 
3 28 2 45 16207 3 // primary key B+ tree index height is page level+1=3
4           29      1       8470    7598    385     
5           30      1       11487   4575    547     
6           31      1       7980    8122    380     
7           28      0       7526    8690    72      
8           28      0       15137   1045    144     
9           28      0       15074   1108    144     
10          28      0       15110   1072    144     
11          28      0       15104   1080    143

4.2 page related

4.2.1 page-account

// view the description of the page

[[email protected] ztj]#  innodb_space -s ../ibdata1 -T ztj/aa -p 3 page-account
Accounting for page 3:
  Page type is INDEX (B+Tree index, table and index data stored in B+Tree structure).
  Extent descriptor for pages 0-63 is at page 0, offset 158.
  Extent is not fully allocated to an fseg; may be a fragment extent.
  Page is marked as used in extent descriptor.
  Extent is in free_frag list of space.
  Page is in fragment array of fseg 1.
  Fseg is in internal fseg of index 63.
  Index root is page 3.
  Index is ztj /aa.PRIMARY.
[[email protected] ztj]# 
[[email protected] ztj]#  innodb_space -s ../ibdata1 -T ztj/aa -p 4 page-account
Accounting for page 4:
  Page type is INDEX (B+Tree index, table and index data stored in B+Tree structure).
  Extent descriptor for pages 0-63 is at page 0, offset 158.
  Extent is not fully allocated to an fseg; may be a fragment extent.
  Page is marked as used in extent descriptor.
  Extent is in free_frag list of space.
  Page is in fragment array of fseg 3.
  Fseg is in internal fseg of index 64.
  Index root is page 4.
  Index is ztj/aa.UNQ_RID.

4.2.2 page-records

// view secondary index store information
innodb_space -s ibdata1 -T ztj/aa -p 4 page-records

[[email protected] data]#  innodb_space -s ibdata1 -T ztj/aa -p 4 page-records
Record 126: (rel_id="10000") → (id=10000)
Record 141: (rel_id="1000010001") → (id=10001)
Record 161: (rel_id="100001000110002") → (id=10002)
Record 186: (rel_id="100001000110003") → (id=10003)
Record 211: (rel_id="100001000110004") → (id=10004)
Record 236: (rel_id="100001000110005") → (id=10005)
Record 261: (rel_id="100001000110006") → (id=10006)
Record 286: (rel_id="100001000110007") → (id=10007)
Record 311: (rel_id="100001000110008") → (id=10008)
Record 336: (rel_id="1000010009") → (id=10009)
Record 356: (rel_id="100001000910010") → (id=10010)
Record 381: (rel_id="100001000910011") → (id=10011)
Record 406: (rel_id="100001000910012") → (id=10012)
Record 431: (rel_id="100001000910013") → (id=10013)
Record 456: (rel_id="100001000910014") → (id=10014)
Record 481: (rel_id="100001000910015") → (id=10015)

 innodb_space -s ibdata1 -T ztj/aa -p 5 page-records
[[email protected] data]#  innodb_space -s ibdata1 -T ztj/aa -p 5 page-records
Record 125: (pid=0) → (id=10000)
Record 138: (pid=10000) → (id=10001)
Record 151: (pid=10000) → (id=10009)
Record 164: (pid=10001) → (id=10002)
Record 177: (pid=10001) → (id=10003)
Record 190: (pid=10001) → (id=10004)
Record 203: (pid=10001) → (id=10005)
Record 216: (pid=10001) → (id=10006)
Record 229: (pid=10001) → (id=10007)
Record 242: (pid=10001) → (id=10008)
Record 255: (pid=10009) → (id=10010)
Record 268: (pid=10009) → (id=10011)
Record 281: (pid=10009) → (id=10012)
Record 294: (pid=10009) → (id=10013)
Record 307: (pid=10009) → (id=10014)
Record 320: (pid=10009) → (id=10015)

4.2.3 page-records

// primary key index store information
innodb_space -s ibdata1 -T ztj/aa -p 3 page-records

[[email protected] data]#  innodb_space -s ibdata1 -T ztj/aa -p 3 page-records
Record 127: (id=10000) → (name="\xE5\x8C\x97\xE4\xBA\xAC", rel_id="10000", pid=0, level=1)
Record 170: (id=10001) → (name="\xE6\x9C\x9D\xE9\x98\xB3\xE5\x8C\xBA", rel_id="1000010001", pid=10000, level=2)
Record 221: (id=10002) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE4\xBB\xA5\xE5\x86\x85", rel_id="100001000110002", pid=10001, level=3)
Record 280: (id=10003) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE5\x88\xB0\xE5\x9B\x9B\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000110003", pid=10001, level=3)
Record 348: (id=10004) → (name="\xE5\x9B\x9B\xE7\x8E\xAF\xE5\x88\xB0\xE4\xBA\x94\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000110004", pid=10001, level=3)
Record 416: (id=10005) → (name="\xE4\xBA\x94\xE7\x8E\xAF\xE5\x88\xB0\xE5\x85\xAD\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000110005", pid=10001, level=3)
Record 484: (id=10006) → (name="\xE7\xAE\xA1\xE5\xBA\x84", rel_id="100001000110006", pid=10001, level=3)
Record 537: (id=10007) → (name="\xE5\x8C\x97\xE8\x8B\x91", rel_id="100001000110007", pid=10001, level=3)
Record 590: (id=10008) → (name="\xE5\xAE\x9A\xE7\xA6\x8F\xE5\xBA\x84", rel_id="100001000110008", pid=10001, level=3)
Record 646: (id=10009) → (name="\xE6\xB5\xB7\xE6\xB7\x80\xE5\x8C\xBA", rel_id="1000010009", pid=10000, level=2)
Record 697: (id=10010) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE4\xBB\xA5\xE5\x86\x85", rel_id="100001000910010", pid=10009, level=3)
Record 756: (id=10011) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE5\x88\xB0\xE5\x9B\x9B\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000910011", pid=10009, level=3)
Record 824: (id=10012) → (name="\xE5\x9B\x9B\xE7\x8E\xAF\xE5\x88\xB0\xE4\xBA\x94\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000910012", pid=10009, level=3)
Record 892: (id=10013) → (name="\xE4\xBA\x94\xE7\x8E\xAF\xE5\x88\xB0\xE5\x85\xAD\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000910013", pid=10009, level=3)
Record 960: (id=10014) → (name="\xE5\x85\xAD\xE7\x8E\xAF\xE4\xBB\xA5\xE5\xA4\x96", rel_id="100001000910014", pid=10009, level=3)
Record 1019: (id=10015) → (name="\xE8\xA5\xBF\xE4\xB8\x89\xE6\x97\x97", rel_id="100001000910015", pid=10009, level=3)

Conclusion: secondary indexes store primary key values. The primary key page stores all the data

4.3 the index related to the

4.3.1 index-recurse

// recurse the whole B+ tree, scanning all pages
innodb_space -s ../ibdata1 -T ztj/aaa -I PRIMARY index-recurse

[[email protected] ztj]#  innodb_space -s ../ibdata1 -T ztj/aaa -I PRIMARY index-recurse
ROOT NODE #3: 16 records, 948 bytes
  RECORD: (id=10000) → (name="\xE5\x8C\x97\xE4\xBA\xAC", rel_id="10000", pid=0, level=1)
  RECORD: (id=10001) → (name="\xE6\x9C\x9D\xE9\x98\xB3\xE5\x8C\xBA", rel_id="1000010001", pid=10000, level=2)
  RECORD: (id=10002) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE4\xBB\xA5\xE5\x86\x85", rel_id="100001000110002", pid=10001, level=3)
  RECORD: (id=10003) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE5\x88\xB0\xE5\x9B\x9B\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000110003", pid=10001, level=3)
  RECORD: (id=10004) → (name="\xE5\x9B\x9B\xE7\x8E\xAF\xE5\x88\xB0\xE4\xBA\x94\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000110004", pid=10001, level=3)
  RECORD: (id=10005) → (name="\xE4\xBA\x94\xE7\x8E\xAF\xE5\x88\xB0\xE5\x85\xAD\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000110005", pid=10001, level=3)
  RECORD: (id=10006) → (name="\xE7\xAE\xA1\xE5\xBA\x84", rel_id="100001000110006", pid=10001, level=3)
  RECORD: (id=10007) → (name="\xE5\x8C\x97\xE8\x8B\x91", rel_id="100001000110007", pid=10001, level=3)
  RECORD: (id=10008) → (name="\xE5\xAE\x9A\xE7\xA6\x8F\xE5\xBA\x84", rel_id="100001000110008", pid=10001, level=3)
  RECORD: (id=10009) → (name="\xE6\xB5\xB7\xE6\xB7\x80\xE5\x8C\xBA", rel_id="1000010009", pid=10000, level=2)
  RECORD: (id=10010) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE4\xBB\xA5\xE5\x86\x85", rel_id="100001000910010", pid=10009, level=3)
  RECORD: (id=10011) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE5\x88\xB0\xE5\x9B\x9B\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000910011", pid=10009, level=3)
  RECORD: (id=10012) → (name="\xE5\x9B\x9B\xE7\x8E\xAF\xE5\x88\xB0\xE4\xBA\x94\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000910012", pid=10009, level=3)
  RECORD: (id=10013) → (name="\xE4\xBA\x94\xE7\x8E\xAF\xE5\x88\xB0\xE5\x85\xAD\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000910013", pid=10009, level=3)
  RECORD: (id=10014) → (name="\xE5\x85\xAD\xE7\x8E\xAF\xE4\xBB\xA5\xE5\xA4\x96", rel_id="100001000910014", pid=10009, level=3)
  RECORD: (id=10015) → (name="\xE8\xA5\xBF\xE4\xB8\x89\xE6\x97\x97", rel_id="100001000910015", pid=10009, level=3)

4.3.2 index-record-offsets

// recurses the entire index tree like the index-record, but prints only the index page offset
innodb_space -s ../ibdata1 -T ztj/aaa -I PRIMARY index-record-offsets

[[email protected] ztj]#  innodb_space -s ../ibdata1 -T ztj/aaa -I PRIMARY index-record-offsets
page_offset         record_offset       
3                   127                 
3                   170                 
3                   221                 
3                   280                 
3                   348                 
3                   416                 
3                   484                 
3                   537                 
3                   590                 
3                   646                 
3                   697                 
3                   756                 
3                   824                 
3                   892                 
3                   960                 
3                   1019  

4.4 record the relevant

4.4.1 record-history

// recurse the whole B+ tree, scanning all pages
innodb_space -s ../ibdata1 -T ztj/aaa -I PRIMARY index-recurse

[[email protected] ztj]#  innodb_space -s ../ibdata1 -T ztj/aaa -I PRIMARY -p 3 -R 127 record-history
Transaction   Type                Undo record
(n/a)         insert              (id=10000) → ()

5. Are the indexes in physical or logical order?

Data within the page: logical and orderly, physical disk ⼀ ⽆ sequence is allowed in the page, in the form of a pointer to the connection
Data, reach logical order [normal 1, 2, 3, 4, 5. Storage is: 1, 3, 2, 5, 4. 1 link 2 link 3 chain
After 4 links between 5 】 ⻚ and ⻚ is: logical and orderly, physically ⽆ sequence. ⾃ ID, logical and orderly
.