MySQL DBA利器innodb_ruby

innodb_ruby简介

innodb_ruby是一款用ruby写的用来分析 innodb 物理文件的专业DBA工具,可以通过这款工具来窥探innodb内部的一些结构。
注意不要在生产环境中使用此工具,以避对线上服务造成影响。官方网址 https://rubygems.org/gems/innodb_ruby

注意如果(Linux)平台安装中遇到错误一般情况是由于缺少依赖库造成的,可以先安装 sudo apt-get install libxslt1-dev libxml2-dev 相关库。

命令语法

在执行以下命令时,建议切换到MySQL 的 datadir 目录里。

sxf@ubuntu:~$ innodb_space --help

Usage: innodb_space <options> <mode>
innodb_space <选项> <模式>
命令主要分 options 和 mode 两大部分。

Invocation examples:

  innodb_space -s ibdata1 [-T tname [-I iname]] [options] <mode>
    Use ibdata1 as the system tablespace and load the tname table (and the
    iname index for modes that require it) from data located in the system
    tablespace data dictionary. This will automatically generate a record
    describer for any indexes.

    参数:
    -s 参数指的是系统表空间文件 ibdata1, 这个一般在datadir目录里可以找到。
    -T 数据表名称,一般为数据库其中一个表的物理文件路径
    -I 表示索引的名称, 如果是主键的话,直接填写 -I PRIMARY 即可,此时可省略此参数

    如 innodb_space -s ibdata1 -T lab/tb space-indexes,则表示查看lab数据库的tb表的索引统计信息


  innodb_space -f tname.ibd [-r ./desc.rb -d DescClass] [options] <mode>
    Use the tname.ibd table (and the DescClass describer where required).

The following options are supported:

  --help, -?
    Print this usage text.

  --trace, -t
    Enable tracing of all data read. Specify twice to enable even more
    tracing (including reads during opening of the tablespace) which can
    be quite noisy.

  --system-space-file, -s <arg>
    Load the system tablespace file or files <arg>: Either a single file e.g.
    "ibdata1", a comma-delimited list of files e.g. "ibdata1,ibdata1", or a
    directory name. If a directory name is provided, it will be scanned for all
    files named "ibdata?" which will then be sorted alphabetically and used to
    load the system tablespace.

  --table-name, -T <name>
    Use the table name <name>.
    表名

  --index-name, -I <name>
    Use the index name <name>.
    索引名

  --space-file, -f <file>
    Load the tablespace file <file>.

  --page, -p <page>
    Operate on the page <page>.
    页数

  --level, -l <level>
    Operate on the level <level>.
    索引树层级数,一般不会超过3

  --list, -L <list>
    Operate on the list <list>.

  --fseg-id, -F <fseg_id>
      Operate on the file segment (fseg) <fseg_id>.

  --require, -r <file>
    Use Ruby's "require" to load the file <file>. This is useful for loading
    classes with record describers.

  --describer, -d <describer>
    Use the named record describer to parse records in index pages.

