MySQL monitoring

[zeguigui]

Here is a simple PHP script to monitor you MySQL servers.

It monitors questions and slow queries (classical) but also the 4.x Query Cache feature.

To use it simply create a script data input method with command line:

Code: Select all

php -f <path_cacti>/scripts/mysql_monitoring.php

Note: you need to modify the script to set your login, pass and host. You should create a user with only process privileges.

Output fields:

Code: Select all

Questions................ Questions
Slow_queries............. Slow Queries
Qcache_queries_in_cache.. Number of queries registered in the cache
Qcache_inserts........... Number of queries added to the cache
Qcache_hits.............. Number of cache hits
Qcache_not_cached........ Number of non-cached queries
Qcache_free_memory....... Amount of free memory for query cache
Qcache_total_blocks...... Total number of blocks in query cache
Qcache_free_blocks....... Number of free memory blocks in query cache
Qcache_lowmem_prunes..... Requests removed from cache

This script can be easily modified to monitor other STATUS variables like threads and others.

Hope this helps!
Guillaume

[Guest]

Hi! Thanks for your script..
O have some problem with the graphs.. The script is working nice..
I need help with the graph template i think.. because i trying almos anything option there, but the graphs dont show any information.. :/

Can help me with the steps for the graphs?

Thanks!!!

Daniel

[zeguigui]

Here is an host template to go with this mysql monitoring.

Note: some of the items are in french... but it is easy to find the meaning from the datasource!

Template exported from cacti 0.8.6c

[markmaunder]

Firstly both Cacti and this package rock. I'm just getting into the mysql pack, but looks very very good.

If you're having problems connecting, you may want to change this line in mysql_stats.php:
die("Error: MySQL connect failed. Check MySQL paramete.....

to read like so:

die("Error: MySQL connect failed. Check MySQL parameters (host/login/password)\n" . mysql_error());

That way you will be able to actually see what the mysql error is. In my case it was:

Client does not support authentication protocol requested by server. Consider upgrading MySQL client

And you fix it by running the following command from your mysql client on the host you are trying to connect to:

mysql> SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');

That way your older php mysql client will be able to connect to your server.

Hope that helps someone else. Thanks again for a great package.

Mark.

[BSOD2600]

how does this script differ from this one? http://forums.cacti.net/viewtopic.php?t=6108

[Wernicke]

Is Questions the amount of questions the last 5 minutes?
What is slow questions?

-Wernicke

[fmangeant]

Is Questions the amount of questions the last 5 minutes?
What is slow questions?

Hi

the data source for "questions" is a COUNTER, so on the graph you see questions/s on a 5 minutes range. If you want to see total # of questions/5 min, add the CDEF "Make per 5 minute" to the graph.

From http://dev.mysql.com/doc/mysql/en/slow-query-log.html, slow queries are "SQL statements that took more than long_query_time seconds to execute", but I don't know anyhting more...