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 awesome ps_helper scripts to convert from picoseconds to something meaningful to me):
Continue reading Practical P_S: How old are your connections? →

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” to IPv4-specific 127.0.0.1, where connections fail.  In working through this problem, I found myself wishing that PROCESSLIST output included information about which mechanism or interface was being used by each connection.  Fortunately, we can leverage PERFORMANCE_SCHEMA to extend PROCESSLIST in meaningful ways – this post aims to demonstrate how to do this by adding information about the interface as an example.

Continue reading Practical P_S: Extending PROCESSLIST →

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 – run into frequently.

If you are a DBA rather than a developer, though, your only real clue that something is wrong may be a perpetually increasing Aborted_clients status variable counter. The manual has a page dedicated to solving such (and related) connection problems, and it references tools such as the general query log and error log.  The Aborted_clients status variable is useful to answer the question, “how many connections have been closed without an explicit quit request from the client?”  And prior to 5.6, that’s about as much information as you could expect to get:

mysql> show global status like 'aborted_clients';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Aborted_clients | 5     |
+-----------------+-------+
1 row in set (0.00 sec)

With PERFORMANCE_SCHEMA in 5.6, we can isolate the problem to specific accounts, and we can calculate the percentage of client connections which were terminated without an explicit quit command from the client.  You can do that with the following query:

SELECT 
    ess.USER,
    ess.HOST,
    (a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) - ess.COUNT_STAR not_closed,
    ((a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) - ess.COUNT_STAR) * 100 / 
       (a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) pct_not_closed
FROM
    performance_schema.events_statements_summary_by_account_by_event_name ess
        JOIN
    performance_schema.accounts a ON (ess.USER = a.USER AND ess.HOST = a.HOST)
WHERE
    ess.EVENT_NAME = 'statement/com/Quit'
        AND (a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) > ess.COUNT_STAR;

The easiest way to test this is to make a handful of connections, issue SET @@session.wait_timeout = 1 – here’s the result of the above query after doing so:

+------+-----------+------------+----------------+
| USER | HOST      | not_closed | pct_not_closed |
+------+-----------+------------+----------------+
| root | localhost |          4 |        44.4444 |
| ODBC | localhost |          1 |       100.0000 |
+------+-----------+------------+----------------+
2 rows in set (0.00 sec)

Knowing which accounts are failing to properly close connections can help quickly spotlight where further investigation should be focused.  And with MySQL 5.6, DBAs can get that information without resorting to the general query log or via application logs.