Skip to content

Check mysql-table-indexes

Overview

Checks user schemas in MySQL/MariaDB for two replication- and performance-relevant defects: base tables with no index at all, and InnoDB base tables without a user-defined PRIMARY KEY. The first check mirrors mysqltuner's mysql_tables(). The second is a Linuxfabrik addition motivated by a well-documented ROW-based-replication hotspot.

Important Notes:

Data Collection:

  • Single LEFT JOIN query against information_schema.tables and information_schema.statistics to find base tables with zero entries in statistics (no index at all). Replaces the previous O(schemas * tables) per-table query storm
  • Single NOT EXISTS query against information_schema.statistics filtered on INDEX_NAME = 'PRIMARY' to find InnoDB base tables without a user-defined PRIMARY KEY
  • Query against information_schema.tables listing every base table in the user schemas, counted for the total emitted as perfdata
  • Logic for the "no indexes" check taken from MySQLTuner:mysql_tables() and verified in sync with MySQLTuner. The InnoDB-without-PRIMARY KEY check is a Linuxfabrik addition

Fact Sheet

Fact Value
Check Plugin Download https://github.com/Linuxfabrik/monitoring-plugins/tree/main/check-plugins/mysql-table-indexes
Nagios/Icinga Check Name check_mysql_table_indexes
Check Interval Recommendation Every day
Can be called without parameters Yes
Runs on Cross-platform
Compiled for Windows No
Requirements User with SELECT privilege (typically GRANT SELECT ON *.*), locked down to 127.0.0.1 - for example monitoring\@127.0.0.1. Usernames in MySQL/MariaDB are limited to 16 chars in specific versions.
3rd Party Python modules pymysql

Help

usage: mysql-table-indexes [-h] [-V] [--always-ok] [-c CRITICAL]
                           [--defaults-file DEFAULTS_FILE]
                           [--defaults-group DEFAULTS_GROUP] [--ignore IGNORE]
                           [--lengthy] [--match MATCH] [--no-perfdata]
                           [--timeout TIMEOUT] [-w WARNING]

Checks user schemas in MySQL/MariaDB for two replication- and
performance-relevant defects: base tables with no index at all (mysqltuner's
`mysql_tables()` check) and InnoDB base tables without a user-defined `PRIMARY
KEY`. The second case is a documented hotspot for ROW-based replication: the
replica has to materialise each row event against an internal hidden 6-byte
index, which can degrade to a full table scan per row event. Alerts when
either count crosses `--warning` / `--critical`. `--match` and `--ignore`
narrow the scan down to a single schema or table.

options:
  -h, --help            show this help message and exit
  -V, --version         show program's version number and exit
  --always-ok           Always returns OK.
  -c, --critical CRITICAL
                        CRIT threshold for the number of bad tables (per
                        category: missing indexes / missing primary key).
                        Supports Nagios ranges. Default: 9
  --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 tables whose name matches this Python regular
                        expression. Matched against the fully qualified table
                        identifier `schema.table`, so one pattern can drop a
                        whole schema or a single table. 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
                        tables are managed by the application. Example:
                        `--ignore="\.tmp_"` to skip temporary tables
                        everywhere.
  --lengthy             Extended reporting. Default: False
  --match MATCH         Only check tables whose name matches this Python
                        regular expression. Matched against the fully
                        qualified table identifier `schema.table`. 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 check the `shop`
                        schema only. Example: `--match="^shop\.orders$"` to
                        check one table only.
  --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)
  -w, --warning WARNING
                        WARN threshold for the number of bad tables (per
                        category: missing indexes / missing primary key).
                        Supports Nagios ranges. Default: 0

Documentation:
https://linuxfabrik.github.io/monitoring-plugins/check-plugins/mysql-table-indexes/

Usage Examples

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

Scope the check to one schema, or mute a noisy one:

./mysql-table-indexes --match="^employees\."
./mysql-table-indexes --ignore="^(staging|tmp)\."

OK output:

Everything is ok. 132 base tables across user schemas.

WARN output:

132 base tables across user schemas.

3 tables without any index [WARNING]:
* `employees`.`current_dept_emp`
* `employees`.`dept_emp_latest_date`
* `legacy`.`audit_log`

5 InnoDB tables without a user-defined `PRIMARY KEY` [WARNING]:
* `legacy`.`audit_log`
* `legacy`.`sessions`
* `staging`.`import_buffer`
* `staging`.`migration_old`
* `tmp`.`workspace`

Recommendations:
* Add at least a `PRIMARY KEY` (or any secondary index) on each of the listed tables. A heap table without any index forces every query against it into a full table scan
* Add a `PRIMARY KEY` to each of the listed InnoDB tables. Without a user-defined `PRIMARY KEY`, InnoDB falls back to a hidden 6-byte clustered index that ROW-based replication cannot use efficiently (worst case: full table scan per row event on the replica). Symptoms: replication lag, high CPU on replicas during writes

States

  • WARN if the number of tables without any index is at or above --warning (default: 1). CRIT at --critical (default: 10).
  • WARN if the number of InnoDB tables without a user-defined PRIMARY KEY is at or above --warning. CRIT at --critical.
  • Tables dropped by --match / --ignore never reach the thresholds and are not part of the total, so a check filtered down to nothing reports OK.
  • --always-ok suppresses all alerts and always returns OK.

Perfdata / Metrics

Name Type Description
mysql_innodb_tables_without_primary_key Number Number of InnoDB base tables in user schemas without a user-defined PRIMARY KEY.
mysql_tables_without_index Number Number of base tables in user schemas with zero entries in information_schema.statistics.
mysql_total_tables Number Total number of base tables across user schemas (excludes system schemas and tables matched by --ignore-schemas / --ignore-tables).

Credits, License