Table of Contents Chapter 15 The InnoDB Storage Engine 15.1 Introduction to InnoDB ........................................................................................................... 2998 15.1.1 Benefits of Using InnoDB Tables ................................................................................ 3000 15.1.2 Best Practices for InnoDB Tables ............................................................................... 3001 15.1.3 Verifying that InnoDB is the Default Storage Engine ..................................................... 3002 15.1.4 Testing and Benchmarking with InnoDB ...................................................................... 3002 15.2 InnoDB and the ACID Model ................................................................................................. 3002 15.3 InnoDB Multi-Versioning ........................................................................................................ 3004 15.4 InnoDB Architecture .............................................................................................................. 3005 15.5 InnoDB In-Memory Structures ................................................................................................ 3006 15.5.1 Buffer Pool ................................................................................................................. 3006 15.5.2 Change Buffer ............................................................................................................ 3011 15.5.3 Adaptive Hash Index .................................................................................................. 3015 15.5.4 Log Buffer .................................................................................................................. 3016 15.6 InnoDB On-Disk Structures .................................................................................................... 3016 15.6.1 Tables ....................................................................................................................... 3016 15.6.2 Indexes ...................................................................................................................... 3041 15.6.3 Tablespaces ............................................................................................................... 3049 15.6.4 Doublewrite Buffer ...................................................................................................... 3072 15.6.5 Redo Log ................................................................................................................... 3074 15.6.6 Undo Logs ................................................................................................................. 3078 15.7 InnoDB Locking and Transaction Model ................................................................................. 3080 15.7.1 InnoDB Locking .......................................................................................................... 3080 15.7.2 InnoDB Transaction Model .......................................................................................... 3085 15.7.3 Locks Set by Different SQL Statements in InnoDB ....................................................... 3094 15.7.4 Phantom Rows ........................................................................................................... 3098 15.7.5 Deadlocks in InnoDB .................................................................................................. 3098 15.7.6 Transaction Scheduling .............................................................................................. 3101 15.8 InnoDB Configuration ............................................................................................................ 3102 15.8.1 InnoDB Startup Configuration ...................................................................................... 3102 15.8.2 Configuring InnoDB for Read-Only Operation ............................................................... 3108 15.8.3 InnoDB Buffer Pool Configuration ................................................................................ 3110 15.8.4 Configuring Thread Concurrency for InnoDB ................................................................ 3124 15.8.5 Configuring the Number of Background InnoDB I/O Threads ........................................ 3125 15.8.6 Using Asynchronous I/O on Linux ............................................................................... 3126 15.8.7 Configuring InnoDB I/O Capacity ................................................................................ 3126 15.8.8 Configuring Spin Lock Polling ..................................................................................... 3128 15.8.9 Purge Configuration .................................................................................................... 3129 15.8.10 Configuring Optimizer Statistics for InnoDB ................................................................ 3131 15.8.11 Configuring the Merge Threshold for Index Pages ...................................................... 3142 15.8.12 Enabling Automatic Configuration for a Dedicated MySQL Server ............................... 3144 15.9 InnoDB Table and Page Compression ................................................................................... 3146 15.9.1 InnoDB Table Compression ........................................................................................ 3147 15.9.2 InnoDB Page Compression ......................................................................................... 3161 15.10 InnoDB Row Formats .......................................................................................................... 3165 15.11 InnoDB Disk I/O and File Space Management ...................................................................... 3171 15.11.1 InnoDB Disk I/O ....................................................................................................... 3172 15.11.2 File Space Management ........................................................................................... 3172 15.11.3 InnoDB Checkpoints ................................................................................................. 3174 2997