Chapter 14 The InnoDB Storage Engine

Table of Contents

14.1 Introduction to InnoDB 1.1
14.1.1 Features of the InnoDB Storage Engine
14.1.2 InnoDB as the Default MySQL Storage Engine
14.1.3 Checking InnoDB Availability
14.1.4 Turning Off InnoDB
14.1.5 Obtaining and Installing the InnoDB Storage Engine
14.1.6 Viewing the InnoDB Storage Engine Version Number
14.1.7 Compatibility Considerations for Downgrade and Backup
14.1.8 InnoDB Storage Engine Change History
14.1.9 Third-Party Software Contributions
14.2 Installing the InnoDB Storage Engine
14.3 Upgrading the InnoDB Storage Engine
14.4 Downgrading the InnoDB Storage Engine
14.4.1 Overview
14.5 InnoDB Concepts and Architecture
14.5.1 MySQL and the ACID Model
14.5.2 The InnoDB Transaction Model and Locking
14.5.3 InnoDB Lock Modes
14.5.4 Consistent Nonlocking Reads
14.5.5 Locking Reads (SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE)
14.5.6 InnoDB Record, Gap, and Next-Key Locks
14.5.7 Avoiding the Phantom Problem Using Next-Key Locking
14.5.8 Locks Set by Different SQL Statements in InnoDB
14.5.9 Implicit Transaction Commit and Rollback
14.5.10 Deadlock Detection and Rollback
14.5.11 How to Cope with Deadlocks
14.5.12 InnoDB Multi-Versioning
14.5.13 InnoDB Table and Index Structures
14.6 InnoDB Configuration
14.7 InnoDB Administration
14.8 InnoDB Tablespace Management
14.8.1 Creating the InnoDB Tablespace
14.8.2 InnoDB File-Per-Table Mode
14.8.3 Enabling and Disabling File-Per-Table Mode
14.8.4 Changing the Number or Size of InnoDB Log Files and Resizing the InnoDB Tablespace
14.8.5 Using Raw Disk Partitions for the Shared Tablespace
14.9 InnoDB Table Management
14.9.1 Creating InnoDB Tables
14.9.2 Moving or Copying InnoDB Tables to Another Machine
14.9.3 Grouping DML Operations with Transactions
14.9.4 Converting Tables from MyISAM to InnoDB
14.9.5 AUTO_INCREMENT Handling in InnoDB
14.9.6 InnoDB and FOREIGN KEY Constraints
14.9.7 Limits on InnoDB Tables
14.10 InnoDB Compressed Tables
14.10.1 Overview of Table Compression
14.10.2 Enabling Compression for a Table
14.10.3 Tuning Compression for InnoDB Tables
14.10.4 Monitoring Compression at Runtime
14.10.5 How Compression Works for InnoDB Tables
14.10.6 SQL Compression Syntax Warnings and Errors
14.11 InnoDB File-Format Management
14.11.1 Enabling File Formats
14.11.2 Verifying File Format Compatibility
14.11.3 Identifying the File Format in Use
14.11.4 Downgrading the File Format
14.11.5 Future InnoDB File Formats
14.12 InnoDB Row Storage and Row Formats
14.12.1 Overview of InnoDB Row Storage
14.12.2 Specifying the Row Format for a Table
14.12.3 DYNAMIC and COMPRESSED Row Formats
14.12.4 COMPACT and REDUNDANT Row Formats
14.13 InnoDB Disk I/O and File Space Management
14.13.1 InnoDB Disk I/O
14.13.2 File Space Management
14.13.3 InnoDB Checkpoints
14.13.4 Defragmenting a Table
14.13.5 Reclaiming Disk Space with TRUNCATE TABLE
14.14 InnoDB Fast Index Creation
14.14.1 Overview of Fast Index Creation
14.14.2 Examples of Fast Index Creation
14.14.3 Implementation Details of Fast Index Creation
14.14.4 Concurrency Considerations for Fast Index Creation
14.14.5 How Crash Recovery Works with Fast Index Creation
14.14.6 Limitations of Fast Index Creation
14.15 InnoDB Startup Options and System Variables
14.16 InnoDB Performance
14.16.1 Overview of InnoDB Performance
14.16.2 InnoDB Buffer Pool Configuration
14.16.3 InnoDB Mutex and Read/Write Lock Implementation
14.16.4 Configuring the Memory Allocator for InnoDB
14.16.5 Configuring InnoDB Change Buffering
14.16.6 Configuring Thread Concurrency for InnoDB
14.16.7 Configuring the Number of Background InnoDB I/O Threads
14.16.8 Group Commit
14.16.9 Configuring the InnoDB Master Thread I/O Rate
14.16.10 Using the PAUSE Instruction in InnoDB Spin Loops
14.16.11 Configuring Spin Lock Polling
14.16.12 InnoDB Integration with MySQL Performance Schema
14.16.13 Better Scalability with Multiple Rollback Segments
14.16.14 Configuring InnoDB Purge Scheduling
14.16.15 Configuring Optimizer Statistics Parameters
14.16.16 Estimating ANALYZE TABLE Complexity for InnoDB Tables
14.17.1 InnoDB INFORMATION_SCHEMA Tables about Compression
14.17.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Tables
14.17.3 InnoDB INFORMATION_SCHEMA Buffer Pool Tables
14.18 InnoDB Monitors
14.18.1 InnoDB Monitor Types
14.18.2 Enabling InnoDB Monitors
14.18.3 InnoDB Standard Monitor and Lock Monitor Output
14.18.4 InnoDB Tablespace Monitor Output
14.18.5 InnoDB Table Monitor Output
14.19 InnoDB Backup and Recovery
14.19.1 The InnoDB Recovery Process
14.20 InnoDB and MySQL Replication
14.21 InnoDB Troubleshooting
14.21.1 Troubleshooting InnoDB I/O Problems
14.21.2 Forcing InnoDB Recovery
14.21.3 Troubleshooting InnoDB Data Dictionary Operations
14.21.4 InnoDB Error Handling
14.21.5 InnoDB Error Codes
14.21.6 Operating System Error Codes