Over three years ago, I noticed that there was no STATUS counter for COM_PING commands – something that is useful for ensuring proper configuration of JDBC connection pools.  Mark Leith even provided a patch, but it’s never been incorporated.  With the advances PERFORMANCE_SCHEMA makes in MySQL 5.6, that’s OK – a STATUS counter becomes somewhat redundant:

mysql> SELECT SUM(count_star) as pings
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name = 'statement/com/Ping';
+-------+
| pings |
+-------+
|    12 |
+-------+
1 row in set (0.02 sec)

Not only does PERFORMANCE_SCHEMA provide capabilities which mirror the STATUS counters, it really goes well beyond what’s capable there. A global counter is interesting, but if you have many app servers, how do you know that each of them is properly configured? A global counter won’t help much – you’ll need statistics compiled by host or account. PERFORMANCE_SCHEMA can provide this easily:

mysql> SELECT host, SUM(count_star) as pings
    -> FROM events_statements_summary_by_host_by_event_name
    -> WHERE event_name = 'statement/com/Ping'
    -> GROUP BY host;
+-----------+-------+
| host      | pings |
+-----------+-------+
| NULL      |     0 |
| localhost |    12 |
+-----------+-------+
2 rows in set (0.00 sec)

With that information, it’s easy to track down which app servers might be hosting a Java application and not configured to do lightweight COM_PING operations for connection pool maintenance.  Leveraging Connector/J’s support for connection attributes, you can even check the configuration of each independent connection pool:

mysql> SELECT
    ->   ca.attr_value,
    ->   SUM(count_star) as pings
    -> FROM
    ->   events_statements_summary_by_thread_by_event_name ess
    -> JOIN
    ->   threads t
    ->     ON (t.thread_id = ess.thread_id)
    -> JOIN
    ->   session_connect_attrs ca
    ->     ON (ca.processlist_id = t.processlist_id)
    -> WHERE ca.attr_name = 'pool'
    ->   AND ess.event_name = 'statement/com/Ping'
    -> GROUP BY ca.attr_value;
+------------+-------+
| attr_value | pings |
+------------+-------+
| first      |    68 |
| second     |     0 |
+------------+-------+
2 rows in set (0.09 sec)

Note that you connection attributes are not persisted beyond current connections, so the tables used are a bit different than the earlier examples.  Just for completeness, here’s the Java code I used for testing:

public static void testAttributesPing() throws Exception {
	Class.forName("com.mysql.jdbc.Driver");
	Properties props = new Properties();
	props.setProperty("user", "root");
	props.setProperty("connectionAttributes", "pool:first");
	Connection conn1 = DriverManager.getConnection(
			"jdbc:mysql://localhost:3307/test", props);

	props.setProperty("connectionAttributes", "pool:second");
	Connection conn2 = DriverManager.getConnection(
			"jdbc:mysql://localhost:3307/test", props);

	while(true) {
		conn1.createStatement().execute("/* ping */ SELECT 1");
		conn2.createStatement().execute("SELECT 1");
		Thread.sleep(1000);
	}

}

Connection attributes are a convenient tool for filtering data in PERFORMANCE_SCHEMA – it’s easy to annotate each individual JDBC resource configuration so that performance and behavior can be monitored independent of other configurations, regardless of whether it’s deployed using the same MySQL account, on the same host, the same application server or JVM, or even the same application.