Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

What is the best way to monitor activity on each database of my server ?

I tried mtop/mytop which display activity in real time but aren't very practical to see all the queries executed on it.

I activated MySQL logs but there's no option to split them per database.

What tools are you using to monitor MySQL servers ?

share|improve this question
up vote 4 down vote accepted

I try to stay away from having multiple databases in one instance. Instead I have multiple instances set up on a server where each one is dedicated to a database.

MySQL Cacti templates are a good monitoring solution. If you are enterprise customer then MySQL Enterprise Monitor works. Also check out mycheckpoint from openark.

EDIT
Here are some tutorials for setting up multiple instances on one server:

You could also use MySQL Sandbox as it automates a lot of different set ups for you.

share|improve this answer
    
Thanks for your comment, do you have a quick tutorial to set up multiple instances per server ? – tostinni Nov 7 '11 at 13:49

You have to understand where most of the tools you are using are getting their data from - SHOW ENGINE INNODB STATUS and SHOW GLOBAL STATUS. This data simply is not available broken down on a database level inside MySQL.

MySQL 5.5, 5.6 and Percona Server have been doing a great job of improving the diagnostics available to you with features like performance_schema and userstats - but I don't know of any GUI/monitoring tools that are taking advantage yet.

I recommend installing Percona Server, and taking a look at table_statistics:

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 | 
+--------------+-------------------------------+-----------+--------------+------------------------+
share|improve this answer

protected by Paul White Nov 15 '15 at 13:54

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.