Data Organization in InnoDB

Data Organization in InnoDB

• 1.
  Data Organization inInnoDB — by royalwzy
• 2.
  Personal Profile More than5 years experience in database use and management Proficient in synchronizing data between heterogeneous database Skilled in designing and optimizing database Install and configure MySQL cluster and high-availability cluster environment Synchronize and merge data from Oracle to MySQL mysqlexp mysqlclone Oracle 10g/11g OCM MySQL 5.6 Database Administrator MCITP/MCTS DBA(MSSQL2008) RHCE Java Programmer Oracle ACEA, Oracle Young Expert ACOUG/SHOUG/OCMU core member ITPUB Moderator and Community Expert Senior lecturer in "ENMO" and “UPLOOKING" Active in OTN & ITPUB and other forums My blog:royalwzy.com
• 3.
  Table of Contents ❖Various files that are created by InnoDB ❖ Logical data organization like tablespaces, pages, segments and extents ❖ Explore each of them in some detail and discuss about their relationship with each other ❖ Data layout within the InnoDB storage engine
• 4.
  The Files inInnoDB ❖ MySQL will store all data within the data directory ❖ By default, when InnoDB is initialized, it creates 3 important files ❖ As of MySQL 5.6, only the system tablespace can contain more than 1 data file. All other tablespaces can contain only one data file ❖ The data files and the redo log files are represented in the memory by the C structure fil_node_t
• 5.
  Logical data organization ❖Tablespaces ❖ Pages ❖ Extents ❖ File Segments “Our tablespace concept is similar to the one of Oracle”
• 6.
  Logical and PhysicalDatabase Structures in Oracle Database Logical Physical Tablespace Data file Segment Extent Oracle data
 block Storage System • SAN • NAS • Exadata • File System • NFS • ASM • RAW OS block
• 7.
  Logical and PhysicalDatabase Structures in InnoDB Database Logical Physical Tablespace Data file Segment Extent Pages Storage System • SAN • NAS • File System • NFS • RAW OS block
• 8.
  Tablesapce ❖ innodb_file_per_table configuration parameter ❖What's the relationship between the tablespace and data files “A tablespace consists of a chain of files. The size of the files does not have to be divisible by the database block size, because we may just leave the last incomplete block unused. When a new file is appended to the tablespace, the maximum size of the file is also specified. At the moment, we think that it is best to extend the file to its maximum size already at the creation of the file, because then we can avoid dynamically extending the file when more space is needed for the tablespace.”
• 9.
  Tablespace Header ❖ Eachtablespace will have a header of type fsp_header_t ❖ Current size of the table space in pages ❖ List of free extents ❖ List of full extents not belonging to any segment ❖ List of partially full/free extents not belonging to any segment ❖ List of pages containing segment headers, where all the segment inode slots are reserved ❖ List of pages containing segment headers, where not all the segment inode slots are reserved ❖ From the tablespace header, we can access the list of segments available in the tablespace
• 11.
  Pages ❖ A datafile is logically divided into equal sized pages ❖ How the pages from different data files relate to one another ❖ Every page has a page header “A block's position in the tablespace is specified with a 32-bit unsigned integer. The files in the chain are thought to be catenated, and the block corresponding to an address n is the nth block in the catenated file (where the first block is named the 0th block, and the incomplete block fragments at the end of files are not taken into account). A tablespace can be extended by appending a new file at the end of the chain.”
• 12.
  Page Types
• 13.
  Extents ❖ An extentis 1MB of consecutive pages ❖ #define FSP_EXTENT_SIZE (1048576U / UNIV_PAGE_SIZE)
• 14.
  File Segments ❖ Atablespace can contain many file segments ❖ Information of segment header ❖ The space to which the inode belongs ❖ The page_no of the inode ❖ The byte offset of the inode ❖ The length of the file segment header (in bytes)
• 15.
  Table ❖ In InnoDB,when a table is created, a clustered index (B-tree) is created internally ❖ The root page of a B-tree will be obtained from the data dictionary “In the root node of a B-tree there are two file segment headers. The leaf pages of a tree are allocated from one file segment, to make them consecutive on disk if possible. From the other file segment we allocate pages for the non-leaf levels of the tree.”
• 16.
  Q & A