Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System

当前位置: 首页 » 技术文章 » MySQL

InnoDB internals and structures


Page是整个InnoDB存储的最基本构件,也是InnoDB磁盘管理的最小单位,与数据库相关的所有内容都存储在这种Page结构里。Page分为几种类型,常见的页类型有数据页(B-tree Node)Undo页(Undo Log Page)系统页(System Page) 事务数据页(Transaction System Page)等。单个Page的大小是16K,每个Page使用一个8biy的int值来唯一标识,对应InnoDB最大可以存64TB。

发现Google的工程师Jeremy Cole用ruby写的工具很不错

下面是Jeremy Cole关于InnoDB的相关链接

  1. On learning InnoDB: A journey to the core: An introduction to theinnodb_rubyandinnodb_diagramsprojects.
  2. A quick introduction to innodb_ruby: How to set upinnodb_rubyand a few demos of what it can do.
  3. The basics of InnoDB space file layout: How InnoDB structures its space files and the pages they contain.
  4. Page management in InnoDB space files: Structures related to management of file segments, extents, and pages within space files.
  5. Exploring InnoDB page management with innodb_ruby: Interactive exploration of the page management data structures from a real InnoDB space file.
  6. The physical structure of InnoDB index pages: A description of InnoDB’s index pages, where data is stored, and how records are placed in them.
  7. B+Tree index structures in InnoDB: A logical high-level exploration of InnoDB’s B+Tree indexes and their efficiency.
  8. The physical structure of records in InnoDB: A low-level illustration of InnoDB’s row storage formats.
  9. Efficiently traversing InnoDB B+Trees with the page directory: A deep examination of efficiency in traversing B+Trees in InnoDB.
  10. InnoDB bugs found during research on InnoDB data storage: An explanation about 7 different bugs found during the research for this work
  11. How does InnoDB behave without a Primary Key?: A short discussion about InnoDB’s implicitROW_IDcolumn which is used in tables without a suitablePRIMARY KEY.
  12. InnoDB Tidbit: The doublewrite buffer wastes 32 pages (512 KiB): How the file segment allocation used in InnoDB plus a bit of programming laziness caused 512 KiB to be wasted in every InnoDB system tablespace.
  13. The basics of the InnoDB undo logging and history system: A short introduction to multi-version concurrency control, undo logging, InnoDB’s history system, and how they are all related.
  14. A little fun with InnoDB multi-versioning: A fun and somewhat scary look at the “hidden” effects of multi-versioning and InnoDB’s history system.
  15. InnoDB with reduced page sizes wastes up to 6% of disk space: InnoDB’s required bookkeeping information for each extent wastes many pages, and it gets a lot worse with reduced (4k or 8k) page sizes.
  16. Visualizing the impact of ordered vs. random index insertion in InnoDB: Using thespace-lsn-age-illustrateandspace-extents-illustratemodes ofinnodb_spaceto visualize the efficiency of index builds.





[root@od ~]# yum install ruby -y


[root@od ~]# wget
[root@od ~]# ls -l ruby-2.5.3.tar.gz
-rw-r--r--. 1 root root 15972577 Nov 17 00:10 ruby-2.5.3.tar.gz
[root@od ~]# tar zxvf ruby-2.5.3.tar.gz
[root@od ~]# cd ruby-2.5.3
[root@od ~]# ./configure
[root@od ~]# make && make install
通git工具clone innodb_ruby
[root@od ~]# git clone
Initialized empty Git repository in /root/innodb_ruby/.git/
remote: Enumerating objects: 2663, done.
Receiving objects:  20% (537/2663), 3.75 MiB | 7 KiB/s
remote: Total 2663 (delta 0), reused 0 (delta 0), pack-reused 2663
Receiving objects: 100% (2663/2663), 16.79 MiB | 9 KiB/s, done.
Resolving deltas: 100% (1451/1451), done.
[root@od ~]# 


    #!/usr/bin/env ruby
    require "mysql"
    m ="", "root", "", "test")
    m.query("DROP TABLE IF EXISTS t")
    (1..1000000).to_a.shuffle.each_with_index do |i, index|
      m.query("INSERT INTO t (i) VALUES (#{i})")
      puts "Inserted #{index} rows..." if index % 10000 == 0




关键词:ruby innodb 


MySQL HA - Innodb Cluster
Open source Innodb recovery tool(undrop for innodb)
InnoDB internals and structures