Baron pointed to two really good articles recently by Brendan Gregg on Filesystem Latency (part1 / part2), that I too would recommend everybody read.

They rightly point out that trying to correlate IO statistics from tools such as iostat to a databases workload, especially when that database is on a shared resource, is fraught with complications – and further to that, not the full picture anyway, even on a dedicated system. Brendan points out that to get the full picture, you really need to be instrumenting your profiling at the read/write layer of the application …

In my previous post, I’ve showed how to use Dtrace to find queries creating disk temporary tables (only available for OS with dtrace: solaris, freebsd, etc).

In MySQL 5.6 (which is not released yet, use “labs” version for now) we can use new performance_schema table events_statements_history or events_statements_history_long to find all performance metrics for all queries including created disk/memory tables, use of index, etc. WOW! This is what I have been waiting for a long time!

To illustrate, I have grabbed mysql-5.6.3-labs-performance-schema-linux2.6-x86_64.tar.gz from labs.mysql.com (this feature is only in labs version) and run sysbench readonly test (you need to disable prepared statements in sysbench, seems to be not working with …

One of the first thing to do to use the performance schema is to add "performance_schema" in the my.cnf file.

This typically prompts a long list of question from users, as there is a lot to know here.

This article attempts to cover more specifically this area.

It is long, but (I hope) also worth it, so keep reading ;)

Also, since some new performance schema features have been advertised in the mysql labs, this article also covers some never seen before parts of the performance schema.

Really, keep reading ;)

Q: Why do I even have to do anything ? Can the performance schema not be enabled by default ?

No. It could, but this is not a good choice.

Let's face it, some users do not even read the documentation.

If someone is not even aware that the performance schema feature exists in 5.5 (or 5.6), turning it on by default for a new 5.5 installation, or even worse, turning …

Another new feature of performance_schema within 5.6 is the ability to monitor both table and index IO – this was added in two parts:

• WL#4895 – PERFORMANCE SCHEMA TABLE IO
• WL#5379 – PERFORMANCE SCHEMA TABLE / INDEX IO SUMMARY

If you read my blog on graphing statement wait times – some of the data that WL#4895 exposes is seen within the examples there, they are within the “light coral” coloured blocks, such as the following from the InnoDB PK lookup example I gave:

Q: Is it possible to enable / disable the performance schema at runtime ?

A: Yes.

Configure, configure and configure

The performance schema is affected by three distinct set of configuration parameters, that take effect at compile time, server startup, or runtime.

Compile time options

When building from the source code, make sure the cmake flag 'WITH_PERFSCHEMA_STORAGE_ENGINE' is set to 'ON' (it is by default).

For packages built by Oracle, the performance schema is included by default.

Server startup options

Simply add the 'performance_schema' option to your my.cnf configuration file.

The effect of this option seems mis understood, leading people to think that the performance schema can only be enabled or disabled at server startup time, which is not the case.

The role of this startup option, with the related sizing parameters, is to initialize the …

Following up to my previous blog on graphing statement execution in performance_schema, Sunny Bains on the InnoDB team pointed out that in looking at the INSERT graph, he didn’t think I had atomic operations enabled within my build.

Particularly here (from trunk):

225 /******************************************************************//**
226 Increments lock_word the specified amount and returns new value.
227 @return lock->lock_word after increment */
228 UNIV_INLINE
229 lint
230 rw_lock_lock_word_incr(
231 /*===================*/
232         rw_lock_t*      lock,           /*!< in/out: rw-lock */
233         ulint           amount)         /*!< in: amount of increment */
234 {
235 #ifdef …

I tried to come up with a number of topics for this post, but none seemed to really convey what I really feel.. And really this blog is about all of them..

• A look at PERFORMANCE_SCHEMA.STAGES and PERFORMANCE_SCHEMA.STATEMENTS
• Graphing MySQL Statement Execution
• A MySQL visual EXPLAIN plan with PERFORMANCE_SCHEMA
• Tracing Sessions with PERFORMANCE_SCHEMA

If that doesn’t whet your appetite (and trust me, I need to, this post is long, but I feel is worth reading all the way to the end), then let me start out by asking the question:

Wouldn’t you like to be able to trace what a SQL statement did, either in the same or another session, on a production instance, after the fact? Wouldn’t you like to know where all of it’s time was spent, and some statistics on what it was doing specifically?

I know, …

Now that mysql 5.5 is GA, development for the performance schema continues in the mysql-trunk code base. A lot of projects have been completed already, which are worth mentioning.

New table io instrumentation

Table io is now instrumented. As the performance instrumentation is located in the MySQL layer and not inside the storage engine, this instrumentation is de facto available to all storage engines, with zero code change.

New table lock instrumentation

Table locks are also instrumented. It is a nice complement to table io, and can be enabled or disabled independently from table io. Not all table waits are io problems, some are also contention caused by locks ... and there is now a way to observe that.

New stages instrumentation

Major execution phases of a statement, called stages, are now instrumented. This instrumentation is critical to identify which part of the execution path (parser, acl, …

I was looking at the latest MySQL versions, and I happened to notice that there has been a great increment in the number of metadata tables, both in the information_schema and performance_schema databases. So I made a simple count of both schemas in the various versions, and draw a graph. The advance looks straightforward.

The PERFORMANCE SCHEMA is dead

The initial implementation of the performance schema used to name tables in the performance schema database in UPPERCASE, as in 'EVENTS_WAITS_CURRENT'.

This naming scheme turned up to be difficult to work with (see Bug#57609), especially considering the established MySQL practice of converting table names to lowercase, using the lower_case_table_names server setting.

Long live the performance schema

Starting with MySQL 5.5.8, the performance schema tables have been renamed to lowercase.

This guarantees that a query such as 'select * from performance_schema.events_waits_current' will work consistently across any server deployment, regardless of the server platform or the server …