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 can somewhat determine how many KILL statements have been executed globally using GLOBAL STATUS:

mysql> SHOW GLOBAL STATUS LIKE '%kill%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill      | 3     |
+---------------+-------+
1 row in set (0.00 sec)

I say “somewhat” because KILL commands which are parsed and processed are counted, including those where the KILL command fails because the target connection no longer exists, or the user lacks necessary privileges to KILL the connection:

mysql> SHOW GLOBAL STATUS LIKE '%kill%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill      | 3     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> KILL CONNECTION 1;
ERROR 1094 (HY000): Unknown thread id: 1
mysql> SHOW GLOBAL STATUS LIKE '%kill%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill      | 4     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> exit
Bye

D:\mysql-advanced-5.6.11-win32>bin\mysql -unothing -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql> KILL CONNECTION 4;
ERROR 1095 (HY000): You are not owner of thread 4
mysql> SHOW GLOBAL STATUS LIKE '%kill%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill      | 5     |
+---------------+-------+
1 row in set (0.00 sec)

Can we do better than this with PERFORMANCE_SCHEMA? You bet we can – we’ll start by getting a global count of KILL commands:

mysql> SELECT event_name, count_star
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name LIKE '%kill%';
+--------------------+------------+
| event_name         | count_star |
+--------------------+------------+
| statement/sql/kill |          5 |
| statement/com/Kill |          2 |
+--------------------+------------+
2 rows in set (0.00 sec)

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

mysql> SELECT event_name, count_star
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name LIKE '%kill%';
+--------------------+------------+
| event_name         | count_star |
+--------------------+------------+
| statement/sql/kill |          6 |
| statement/com/Kill |          2 |
+--------------------+------------+
2 rows in set (0.00 sec)

So here’s an interesting first observation regarding the PERFORMANCE_SCHEMA instrumentation – it differentiates between handling the KILL SQL syntax and the COM_PROCESS_KILL client/server protocol. Those commands sent as a KILL CONNECTION SQL statement are found in statement/sql/kill, while the protocol COM_PROCESS_KILL commands are found in statement/com/kill. We’re probably less interested in the different mechanisms than a total count, so I’ll rewrite the query like so:

mysql> SELECT SUM(count_star)
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name LIKE '%kill%';
+-----------------+
| SUM(count_star) |
+-----------------+
|               8 |
+-----------------+
1 row in set (0.05 sec)

Because PERFORMANCE_SCHEMA also provides a SUM_ERRORS column, we can factor out those KILL statements which failed:

mysql> SELECT SUM(count_star - sum_errors)
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name LIKE '%kill%';
+------------------------------+
| SUM(count_star - sum_errors) |
+------------------------------+
|                            5 |
+------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE '%kill%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill      | 9     |
+---------------+-------+
1 row in set (0.00 sec)

So we now have a more accurate global count of operations which killed a connection, compared to SHOW GLOBAL STATUS output. Being able to distinguish between successful and failed KILL attempts can be useful – you might want to know if an account is regularly attempting to KILL connections for which it lacks permission. PERFORMANCE_SCHEMA can help you there.

Using PERFORMANCE_SCHEMA, you can also identify the user, account or host which issues the KILL operation. To get per-account information, we’ll use the events_statements_summary_by_account_by_event_name table:

mysql> SELECT user, host, event_name, count_star, sum_errors
    -> FROM events_statements_summary_by_account_by_event_name
    -> WHERE event_name LIKE '%kill%';
+---------+-----------+--------------------+------------+------------+
| user    | host      | event_name         | count_star | sum_errors |
+---------+-----------+--------------------+------------+------------+
| nothing | localhost | statement/sql/kill |          3 |          3 |
| nothing | localhost | statement/com/Kill |          0 |          0 |
| root    | localhost | statement/sql/kill |          4 |          1 |
| root    | localhost | statement/com/Kill |          3 |          0 |
| NULL    | NULL      | statement/sql/kill |          0 |          0 |
| NULL    | NULL      | statement/com/Kill |          0 |          0 |
+---------+-----------+--------------------+------------+------------+
6 rows in set (0.01 sec)

If we want to identify who’s doing the killing, we can do the following:

mysql> SELECT user, host, SUM(count_star - sum_errors) kills
    -> FROM events_statements_summary_by_account_by_event_name
    -> WHERE event_name LIKE '%kill%'
    -> GROUP BY user, host
    -> HAVING kills > 0;
+------+-----------+-------+
| user | host      | kills |
+------+-----------+-------+
| root | localhost |     6 |
+------+-----------+-------+
1 row in set (0.05 sec)

One thing to note is that the SQL command for KILL allows one to terminate queries, instead of connections (the client/server protocol has no such option).  This means that a KILL QUERY command will increment the PERFORMANCE_SCHEMA and GLOBAL STATUS counters, while it won't cause a connection to terminate.  Here's an example:

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

mysql> SELECT event_name, count_star
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name LIKE '%kill%';
+--------------------+------------+
| event_name         | count_star |
+--------------------+------------+
| statement/sql/kill |          0 |
| statement/com/Kill |          0 |
+--------------------+------------+
2 rows in set (0.00 sec)

mysql> KILL QUERY 13;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT event_name, count_star
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name LIKE '%kill%';
+--------------------+------------+
| event_name         | count_star |
+--------------------+------------+
| statement/sql/kill |          1 |
| statement/com/Kill |          0 |
+--------------------+------------+
2 rows in set (0.00 sec)

So PERFORMANCE_SCHEMA can provide visibility into who is doing the killing, but not (yet) who – or what – is being killed. In order to obtain that information, you’ll need to enable the MySQL General Query Log or MySQL Enterprise Audit.