Idle connections can cause problems both at the application side, increasing the risk of connection timeouts for applications where persistent connections are used, and the server side, where resources remain allocated to idle connections.  Any application with persistent connections, such as a JDBC application using a connection pool, will have periods where connections are idle – but it’s good to know how much time is spent idle.  Too much idle time might mean connections pools configured to allow too many connections to sit idle in a connection pool, or not properly doing connection pool maintenance.

PERFORMANCE_SCHEMA in MySQL 5.6 makes it trivial to measure absolute time spent waiting.  This will show total, average and maximum idle times by account:

mysql> SELECT …

I’ve often wished that PROCESSLIST exposed when a connection was first established, and I find myself wishing for this information more now with MySQL 5.6.  Improvements to PERFORMANCE_SCHEMA make it trivial to see how much time is being spent in various operations for a given connection – but it would make some analysis (“what percentage of connection time is spent doing X?”) easier.

That said, it is possible to approximate connection age with PERFORMANCE_SCHEMA in MySQL 5.6.  I say “approximate” because results will vary based on what instrumentation exists, is enabled, and is collecting timing data.  That’s because we’re just doing a SUM() on the SUM_TIMER_WAIT column for all instrumented waits.  Here’s an example (FYI, I’m using the format_time() function from Mark Leith’s …

In a previous post, I described how to leverage PERFORMANCE_SCHEMA in MySQL 5.6 to identify connections which had not been properly closed by the client.  One possible cause of connections being closed without explicit request from the client is when another process issues a KILL CONNECTION command:

mysql> SHOW GLOBAL STATUS LIKE 'aborted_clients';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Aborted_clients | 0     |
+-----------------+-------+
1 row in set (0.02 sec)

mysql> KILL CONNECTION 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'aborted_clients';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Aborted_clients | 1     |
+-----------------+-------+
1 row in set (0.00 sec)

You …

MySQL Server has an aborted_connect status counter which will show you the number of failed attempts to establish a new connection.  The manual describes potential causes as follows:

• A client does not have privileges to connect to a database.
• A client uses an incorrect password.
• A connection packet does not contain the right information.
• It takes more than connect_timeout seconds to get a connect packet. See Section 5.1.4, “Server System Variables”.

It goes on to make the following statement:

If …

MySQL 5.6 introduced major advances to monitoring made via PERFORMANCE_SCHEMA, but also made a change in how it binds to the network by default.  In MySQL 5.5, the –bind-address configuration option defaulted to “0.0.0.0″, meaning only IPv4.  This changed to “*” in MySQL 5.6, accepting connections on both IPv6 and IPv4 interfaces.  Somehow (I’ve not looked into it yet), my (unsupported) WindowsXP installation now refuses to bind to IPv4, which caused surprising problems for certain tools that seem to internally map “localhost” …

I’ve previously written about several problems which can benefit from additional visibility provided by PERFORMANCE_SCHEMA in MySQL 5.6, and it’s time to add to that list.  A very common problem involves connections which are not properly closed – they simply idle until they reach wait_timeout (or interactive_timeout, depending on the client flags set), and the server terminates the connection.  Who knows what the root cause is – perhaps the client terminated without cleaning up connections, or maybe there was just no load, or maybe the network cable was unplugged.  It’s something application developers – particularly those using persistent connections managed by a pool – …

I was testing out the latest MySQL 5.6 on Windows (having used the auto-installer) and happened to notice my RAM usage (via Windows Task Manager) was reporting quite a high value, when I had very modest ram/buffer settings (should have been around 40M, but instead it was around 400M).

After double/triple-checking my settings to make sure I didn’t overlook something obvious, I searched the bugs database, and ran across bug #68287:

“High Memory Usage with MySQL 5.6.12 GA in ‘Development Machine’ mode”

Turns out, using the auto-installer set the value of table_definition_cache=1400, when the minimum value is 400. Reducing it to 400, and restarting MySQL immediately lowered the RAM usage, and is the “work-around” identified in the bug report.

Having encountered this, …

In case you missed it, more and more people are now spreading the word about the Performance Schema, which is a very good thing.

#DBHangOps 4/10/13

Mark Leith presents the Performance Schema and ps_helpers.

Random quotes from the recording:
"I am already seeing so many benefits out of this, ..."
"This looks fantastic"
"Very cool"

OurSQL Episode 139: Starting to Perform

Sheeri and Gerry present the Performance Schema.

Ramdom quote from the recording:
"I am looking at this feature [digests], and I think it's amazing"

Webinar: MySQL 5.6 Performance Schema

PeterZ …

While Domas may have rather effictively trolled the discussion with his post on howto configure table/user statistics (which gave me a good chuckle I do have to say), it’s at least incorrect for Percona Server as you have to enable the “userstat” server option :)

That being said, once enabled there are no extra configuration variables to think about. This is a huge advantage over configuring PERFORMANCE_SCHEMA - which has a total of THIRTY configuration options (31 if you include the global enable/disable option).

Some of these thirty odd configuration variables are only going to matter if you’re loading your own plugins, and even then, it’s probably only going to matter if they use the MySQL mutex implementations rather than, …

On configuring the Performance Schema

This article is a user guide about MySQL 5.6 Performance Schema configuration. As with many things, the way to approach problems may vary a lot based on systems, user experiences, or just plain opinions, so the "Your Mileage May Vary" caution applies here.

It is easy to get lost in details, and yet starting with the big picture in mind helps to understand not only how, but also more importantly why, to do things ...

The magic recipe is as follows

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

Define your goals
Performance instrumentation in general can be used for many different things, ranging from casual monitoring in production to debugging in development, with every flavor in …