记录黑客技术中优秀的内容,传播黑客文化,分享黑客技术精华

InnoDB 表空间可视化工具innodb_ruby

2022-01-13 04:41

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.2

1.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

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 rubygems

2. 工具使用

2.1 功能介绍

innodb_space包含较多选项,可通过innodb_space --help命令查看具体内容,主要几个参数如下:

  --system-space-file, -s 
Load the system tablespace file or files : 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
Use the table name .

--index-name, -I
Use the index name .

--space-file, -f
Load the tablespace 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
#:

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=>
, fseg=2>,
:internal=>
, 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=>"infimumx00",
: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)

其他的内容也很多,可以查具体的索引,记录结构等,后续再结合实例继续探索。

文章来源于互联网:InnoDB 表空间可视化工具innodb_ruby


知识来源: https://secvery.com/7052.html

阅读:71274 | 评论:0 | 标签:无

想收藏或者和大家分享这篇好文章→复制链接地址

“InnoDB 表空间可视化工具innodb_ruby”共有0条留言

发表评论

姓名:

邮箱:

网址:

验证码:

黑帝公告 📢

永久免费持续更新精选优质黑客技术文章Hackdig,帮你成为掌握黑客技术的英雄

↓赞助商 🙇🧎

标签云 ☁

本页关键词 💎