Databases Reference
In-Depth Information
The Performance Schema
The Performance Schema (which resides in the
PERFORMANCE_SCHEMA
database) is
MySQL's new home for enhanced instrumentation, as of MySQL 5.5. We discussed it
a bit in
Chapter 3
.
By default, the Performance Schema is disabled, and you have to turn it on and enable
specific instrumentation points (“consumers”) that you wish to collect. We bench-
marked the server in a few different configurations and found that the Performance
Schema caused around an 8% to 11% overhead even when it was collecting no data,
and 19% to 25% with all consumers enabled, depending on whether it was a read-only
or read/write workload. Whether this is a little or a lot is up to you to decide.
This is slated to improve in MySQL 5.6, especially when the feature itself is enabled
but all of the instrumentation points are disabled. This will make it more practical for
some users to enable the Performance Schema, but leave it inactive until they want to
gather some information.
In MySQL 5.5, the Performance Schema contains tables that instrument instances of
condition variables, mutexes, read/write locks, and file I/O. There are also tables that
instrument the waits on the instances, and these are what you'll usually be interested
in querying first, with joins to the instance tables. These event wait tables come in a
few variations that hold current and historical information about server performance
and behavior. Finally, there are is a group of “setup tables,” which you use to enable
or disable the desired consumers.
In MySQL 5.6.3 development milestone release 6, the number of tables in the Perfor-
mance Schema increases from 17 to 49. That means that there is a lot more instru-
mentation in MySQL 5.6! Added instrumentation includes SQL statements, stages of
statements (basically the same thing as the thread status you can see in
SHOW PROCESS
LIST
), tables, indexes, hosts, threads, users, accounts, and a larger variety of summary
and history tables, among other things.
How can you use these tables? With 49 of them, the time has come for someone to
write tools to help with this. However, for some good examples of old-fashioned SQL
against the Performance Schema tables, you can read some of the articles on Oracle
engineer Mark Leith's blog, such as
http://www.markleith.co.uk/?p=471
.
