A common problem was a server collapsing from short-running queries. This was hard to see in SHOW PROCESSLIST but easy to spot with USER_STATISTICS.
We also had a framework to sample, archive and aggregate SHOW PROCESSLIST output from production servers.
We have added code to measure database activity and aggregate the results per account, table and index. We have also added SQL statements to display these values.
One of these days we will integrate this with the information schema.
Note that *rows changed* includes rows from insert, update, delete and replace statements.
The commands are:
- SHOW USER_STATISTICS
- SHOW TABLE_STATISTICS
- SHOW INDEX_STATISTICS
- SHOW CLIENT_STATISTICS
- FLUSH TABLE_STATISTICS
- FLUSH INDEX_STATISTICS
- FLUSH CLIENT_STATISTICS
This displays resource consumption for all sessions per database account:
- number of seconds executing SQL commands (wall time and CPU time)
- number of concurrent connections (the current value)
- number of connections created
- number of rollbacks
- number of commits
- number of select statements
- number of row change statements
- number of other statements and internal commands
- number of rows fetched
- number of rows changed
- number of bytes written to the binlog
- number of network bytes sent and received
- number of rows read from any table
- number of failed attempts to create a connection
- number of connections closed because of an error or timeout
- number of access denied errors
- number of queries that return no rows
This has the same values as SHOW USER_STATISTICS but they are aggregated by client IP address rather than by database account name.
This displays the number of rows fetched and changed per table. It also displays the number of rows changed multiplied by the number of indexes on the table.
This displays the number of rows fetched per index. It can be used to find unused indexes.
Each of the flush commands clears the counters used for the given SHOW command.