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-perfschema option. 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
Schema feature, for example to query tables in the
Examine the following kinds of InnoDB objects by querying the
performance_schema tables. The
items associated with InnoDB all contain the substring
innodb in the
Mutexes in the
RW-locks in the
File I/O operations in the
Threads in the
During performance testing, examine the performance data in
EVENTS_WAITS_HISTORY_LONG tables. If you
are interested especially in InnoDB-related objects, use the
where 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-perfschema build option.
This is the User’s Guide for InnoDB storage engine 1.1 for MySQL 5.5, generated on 2010-04-13 (revision: 19994) .