This is a module for monitoring the MySQL SQL database server health (latest versions in the 4 series). Carefully chosen relevant indicators are displayed, allowing the administrator to set meaningful thresholds.

On UNIX type platforms, it requires the mysqltcl package (at http://www.xdobry.de/mysqltcl/) for connection via the native MySQL protocol, or the tclodbc package (at http://tclodbc.sourceforge.net/) for connection via ODBC (Open DataBase Connectivity).
On Windows platforms, please read the install.txt file.

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.
• key efficiency: efficiency of the key cache in %. Calculated using status variables according to the following formula:

  100 - ((Key_reads / Key_read_requests) * 100)

  where the number of physical reads of a key block from disk has too be small compared to the actual number of requests to read a key block from the cache. An efficiency greater than 90 percent is good.

Error handling:

When an error occurs (communicating with the database server or any other type), cells in the version and uptime rows become empty, while all other cells are set to void numeric values (displayed as ?). A descriptive error message is also generated in such a case.

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

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

Threads support:

If you use Tcl/Tk 8.4.1 or above with the Tcl Threads package 2.5 or above, then the module will use threads for the database connection. This means that the core (moodss user interface for example) cannot be hung (moodss menus not responding, window not being updated, ...) when the connection with the database is failing.
You can check that moodss actually is in threads mode by looking at the bottom line of the module help window, while moodss is running: a status line will indicate whether threads are in use.