Performance monitoring in MySQL

There are several types of performance monitoring. Some of them are made easier when workload metrics are summarized by database user, table and index. The Google patch added SHOW TABLE_STATISTICS, SHOW INDEX_STATISICS and SHOW USER_STATISTICS to MySQL several years ago. Since then they have been ported and improved in the Facebook patch, MariaDB and Percona Server. I think that Eric Bergen has also ported it. I guess you could say the community has spoken.

I like these features because they have been extremely useful to me. They are also simple to use and have little impact on performance. Most of what you need to know about them can be determined by listing the columns in the tables. But more than anything else they are easy to use.

The Facebook patch has these columns for user_statistics:
USER_NAME
BINLOG_BYTES_WRITTEN
BYTES_RECEIVED
BYTES_SENT
COMMANDS_DDL
COMMANDS_DELETE
COMMANDS_HANDLER
COMMANDS_INSERT
COMMANDS_OTHER
COMMANDS_SELECT
COMMANDS_TRANSACTION
COMMANDS_UPDATE
CONNECTIONS_CONCURRENT
CONNECTIONS_DENIED_MAX_GLOBAL
CONNECTIONS_DENIED_MAX_USER
CONNECTIONS_LOST
CONNECTIONS_TOTAL
DISK_READ_BYTES
DISK_READ_REQUESTS
DISK_READ_SVC_USECS
DISK_READ_WAIT_USECS
ERRORS_ACCESS_DENIED
ERRORS_TOTAL
MICROSECONDS_CPU
MICROSECONDS_WALL
QUERIES_EMPTY
ROWS_DELETED
ROWS_FETCHED
ROWS_INSERTED
ROWS_READ
ROWS_UPDATED
ROWS_INDEX_FIRST
ROWS_INDEX_NEXT
TRANSACTIONS_COMMIT
TRANSACTIONS_ROLLBACK

The Facebook patch has these columns for table_statistics:

TABLE_SCHEMA

TABLE_NAME

TABLE_ENGINE

ROWS_INSERTED

ROWS_UPDATED

ROWS_DELETED

ROWS_READ

ROWS_REQUESTED

ROWS_INDEX_FIRST

ROWS_INDEX_NEXT

IO_READ_BYTES

IO_READ_REQUESTS

IO_READ_SVC_USECS

IO_READ_SVC_USECS_MAX

IO_READ_WAIT_USECS

IO_READ_WAIT_USECS_MAX

IO_READ_OLD_IOS

IO_WRITE_BYTES

IO_WRITE_REQUESTS

IO_WRITE_SVC_USECS

IO_WRITE_SVC_USECS_MAX

IO_WRITE_WAIT_USECS

IO_WRITE_WAIT_USECS_MAX

IO_WRITE_OLD_IOS

IO_INDEX_INSERTS

The Facebook patch has these columns for index_statistics:

TABLE_SCHEMA

TABLE_NAME

INDEX_NAME

TABLE_ENGINE

ROWS_INSERTED

ROWS_UPDATED

ROWS_DELETED

ROWS_READ

ROWS_REQUESTED

ROWS_INDEX_FIRST

ROWS_INDEX_NEXT

IO_READ_BYTES

IO_READ_REQUESTS

IO_READ_SVC_USECS

IO_READ_SVC_USECS_MAX

IO_READ_WAIT_USECS

IO_READ_WAIT_USECS_MAX

IO_READ_OLD_IOS