The following modes are supported:
模式项列表

  系统表空间
  system-spaces
    Print a summary of all spaces in the system.
    

  数据字典表(information_schema中数据库SYS_TABLES表内容,下同)
  data-dictionary-tables
    Print all records in the SYS_TABLES data dictionary table.

  data-dictionary-columns
    Print all records in the SYS_COLUMNS data dictionary table.

  data-dictionary-indexes
    Print all records in the SYS_INDEXES data dictionary table.

  data-dictionary-fields
    Print all records in the SYS_FIELDS data dictionary table.

  
  汇总表空间中的所有页信息,需要使用 --page/-p 参数指定页数
  space-summary
    Summarize all pages within a tablespace. A starting page number can be
    provided with the --page/-p argument.

  汇总表空间中的所有索引页信息,对于分析每个页记录填充率情况的时候很有用,同样需要使用--page/-p指定页数
  space-index-pages-summary
    Summarize all "INDEX" pages within a tablespace. This is useful to analyze
    page fill rates and record counts per page. In addition to "INDEX" pages,
    "ALLOCATED" pages are also printed and assumed to be completely empty.
    A starting page number can be provided with the --page/-p argument.

  与space-index-pages-summary差不多,但只显示一些摘要信息,需要配合参数一块使用
  space-index-fseg-pages-summary
    The same as space-index-pages-summary but only iterate one fseg, provided
    with the --fseg-id/-F argument.

  space-index-pages-free-plot
    Use Ruby's gnuplot module to produce a scatterplot of page free space for
    all "INDEX" and "ALLOCATED" pages in a tablespace. More aesthetically
    pleasing plots can be produced with space-index-pages-summary output,
    but this is a quick and easy way to produce a passable plot. A starting
    page number can be provided with the --page/-p argument.

  遍历空间中的所有页面,统计每个类型的页共占用了多少页
  space-page-type-regions
    Summarize all contiguous regions of the same page type. This is useful to
    provide an overall view of the space and allocations within it. A starting
    page number can be provided with the --page/-p argument.

  按类型汇总所有页面信息
  space-page-type-summary
    Summarize all pages by type. A starting page number can be provided with
    the --page/-p argument.

  表空间中所有索引统计信息(系统空间或每个文件表空间)
  space-indexes
    Summarize all indexes (actually each segment of the indexes) to show
    the number of pages used and allocated, and the segment fill factor.

  space-lists
    Print a summary of all lists in a space.

  space-list-iterate
    Iterate through the contents of a space list.

  space-extents
    Iterate through all extents, printing the extent descriptor bitmap.

  space-extents-illustrate
    Iterate through all extents, illustrating the extent usage using ANSI
    color and Unicode box drawing characters to show page usage throughout
    the space.

  space-extents-illustrate-svg
    Iterate through all extents, illustrating the extent usage in SVG format
    printed to stdout to show page usage throughout the space.

  space-lsn-age-illustrate
    Iterate through all pages, producing a heat map colored by the page LSN
    using ANSI color and Unicode box drawing characters, allowing the user to
    get an overview of page modification recency.

  space-lsn-age-illustrate-svg
    Iterate through all pages, producing a heat map colored by the page LSN
    producing SVG format output, allowing the user to get an overview of page
    modification recency.

  space-inodes-fseg-id
    Iterate through all inodes, printing only the FSEG ID.

  space-inodes-summary
    Iterate through all inodes, printing a short summary of each FSEG.

  space-inodes-detail
    Iterate through all inodes, printing a detailed report of each FSEG.

  通过递归整个B+树(通过递归扫描所有页面,而不仅仅是按列表的叶子页面)来执行索引扫描(执行完整索引扫描)
  index-recurse
    Recurse an index, starting at the root (which must be provided in the first
    --page/-p argument), printing the node pages, node pointers (links), leaf
    pages. A record describer must be provided with the --describer/-d argument
    to recurse indexes (in order to parse node pages).

  将索引作为索引递归进行递归处理,但在索引页中打印每条记录的偏移量
  index-record-offsets
    Recurse an index as index-recurse does, but print the offsets of each
    record within the page.

  index-digraph
    Recurse an index as index-recurse does, but print a dot-compatible digraph
    instead of a human-readable summary.

  打印指定 level 级别的所有page信息
  index-level-summary
    Print a summary of all pages at a given level (provided with the --level/-l
    argument) in an index.

  index-fseg-internal-lists
  index-fseg-leaf-lists
    Print a summary of all lists in an index file segment. Index root page must
    be provided with --page/-p.

  index-fseg-internal-list-iterate
  index-fseg-leaf-list-iterate
    Iterate the file segment list (whose name is provided in the first --list/-L
    argument) for internal or leaf pages for a given index (whose root page
    is provided in the first --page/-p argument). The lists used for each
    index are "full", "not_full", and "free".

  index-fseg-internal-frag-pages
  index-fseg-leaf-frag-pages
    Print a summary of all fragment pages in an index file segment. Index root
    page must be provided with --page/-p.

  page-dump
    Dump the contents of a page, using the Ruby pp ("pretty-print") module.

  page-account
    Account for a page's usage in FSEGs.

  page-validate
    Validate the contents of a page.

  页目录字典记录
  page-directory-summary
    Summarize the record contents of the page directory in a page. If a record
    describer is available, the key of each record will be printed.

  对一个页的所有记录进行汇总
  page-records
    Summarize all records within a page.

  详细说明一个页面的内容,并且根据类型进行着色显示
  page-illustrate
    Produce an illustration of the contents of a page.

  record-dump
    Dump a detailed description of a record and the data it contains. A record
    offset must be provided with -R/--record.

  record-history
    Summarize the history (undo logs) for a record. A record offset must be
    provided with -R/--record.

  undo-history-summary
    Summarize all records in the history list (undo logs).

  undo-record-dump
    Dump a detailed description of an undo record and the data it contains.
    A record offset must be provided with -R/--record.

