IntroductionWe 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. DetailsNote 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
SHOW USER_STATISTICSThis 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
SHOW CLIENT_STATISTICSThis has the same values as SHOW USER_STATISTICS but they are aggregated by client IP address rather than by database account name. SHOW TABLE_STATISTICSThis 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. SHOW INDEX_STATISTICSThis displays the number of rows fetched per index. It can be used to find unused indexes. Flush commandsEach of the flush commands clears the counters used for the given SHOW command.
|
Sign in to add a comment
Sounds very useful. I'll look for a download. (I guess it is available somewhere?)
I found it in SVN.
Updated version (still MySQL 5.0) is in the OurDelta? patchset and builds (http://ourdelta.org) For instance, the original patch produced additional reserved words in the parser.
Would be good to add bytes counts too. And counts for disk I/O if possible at all.
It's work in 5.1.32 ???