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

  1. number of seconds executing SQL commands (wall time and CPU time)
  2. number of concurrent connections (the current value)
  3. number of connections created
  4. number of rollbacks
  5. number of commits
  6. number of select statements
  7. number of row change statements
  8. number of other statements and internal commands
  9. number of rows fetched
  10. number of rows changed
  11. number of bytes written to the binlog
  12. number of network bytes sent and received
  13. number of rows read from any table
  14. number of failed attempts to create a connection
  15. number of connections closed because of an error or timeout
  16. number of access denied errors
  17. 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

Comments are closed.