MySQL 8.0 Reference Manual 14 MySQL Data Dictionary ............................................................................................................ 2989 14.1 Data Dictionary Schema ................................................................................................ 2989 14.2 Removal of File-based Metadata Storage ....................................................................... 2991 14.3 Transactional Storage of Dictionary Data ....................................................................... 2991 14.4 Dictionary Object Cache ................................................................................................ 2991 14.5 INFORMATION_SCHEMA and Data Dictionary Integration .............................................. 2992 14.6 Serialized Dictionary Information (SDI) ........................................................................... 2994 14.7 Data Dictionary Usage Differences ................................................................................ 2995 14.8 Data Dictionary Limitations ............................................................................................ 2996 15 The InnoDB Storage Engine ..................................................................................................... 2997 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 xvi