• Define your goals
• Define what to instrument
• Define how much detail to collect
• Provide sizing data
• Monitor sizing problems

If you haven’t explored the MySQL Performance Schema yet, this is a good place to start.  This is Performance Schema 101, a basic introduction to the MySQL 5.6 performance_schema, which records runtime statistics from the MySQL database. The performance_schema is intended to provide access to useful information about server execution while having minimal impact on server performance.  Performance_schema is the name of both the storage engine and the database itself, and it was initially implemented  in MySQL 5.5. In MySQL 5.6 the engineers added quite a bit of new instrumentation.

The performance_schema database uses views or temporary tables that actually use little to no persistent disk storage .Memory allocation is all done at server startup, so there is no ongoing memory reallocation or

The performance schema tables

mysql> show tables;
+----------------------------------------------+
| Tables_in_performance_schema                 |
+----------------------------------------------+
| cond_instances                               |
|

Monitoring MySQL’s replication has always been a bit hit and miss, especially when trying to detect whether a slave is becoming overloaded or not. There’s been a few ways to do this in the past:

• Monitor the Seconds_behind_master variable from SHOW SLAVE STATUS, which is the difference between the timestamp passed down from the master that the event was executed, to the time that the SQL thread started executing the event.
• Use something like mk-heartbeat, which operates in the same way as Seconds_Behind_Master (trying to show you the actual time difference between the master and slave),

I’ve written before about how you can monitor the last wait per connection with Performance Schema in MySQL 5.5.

That solution joined the performance_schema.events_waits_current with the performance_schema.threads and INFORMATION_SCHEMA.PROCESSLIST tables.

Joining to INFORMATION_SCHEMA.PROCESSLIST is a necessary evil

I love our community.

Not long after posting my update on ps_helper, I had a couple of comments around the formatting of values within the output. Daniël van Eeden gave the suggestion that I could add a couple of Stored Functions, for formatting byte and time based values.

Of course, this was a great idea – not least for myself, because I no longer have to worry about how to format certain columns in the output.

I’ve added the following:

format_bytes()
format_time()

MY.CNF, or easier configuration

mysql> select variable_name from information_schema.global_status
where variable_name like "com\_%" order by variable_name limit 20;
+------------------------+
| variable_name          |
+------------------------+
| COM_ADMIN_COMMANDS     |
|

TABLE performance_schema.table_io_waits_summary_by_table

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

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

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

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

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,

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,

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