Historical - UserTable Monitoring

This is a post about user, table and index monitoring added by my team at Google. It was shared at code.google.com which has since shutdown. Eventually I wrote a better guide here. This was in production around 2007, several years before something similar was provided by upstream. This monitoring allowed us to keep MySQL from collapsing. I can't imagine how anyone did web-scale MySQL without it. The big-3 problems for me back then were: lack of monitoring, replication and InnoDB on many-core.

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.

Introduction

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.

Details

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

SHOW USER_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

SHOW CLIENT_STATISTICS

This has the same values as SHOW USER_STATISTICS but they are aggregated by client IP address rather than by database account name.

SHOW TABLE_STATISTICS

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.

SHOW INDEX_STATISTICS

This displays the number of rows fetched per index. It can be used to find unused indexes.

Flush commands

Each of the flush commands clears the counters used for the given SHOW command.