Service MySQL

Service MySQL Profile

This profile monitors several statistics for MySQL servers, local or remote, using the connection with tcp port 3306 to MySQL.

Services Configuration

Service - Definitions in Monarch are stored under this name.
Command Line - Service command name with arguments to be passed to the plugin.
Plugin Command Line - Plugin script called by Nagios for this Service.

Extended Info - The Extended Service Info definition, typically used for generating graphs.

Command lines displayed below are intended to be single line commands.

Service	Command Line	Plugin Command Line
mysql_aborted_clients	check_mysql_status!aborted_clients!lt100	$USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"
mysql_opened_tables	check_mysql_status!opened_tables!lt20	$USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"
mysql_questions	check_mysql_status!questions!lt1000000	$USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"
mysql_select_full_join	check_mysql_status!select_full_join!0	$USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"
mysql_select_scan	check_mysql_status!select_scan!lt10	$USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"
mysql_slow_queries	check_mysql_status!slow_queries!lt10	$USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"
mysql_threads_connected	check_mysql_status!threads_connected!lt50	$USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"
mysql_threads_created	check_mysql_status!threads_created!lt5000	$USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"
mysql_threads_running	check_mysql_status!threads_running!lt50	$USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"

Profile Package

This package includes the following files:

Profile Definitions

service-profile-mysql-service.xml
perfconfig-mysql-service.xml

Plugins Scripts

check_mysql_status.php

Installation

GroundWork Monitor includes many monitoring profiles for a variety of devices, systems and applications. Profiles already imported on a new GroundWork installation include Service Ping, SNMP Network, and SSH UNIX. The GroundWork Monitor Configuration tool is used to import updated Profiles and Profiles that require additional setup; the Profile XML file and its companion Performance Configuration definition file. Services can also be imported in addition to Service Profiles in the Profile Importer. The import process is documented under GROUNDWORK PROFILES > How to import profiles.

Implementation

This section contains detail settings used by this Profile. These parameters can be altered with the Configuration tool.

The host to be monitored must provide permission to the user “nagios” on the GroundWork Monitor server to access the MySQL instance. The default is to use an empty password (not the Linux password). If a password is desired it must be coded into the plugin “check_mysql_status” at the line with the following variable:

$mysql_password = "";

Subsequently and in either case of password or no password, on the host to be monitored execute the following command line as user root to set the permissions so that the profile service checks will succeed (you must also supply the root user password if necessary):

‘echo “grant all on *.* to nagios@<ip address of GW server> identified by “<password>” “ | mysql --u root --p’

Command Parameters

Command parameters are in the Configuration Services section with the following names and default values. Any bolded arguments MUST be set before this Service Profile will work properly.

A word about the threshold value. This can be in 4 different forms; if the test passes, return is OK (0) status; if the test fails the plugin returns Critical (2) status:

A number that must be matched: 0
Less than a number: lt50
Greater than a number: gt100
Within a range of numbers: 20 - 30

mysql_aborted_clients

Counter incremented whenever (counter)

- The client program did not call mysql close before exiting
- The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server
- The client program ended abruptly in the middle of a data transfer
$ARG1$ - Aborted_clients
$ARG2$ - Critical threshold lt100

mysql_opened_tables

The number of tables that have been opened. If Opened_tables is big, your table_cache value is probably too small. (guage)
$ARG1$ - opened_tables
$ARG2$ - Critical threshold lt20

mysql_questions

The number of statements that clients have sent to the server (counter)
$ARG1$ - Questions
$ARG2$ - Critical threshold lt1000000

mysql_select_full_join

The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your tables (counter)
$ARG1$ - Select_full_join
$ARG2$ - Critical threshold 0

mysql_select_scan

The number of joins that did a full scan of the first table (counter)
$ARG1$ - select_scan
$ARG2$ - Critical threshold lt10

mysql_slow_queries

The number of queries that have taken more than long_query_time seconds. Note for further investigation: To enable the slow query log, start mysqld with the --log-slow-queries[=file_name] option. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. (counter)
$ARG1$ - slow_queries
$ARG2$ - Critical threshold lt10

mysql_threads_connected

The number of currently open connections (guage)
$ARG1$ - Threads_connected
$ARG2$ - Critical threshold lt20

mysql_threads_created

The number of threads created to handle connections. If Threads_created is big, you may want to increase the thread_cache_size value. The cache miss rate can be calculated as Threads_created/Connections. (counter)
$ARG1$ - threads_created
$ARG2$ - Critical threshold lt5000

mysql_threads_running

The number of threads that are not sleeping (gauge)
$ARG1$ - threads_running
$ARG2$ - Critical threshold lt50

Each of these tests passes an argument which is meaningful in the context of the mysql command “SHOW STATUS”. The counters that increment are all reset by the “FLUSH STATUS” command. The ambitious administrator may create new services after referring to the documentation of the “SHOW STATUS” variables at http://www.myslq.org. There are over 200 documented.

Performance Graphing Parameters

The following parameters are used to generate performance charts. These parameters are set using the Configuration>Performance tool in GroundWork Monitor.