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
    ->  user,
    ->  host,
    ->  ps_helper.format_time(sum_timer_wait) total_idle,
    ->  ps_helper.format_time(avg_timer_wait) average_idle,
    ->  ps_helper.format_time(max_timer_wait) max_idle
    -> FROM events_waits_summary_by_account_by_event_name
    -> WHERE event_name = 'idle'
    ->  AND host IS NOT NULL;
+---------+-----------+---------------+---------------+---------------+
| user    | host      | total_idle    | average_idle  | max_idle      |
+---------+-----------+---------------+---------------+---------------+
| nothing | localhost | 05:54:56.1089 | 00:25:21.1506 | 05:08:17.4926 |
| root    | localhost | 13:04:08.5643 | 00:03:30.9801 | 02:08:05.6043 |
+---------+-----------+---------------+---------------+---------------+
2 rows in set (0.50 sec)

That’s a good initial start – total idle time is something worth looking at in any context, but it’s somewhat expected to see larger values for accounts supporting applications using persistent connections. For accounts where JDBC connection pooling is in use, you would expect to see both average and maximum idle times below the threshold for your connection pool maintenance thread to check connections. If you see average or maximum times exceeding those values, it could be a sign that your connection pool maintenance threads have too many idle connections to maintain (pool is oversized).

It would also be a concern if average or max idle times approach or exceed wait_timeout (or interactive_timeout) – that suggests that application connections are not being maintained properly (or possibly leaked, such that persistent connections aren’t being reused).

All of this is highly dependent upon proper configuration and behavior of application-side code, so it’s possible that you’ll want to look at this data not by account, but rather by host from which the connection originates. That makes it much easier to identify potentially problematic configuration on a specific application host, even if the account is shared across many such hosts. PERFORMANCE_SCHEMA also makes this easy:

mysql> SELECT
    ->  host,
    ->  ps_helper.format_time(sum_timer_wait) total_idle,
    ->  ps_helper.format_time(avg_timer_wait) average_idle,
    ->  ps_helper.format_time(max_timer_wait) max_idle
    -> FROM events_waits_summary_by_host_by_event_name
    -> WHERE event_name = 'idle'
    ->  AND host IS NOT NULL;
+-----------------------------+---------------+--------------+---------------+
| host                        | total_idle    | average_idle | max_idle      |
+-----------------------------+---------------+--------------+---------------+
| TFARMER-MYSQL.wh.oracle.com | 20.61 s       | 6.87 s       | 12.52 s       |
| localhost                   | 00:04:38.1177 | 27.81 s      | 00:02:05.8972 |
+-----------------------------+---------------+--------------+---------------+
2 rows in set (0.22 sec)

It’s also possible to get a very rough estimate of the percentage of time connections are spending idle. The accuracy of this is constrained by not having access to reliable information about the duration of a connection as well as the elapsed time for incomplete events, as noted in my previous blog post. But this gets us pretty close:

mysql> SELECT
    ->  user,
    ->  host,
    ->  100 * (SUM(IF(event_name = 'idle', sum_timer_wait, 0))
    ->    / SUM(sum_timer_wait)) pct_idle,
    ->  ps_helper.format_time(
    ->    SUM(IF(event_name = 'idle', sum_timer_wait, 0))
    ->  ) total_idle
    -> FROM events_waits_summary_by_account_by_event_name
    -> WHERE host IS NOT NULL
    -> GROUP BY user, host;
+---------+-----------------------------+----------+---------------+
| user    | host                        | pct_idle | total_idle    |
+---------+-----------------------------+----------+---------------+
| root    | localhost                   |  99.9919 | 00:18:55.3054 |
| test_hc | TFARMER-MYSQL.wh.oracle.com | 100.0000 | 20.61 s       |
+---------+-----------------------------+----------+---------------+
2 rows in set (0.02 sec)

Note that this only looks at completed events, so a connection which idles for hours immediately after connection won’t be reflected in the above queries.