参数详解

测试数据库 lab ,表名 tb ,表结构如下,

CREATE TABLE `tb` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `num` int(11) NOT NULL,
  `age` tinyint(1) unsigned DEFAULT '13',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40001 DEFAULT CHARSET=latin1;

这里先添加了3万多的测试数据。

系统表空间 system-spaces

root@ubuntu:/var/lib/mysql# innodb_space -s ibdata1 -T lab/tb system-spaces
name                            pages       indexes
(system)                        768         7
lab/tb                          448         1
mysql/engine_cost               6           1
mysql/gtid_executed             6           1
mysql/help_category             7           2
mysql/help_keyword              15          2
mysql/help_relation             9           1
mysql/help_topic                576         2
mysql/innodb_index_stats        6           1
mysql/innodb_table_stats        6           1
mysql/plugin                    6           1
mysql/server_cost               6           1
mysql/servers                   6           1
mysql/slave_master_info         6           1
mysql/slave_relay_log_info      6           1
mysql/slave_worker_info         6           1
mysql/time_zone                 6           1
mysql/time_zone_leap_second     6           1
mysql/time_zone_name            6           1
mysql/time_zone_transition      6           1
mysql/time_zone_transition_type 6           1
sys/sys_config                  6           1

innodb_space列出所有物理对象的数量。这些文件一般在相应数据库中可以找到扩展名为.ibd 的文件,如 sys库的sys_config.ibd文件

索引结构、数据分配情 space-indexes

root@ubuntu:/var/lib/mysql# innodb_space -s ibdata1 -T lab/tb space-indexes
id          name                            root        fseg        fseg_id     used        allocated   fill_factor
43          PRIMARY                         3           internal    1           1           1           100.00%
43          PRIMARY                         3           leaf        2           75          96          78.12%

列说明:
name:索引的名称,PRIMARY代表的就是聚集索引,因为InnoDB表是聚集所以组织表,行记录就是聚集索引;idx_c就是辅助索引的名称。
root:索引中根节点的page号;可以看出聚集索引的根节点是第3个page(为什么是从第三个page开始,看下文space-page-type-regions),辅助索引的根节点是第4个page。
fseg:page的说明,internal表示非叶子节点或属于根节点,leaf表示叶子节点(也就是数据页)。
used:索引使用了多少个page,可以看出聚集索引的根节点点使用了1个page,叶子节点使用了3个page;辅助索引idx_c的叶子节点使用了1个page。
allocated:索引分配了多少个page,可以看出聚集索引的根节点分配了1个page,叶子节点分配了3个page;辅助索引idx_c的叶子节点分配了1个page
fill_factor:索引的填充度,所有的填充度都是100%。

遍历空间中的所有页面,统计每个类型的页共占用了多少 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
6           6           1           FREE (INDEX)
7           36          30          INDEX
37          63          27          FREE (ALLOCATED)
64          106         43          INDEX
107         127         21          FREE (ALLOCATED)

列说明:
start:从第几个page开始。
end:从第几个page结束。
count:占用了多少个page。
type:page的类型。

从上面的结果可以看出:“FSP_HDR”、“IBUF_BITMAP”、“INODE”是分别占用了0,1,2号的page,从3号page开始才是存放数据和索引的页(Index)
接下来,根据得到的聚集索引和辅助索引的根节点来获取索引上的其他page的信息。

索引级数统计信息 index-level-summary

