21.9.7 Performance Schema Connection Tables

21.9.7.1 The accounts Table
21.9.7.2 The hosts Table
21.9.7.3 The users Table

As of MySQL 5.6.3, the Performance Schema provides statistics about connections to the server. When a client connects, it does so under a particular user name and from a particular host. The Performance Schema tracks connections per account (user name plus host name) and separately per user name and per host name, using these tables:

There are also summary tables that aggregate information about connections. See Section 21.9.9.7, “Connection Summary Tables”.

The meaning of account in the connection tables is similar to its meaning in the MySQL grant tables in the mysql database, in the sense that the term refers to a combination of user and host values. Where they differ is that in the grant tables, the host part of an account can be a pattern, whereas in the connection tables the host value is always a specific nonpattern host name.

The connection tables all have CURRENT_CONNECTIONS and TOTAL_CONNECTIONS columns to track the current and total number of connections per tracking value on which statistics are based. The tables differ in what they use for the tracking value. The accounts table has USER and HOST columns to track connections per user name plus host name combination. The users and hosts tables have a USER and HOST column, respectively, to track connections per user name and per host name.

Suppose that clients named user1 and user2 each connect one time from hosta and hostb. The Performance Schema tracks the connections as follows:

When a client connects, the Performance Schema determines which row in each connection table applies to the connection, using the tracking value appropriate to each table. If there is no such row, one is added. Then the Performance Schema increments by one the CURRENT_CONNECTIONS and TOTAL_CONNECTIONS columns in that row.

When a client disconnects, the Performance Schema decrements by one the CURRENT_CONNECTIONS column in the row and leaves the TOTAL_CONNECTIONS column unchanged.

The Performance Schema also counts threads for internal threads and user sessions that failed to authenticate. These are counted in rows with USER and HOST column values of NULL.

Each connection table can be truncated with TRUNCATE TABLE, which has this effect: