Chapter 15 The InnoDB Storage Engine

Table of Contents

15.1 Introduction to InnoDB

15.1.1 Benefits of Using InnoDB Tables

15.1.2 Best Practices for InnoDB Tables

15.1.3 Verifying that InnoDB is the Default Storage Engine

15.1.4 Testing and Benchmarking with InnoDB

15.2 InnoDB and the ACID Model

15.3 InnoDB Multi-Versioning

15.4 InnoDB Architecture

15.5 InnoDB In-Memory Structures

15.5.1 Buffer Pool

15.5.2 Change Buffer

15.5.3 Adaptive Hash Index

15.5.4 Log Buffer

15.6 InnoDB On-Disk Structures

15.6.1 Tables

15.6.2 Indexes

15.6.3 Tablespaces

15.6.4 Doublewrite Buffer

15.6.5 Redo Log

15.6.6 Undo Logs

15.7 InnoDB Locking and Transaction Model

15.7.1 InnoDB Locking

15.7.2 InnoDB Transaction Model

15.7.3 Locks Set by Different SQL Statements in InnoDB

15.7.4 Phantom Rows

15.7.5 Deadlocks in InnoDB

15.7.6 Transaction Scheduling

15.8 InnoDB Configuration

15.8.1 InnoDB Startup Configuration

15.8.2 Configuring InnoDB for Read-Only Operation

15.8.3 InnoDB Buffer Pool Configuration

15.8.4 Configuring Thread Concurrency for InnoDB

15.8.5 Configuring the Number of Background InnoDB I/O Threads

15.8.6 Using Asynchronous I/O on Linux

15.8.7 Configuring InnoDB I/O Capacity

15.8.8 Configuring Spin Lock Polling

15.8.9 Purge Configuration

15.8.10 Configuring Optimizer Statistics for InnoDB

15.8.11 Configuring the Merge Threshold for Index Pages

15.8.12 Enabling Automatic Configuration for a Dedicated MySQL Server

15.9 InnoDB Table and Page Compression

15.9.1 InnoDB Table Compression

15.9.2 InnoDB Page Compression

15.10 InnoDB Row Formats

15.11 InnoDB Disk I/O and File Space Management

15.11.1 InnoDB Disk I/O

15.11.2 File Space Management

15.11.3 InnoDB Checkpoints

15.11.4 Defragmenting a Table

15.11.5 Reclaiming Disk Space with TRUNCATE TABLE

15.12 InnoDB and Online DDL

15.12.1 Online DDL Operations

15.12.2 Online DDL Performance and Concurrency

15.12.3 Online DDL Space Requirements

15.12.4 Online DDL Memory Management

15.12.5 Configuring Parallel Threads for Online DDL Operations

15.12.6 Simplifying DDL Statements with Online DDL

15.12.7 Online DDL Failure Conditions

15.12.8 Online DDL Limitations

15.13 InnoDB Data-at-Rest Encryption

15.14 InnoDB Startup Options and System Variables

15.15 InnoDB INFORMATION_SCHEMA Tables

15.15.1 InnoDB INFORMATION_SCHEMA Tables about Compression

15.15.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information

15.15.3 InnoDB INFORMATION_SCHEMA Schema Object Tables

15.15.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables

15.15.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables

15.15.6 InnoDB INFORMATION_SCHEMA Metrics Table

15.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table

15.15.8 Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES

15.16 InnoDB Integration with MySQL Performance Schema

15.16.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema

15.16.2 Monitoring InnoDB Mutex Waits Using Performance Schema

15.17 InnoDB Monitors

15.17.1 InnoDB Monitor Types

15.17.2 Enabling InnoDB Monitors

15.17.3 InnoDB Standard Monitor and Lock Monitor Output

15.18 InnoDB Backup and Recovery

15.18.1 InnoDB Backup

15.18.2 InnoDB Recovery

15.19 InnoDB and MySQL Replication

15.20 InnoDB memcached Plugin

15.20.1 Benefits of the InnoDB memcached Plugin

15.20.2 InnoDB memcached Architecture

15.20.3 Setting Up the InnoDB memcached Plugin

15.20.4 InnoDB memcached Multiple get and Range Query Support

15.20.5 Security Considerations for the InnoDB memcached Plugin

15.20.6 Writing Applications for the InnoDB memcached Plugin

15.20.7 The InnoDB memcached Plugin and Replication

15.20.8 InnoDB memcached Plugin Internals

15.20.9 Troubleshooting the InnoDB memcached Plugin

15.21 InnoDB Troubleshooting

15.21.1 Troubleshooting InnoDB I/O Problems

15.21.2 Troubleshooting Recovery Failures

15.21.3 Forcing InnoDB Recovery

15.21.4 Troubleshooting InnoDB Data Dictionary Operations

15.21.5 InnoDB Error Handling

15.22 InnoDB Limits

15.23 InnoDB Restrictions and Limitations