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:
The Performance Schema feature must be available and enabled, as described in Section 21.2, “Performance Schema Configuration”. 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 generally familiar with how to use the
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
For the definitions of the
Section 21.9.3, “Performance Schema Instance Tables”. For the
definitions of the
*_summary_* tables, see
Section 21.9.14, “Performance Schema Summary Tables”. For the
definition of the
thread table, see
Section 21.9.15, “Performance Schema Miscellaneous Tables”. For
the definition of the
*_history_* tables, see
Section 21.9.4, “Performance Schema Wait Event Tables”.
Mutexes in the
mutex_instances table. (Mutexes and
RW-locks related to the
pool are not included in this coverage; the same applies
to the output of the
SHOW ENGINE INNODB
RW-locks in the
File I/O operations in the
Threads in the
Stage events for
TABLE and buffer pool load operations in the
event tables (added in MySQL 5.7.6). For more
Section 22.214.171.124, “Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance
Section 126.96.36.199.1, “Monitoring Buffer Pool Load Progress Using Performance Schema”.
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 EVENT_NAME LIKE '%innodb%' to
see just those entries; otherwise, examine the performance
statistics for the overall MySQL server.
For more information about the MySQL Performance Schema, see Chapter 21, MySQL Performance Schema.