UserStats
Summary
Activity monitoring per table, index, account, and machine, to measure database activity and aggregate the results per account, table and index. The information is made accessible through SQL SHOW statements and INFORMATION_SCHEMA. Statistics can be cleared through SQL FLUSH statements and MySQL client API
Note that rows changed includes rows from insert, update, delete and replace statements, but only from InnoDB and MyISAM tables. The patch could be extended (in 5.1) to for instance PBXT.
Patch Origin
Google (Mark Callaghan), updates and fixes by Percona and Open Query.
Commands
- SHOW TABLE_STATISTICS - Display 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.
- SHOW INDEX_STATISTICS - Display the number of rows fetched per index. It can be used to find unused indexes.
- SHOW USER_STATISTICS - Display resource consumption for all sessions per database account
- SHOW CLIENT_STATISTICS - Same values as SHOW USER_STATISTICS but aggregated by client IP address rather than by database account name.
- FLUSH TABLE_STATISTICS - Each of the flush commands clears the counters used for the given SHOW command.
- FLUSH INDEX_STATISTICS
- FLUSH CLIENT_STATISTICS
- FLUSH USER_STATISTICS
Output
- 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
INFORMATION_SCHEMA
- TABLE_STATISTICS
- INDEX_STATISTICS
- CLIENT_STATISTICS
- USER_STATISTICS
Please note that in patchset d6 builds, these table names need to quoted with backticks as the parser regards them as reserved words. A patch for this has already been committed.
MySQL client API
Adds REFRESH_*_STATS commands to MySQL client API mysql_refresh(). Client app must use OurDelta libmysqlclient-dev library to have these bitmapped defines, otherwise they can be hardcoded:
REFRESH_TABLE_STATS 256 REFRESH_INDEX_STATS 512 REFRESH_USER_STATS 1024 REFRESH_CLIENT_STATS 8192