1. 极安网首页
  2. 网络安全新闻

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

原创文章,作者: Admin ,转载请注明出处:https://secvery.com/7052.html