21.9.6. Performance Schema Connection Tables

21.9.6.1. The accounts Table
21.9.6.2. The hosts Table
21.9.6.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:

accounts: Connection statistics per client account
hosts: Connection statistics per client host name
users: Connection statistics per client user name

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:

The accounts table will have four rows, for the user1/hosta, user1/hostb, user2/hosta, and user2/hostb account values, each row counting one connection per account.
The users table will have two rows, for user1 and user2, each row counting two connections per user name.
The hosts table will have two rows, for hosta and hostb, each row counting two connections per host name.

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.

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

Rows with CURRENT_CONNECTIONS = 0 are deleted.
For rows with CURRENT_CONNECTIONS > 0, TOTAL_CONNECTIONS is reset to CURRENT_CONNECTIONS.
Connection summary tables that depend on the connection table are truncated implicitly (summary values are set to 0). For more information about implicit truncation, see Section 21.9.8.7, "Connection Summary Tables".