root@ubuntu:/var/lib/mysql# innodb_space -s ibdata1 -T lab/tb -I PRIMARY -l 0 index-level-summary
page    index   level   data    free    records min_key
4       43      0       14952   1036    534     id=1
5       43      0       14952   1036    534     id=535
7       43      0       14952   1036    534     id=1069
8       43      0       14952   1036    534     id=1603
9       43      0       14952   1036    534     id=2137
10      43      0       14952   1036    534     id=2671
11      43      0       14952   1036    534     id=3205
12      43      0       14952   1036    534     id=3739
13      43      0       14952   1036    534     id=4273
14      43      0       14952   1036    534     id=4807
15      43      0       14952   1036    534     id=5341
16      43      0       14952   1036    534     id=5875
17      43      0       14952   1036    534     id=6409
18      43      0       14952   1036    534     id=6943
19      43      0       14952   1036    534     id=7477
20      43      0       14952   1036    534     id=8011
21      43      0       14952   1036    534     id=8545
22      43      0       14952   1036    534     id=9079
23      43      0       14952   1036    534     id=9613
24      43      0       14952   1036    534     id=10147
25      43      0       14952   1036    534     id=10681
26      43      0       14952   1036    534     id=11215
27      43      0       14952   1036    534     id=11749
28      43      0       14952   1036    534     id=12283
29      43      0       14952   1036    534     id=12817
30      43      0       14952   1036    534     id=13351
31      43      0       14952   1036    534     id=13885
32      43      0       14952   1036    534     id=14419
33      43      0       14952   1036    534     id=14953
34      43      0       14952   1036    534     id=15487
35      43      0       14952   1036    534     id=16021
36      43      0       14952   1036    534     id=16555
64      43      0       14952   1036    534     id=17089
65      43      0       14952   1036    534     id=17623
66      43      0       14952   1036    534     id=18157
67      43      0       14952   1036    534     id=18691
68      43      0       14952   1036    534     id=19225
69      43      0       14952   1036    534     id=19759
70      43      0       14952   1036    534     id=20293
71      43      0       14952   1036    534     id=20827
72      43      0       14952   1036    534     id=21361
73      43      0       14952   1036    534     id=21895
74      43      0       14952   1036    534     id=22429
75      43      0       14952   1036    534     id=22963
76      43      0       14952   1036    534     id=23497
77      43      0       14952   1036    534     id=24031
78      43      0       14952   1036    534     id=24565
79      43      0       14952   1036    534     id=25099
80      43      0       14952   1036    534     id=25633
81      43      0       14952   1036    534     id=26167
82      43      0       14952   1036    534     id=26701
83      43      0       14952   1036    534     id=27235
84      43      0       14952   1036    534     id=27769
85      43      0       14952   1036    534     id=28303
86      43      0       14952   1036    534     id=28837
87      43      0       14952   1036    534     id=29371
88      43      0       14952   1036    534     id=29905
89      43      0       14952   1036    534     id=30439
90      43      0       14952   1036    534     id=30973
91      43      0       14952   1036    534     id=31507
92      43      0       14952   1036    534     id=32041
93      43      0       14952   1036    534     id=32575
94      43      0       14952   1036    534     id=33109
95      43      0       14952   1036    534     id=33643
96      43      0       14952   1036    534     id=34177
97      43      0       14952   1036    534     id=34711
98      43      0       14952   1036    534     id=35245
99      43      0       14952   1036    534     id=35779
100     43      0       14952   1036    534     id=36313
101     43      0       14952   1036    534     id=36847
102     43      0       14952   1036    534     id=37381
103     43      0       14952   1036    534     id=37915
104     43      0       14952   1036    534     id=38449
105     43      0       14952   1036    534     id=38983
106     43      0       13552   2460    484     id=39517
root@ubuntu:/var/lib/mysql# innodb_space -s ibdata1 -T lab/tb -I PRIMARY -l 1 index-level-summary
page    index   level   data    free    records min_key
3       43      1       1050    15168   75      id=1
root@ubuntu:/var/lib/mysql# innodb_space -s ibdata1 -T lab/tb -I PRIMARY -l 2 index-level-summary
page    index   level   data    free    records min_key

这里我们分别查看了0、1和2级别的信息,但2级别是没有任何信息输出的,所以这里的索引树高度是2。

列说明:

page 页数,可以看到并不一定是连续的
index 待确认
level 级数
data 数据大小
free 空闲大小
records 记录个数
min_key 最小记录id,每个page都会有一个最小记录id,二分法查找记录时使用.

查看汇总页记录 page-records

