nagios monitoring

nagios: Nagios plugins for monitoring MySQL databases and database servers.

Plugin Used :: check_mysql_health - check_mysql_health is a plugin for Nagios that allows you to monitor a MySQL database. Among the list of metrics are time to login, index usage, bufferpool hit rate, query cache hit rate, slow queries, temp tables on disk, table cache hit rate, connected threads, and many more.

Checks for:
1> CPU Load :: Check CPU load percentage.
   [root@ ~]# top -n 1 -b | head -5
  
2> Disk partitions :: Check the disk partition free and used percentage.
   [root@ ~]# df -h
  
3> Users logged in :: How many users been logged in at database server.
   [root@ ~]# who
  
4> Memory percentage :: How much memory been used by the MySQL process.
   ps aux | grep -i mysqld | grep -v grep
  
5> MySQL Connection time :: Determines how long connection establishment and login take
   time mysql -u$DB_USER -p$DB_PASSWD -D$DB_NAME -e "exit"
  
6> MySQL Failed Lock Rate :: Table lock contention. Rate of failed table locks.
   mysql> show global status like 'Table_locks_waited';
  
7> MySQL Hitrate Table Cache :: Hitrate in the Table-Cache. Table cache hitrate
   mysql> show global status like 'open%table%';
   Table Cache Hit Rate = open_tables/opened_tables
  
8> MySQL InnoDB Buffer Wait Rate :: Rate of the InnoDB Buffer Pool Waits
   mysql> show global status like 'Innodb_buffer_pool_wait_free';
  
9> MySQL InnoDB Log Wait Rate :: InnoDB log waits because of a too small log buffer. Rate of the InnoDB Log Waits
   mysql> show global status like 'Innodb_log_waits';

10> MySQL Long Running Procs :: long running processes. Sum of processes that are running longer than 1 minute.
    mysql> select * from information_schema.processlist where time>60 and command != 'Sleep';

11> MySQL Lowmem Prunes :: Query cache entries pruned because of low memory
    mysql> show global status like 'Qcache_lowmem_prunes';

12> MySQL Open Files :: Number of open files (of upper limit)
    mysql> show global status like 'Open_files';

13> MySQL Open Threads :: How many database threads been created.
    mysql> show global status like 'Threads_connected';

14> MySQL Slave IO :: Checks if the IO-Thread of the Slave-DB is running
    mysql> show slave status\G
   
15> MySQL Slave Lag :: Delay between Master and Slave
    mysql> show slave status\G
   
16> MySQL Slave SQL :: Checks if the SQL-Thread of the Slave-DB is running
    mysql> show slave status\G
   
17> MySQL Slow Queries :: Rate of queries that were detected as “slow”
    mysql> show global status like 'Slow_queries';

18> MySQL Tmp Disk Tables :: Percent of the temporary tables that were created on the disk instead in memory
    mysql> show global status like 'Created_tmp_disk_tables';

19> MySQL Uptime :: Time since start of the database server (the server is running).
    mysql> SHOW STATUS LIKE 'Uptime'
   
20> Mysql Service Port :: Check if the services are running on designated MySQL database port.
    [root@ ~]# netstat -na | grep -i 3306 | grep -i "ESTABLISHED" | wc -l
    [root@ ~]# netstat -na | grep -i 3306 | grep -i "LISTEN" | wc -l
   
21> Total Processes :: How many processes by running at OS.
    [root@ ~]# ps -eaf | wc -l

22> Check memcache port :: Check if the services are running on designated MySQL memcache port.
    [root@ ~]# netstat -na | grep -i 11211 | grep -i "ESTABLISHED" | wc -l
    [root@ ~]# netstat -na | grep -i 11211 | grep -i "LISTEN" | wc -l

23> InnoDB Buffer Pool Hit rate :: Check if innodb buffer pool hit rate percentage.
     mysql> show engine innodb status\G
     # grep for keyword "Buffer pool hit rate"


References:
http://kedar.nitty-witty.com/blog/10-steps-mysql-monitoring-nagios-installation-configuration
https://exchange.nagios.org/directory/Plugins/Databases/MySQL
https://exchange.nagios.org/directory/MySQL/check_mysql_health/details
https://labs.consol.de/nagios/check_mysql_health/