Starting with InnoDB 1.1 with MySQL 5.5, you can profile certain internal InnoDB operations using the MySQL Performance Schema feature. 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 1.1 or higher.
You must be generally familiar with how to use the Performance Schema feature, for example to query tables in the
Examine the following kinds of InnoDB objects by querying the appropriate
performance_schematables. The items associated with InnoDB all contain the substring
For the definitions of the
*_instancestables, see Performance Schema Instance Tables. For the definitions of the
*_summary_*tables, see Performance Schema Summary Tables. For the definition of the
threadtable, see Performance Schema Miscellaneous Tables. For the definition of the
*_history_*tables, see Performance Schema Wait Event Tables.
Mutexes in the
mutex_instancestable. (Mutexes and RW-locks related to the
InnoDBbuffer pool are not included in this coverage; the same applies to the output of the
SHOW ENGINE INNODB MUTEXcommand.)
RW-locks in the
RW-locks in the
File I/O operations in the
Threads in the
During performance testing, examine the performance data in the
events_waits_history_longtables. If you are interested especially in InnoDB-related objects, use the clause
WHERE EVENT_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
For more information about the MySQL Performance Schema, see MySQL Performance Schema.