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 ,表结构如下,