Take the 2-minute tour ×
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.

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
add comment

3 Answers 3

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
add comment

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
add comment

MONyog v5.5 has Real-Time feature which tells you what’s happening on your MySQL server RIGHT NOW. It details key metrics like top 200 queries, slow queries, databases, locked queries, locking queries, tables, users, hosts and thread states, all of this in real-time as it happens. Information is retrieved from the server every one second. And the best part is that you can get started with real-time with just a click of a button without the overhead of enabling logs.

enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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