This is a module for monitoring the MySQL SQL database server health (latest versions in the 3.23 or 4 series). Carefully chosen relevant indicators are displayed, allowing the administrator to set meaningful thresholds.
It requires the mysqltcl package (at http://www.xdobry.de/mysqltcl/) for connection via the native MySQL protocol, or the tclodbc package (at http://sourceforge.net/projects/tclodbc) for connection via ODBC (Open DataBase Connectivity).
Note that although this module is for Linux, the MySQL server to be monitored could be running on any remote machine, even a Windows one.
Data is drawn from the SHOW STATUS and SHOW VARIABLES query results and initially displayed in 1 table:

There are 2 data columns:
- The absolute value for a variable, as directly returned by the database server, or calculated in the case of indicators.
- The per hour values for variables or indicators that support it, such as counters, quantities, ... It is left blank for variables that contain text or contains a ? character for numeric variables where a per hour value makes no sense.
The data rows are:
- The version number for the server, for better identification.
- The uptime of the server. A threshold of type unknown can be set on the absolute value cell to detect an unreachable server or a server that has gone down.
- The number of slow queries (that have taken more than long_query_time). A threshold of type up with a value of around 10 to 20 can be set on the per hour cell to detect that too many queries take a long time to execute and should be optimized. To pinpoint a reason for slowness more easily, use the --log-slow-queries and --log-long-format options with the mysqld server.
- The simultaneous remaining connections left before the number of connections reaches the maximum number of simultaneous clients allowed (as defined by the max_connections variable). A threshold of type down on a low value (such as 1 to 5) can be set on the absolute value cell to detect that more clients may not be able to connect to the database (see the MySQL documentation about the too many connections error).
- select scan: the number of joins where we scanned the first table. A threshold of type up with a value of around 50 to 100 can be set on the per hour cell to detect that some queries should be optimized (see optimizing queries below).
- select full join: the number of joins without keys (should be 0). A threshold of type up with a low value can be set on the per hour cell to detect that some queries should be optimized, possibly by adding indices to some tables (see optimizing queries below).
Note that full joins are worse than select scans, since they are actually Cartesian products.
- aborted connects: the number of tries to connect to the MySQL server that failed. A threshold of type up with a value of around 10 to 100 can be set on the per hour cell to detect cases such as:
- when a connection packet doesn't contain the right information.
- when the user didn't have privileges to connect to a database.
- when a user uses a wrong password.
- when it takes more than connect_timeout seconds to get a connect package.
Note that the above could indicate that someone is trying to break into your database!
- created tmp disk tables: the number of implicit temporary tables on disk created while executing statements. A threshold of type up with a value of around 100 to 1000 can be set on the per hour cell to detect that some queries should be optimized (see optimizing queries below).
- created tmp files: how many temporary files mysqld has created. Temporary files can for example be created by ORDER BY, ordered GROUP BY, some COUNT(DISTINCT()), single and multiple tables UPDATE, DELETE (large ones), ... A threshold of type up with a value of around 100 to 1000 can be set on the per hour cell to detect that some queries should be optimized, possibly by adding indices, or eventually that the client application itself needs optimization, ... There is no general recipee in order to solve this problem, each application being specific.
Optimizing queries:
Please see the MySQL documentation about optimizing queries and the EXPLAIN command.
Use EXPLAIN to get see what is wrong. Do it not just for SELECT queries but also for UPDATE and DELETE queries.
For example, on a query such as:
UPDATE table SET ... WHERE x = 0
try:
EXPLAIN SELECT * FROM table WHERE x = 0
Module options:
-
--dsn
ODBC Data Source Name (see your database/system administrator if in doubt). In this case, the tclodbc package is used for connecting to the database. This option is incompatible with the --host or --p (--port) options.
-
--host
host name or IP address where the database server is running (defaults to localhost).
-
--p (--port)
port used by the database server. 3306 is used internally if not specified.
-
--user
database user name (defaults to current user).
-
--password
database password for user (no default).
Examples:
$ moodss myhealth --host 1.2.3.4
$ moodss myhealth --host dbserver.company.com --port 3307
$ moodss myhealth --host dbserver.company.com --user status --password xxx
$ moodss myhealth --dsn mydb --user status --password xxx