点击上方蓝字关注我
innodb_ruby是jeremycole的一个用于分析Innodb相关结构的一个程序,也是非常方便我们研究Innodb的结构的工具。
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 --versionruby 2.7.1p83 (2020-03-31 revision a0c7c23c9c) [x86_64-linux]# gem --version3.1.2
1.2 安装innodb_ruby
安装完ruby,再进行安装即可
# gem install innodb_rubyFetching bindata-1.8.3.gemSuccessfully installed bindata-1.8.3Fetching rake-13.0.3.gemSuccessfully installed rake-13.0.3Fetching digest-crc-0.6.3.gemBuilding native extensions. This could take a while...Successfully installed digest-crc-0.6.3Fetching innodb_ruby-0.9.16.gemSuccessfully installed innodb_ruby-0.9.16Parsing documentation for bindata-1.8.3Installing ri documentation for bindata-1.8.3Parsing documentation for rake-13.0.3Installing ri documentation for rake-13.0.3Parsing documentation for digest-crc-0.6.3Installing ri documentation for digest-crc-0.6.3Parsing documentation for innodb_ruby-0.9.16Installing ri documentation for innodb_ruby-0.9.16Done installing documentation for bindata, rake, digest-crc, innodb_ruby after 4 seconds4 gems installed
安装完毕可以查看帮助
# innodb_space --helpUsage: innodb_space <options> <mode>
1.3 常见错误
错误1:
# gem install innodb_rubyFetching: bindata-1.8.3.gem (100%)Successfully installed bindata-1.8.3Fetching: 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 rubygems
2. 工具使用
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 changedmysql> 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> exitBye
-- 在数据目录下操作# cd /data/mysql/mysql3306/data/# innodb_space -s ibdata1 system-spacesname 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-indexesid 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-regionsstart 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-summarytype 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 bitmapINODE 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-accountAccounting 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-accountAccounting 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-recordsRecord 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-recordsRecord 127: (c1="abc") → (id=1)
Record 141: (c1="cbd") → (id=2)
Record 170: (c1="i1000") → (id=1000)
Record 155: (c1="item") → (id=10)
其他的内容也很多,例如可以查具体的索引,记录结构等,本次只是抛砖引玉,小伙伴们可以自行探索,后续有机会也会结合实例继续学习。
往期精彩回顾
2. mysql8.0新增用户及加密规则修改的那些事
3. 比hive快10倍的大数据查询利器-- presto
4. 监控利器出鞘:Prometheus+Grafana监控MySQL、Redis数据库
5. PostgreSQL主从复制--物理复制
6. MySQL传统点位复制在线转为GTID模式复制
扫码关注