InnoDB Architecture and Performance Optimization, Peter Zaitsev

Brief Innodb Architecture and Performance OptimizationOct 26, 2010HighLoad++Moscow, Russiaby Peter Zaitsev, Percona Inc-2-Architecture and Performance• Advanced Performance Optimization requires transparency– X-ray vision• Impossible without understanding system architecture• Focus on Conceptual Aspects– Exact Checksum algorithm Innodb uses is not important– What matters• How fast is that algorithm ?• How checksums are checked/updated-3-General Architecture• Traditional OLTP Engine– “Emulates Oracle Architecture”• Implemented using MySQL Storage engine API• Row Based Storage. Row Locking. MVCC• Data Stored in Tablespaces• Log of changes stored in circular log files– Redo logs• Tablespace pages cached in “Buffer Pool”-4-Storage Files LayoutPhysical Structure of Innodb Tabespaces and Logs-5-Innodb Tablespaces• Al data stored in Tablespaces– Changes to these databases stored in Circular Logs– Changes has to be reflected in tablespace before log record is overwritten• Single tablespace or multiple tablespace– innodb_file_per_table=1• System information always in main tablespace– Ibdata1– Main tablespace can consist of many files• They are concatenated-6-Tablespace Format• Tablespace is Collection of Segments– Segment is like a “file”• Segment is number of extents– Typical y 64 of 16K page sizes– Smal er extents for very smal objects• First Tablespace page contains header– Tablespace size– Tablespace id -7-Types of Segments• Each table is Set of Indexes– Innodb table is “index organized table”– Data is stored in leaf pages of PRIMARY key• Each index has– Leaf node segment– Non Leaf node segment• Special Segments– Rol back Segment– Insert buffer, etc-8-Innodb Space Allocation• Small Segments (less than 32 pages)– Page at the time• Large Segments– Extent at the time (to avoid fragmentation)• Free pages recycled within same segment• Al pages in extent must be free before it is used in different segment of same tablespace– innodb_file_per_table=1 - free space can be used by same table only• Innodb never shrinks its tablespaces-9-Innodb Log Files• Set of log files– ib_logfile?– 2 log files by default. Effectively concatenated • Log Header– Stores information about last checkpoint• Log is NOT organized in pages, but records– Records aligned 512 bytes, matching disk sector• Log record format “physiological”– Stores Page# and operation to do on it• Only REDO operations are stored in logs.-10-Storage Tuning Parameters• innodb_file_per_table– Store each table in its own file/tablespace • innodb_autoextend_increment– Extend system tablespace in this increment• innodb_log_file_size• innodb_log_files_in_group– Log file configuration• Innodb page size– XtraDB onlyDocument Outline

• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