generate a per-datebase usage report

Polyglot Projects Percona Toolkit
1

For shared hosting, or systems where there are many co-installed databases I would like to identify those which have high resource requirements, and any of those that are not in use.

Presumably I could either sample the data, using something like pt-collect, or I could configure the slow.log to log to disk some query event data.

Is there a preferred solution for this task?

2

If you’re using Percona Server, you can enable User Statistics. More information on this feature is found in http://www.percona.com/doc/percona-server/5.5/diagnostics/us er_stats.html?id=percona-server:features:userstatv2

When enabled, you can query table usage:
mysql> SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS
WHERE TABLE_NAME=tables_priv;
±-------------±------------------------------±----------+ --------------±-----------------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
±-------------±------------------------------±----------+ --------------±-----------------------+
| mysql | tables_priv | 2 | 0 | 0 |
±-------------±------------------------------±----------+ --------------±-----------------------+

Please let me know if you have any questions.