Page History

Abstract

This document describes how you can monitor an MSSQL MySQL database server such as:

Connection time
The number of connections
Cache hit
etc

There are some steps you’ll need to follow in order to monitor a new database machine:

Understand what is already available (in the Shinken Entreprise installation)
Setup the MSSQL MySQL user account
Test the connection to the database
Setup your server host definition

Panel

title	On this page

Table of Contents

maxLevel	3

What is already available in the Shinken Installation

To make your life a bit easier, a few configuration tasks have already been done for you:

Installation of check_mssqlmysql_health plugin : /var/lib/shinken/libexec/check_mssqlmysql_health
Several host templates are ready to be used

Tip

title	Note

We suppose here that the MSSQL MySQL server you want to monitor is named srv-winlin-1 and is a WindowsLinux. Please change the configuration and commands according with the real name of your server.

Setup the

MSSQL user

MySQL user account

Create the database user

Look at the labs.consol.de/lang/en/nagios/check_mssql_health/ page about how to configure your user connection.

Connect with a root account on your MySQL database. change ‘password’ with your mysql root password:

Code Block

language	bash

 lin-srv-1:# mysql -u root -ppassword

And create a shinken user:

Code Block

language	sql

GRANT usage ON *.* TO 'shinken'@'%' IDENTIFIED BY 'shinkenpassword';

It’s a good thing to change the shinkenpassword to another password. Then you need to update the /etc/shinken/resource.d/mysql.cfg

Code Block
$MYSQLUSER$=shinken $MYSQLPASSWORD$=shinkenpassword

Test the connection

To see if the connection to the MSSQL MySQL server is ok using a domain user, just launch :

Code Block

language	bash

/var/lib/shinken/libexec/check_mssqlmysql_health --serverhostname "srv-winlin-1" --username "shinkendom\\shinken" --password "shinkenpassword" --mode connection-time

Manage more than 1 database on the same host

Some checks presents in MSSQL templates provided by Shinken Entreprise use the "Duplicate For Each" functionality.

For each database name listed in the DATA "DATABASES", check will be duplicated. An host, having 2 databases, will then have in double each checks with the datatabase name in the check.

Info

title	Example

Let's look an example: an host has the following:

The data "DATABASES" set to "DB1,DB2,DB3"
The MSSQL template attached on it.
Let's consider the Mssql-$KEY$-database-free check. It will then have 2 checks:
- Mssql-DB1-database-free
- Mssql-DB2-database-free

Each check able to be used on a database base will be indicated with a scope "database".

What is checked with the templates

Public templates

mssql

mysql

This is the very basic MSSQL MySQL template allowing to know if your MSSQL MySQL database server is running and usable.

Every MSSQL MySQL templates (except MssqlMysql-connection-method) use it as a parent template.

Check	Detail	Scope	Check range	Default Warning	Default Critical
connection	Time to connect to the server	database-free	Free space in database	database	0 to 100%n	2%1	5%	5
restart	Time the server is running	free-list-stalls	Requests per second that had to wait for a free page	server	0 to n	4	10	page-life-expectancy	Seconds a page is kept in memory before being flushed	:	5:
slow_queries	slow_queriesserver	0 to n	300:	180:	0.1	1
tmp_disk_tables	Percent of temp tables created on disk	total-server-memory	The amount of memory that SQL Server has allocated to it	server	0 to n	100000025	500000050

mssql

mysql-full

Contains all MSSQL MySQL host templates listed in the Shinken Adminstrators templates.

Warning

title	Warning

Using the mssqlmysql-full template will require a lot of resources on your poller daemon. We advise to use only the needed templates.

