Chapter 14 The InnoDB Storage Engine

Table of Contents     [+/-]

14.1 Introduction to InnoDB     [+/-]

14.1.1 Benefits of Using InnoDB Tables

14.1.2 Best Practices for InnoDB Tables

14.1.3 Checking InnoDB Availability

14.1.4 Upward and Downward Compatibility

14.1.5 Testing and Benchmarking with InnoDB

14.1.6 Turning Off InnoDB

14.1.7 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.5 InnoDB and the ACID Model

14.6 InnoDB Multi-Versioning

14.7 InnoDB Architecture     [+/-]

14.7.1 Buffer Pool

14.7.2 Change Buffer

14.7.3 Adaptive Hash Index

14.7.4 Redo Log Buffer

14.7.5 System Tablespace

14.7.6 InnoDB Data Dictionary

14.7.7 Doublewrite Buffer

14.7.8 Undo Log

14.7.9 File-Per-Table Tablespaces

14.7.10 Redo Log

14.8 InnoDB Locking and Transaction Model     [+/-]

14.8.1 InnoDB Locking

14.8.2 InnoDB Transaction Model

14.8.3 Locks Set by Different SQL Statements in InnoDB

14.8.4 Phantom Rows

14.8.5 Deadlocks in InnoDB

14.9 InnoDB Configuration     [+/-]

14.9.1 InnoDB Startup Configuration

14.9.2 InnoDB Buffer Pool Configuration

14.9.3 Configuring the Memory Allocator for InnoDB

14.9.4 Configuring InnoDB Change Buffering

14.9.5 Configuring Thread Concurrency for InnoDB

14.9.6 Configuring the Number of Background InnoDB I/O Threads

14.9.7 Configuring the InnoDB Master Thread I/O Rate

14.9.8 Configuring Spin Lock Polling

14.9.9 Configuring InnoDB Purge Scheduling

14.9.10 Configuring Optimizer Statistics for InnoDB

14.10 InnoDB Tablespaces     [+/-]

14.10.1 Resizing the InnoDB System Tablespace

14.10.2 Changing the Number or Size of InnoDB Redo Log Files

14.10.3 Using Raw Disk Partitions for the System Tablespace

14.10.4 InnoDB File-Per-Table Tablespaces

14.11 InnoDB Tables and Indexes     [+/-]

14.11.1 Creating InnoDB Tables

14.11.2 Role of the .frm File for InnoDB Tables

14.11.3 Physical Row Structure of InnoDB Tables

14.11.4 Moving or Copying InnoDB Tables to Another Machine

14.11.5 Converting Tables from MyISAM to InnoDB

14.11.6 AUTO_INCREMENT Handling in InnoDB

14.11.7 InnoDB and FOREIGN KEY Constraints

14.11.8 Limits on InnoDB Tables

14.11.9 Clustered and Secondary Indexes

14.11.10 Physical Structure of an InnoDB Index

14.12 InnoDB Table Compression     [+/-]

14.12.1 Overview of Table Compression

14.12.2 Enabling Compression for a Table

14.12.3 Tuning Compression for InnoDB Tables

14.12.4 Monitoring Compression at Runtime

14.12.5 How Compression Works for InnoDB Tables

14.12.6 SQL Compression Syntax Warnings and Errors

14.13 InnoDB File-Format Management     [+/-]

14.13.1 Enabling File Formats

14.13.2 Verifying File Format Compatibility

14.13.3 Identifying the File Format in Use

14.13.4 Downgrading the File Format

14.14 InnoDB Row Storage and Row Formats     [+/-]

14.14.1 Overview of InnoDB Row Storage

14.14.2 Specifying the Row Format for a Table

14.14.3 DYNAMIC and COMPRESSED Row Formats

14.14.4 COMPACT and REDUNDANT Row Formats

14.15 InnoDB Disk I/O and File Space Management     [+/-]

14.15.1 InnoDB Disk I/O

14.15.2 File Space Management

14.15.3 InnoDB Checkpoints

14.15.4 Defragmenting a Table

14.15.5 Reclaiming Disk Space with TRUNCATE TABLE

14.16 InnoDB Fast Index Creation     [+/-]

14.16.1 Overview of Fast Index Creation

14.16.2 Examples of Fast Index Creation

14.16.3 Implementation Details of Fast Index Creation

14.16.4 Concurrency Considerations for Fast Index Creation

14.16.5 How Crash Recovery Works with Fast Index Creation

14.16.6 Limitations of Fast Index Creation

14.17 InnoDB Startup Options and System Variables

14.18 InnoDB INFORMATION_SCHEMA Tables     [+/-]

14.18.1 InnoDB INFORMATION_SCHEMA Tables about Compression

14.18.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Tables

14.18.3 InnoDB INFORMATION_SCHEMA Buffer Pool Tables

14.19 InnoDB Integration with MySQL Performance Schema     [+/-]

14.19.1 Monitoring InnoDB Mutex Waits Using Performance Schema

14.20 InnoDB Monitors     [+/-]

14.20.1 InnoDB Monitor Types

14.20.2 Enabling InnoDB Monitors

14.20.3 InnoDB Standard Monitor and Lock Monitor Output

14.20.4 InnoDB Tablespace Monitor Output

14.20.5 InnoDB Table Monitor Output

14.21 InnoDB Backup and Recovery     [+/-]

14.21.1 The InnoDB Recovery Process

14.22 InnoDB and MySQL Replication

14.23 InnoDB Troubleshooting     [+/-]

14.23.1 Troubleshooting InnoDB I/O Problems

14.23.2 Forcing InnoDB Recovery

14.23.3 Troubleshooting InnoDB Data Dictionary Operations

14.23.4 InnoDB Error Handling