Skip to content

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 = ON in my.cnf and restart the server. MySQL ships it ON by default
  • sys.schema_unused_indexes and sys.schema_redundant_indexes exist 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
  • --match and --ignore take Python regular expressions and are matched against the fully qualified index identifier schema.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-unused if 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_indexes lists 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 INDEX statement per finding. --lengthy shows 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 prerequisite
  • SELECT ... FROM sys.schema_unused_indexes excluding the four server-managed schemas
  • SELECT ... 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-redundant and --warning-unused the 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 = OFF on the server (the views cannot be populated).
  • Indexes dropped by --match / --ignore never reach the thresholds, so a check filtered down to nothing reports OK.
  • --always-ok suppresses 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