9

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 ?

Improve this question

2 Answers 2

Reset to default
4

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.

Improve this answer
1
  • Thanks for your comment, do you have a quick tutorial to set up multiple instances per server ?
    – tostinni
    Nov 7, 2011 at 13:49
3

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 | 
+--------------+-------------------------------+-----------+--------------+------------------------+
Improve this answer

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