In my last post, I described a specific problem with prepared statements into which PERFORMANCE_SCHEMA can give visibility.  That made me wonder whether PERFORMANCE_SCHEMA can also be used to identify other areas where prepared statements run into problems.  The most significant problem tends to be leakage of prepared statements.  This can inflate memory usage, both on the server and application side, and it’s not uncommon to find applications which fail to close prepared statements.

So the question is, what can PERFORMANCE_SCHEMA tell us about how connections close (or more importantly, fail to close) prepared statements?

At the most basic level, one can check the number of PREPARE statements executed compared to DEALLOCATE PREPARE, and you can do that using global status variables.  You’re shooting for general equality between (Com_prepare_sql + Com_stmt_prepare) and (Com_stmt_close + …

Prepared statements have been with MySQL since version 4.1, including the protocol plumbing that helps support it.  What I didn’t realize – until a recent expedition through a general query log – is that the mysql command-line interface doesn’t implement the protocol commands that support this explicitly.  I came to this realization after observing a byproduct of this behavior.

The initial observation that triggered this exploration was noting that PREPARE and EXECUTE statements, when issued from the mysql command-line interface, result in two entries per command in the general query log:

6 Query    PREPARE stmt FROM 'SELECT RAND()'
6 Prepare    SELECT RAND()
6 Query    EXECUTE stmt
6 Execute    SELECT RAND()

Contrast this behavior with what is seen when a client sends COM_PREPARE and COM_EXECUTE, such as below with Connector/J (and useServerPrepStmts=true):

14 Prepare    SELECT * FROM t1 WHERE …

A few days ago, Ronald Bradford asked for a mysqldump –ignore-database option.

As a workaround, he proposes:

mysqldump --databases `mysql --skip-column-names \
   -e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') \
   FROM information_schema.schemata WHERE schema_name \
   NOT IN ('mysql','performance_schema','information_schema');" \
   >` >/mysql/backup/rds2.sql

It's a clever solution, but unfortunately it only works if you have a handful of schemas. If your databases happens to have several dozens (or hundreds or thousands) of schemas (which is where you need this option more), then the output will be truncated to the length of group_concat_max_len (by default, 1024.)

There are two alternative methods.

The all-shell methodThis method lets …

Every old mansion has some old history, and the older it is, the scarier it gets ...

MySQL is no exception: there are some very old ghosts still lurking inside the server.

One of these ghosts is the 'host cache', which haunts the server main entrance halls, just right pass the TCP/IP front door.

How long has this ghost been there ? From the very beginning it seems, as is was first spotted on April 26, 2006 ... Yes, that was 6 years ago, back in the 4.1 old times.

This ghost has been haunting DBA's worst nightmares since then.

Public sightings include:
- Bug#22821 Adding "SHOW HOST_CACHE" patch from Jeremy Cole
- Bug#24906 No command to …

In MySQL 5.6.3, that is, a while ago already, the performance schema added instrumentation for statements.

This major enhancement seem to have gone unnoticed, so a quick review of how it works, especially compared to the existing COM_ status counters that everyone is used to, can perhaps put some light here.

The MySQL server maintains 'COM_%' counters for statements executed.

These counters are available in both the session and global status.

Let's look at a few:

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     |
| COM_ALTER_DB           |
| COM_ALTER_DB_UPGRADE   |
| COM_ALTER_EVENT        |
| COM_ALTER_FUNCTION     |
| COM_ALTER_PROCEDURE    |
| COM_ALTER_SERVER       |
| …

New performance schema features in 5.6.4
As development of the 5.6 serie continues, a new milestone (5.6.4) is now available.

The source code for mysql-trunk is on launchpad, and the MySQL 5.6 documentation is also public.

In this milestone, a few new performance schema features have been implemented, as can be seen in the changelog.

MY.CNF, or easier configuration
Until now, the performance schema used a very simple, or shall I say, crude, way to decide what needs to be instrumented or not: flip the master switch on (performance_schema), and you get absolutely everything available, with as much details as possible, by …

I gave two monitoring related talks at OpenWorld, thanks to all that came along!

Both were monitoring related, the first an introduction to MySQL Enterprise Monitor, and the second a look at some of the new instrumentation that is getting developed, primarily within the MySQL 5.6 release. 

If you'd like to get a quick overview of how MySQL Enterprise Monitor works, then take a look through the "Getting to Know MySQL Enterprise Monitor" talk. This gives you a high level view of how the different pieces fit together, and then each of the important factors within the user interface:

Getting to Know MySQL Enterprise Monitor

And if you are interested in seeing how the instrumentation and monitoring landscape will look when 5.6 hits the streets, then you can get a sneak peak at the …

I gave two monitoring related talks at OpenWorld, thanks to all that came along!

Both were monitoring related, the first an introduction to MySQL Enterprise Monitor, and the second a look at some of the new instrumentation that is getting developed, primarily within the MySQL 5.6 release. 

If you'd like to get a quick overview of how MySQL Enterprise Monitor works, then take a look through the "Getting to Know MySQL Enterprise Monitor" talk. This gives you a high level view of how the different pieces fit together, and then each of the important factors within the user interface:

Getting to Know MySQL Enterprise Monitor

And if you are interested in seeing how the instrumentation and monitoring landscape will look when 5.6 hits the streets, then you can get a sneak peak at the …

The problem For illustration, let's assume a MySQL server, with a couple of users (A, B, C, ...) who perform queries against some tables (T1, T2, etc) in the database.
Sooner of later, someone will want to know which user is performing which amount of table io against which table ... How can instrumentation help with that ?

Performance schema performance tuning One of the most important question users ask before deciding to use the performance schema is: what is the overhead ? The underlying concern of course is to make sure deploying the performance schema does not negatively impact production ("First, do no harm").
The question is simple, and yet the answer is not so simple, as it depends on so many things.
As seen in some benchmarks done already, the overhead when using the performance schema can vary a lot, and in some cases really causes degradations.
A lot of time has been spent investigating the root causes (note the plural form) for the performance schema overhead in general, so I think it is a good time to share current findings.

Server workload Analysing performances, and testing the effects of different code or configuration changes, can hardly be done in a vacuum. That's where benchmarks come in.
The problem with …