Check mysql-table-indexes

Overview

Checks for missing indexes in MySQL/MariaDB tables. Logic is taken from MySQLTuner script.

User account requires:

• Access to INFORMATION_SCHEMA (user with no privileges is sufficient).

• SELECT privileges on all schemas and tables to provide accurate results.

Hints:

• See additional notes for all mysql monitoring plugins

• For most INFORMATION_SCHEMA tables, each MySQL user has the right to access them, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges.. So you can’t grant permission to INFORMATION_SCHEMA directly, you have to grant SELECT permission to the tables on your own schemas, and as you do, those tables will start showing up in INFORMATION_SCHEMA queries. Then this check provide correct results.

• Requires MySQL/MariaDB v5.5+.

Fact Sheet

Check Plugin Download	https://github.com/Linuxfabrik/monitoring-plugins/tree/main/check-plugins/mysql-table-indexes
Check Interval Recommendation	Once a day
Can be called without parameters	Yes
Compiled for	Linux, Windows
3rd Party Python modules	pymysql

Help

usage: mysql-table-indexes [-h] [-V] [--always-ok]
                           [--defaults-file DEFAULTS_FILE]
                           [--defaults-group DEFAULTS_GROUP]
                           [--timeout TIMEOUT]

Checks for missing indexes in MySQL/MariaDB tables.

options:
  -h, --help            show this help message and exit
  -V, --version         show program's version number and exit
  --always-ok           Always returns OK.
  --defaults-file DEFAULTS_FILE
                        Specifies a cnf file to read parameters like user,
                        host and password from (instead of specifying them on
                        the command line), for example
                        `/var/spool/icinga2/.my.cnf`. Default:
                        /var/spool/icinga2/.my.cnf
  --defaults-group DEFAULTS_GROUP
                        Group/section to read from in the cnf file. Default:
                        client
  --timeout TIMEOUT     Network timeout in seconds. Default: 3 (seconds)

Usage Examples

./mysql-table-indexes --defaults-file=/var/spool/icinga2/.my.cnf

Output:

Tables without indexes: employees.current_dept_emp, employees.dept_emp_latest_date [WARNING]

States

• WARN if tables without indexes are found.

Perfdata / Metrics

There is no perfdata.

Credits, License

• Authors: Linuxfabrik GmbH, Zurich

• License: The Unlicense, see LICENSE file.

• Credits:

  • heavily inspired by MySQLTuner (https://github.com/major/MySQLTuner-perl)