- 7 Performance and Scalability Enhancements
- 7.1 Overview of InnoDB Performance
- 7.2 Faster Locking for Improved Scalability
- 7.3 Using Operating System Memory Allocators
- 7.4 Controlling InnoDB Change Buffering
- 7.5 Controlling Adaptive Hash Indexing
- 7.6 Changes Regarding Thread Concurrency
- 7.7 Changes in the Read-Ahead Algorithm
- 7.8 Multiple Background I/O Threads
- 7.9 Asynchronous I/O on Linux
- 7.10 Group Commit
- 7.11 Controlling the Master Thread I/O Rate
- 7.12 Controlling the Flushing Rate of Dirty Pages
- 7.13 Using the PAUSE instruction in InnoDB spin loops
- 7.14 Control over Spin Lock Polling
- 7.15 Making Buffer Cache Scan Resistant
- 7.16 Improvements to Crash Recovery Performance
- 7.17 Integration with MySQL PERFORMANCE_SCHEMA
- 7.18 Improvements to Performance from Multiple Buffer Pools
- 7.19 Better Scalability with Multiple Rollback Segments
- 7.20 Better Scalability with Improved Purge Scheduling
- 7.21 Improved Log Sys Mutex
- 7.22 Separate Flush List Mutex
Starting with InnoDB storage engine 1.1, you can profile certain internal InnoDB operations using the Performance Schema feature from MySQL 5.5 and higher. This type of tuning is primarily for expert users, those who push the limits of MySQL performance, read the MySQL source code, and evaluate optimization strategies to overcome performance bottlenecks. DBAs can also use this feature for capacity planning, to see whether their typical workload encounters any performance bottlenecks with a particular combination of CPU, RAM, and disk storage; and if so, to judge whether performance can be improved by increasing the capacity of some part of the system.
To use this feature to examine InnoDB performance:
You must be running MySQL 5.5 or higher. You must build the database server from source, enabling the Performance Schema feature by building with the
--with-perfschemaoption. Since the Performance Schema feature introduces some performance overhead, you should use it on a test or development system rather than on a production system.You must be running InnoDB storage engine 1.1 or higher.
You must be generally familiar with how to use the Performance Schema feature, for example to query tables in the
performance_schemadatabase.-
Examine the following kinds of InnoDB objects by querying the appropriate
performance_schematables. The items associated with InnoDB all contain the substringinnodbin theNAMEcolumn.Mutexes in the
MUTEX_INSTANCEStable.RW-locks in the
RWLOCK_INSTANCEStable.File I/O operations in the
FILE_INSTANCES,FILE_SUMMARY_BY_EVENT_NAME, andFILE_SUMMARY_BY_INSTANCEtables.Threads in the
PROCESSLISTtable.
During performance testing, examine the performance data in the
EVENTS_WAITS_CURRENTandEVENTS_WAITS_HISTORY_LONGtables. If you are interested especially in InnoDB-related objects, use the clausewhere name like "%innodb%"to see just those entries; otherwise, examine the performance statistics for the overall MySQL server.You must be running MySQL 5.5, with the Performance Schema enabled by building with the
--with-perfschemabuild option.
This is the User’s Guide for InnoDB storage engine 1.1 for MySQL 5.5, generated on 2010-04-13 (revision: 19994) .
