MySQL Service (via SQL)

Description

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

Profile Package

This package includes the following files:

  • Profile definition: service-profile-service-mysql.xml

  • Plugin script: check_mysql_status.php

Installation

GroundWork Monitor includes many monitoring profiles for a variety of devices, systems and applications. Some profiles are pre-imported on a new GroundWork installation and others are distributed with the product. The configuration tool is used to import updated profiles and profiles that require additional setup, services can also be imported, see Importing Profiles.

Services Configuration

Note: 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

Note: Any bolded arguments MUST be set before this Service Profile will work properly.

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.mysql.org. There are over 200 documented.

For plugin details you can run the service help command from within the nagios container. For example: Get to the nagios container from the gw8 directory: docker-compose exec -u 1000 nagios bash, then to the libexec directory: cd /usr/local/nagios/libexec, and enter a service help command e.g., ./check_snmp --help to receive help content.

Service/Command Line/Plugin CommandCommand Parameters

This column lists the Service Definition name, Service Command name with arguments to be passed to the plugin, and the Plugin Command line which is the plugin script called by Nagios for the service.

Command parameters are in the configuration services section with the following names and default values.

  • mysql_aborted_clients

  • check_mysql_status!aborted_clients!lt100

  • $USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"

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

  • check_mysql_status!opened_tables!lt20

  • $USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"

The number of tables that have been opened. If Opened_tables is big, your table_cache value is probably too small. (gauge)

  • $ARG1$:  opened_tables

  • $ARG2$: Critical threshold lt20

  • mysql_questions

  • check_mysql_status!questions!lt1000000

  • $USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"

The number of statements that clients have sent to the server  (counter)

  • $ARG1$: questions

  • $ARG2$: Critical threshold lt1000000

  • mysql_select_full_join

  • check_mysql_status!select_full_join!0

  • $USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"

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

  • check_mysql_status!select_scan!lt10

  • $USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"

The number of joins that did a full scan of the first table (counter)

  • $ARG1$: select_scan

  • $ARG2$: Critical threshold lt10

  • mysql_slow_queries

  • check_mysql_status!slow_queries!lt10

  • $USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"

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

  • check_mysql_status!threads_connected!lt50

  • $USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"

The number of currently open connections (guage)

  • $ARG1$: threads_connected

  • $ARG2$: Critical threshold lt20

  • mysql_threads_created

  • check_mysql_status!threads_created!lt5000

  • $USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"

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

  • check_mysql_status!threads_running!lt50

  • $USER1$/check_mysql_status.php --H $HOSTADDRESS$ -v "$ARG1$" --t "$ARG2$"

The number of threads that are not sleeping (gauge)

  • $ARG1$: threads_running

  • $ARG2$: Critical threshold lt50

Implementation Notes

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’

Related Resources