Check mysql-index-health¶
Overview¶
Reports the two index-housekeeping findings that MySQLTuner flags inside its mysql_pfs() block: unused indexes (never read since the last server start, listed in sys.schema_unused_indexes) and redundant indexes (a narrower index fully covered by a wider one, listed in sys.schema_redundant_indexes). Both views are populated by the Performance Schema; the plugin reports UNKNOWN with a clear hint when performance_schema = OFF (the MariaDB default). The check is meant as a trip-wire only: it surfaces the findings, not the full per-index analysis. System schemas (mysql, information_schema, performance_schema, sys) are excluded. --match and --ignore narrow the report further, down to a single schema, table or index name. Counters are cumulative since server start; restarting the server resets them, so the plugin stays silent (OK with a wait hint) until server uptime crosses --min-uptime-hours (default 24h). This avoids the false-clean signal right after a restart and the false-positive "unused" signal that would fire before weekly or monthly jobs have had a chance to touch their indexes. Index housekeeping is never a wake-up-at-night finding, so the plugin only emits WARN (and the implicit OK), never CRIT. Alerts when the count of redundant indexes crosses --warning-redundant, or the count of unused indexes crosses --warning-unused. Both are off by default and the indexes are only listed, because in a schema an application manages itself the admin cannot drop either kind. Supports extended reporting via --lengthy.
The plugin does not duplicate mysqltuner's deep diagnostic output; it is sized for a once-a-day check, not a 60-second one (the underlying counters are cumulative since server start, so sub-daily sampling would add nothing).
Important Notes:
- See additional notes for all mysql monitoring plugins
- The plugin needs the Performance Schema. On MariaDB it is OFF by default; enable it with
performance_schema = ONinmy.cnfand restart the server. MySQL ships it ON by default sys.schema_unused_indexesandsys.schema_redundant_indexesexist in MySQL 5.7+ and MariaDB 10.6+- Counters are cumulative since the last server start. Restarting the server resets them, so a freshly booted host may take a few hours before unused-index numbers settle (the query pattern needs time to exercise all indexes)
- System schemas (
mysql,information_schema,performance_schema,sys) are excluded, matching the WHERE clause used by mysqltuner --matchand--ignoretake Python regular expressions and are matched against the fully qualified index identifierschema.table.index, so the same parameter scopes the check to a schema (^shop\.), to a table (^shop\.orders\.) or to an index name across all tables (\.idx_legacy$). Both filter the findings before the counters, the thresholds, the hotspot hint and the perfdata are calculated, so a filtered check reports only what it was asked to look at- Index housekeeping is never a wake-up-at-night finding, so the plugin only emits WARN (and the implicit OK), never CRIT
- Neither finding alerts by default. In a schema an application manages itself (Nextcloud, Icinga DB, Metabase), the admin cannot drop an index: the next update or migration brings it back. For databases whose schema and queries you or your developers control, set
--warning-redundant=0, and--warning-unusedif you want unused indexes to alert as well. An unused index says less than a redundant one, because nothing read it since the last server start says little about whether it can go - Primary keys are never reported as redundant, although
sys.schema_redundant_indexeslists one when a narrower unique key covers its leading columns. In InnoDB the primary key is the clustered index, dropping it rebuilds the table around a hidden row id - Output ships a ready-to-paste
ALTER TABLE ... DROP INDEXstatement per finding.--lengthyshows the full statement; the default truncates after 80 characters so the table stays readable in Icinga Web 2 - Redundant indexes are safe to drop because the dominant index already covers every query the redundant one served. Unused indexes need verification first: "unused since last server start" can miss weekly or monthly jobs, recently restarted servers, and indexes that back foreign-key constraints. Wait at least one full business cycle before dropping
Data Collection:
SHOW GLOBAL VARIABLES LIKE 'performance_schema'to detect the prerequisiteSELECT ... FROM sys.schema_unused_indexesexcluding the four server-managed schemasSELECT ... FROM sys.schema_redundant_indexes(already excludes server schemas internally)
Fact Sheet¶
| Fact | Value |
|---|---|
| Check Plugin Download | https://github.com/Linuxfabrik/monitoring-plugins/tree/main/check-plugins/mysql-index-health |
| Nagios/Icinga Check Name | check_mysql_index_health |
| Check Interval Recommendation | Every day |
| Can be called without parameters | Yes |
| Runs on | Cross-platform |
| Compiled for Windows | No (runs with Python interpreter) |
| Requirements | User with SELECT privilege on *.* (the sys views inherit from performance_schema). Performance Schema enabled on the server (MySQL: default; MariaDB: requires performance_schema = ON in my.cnf plus a restart). |
| 3rd Party Python modules | pymysql |
Help¶
usage: mysql-index-health [-h] [-V] [--always-ok]
[--defaults-file DEFAULTS_FILE]
[--defaults-group DEFAULTS_GROUP] [--ignore IGNORE]
[--lengthy] [--match MATCH]
[--min-uptime-hours MIN_UPTIME_HOURS]
[--no-perfdata] [--timeout TIMEOUT]
[--warning-redundant WARN_REDUNDANT]
[--warning-unused WARN_UNUSED]
Reports the two index-housekeeping findings that MySQLTuner flags inside its
`mysql_pfs()` block: unused indexes (never read since the last server start,
listed in `sys.schema_unused_indexes`) and redundant indexes (a narrower index
fully covered by a wider one, listed in `sys.schema_redundant_indexes`). Both
views are populated by the Performance Schema; the plugin reports UNKNOWN with
a clear hint when `performance_schema = OFF` (the MariaDB default). The check
is meant as a trip-wire only: it surfaces the findings, not the full per-index
analysis. System schemas (`mysql`, `information_schema`, `performance_schema`,
`sys`) are excluded. `--match` and `--ignore` narrow the report further, down
to a single schema, table or index name. Counters are cumulative since server
start; restarting the server resets them, so the plugin stays silent (OK with
a wait hint) until server uptime crosses `--min-uptime-hours` (default 24h).
This avoids the false-clean signal right after a restart and the
false-positive "unused" signal that would fire before weekly or monthly jobs
have had a chance to touch their indexes. Index housekeeping is never a
wake-up-at-night finding, so the plugin only emits WARN (and the implicit OK),
never CRIT. Alerts when the count of redundant indexes crosses
`--warning-redundant`, or the count of unused indexes crosses
`--warning-unused`. Both are off by default and the indexes are only listed,
because in a schema an application manages itself the admin cannot drop either
kind. Supports extended reporting via --lengthy.
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
MySQL/MariaDB cnf file to read user, host and password
from. Example: `--defaults-
file=/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
--ignore IGNORE Ignore indexes whose name matches this Python regular
expression. Matched against the fully qualified index
identifier `schema.table.index`, so one pattern can
drop a whole schema, a single table or one index name
across all tables. Case-sensitive by default; use
`(?i)` for case-insensitive matching. Can be specified
multiple times. Default: None. Example:
`--ignore="^icinga_director\."` to skip a schema whose
indexes are managed by the application. Example:
`--ignore="\.idx_legacy$"` to skip one index name
everywhere.
--lengthy Extended reporting.
--match MATCH Only report indexes whose name matches this Python
regular expression. Matched against the fully
qualified index identifier `schema.table.index`. Case-
sensitive by default; use `(?i)` for case-insensitive
matching. Can be specified multiple times. If both
`--match` and `--ignore` are given, an item must match
`--match` AND not match `--ignore` to be reported
(include first, exclude second). Default: None.
Example: `--match="^shop\."` to report on the `shop`
schema only. Example: `--match="^shop\.orders\."` to
report on one table only.
--min-uptime-hours MIN_UPTIME_HOURS
Minimum server uptime (in hours) before the plugin
starts evaluating findings. Performance Schema
counters reset on every server restart, so a freshly
booted server has no record of which indexes are read
or unused; alerting on that data would produce false
positives. Below the threshold the plugin reports OK
with an info hint. Example: `--min-uptime-hours=48`.
Default: 24
--no-perfdata Suppress the performance data section from the output.
The status message and the exit code are unaffected,
so alerting keeps working while trending data is
dropped.
--timeout TIMEOUT Network timeout in seconds. Default: 3 (seconds)
--warning-redundant WARN_REDUNDANT
WARN threshold for the number of matching items.
Applies to redundant indexes. Supports Nagios ranges.
Example: `--warning-redundant=0`. Default: not
evaluated, the redundant indexes are only listed.
--warning-unused WARN_UNUSED
WARN threshold for the number of matching items.
Applies to unused indexes. Supports Nagios ranges.
Example: `--warning-unused=10`. Default: not
evaluated, the unused indexes are only listed.
Documentation:
https://linuxfabrik.github.io/monitoring-plugins/check-plugins/mysql-index-health/
Usage Examples¶
./mysql-index-health --defaults-file=/var/spool/icinga2/.my.cnf
Skip the schemas whose indexes are managed by an application and are not the admin's to drop:
./mysql-index-health --ignore="^(icinga_director|icingaweb2|icingadb)\."
Report on one schema only:
./mysql-index-health --match="^shop\."
Alert on redundant indexes, for a database whose schema and queries you control:
./mysql-index-health --warning-redundant=0
OK output:
Everything is ok. 0 unused indexes, 0 redundant indexes.
UNKNOWN output (Performance Schema off):
Performance Schema is OFF, index-health views are unobservable. Enable with `performance_schema = ON` in `my.cnf` and restart the server, or set `--always-ok` if the host is intentionally left without Performance Schema.
WARN output:
2 unused indexes, 1 redundant index [WARNING].
67% of findings concentrate in schema `orders` (2 of 3); start the cleanup there.
Run `mysqltuner --pfstat` on the host for the full list with remediation hints.
Redundant indexes (safe to drop; the dominant index already covers the same query patterns):
! Schema ! Table ! Redundant Index ! Dominant Index ! SQL Drop !
! ------- ! ----- ! --------------- ! -------------- ! ---------------------------------------------- !
! orders ! line ! idx_a ! idx_a_b ! ALTER TABLE `orders`.`line` DROP INDEX `idx_a` !
Unused indexes (verify before dropping: "unused since last server start" can miss weekly or monthly jobs, recently restarted servers, and indexes that back foreign-key constraints; wait at least one full business cycle, then drop):
! Schema ! Table ! Index ! SQL Drop !
! ------- ! ------- ! ---------- ! --------------------------------------------------- !
! orders ! line ! idx_status ! ALTER TABLE `orders`.`line` DROP INDEX `idx_status` !
! reports ! summary ! idx_year ! ALTER TABLE `reports`.`summary` DROP INDEX ... !
States¶
- OK if neither count crosses its threshold. Without
--warning-redundantand--warning-unusedthe indexes are listed and counted in the perfdata, but never alert. - WARN if the redundant-index count crosses
--warning-redundant(default: not evaluated), or the unused-index count crosses--warning-unused(default: not evaluated). - No CRIT path: index housekeeping is never a wake-up-at-night finding.
- UNKNOWN if
performance_schema = OFFon the server (the views cannot be populated). - Indexes dropped by
--match/--ignorenever reach the thresholds, so a check filtered down to nothing reports OK. --always-oksuppresses all alerts and always returns OK.
Perfdata / Metrics¶
| Name | Type | Description |
|---|---|---|
| mysql_redundant_indexes | Number | Count of indexes covered by a wider sibling on the same table. A non-zero value usually means a CREATE INDEX from a migration is redundant after a later, wider index was added. |
| mysql_unused_indexes | Number | Count of indexes that have not been read since the last server start. A non-zero value is a candidate-for-removal list, not a hard verdict: short-lived workloads need time to exercise all indexes. |
Credits, License¶
- Authors: Linuxfabrik GmbH, Zurich
- License: The Unlicense, see LICENSE file.
-
Credits:
- heavily inspired by MySQLTuner (https://github.com/major/MySQLTuner-perl)