Innodb_ruby is jeremycole’s program for analyzing innodb-related structures. It is also a very convenient tool for studying Innodb structures **.

1. Install tools **

1.1 install ruby

CentOS Linux release 7.6.1810 (Core), default yum ruby 2.0, innodb_Ruby 2.2 or higher, so modify yum to install the specified higher version

yum install  -y  centos-release-scl-rh
Copy the code

Repos. D/install centos-sclo -scl-rh.repo

yum install rh-ruby27 rh-ruby27-ruby-devel -y
Copy the code

After the installation is complete, change the version. If version 2.2 or later is installed, this step is mandatory. Otherwise, the earlier version is still used by default

 scl  enable  rh-ruby27 bash
Copy the code

Check the version when you’re done

\# ruby -- versionRuby 2.7.1p83 (revision A0C7C23c9c) \[x86\_64-linux\]# gem --version3.1.2Copy the code

1.2 installation innodb_ruby

After installing Ruby, you can install it again

\# gem install innodb\_rubyFetching bindata-1.8.3. GemSuccessfully installed bindata-1.8.3Fetching GemSuccessfully installed rake-13.0.3. GemBuilding native Extensions. This could Fetching rake-13.0.3Fetching take a while... Parsing Successfully installed innodb\_ruby 0.9.16. GemSuccessfully installed innodb\_ruby 0.9.16Parsing Successfully installed digest-crc-0.6.3 Parsing Documentation for Bindata -1.8.3Installing ri Documentation for Bindata -1.8.3Parsing documentation for Rake-13.0.3 Installing RI Documentation for rake-13.0.3Parsing 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.16Done installing documentation for bindata, rake, digest-CRC, innodb\_ruby after 4 seconds4 gems installedCopy the code

You can view the help after installation

\# innodb\_space --helpUsage: innodb\_space <options> <mode>

Copy the code

1.3 Common Errors

Error 1:

\# gem install innodb\_rubyFetching: bindata-1.8.3. Gem (100%)Successfully installed bindata-1.8.3Fetching: Gem (100%)ERROR: ERROR installing Innodb \_ruby: Rake requires Ruby version >= 2.2.Copy the code

This error is caused by the low Ruby version

Mistake 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 the header files for the ruby at  /opt/rh/rh-ruby23/root/usr/share/include/ruby.h rake failed, The 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. The Results logged to/opt/rh/rh - ruby23 / root/usr/local/lib64 / gems/ruby/digest - CRC - 0.6.3 / gem \ _make outCopy the code
Ruby-devel is not installedCopy the code

yum -y install ruby rubygems

* * 2 \. Tool use * * = = = = = = = = = = = = = = = = 2.1 function of * * * * -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the innodb \ _space contains more options, but through the innodb \ _space -- help command to check the specific content, The main parameters are as follows:Copy the code

–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 Operations ** -------------- First create a test environment, create a library and tableCopy the code

mysql> create database testdb; Query OK, 1 row affected (0.01sec)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 List the number of all physical objects **Copy the code

Mysql > show variables like ‘%datadir%’; +—————+—————————–+| Variable_name | Value |+—————+—————————–+| Datadir | / data/mysql/mysql3306 / data / | + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — — — — — — — — — — — — — — — — + 1 row in the set (0.03 SEC) mysql > ExitBye – in the data directory operation # 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

Because the test table created contains primary keys and c1 fields, the result is as followsCopy the code

# innodb_space -s ibdata1 -T testdb/test1 space-indexesid name root fseg fseg_id used allocated fill_factor 44 PRIMARY 3 1 1 100.00% 44 PRIMARY 3 leaf 2 00.00% 45 C1 4 internal 3 1 1 100.00% 45 C1 4 leaf 4 00.00%

** column name ** ** Description ** ID Index ID name, PRIMARY represents the PRIMARY key index (clustered index), because InnoDB table is clustered index organization table, row records are clustered index root node page number fseg page type: Internal non-leaf nodes; Allocated page of the index fill\_factor The percentage result of the index can be allocated to the mysql.innodb\_index\_stats table.Copy the code

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.01sec)

### **2.2.3 Counting the number of pages occupied by each type of page **Copy the code

# 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 Total pages per type **Copy the code

# innodb_space -s ibData1 -t testdb/test1 space-page-type- SummaryType count Percent description INDEX 2 28.57b +Tree Index Reserved 2 28.57 Freshly reserved FSP_HDR 1 14.29 File Space Header IBUF_BITMAP 1 14.29 Insert Buffer BitmapINODE 1 14.29 File segment inode

** Each page will display color blocks (colored according to index/purpose), adjust the size according to the amount of data in the page, you can view it after multiple operationsCopy the code

innodb_space -s ibdata1 -T testdb/test1 space-extents-illustrat

! [images] (https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a312443083684d359877172e63e1fbd3~tplv-k3u1fbpfcp-zoom-1.image) ** Display color blocks for each page (modify LSN age shading by page)Copy the code

innodb_space -s ibdata1 -T testdb/test1 space-lsn-age-illustrate

! [images] (https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/4e02e2458f26414b85087c33503779da~tplv-k3u1fbpfcp-zoom-1.image) ### **2.2.7 Viewing the information about the specified page ** See 2.2.2 page number (root value) to view the information about the corresponding page and query the specific result descriptionCopy the code

