GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

generate a per-datebase usage report

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • generate a per-datebase usage report

    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.

    Comment

    Working...
    X