Shinken Administrator templates (can't be seen by other users)

mssql

mysql-connection-method

This template describes the method to connect to MSSQL MySQL server. Every MSSQL MySQL templates use it as a parent template.

mssql

mysql-

availability

cluster

This template gives informations about the database availabilityMySQL cluster.

Check	Detail	Scope	Check range	Default Warning	Default Critical
connection	Time to connect to the server	server	0 to n sec	1	2

mssql-backup-age

mysql-innoDB

This template gives informations about the backup age of your databasesinnoDB buffers and logwaits.

Check	Detail	Scope	Check range	Default Warning	Default Critical
bufferpool_hitrate	InnoDB buffer pool hitrate	database-backup-age	Elapsed time (in hours) since a database was last backed up	database	0 to n hours	4899:	72

mssql-batch-requests

This template gives informations about the batch requests.

Check	Detail	Scope	Check range	Default Warning	Default Critical
95:
bufferpool_wait_free	InnoDB buffer pool waits for clean page available
batch-requests	Batch requests per second	server	0 to n	1001	200

mssql-checkpoint-pages

This template gives informations about the checkpoint pages.

Check	Detail	Scope	Check range	Default Warning	Default Critical
checkpoint-pages	Dirty pages flushed to disk per second. (usually by a checkpoint)	server	0 to n	100	500

mssql-compilations

This template gives informations about compilations.

Check	Detail	Scope	Check range	Default Warning	Default Critical
sql-initcompilations	Initial compilations per second	server	0 to n	100	200
sql-recompilations	Re-Compilations per second	server	0 to n	1	10

10
log_waits	InnoDB log waits because of a too small log buffer	0 to n	1	10

mysql-MyISAM

mssql-full-scans

This template gives informations about the full scansMyISAM key cache hitrate.

Check DetailScope Check range Default Warning Default Critical
fullkeycache-scans Full table scans per second hitrate MyISAM key cache hitrateserver 0 to n 10099: 50095:
mssql

mysql-

latch

performance

This template gives informations about the latchesglobal server performance.

Check	Detail	Scope	Check range	Default Warning	Default Critical
index_usage	Usage of indexes	latches-wait-time	Average time for a latch to wait before the request is met	server	0 to n	190:	5	80:
long_running_procs	long running processes	latches-waits	Number of latch requests that could not be granted immediately	server	0 to n	1050

mssql-lazy-writes

This template gives informations about the lazy writes.

Check	Detail	Scope	Check range	Default Warning	Default Critical
lazy-writes	Lazy writes per second	server	0 to n	20	40

20
table_lock_contention	Table lock contention

mssql-locks

This template gives informations about locks.

Check	Detail	Scope	Check range	Default Warning	Default Critical
locks-deadlock	The number of deadlocks per second	server	0 to n	1	5	locks-timeouts	The number of locks per second that timed out	server	2
tablecache_hitrate	Table cache hitrate	0 to n	199:	95:
threadcache_hitrate	Hit rate of the thread-cache	5	locks-waits	The number of locks per second that had to wait	server	0 to n	10010	50020

mssql

mysql-

server

query-

performance

cache

This template gives informations about MSSQL server performancequery cache.

Check	Detail	Scope	Check range	Default Warning	Default Critical
qcache_hitrate	Query cache hitrate	cpu-busy	Cpu busy in percent	server	0 to n	80	90	io-busy	IO busy in percent	server	0 to n90:	80:	90	mem-pool-data-buffer-hit-ratio	Data Buffer Cache Hit Ratio
qcache_lowmem_prunes	Query cache entries pruned because of low memoryserver	0 to n	90:180:	10

mssql

mysql-

transactions

usage

This template gives informations about databases transactionsMySQL server usage.

Check	Detail	Scope	Check range	Default Warning	Default Critical
open_files	Percent of opened files	transactions	Transactions per second (per database)	database	0 to n	1000080	50000

mssql-usage

This template gives informations about the database usage.

Check	Detail	Scope	Check range	Default Warning
95
threads_connected
connected-users	Number of currently connected usersserveropen connections	0 to n	5010	8020

How to

Configuration of

Mssql

Mysql-connection-method host template

Click on Hosts then on Hosts Templates in the Elements menu
In the Name field, type mssqlmysql-connection. Then clic on mssqlmysql-connection-method	Image RemovedImage Added
Clic on Data tab
You can setup the following DATA : DATABASESMYSSQLPASSWORD : the name(s) of the database(s) to be monitored.MSSQLPASSWORD : the MSSQL MySQL password of the user used to connect to database MSSQLUSERMYSQLUSER : the MSSQL MySQL user name used to connect to database	Image Removed

Image Added

Tip

You can also set the user and password directly in /etc/shinken/resource.d/myslq.cfg

Code Block
$MYSQLUSER$=shinken $MYSQLPASSWORD$=shinkenpassword

Attach a MySQL

The parameters specifics to each server, such as database name for example, has to be done in the host's data itself.

Tip

title	Tip

It's a best practice to use the same database monitoring user on every MSSQL server monitored. Doing so, you can configure the database user/password only once in the host template oracle-connection-method.

Attach a MSSQL

template to you host

Clic on Hosts in the Elements menu
Add the chosen Oracle MySQL host template to the Host Templates to inherit field. For example the Oracle MySQL host template.	Image RemovedImage Added
Click on the Data tab	Image Removed
Look for the field DATABASES in the From templates data
Type the name of the database to be monitoring	Image Removed
Image Added
Check if the Following data are ok.

Tip

title	Tip

You can monitor multiple database on the same host in typing the name of the database name separated by a comma.

Exemple : DB1,DB2,DB3

Page tree

Page History

Versions Compared

Old Version 1

New Version Current

Key

Abstract

What is already available in the Shinken Installation

Setup the

MySQL user account

Create the database user

Test the connection

Manage more than 1 database on the same host

What is checked with the templates

Public templates

mysql

mysql-full

Shinken Administrator templates (can't be seen by other users)

mysql-connection-method

mysql-

cluster

mysql-innoDB

mssql-batch-requests

mssql-checkpoint-pages

mssql-compilations

mysql-MyISAM

This template gives informations about the full scansMyISAM key cache hitrate. CheckDetailScopeCheck rangeDefault WarningDefault Criticalfullkeycache-scansFull table scans per secondhitrateMyISAM key cache hitrateserver0 to n10099:50095:mssql

mysql-

performance

mssql-lazy-writes

mssql-locks

mysql-

query-

cache

mysql-

usage

mssql-usage

How to

Configuration of

Mysql-connection-method host template

Attach a MySQL

template to you host

This template gives informations about the full scansMyISAM key cache hitrate.

Check DetailScope Check range Default Warning Default Critical
fullkeycache-scans Full table scans per second hitrate MyISAM key cache hitrateserver 0 to n 10099: 50095:
mssql