check mysql health_check_mysql_health

金阳曜

2023-12-01

nagios监控mysql插件：

http://labs.consol.de/lang/en/nagios/check_mysql_health/

Posted on July 6th, 2009 by admin

Description

check_mysql_health is a plugin to check various parameters of a MySQL database.

Command line parameters

–hostname The database server which should be monitored. In case of "localhost" this parameter can be omitted.

–username The database user.

–password Password of the database user.

–mode With the mode-parameter you tell the plugin what it should do. See the list of possible values further down.

–name Here the check can be limited to a single object. (Momentarily this parameter is only used for mode=sql)

–name2 If you use –mode=sql, then the SQL-Statement appears in the

output and performance values. With the parameter name2 you’re able to

specify a string for this..

–warning Determined values outside of this range trigger a WARNING.

–critical Determined values outside of this range trigger a CRITICAL.

–environment =With this you can pass environment variables to the script. Multiple declarations are possible.

–method With this parameter you tell the plugin how it should connect to

the database. (dbi for using DBD::mysql (default), mysql for

mysql-Tool).

–units

The declaration from units serves the "beautification" of the output from mode=sql

Use the option –mode with various keywords to tell the Plugin which values it should determine and check.

Keyword

Description

Range

connection-time

Determines how long connection establishment and login take

0..n Seconds (1, 5)

uptime

Time since start of the database server (recognizes DB-Crash+Restart)

0..n Seconds (10:, 5: Minutes)

threads-connected

Number of open connections

1..n (10, 20)

threadcache-hitrate

Hitrate in the Thread-Cache

0%..100% (90:, 80:)

q[uery]cache-hitrate

Hitrate in the Query Cache

0%..100% (90:, 80:)

q[uery]cache-lowmem-prunes

Displacement out of the Query Cache due to memory shortness

n/sec (1, 10)

[myisam-]keycache-hitrate

Hitrate in the Myisam Key Cache

0%..100% (99:, 95:)

[innodb-]bufferpool-hitrate

Hitrate in the InnoDB Buffer Pool

0%..100% (99:, 95:)

[innodb-]bufferpool-wait-free

Rate of the InnoDB Buffer Pool Waits

0..n/sec (1, 10)

[innodb-]log-waits

Rate of the InnoDB Log Waits

0..n/sec (1, 10)

tablecache-hitrate

Hitrate in the Table-Cache

0%..100% (99:, 95:)

table-lock-contention

Rate of failed table locks

0%..100% (1, 2)

index-usage

Sum of the Index-Utilization (in contrast to Full Table Scans)

0%..100% (90:, 80:)

tmp-disk-tables

Percent of the temporary tables that were created on the disk instead in memory

0%..100% (25, 50)

slow-queries

Rate of queries that were detected as "slow"

0..n/sec (0.1, 1)

long-running-procs

Sum of processes that are runnning longer than 1 minute

0..n (10, 20)

slave-lag

Delay between Master and Slave

0..n Seconds

slave-io-running

Checks if the IO-Thread of the Slave-DB is running

slave-sql-running

Checks if the SQL-Thread of the Slave-DB is running

sql

Result of any SQL-Statement that returns a number.

The statement itself is passed over with the parameter –name. A Label

for the performance data output can be passed over with the parameter

–name2. The parameter –units can add units to the output (%, c, s, MB,

GB,..). If the SQL-Statement includeds special characters or spaces, it

can first be encoded with the mode encode.

0..n

open-files

Number of open files (of upper limit)

0%..100% (80, 95)

encode

Reads standard input (STDIN) and outputs an encoded string.

cluster-ndb-running

Checks if all cluster nodes are running.

Depending on the chosen mode two labels can appear in the performance data output.

= and =

The determinded values apply to the complete runtime of the database and to the time since the last run of check_mysl_health.

Example: qcache_hitrate=71.63%;90:;80: qcache_hitrate_now=8.25%

The Hitrate of the Query-Cache is calculated from Qcache_hits / (

Qcache_hits + Com_select ). This values are continuously increased. A

serious change in access behaviour affects the hitrate only slowly. To

be able to recognize temporarily fluctuations in the hitrate and, for

example, assign it to an application update, the value

qcache_hitrate_now is printed out additionally. This value is calculated

through the difference (delta) between Qcache_hits and Com_select

(actual value of the variables minus the value since the last run from

check_mysql_health).

Here the command line parameter –lookback is used.

if this is missing, than qcache_hitrate_now is calculated from the

difference (delta) between Qcache_hits and Com_select since the last

run from check_mysql_health. Important for the exitcode of the plugin is

the long-term result qcache_hitrate (since database start).

if –lookback is specified with an argument n, than

qcache_hitrate_now is calculated from the difference (delta) from

Qcache_hits and Com_select since the last n seconnds.

For example: With –lookback 3600 you’ll get the average hitrate

of the last hour, calculated back from the last plugin execution. The

exitcode now also depends on this short-term test result.

It’s recommended to use –lookback but specify at least half an hour

(–lookback 1800) because the now-value underlies a heavy fluctuation

which would lead to frequent alarms.

Pleae note, that the thresholds must be specified according to the Nagios plug-in development Guidelines.

"10" means "Alarm, if > 10" und

"90:" means "Alarm, if < 90"

Connect to the database

Creating a database user

In order to be able to collect the needed information from the database a database user with specific privileges is required:

GRANT usage ON *.* TO 'nagios'@'nagiosserver' IDENTIFIED BY 'nagiospassword'

Connectionstring

To connect to the database you use the parameters –username and

–password. The database server which should be used can be specified

more precise with –hostname and –socket or –port.

Use of environment variables

It’s possible to omit –hostname, –username and –password as well as

–socket and –port completely, if you provide the corresponding values in

environment variables. Since Version 3.x it is possible to extend

service definitions in Nagios through own attributes (custom object

variables). These will appear during the exectution of the check command

in the environment.

The environment variables are:

NAGIOS__SERVICEMYSQL_HOST (_mysql_host in the service definition)

NAGIOS__SERVICEMYSQL_USER (_mysql_user in the service definition)

NAGIOS__SERVICEMYSQL_PASS (_mysql_pass in the service definition)

NAGIOS__SERVICEMYSQL_PORT (_mysql_port in the service definition)

NAGIOS__SERVICEMYSQL_SOCK (_mysql_sock in the service definition)

Examples

nagios$ check_mysql_health --hostname mydb3 --username nagios --password nagios

--mode connection-time

OK - 0.03 seconds to connect as nagios | connection_time=0.0337s;1;5

nagios$ check_oracle_health --mode=connection-time

OK - 0.17 seconds to connect | connection_time=0.1740;1;5

nagios$ check_mysql_health --mode querycache-hitrate

CRITICAL - query cache hitrate 70.97% | qcache_hitrate=70.97%;90:;80: qcache_hitrate_now=72.25% selects_per_sec=270.00

nagios$ check_mysql_health --mode querycache-hitrate

--warning 80: --critical 70:

WARNING - query cache hitrate 70.82% | qcache_hitrate=70.82%;80:;70: qcache_hitrate_now=62.82% selects_per_sec=420.17