InnoDB 表空间可视化工具innodb_ruby
1. 工具安装
1.1 安装ruby
操作系统版本：CentOS Linux release 7.6.1810 (Core)，默认的yum源安装后ruby的版本是2.0 ,而innodb_ruby需要2.2及以上版本，因此修改yum源，再安装指定高版本
# yum install -y centos-release-scl-rh 会在/etc/yum.repos.d/目录下多出一个CentOS-SCLo-scl-rh.repo源,然后安装2.3版本
yum install rh-ruby27 rh-ruby27-ruby-devel -y完成安装后切换版本，如果之前安装了2.2以下版本，此步骤必须做，以免默认使用的依旧是低版本的
# scl enable rh-ruby27 bash完成后检查一下版本
# ruby --version
ruby 2.7.1p83 (2020-03-31 revision a0c7c23c9c) [x86_64-linux]
# gem --version
3.1.21.2 安装innodb_ruby
安装完ruby,再进行安装即可
# gem install innodb_ruby
Fetching bindata-1.8.3.gem
Successfully installed bindata-1.8.3
Fetching rake-13.0.3.gem
Successfully installed rake-13.0.3
Fetching digest-crc-0.6.3.gem
Building native extensions. This could take a while...
Successfully installed digest-crc-0.6.3
Fetching innodb_ruby-0.9.16.gem
Successfully installed innodb_ruby-0.9.16
Parsing documentation for bindata-1.8.3
Installing ri documentation for bindata-1.8.3
Parsing documentation for rake-13.0.3
Installing ri documentation for rake-13.0.3
Parsing documentation for digest-crc-0.6.3
Installing ri documentation for digest-crc-0.6.3
Parsing documentation for innodb_ruby-0.9.16
Installing ri documentation for innodb_ruby-0.9.16
Done installing documentation for bindata, rake, digest-crc, innodb_ruby after 4 seconds
4 gems installed安装完毕可以查看帮助
# innodb_space --help
Usage: innodb_space <options> <mode>1.3 常见错误
错误1:
# gem install innodb_ruby
Fetching: bindata-1.8.3.gem (100%)
Successfully installed bindata-1.8.3
Fetching: rake-13.0.3.gem (100%)
ERROR: Error installing innodb_ruby:
rake requires Ruby version >= 2.2.此报错就是ruby版本低所致，安装前面的方式处理即可
错误2：
ERROR: Error installing innodb_ruby:
ERROR: Failed to build gem native extension.
current directory: /opt/rh/rh-ruby23/root/usr/local/share/gems/gems/digest-crc-0.6.3/ext/digest
/opt/rh/rh-ruby23/root/usr/bin/ruby -rubygems /opt/rh/rh-ruby23/root/usr/local/share/gems/gems/rake-13.0.3/exe/rake RUBYARCHDIR=/opt/rh/rh-ruby23/root/usr/local/lib64/gems/ruby/digest-crc-0.6.3 RUBYLIBDIR=/opt/rh/rh-ruby23/root/usr/local/lib64/gems/ruby/digest-crc-0.6.3
mkmf.rb can't find header files for ruby at /opt/rh/rh-ruby23/root/usr/share/include/ruby.h
rake failed, exit code 1
Gem files will remain installed in /opt/rh/rh-ruby23/root/usr/local/share/gems/gems/digest-crc-0.6.3 for inspection.
Results logged to /opt/rh/rh-ruby23/root/usr/local/lib64/gems/ruby/digest-crc-0.6.3/gem_make.out是因为ruby-devel未安装导致，安装对应版本的即可
# yum -y install ruby rubygems2. 工具使用
2.1 功能介绍
innodb_space包含较多选项，可通过innodb_space --help命令查看具体内容，主要几个参数如下：
--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>.2.2 实操
先创建一个测试环境，创建一个库及表
mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)
mysql> use testdb;
Database changed
mysql> create table test1(id int primary key auto_increment,c1 varchar(10),dt datetime ,key c1(c1));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test1 values(1,'abc',now());
Query OK, 1 row affected (0.03 sec)2.2.1 列出所有物理对象的数量
-- 查看数据目录
mysql> show variables like '%datadir%';
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| datadir | /data/mysql/mysql3306/data/ |
+---------------+-----------------------------+
1 row in set (0.03 sec)
mysql> exit
Bye
-- 在数据目录下操作
# cd /data/mysql/mysql3306/data/
# innodb_space -s ibdata1 system-spaces
name pages indexes
(system) 768 10
mysql/engine_cost 6 1
mysql/gtid_executed 6 1
mysql/help_category 7 2
mysql/help_keyword 15 2
mysql/help_relation 8 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
testdb/test1 7 2 2.2.2 查看索引信息
因为创建的测试表包含主键及c1字段的，结果如下
# innodb_space -s ibdata1 -T testdb/test1 space-indexes
id name root fseg fseg_id used allocated fill_factor
44 PRIMARY 3 internal 1 1 1 100.00%
44 PRIMARY 3 leaf 2 0 0 0.00%
45 c1 4 internal 3 1 1 100.00%
45 c1 4 leaf 4 0 0 0.00% 对应内容简述
|
列名 |
说明 |
|---|---|
|
id |
索引id |
|
name |
索引名称，PRIMARY代表主键索引（聚集索引），因为InnoDB表是聚集索引组织表，行记录就是聚集索引 |
|
root |
索引中根节点的page号 |
|
fseg |
page类型：internal非叶子节点；leaf叶子节点 |
|
used |
该索引使用的page页 |
|
allocated |
该索引分配的page页 |
|
fill_factor |
该索引使用百分比 |
结果可以与mysql.innodb_index_stats表对应上。
mysql> select * from mysql.innodb_index_stats where database_name='testdb' and table_name='test1';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| testdb | test1 | PRIMARY | 2021-04-25 09:56:47 | n_diff_pfx01 | 0 | 1 | id |
| testdb | test1 | PRIMARY | 2021-04-25 09:56:47 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| testdb | test1 | PRIMARY | 2021-04-25 09:56:47 | size | 1 | NULL | Number of pages in the index |
| testdb | test1 | c1 | 2021-04-25 09:56:47 | n_diff_pfx01 | 0 | 1 | c1 |
| testdb | test1 | c1 | 2021-04-25 09:56:47 | n_diff_pfx02 | 0 | 1 | c1,id |
| testdb | test1 | c1 | 2021-04-25 09:56:47 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| testdb | test1 | c1 | 2021-04-25 09:56:47 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.01 sec)2.2.3 统计每个类型的页占用页的数量
# innodb_space -s ibdata1 -T testdb/test1 space-page-type-regions
start end count type
0 0 1 FSP_HDR
1 1 1 IBUF_BITMAP
2 2 1 INODE
3 4 2 INDEX
5 6 2 FREE (ALLOCATED)2.2.4 每个类型页数总计
# innodb_space -s ibdata1 -T testdb/test1 space-page-type-summary
type count percent description
INDEX 2 28.57 B+Tree index
ALLOCATED 2 28.57 Freshly allocated
FSP_HDR 1 14.29 File space header
IBUF_BITMAP 1 14.29 Insert buffer bitmap
INODE 1 14.29 File segment inode 2.2.5 统计所有的页在表空间的饱和度信息
每个页面显示彩色块（按index/purpose着色），根据页面中的数据量调整大小，可以多操作后再查看
# innodb_space -s ibdata1 -T testdb/test1 space-extents-illustrat
2.2.6 统计所有的页在表空间的饱和度信息
每个页面显示彩色块 (按页面修改LSN的年龄着色)
# innodb_space -s ibdata1 -T testdb/test1 space-lsn-age-illustrate
2.2.7 查看指定页面的信息
参考中2.2.2中page号（root值），查看对应页面的信息，可以查询具体的结果说明
# innodb_space -s ibdata1 -T testdb/test1 -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 44.
Index root is page 3.
Index is testdb/test1.PRIMARY.
# innodb_space -s ibdata1 -T testdb/test1 -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 45.
Index root is page 4.
Index is testdb/test1.c1.
2.2.8 查看页结构信息
查看指定页信息，本次只查看主键页的信息，这样包含了所有字段的内容，内容在type=>:clustered 部分，例如：
# innodb_space -s ibdata1 -T testdb/test1 -p 3 page-dump
#<Innodb::Page::Index:0x0000000002d848c8>:
fil header:
{:checksum=>1296112206,
:offset=>3,
:prev=>nil,
:next=>nil,
:lsn=>2535779,
:type=>:INDEX,
:flush_lsn=>0,
:space_id=>23}
fil trailer:
{:checksum=>1296112206, :lsn_low32=>2535779}
page header:
{:n_dir_slots=>2,
:heap_top=>152,
:garbage_offset=>0,
:garbage_size=>0,
:last_insert_offset=>127,
:direction=>:no_direction,
:n_direction=>0,
:n_recs=>1,
:max_trx_id=>0,
:level=>0,
:index_id=>44,
:n_heap=>3,
:format=>:compact}
fseg header:
{:leaf=>
<Innodb::Inode space=<Innodb::Space file="testdb/test1.ibd", page_size=16384, pages=7>, fseg=2>,
:internal=>
<Innodb::Inode space=<Innodb::Space file="testdb/test1.ibd", page_size=16384, pages=7>, fseg=1>}
sizes:
header 120
trailer 8
directory 4
free 16220
used 164
record 32
per record 32.00
page directory:
[99, 112]
system records:
{:offset=>99,
:header=>
{:next=>127,
:type=>:infimum,
:heap_number=>0,
:n_owned=>1,
:min_rec=>false,
:deleted=>false,
:length=>5},
:next=>127,
:data=>"infimum\\x00",
:length=>8}
{:offset=>112,
:header=>
{:next=>112,
:type=>:supremum,
:heap_number=>1,
:n_owned=>2,
:min_rec=>false,
:deleted=>false,
:length=>5},
:next=>112,
:data=>"supremum",
:length=>8}
garbage records:
records:
{:format=>:compact,
:offset=>127,
:header=>
{:next=>112,
:type=>:conventional,
:heap_number=>2,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>[],
:lengths=>{"c1"=>3},
:externs=>[],
:length=>7},
:next=>112,
:type=>:clustered,
:key=>[{:name=>"id", :type=>"INT", :value=>1}],
:row=>
[{:name=>"c1", :type=>"VARCHAR(30)", :value=>"abc"},
{:name=>"dt", :type=>"DATETIME", :value=>"184913516-11-99 82:08:00"}],
:sys=>
[{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>1287},
{:name=>"DB_ROLL_PTR",
:type=>"ROLL_PTR",
:value=>
{:is_insert=>true, :rseg_id=>39, :undo_log=>{:page=>286, :offset=>272}}}],
:length=>28,
:transaction_id=>1287,
:roll_pointer=>
{:is_insert=>true, :rseg_id=>39, :undo_log=>{:page=>286, :offset=>272}}}2.2.9 会送一个页面的所有记录
先多插入一些记录，看起来更全面一些
mysql> insert into test1 values(2,'cbd','2020-01-01'),(10,'item','2021-01-01'),(1000,'i1000',now());
Query OK, 3 rows affected (0.50 sec)
Records: 3 Duplicates: 0 Warnings: 0再查看内容
# innodb_space -s ibdata1 -T testdb/test1 -p 3 page-records
Record 127: (id=1) → (c1="abc", dt="184913516-12-00 01:74:08")
Record 159: (id=2) → (c1="cbd", dt="184795578-98-73 15:20:00")
Record 191: (id=10) → (c1="item", dt="184887058-35-47 52:71:68")
Record 224: (id=1000) → (c1="i1000", dt="184913538-66-52 04:94:08")可见，该主键索引的所有内容每页就是所有记录内容。
在看一下二级索引c1的内容，也便于理解二级索引，会有主键id的信息
# innodb_space -s ibdata1 -T testdb/test1 -p 4 page-records
Record 127: (c1="abc") → (id=1)
Record 141: (c1="cbd") → (id=2)
Record 170: (c1="i1000") → (id=1000)
Record 155: (c1="item") → (id=10)其他的内容也很多，可以查具体的索引，记录结构等，后续再结合实例继续探索。
THE END
0
二维码
海报
InnoDB 表空间可视化工具innodb_ruby
操作系统版本：CentOS Linux release 7.6.1810 (Core)，默认的yum源安装后ruby的版本是2.0 ,而innodb_ruby需...