# 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 view page structure information to check the specified page information, the only view the primary key information on the page, it contains all the content of the field, the content in the type = > : clustered part, such as:Copy the code

# 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=>
<:inode file="testdb/test1.ibd" page_size="16384," pages="7" space="<Innodb::Space">
, fseg=2>, :internal=>
<:inode file="testdb/test1.ibd" page_size="16384," pages="7" space="<Innodb::Space">
, 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 will send all records on a page ** Insert more records first to look more comprehensiveCopy the code

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

Look at the contentCopy the code

# innodb_space -s ibdata1 -T testdb/test1 -p 3 page-recordsRecord 127: (id=1) → (c1=” ABC “, dt=”184913516-12-00 01:74:08″) (id=2) → (c1=” CBD “, dt=”184795578-98-73 15:20:00″) (id=1) → (c1=”i1000”, dt=”184913538-66-52 “) → (c1=”i1000″, dt=”184913538-66-52 04:94:08”)

As you can see, all the content of the primary key index is all the record content per page. The primary key id is c1. The primary key id is c1Copy the code

# innodb_space -s ibdata1 -t testDB /test1 -p 4 Page – Record 127: (c1=” ABC “) → (id=1)Record 141: (c1 = “CBD”) – > (id = 2) Record 170: (c1 = “i1000”) – > (id = 1000) Record 155: (c1 = “item”) – > (id = 10)

There are also many other contents, such as specific index and record structure, etc. This time is just a primer, friends can explore by themselves, and they will continue to learn with examples if they have the opportunity. ! [images] (https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d404ee248e8546f98ca46c773ba1ab9b~tplv-k3u1fbpfcp-zoom-1.image) 1. 4. Smart refrigerator [MySQL MHA of high availability cluster deployment] (HTTP: / / http://mp.weixin.qq.com/s?__biz=MzIxMzYxMjk2MQ==&mid=2247484084&idx=1&sn=45a157c10972cbde7788ccbd2f37 a2ff&chksm=97b5627aa0c2eb6cc3db070230cb38fa3a075aeb86cbdd4ef2d1e9bd8fd4f1e2d9ea735317bd&scene=21#wechat_redirect) 2. [mysql8.0 those things] of revise the rules of new users and encryption (http://mp.weixin.qq.com/s?__biz=MzIxMzYxMjk2MQ==&mid=2247483672&idx=1&sn=f86d339d8c871d9087a70 b4518f5e305&chksm=97b561d6a0c2e8c0d9e313ae88c3d0970d6620e378a26c9785a962a4f689029297626996757c&scene=21#wechat_redirect)  ----------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ - 3. 10 times faster than hive of big data query tool -- presto -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- [four \. Monitoring tool drawn: Prometheus + MySQL, Redis database] Grafana monitoring (http://mp.weixin.qq.com/s?__biz=MzIxMzYxMjk2MQ==&mid=2247483770&idx=1&sn=997833e9868 ff44bd475ab49db20a136&chksm=97b561b4a0c2e8a2dc8bf4059cd2b3f57adb848f8f5ab08dabb128221faf40067c1ad866653f&scene=21#wechat _redirect) 5. PostgreSQL primary/secondary replication -- physical replication 6. MySQL traditional point replication online to GTID replication 7[. MySQL sensitive data encryption and decryption] (HTTP: / / http://mp.weixin.qq.com/s?__biz=MzIxMzYxMjk2MQ==&mid=2247483933&idx=1&sn=98b9e7e39ca47ca8565fe09ca60f4bc 3&chksm=97b562d3a0c2ebc5b72687d66bbb1f147b9ba899130db73c341f656cbd659a8c274869b509d6&scene=21#wechat_redirect) 8[. MySQL data backup and restore (a)] (HTTP: / / http://mp.weixin.qq.com/s?__biz=MzIxMzYxMjk2MQ==&mid=2247483939&idx=1&sn=073125f1ed17465cfcd471f8193798 8e&chksm=97b562eda0c2ebfbf7e9271eebcca0f3ff319656179840370b2c089ccadb80fbfc0001e11991&scene=21#wechat_redirect) 9[. MySQL data backup and restore (2)] (http://mp.weixin.qq.com/s?__biz=MzIxMzYxMjk2MQ==&mid=2247483944&idx=1&sn=eb134e16e4b8cd1a5655d6168f5351 72&chksm=97b562e6a0c2ebf0d26679a44b834027ba942fa747805b289dd8bf9edcb02317018e36012a7b&scene=21#wechat_redirect) ! [images] (https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/c719810dae904e93ab7ebbe77c594f13~tplv-k3u1fbpfcp-zoom-1.image) Scan code attention! [images] (https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/9c1e170767c845e0af288991ea512de8~tplv-k3u1fbpfcp-zoom-1.image)! [images] (https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/fa705397c2924c5eb97ae9a319942e39~tplv-k3u1fbpfcp-zoom-1.image)! [images] (https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a7df0a71f3e040e19dd11203f83d68b3~tplv-k3u1fbpfcp-zoom-1.image)Copy the code