root@ubuntu:/var/lib/mysql# innodb_space -s ibdata1 -T lab/tb -p 3 page-records
Record 126: (id=1) → #4
Record 140: (id=535) → #5
Record 154: (id=1069) → #7
Record 168: (id=1603) → #8
Record 182: (id=2137) → #9
Record 196: (id=2671) → #10
Record 210: (id=3205) → #11
Record 224: (id=3739) → #12
Record 238: (id=4273) → #13
Record 252: (id=4807) → #14
Record 266: (id=5341) → #15
Record 280: (id=5875) → #16
Record 294: (id=6409) → #17
Record 308: (id=6943) → #18
Record 322: (id=7477) → #19
Record 336: (id=8011) → #20
Record 350: (id=8545) → #21
Record 364: (id=9079) → #22
Record 378: (id=9613) → #23
Record 392: (id=10147) → #24
Record 406: (id=10681) → #25
Record 420: (id=11215) → #26
Record 434: (id=11749) → #27
Record 448: (id=12283) → #28
Record 462: (id=12817) → #29
Record 476: (id=13351) → #30
Record 490: (id=13885) → #31
Record 504: (id=14419) → #32
Record 518: (id=14953) → #33
Record 532: (id=15487) → #34
Record 546: (id=16021) → #35
Record 560: (id=16555) → #36
Record 574: (id=17089) → #64
Record 588: (id=17623) → #65
Record 602: (id=18157) → #66
Record 616: (id=18691) → #67
Record 630: (id=19225) → #68
Record 644: (id=19759) → #69
Record 658: (id=20293) → #70
Record 672: (id=20827) → #71
Record 686: (id=21361) → #72
Record 700: (id=21895) → #73
Record 714: (id=22429) → #74
Record 728: (id=22963) → #75
Record 742: (id=23497) → #76
Record 756: (id=24031) → #77
Record 770: (id=24565) → #78
Record 784: (id=25099) → #79
Record 798: (id=25633) → #80
Record 812: (id=26167) → #81
Record 826: (id=26701) → #82
Record 840: (id=27235) → #83
Record 854: (id=27769) → #84
Record 868: (id=28303) → #85
Record 882: (id=28837) → #86
Record 896: (id=29371) → #87
Record 910: (id=29905) → #88
Record 924: (id=30439) → #89
Record 938: (id=30973) → #90
Record 952: (id=31507) → #91
Record 966: (id=32041) → #92
Record 980: (id=32575) → #93
Record 994: (id=33109) → #94
Record 1008: (id=33643) → #95
Record 1022: (id=34177) → #96
Record 1036: (id=34711) → #97
Record 1050: (id=35245) → #98
Record 1064: (id=35779) → #99
Record 1078: (id=36313) → #100
Record 1092: (id=36847) → #101
Record 1106: (id=37381) → #102
Record 1120: (id=37915) → #103
Record 1134: (id=38449) → #104
Record 1148: (id=38983) → #105
Record 1162: (id=39517) → #106

每一行代表一个page记录,id=1表示这个表中的记录最小主键id=1, #4则表示在页号是4。

上面我们使用 index-level-summary 查看的level 1级别的索引page 3中共有75条记录,最小id为1,这里通过 page-records确认了这一点。

这里查看的是聚集索引(主键索引),如果是普通索引的话,会看到打印内容有一些不一样,类似于 RECORD: (age=21) → (id=100) 这种的,即指向了主键值。

现在我们在看一下page 4中的内容

root@ubuntu:/var/lib/mysql# innodb_space -s ibdata1 -T lab/tb -p 4 page-records | head
Record 126: (id=1) → (num=1, age=13)
Record 154: (id=2) → (num=2, age=13)
Record 182: (id=3) → (num=3, age=13)
Record 210: (id=4) → (num=4, age=13)
Record 238: (id=5) → (num=5, age=13)

我们发现输出的内容与page 3 的有些不一样,这里输出的是完整的详情记录,但page 3是一个一条记录与页的对应关系,我们一般称其为页目录。

推荐阅读

http://vlambda.com/wz_xeipHG6Q3r.html
https://www.cnblogs.com/cnzeno/p/6322842.html
https://blog.csdn.net/weixin_34368949/article/details/91381989
https://www.jianshu.com/p/c51873ea129a