Check mysql-table-definition-cache¶
Overview¶
Checks the table definition cache size in MySQL/MariaDB against the number of tables, views and system tables outside information_schema, which never takes a slot in the cache. When table_definition_cache is smaller than the table count, definitions get evicted and re-read on each access, which costs .frm parses on disk-heavy workloads. Logic taken from MySQLTuner:mysql_stats(). Unlike MySQLTuner, the information_schema tables are not counted.
Important Notes:
- Requires MySQL/MariaDB v5.1+
- See additional notes for all mysql monitoring plugins
table_definition_cacheautosizes only when it is left unset: MySQL then picks a value up to 2000, while MariaDB always settles on 400. To keep more definitions cached than that, set an explicit value above the table count- User account requires access to
INFORMATION_SCHEMA(user with no privileges is sufficient) and SELECT privileges on all schemas and tables to provide accurate results - 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.
Data Collection:
- Queries
SHOW GLOBAL VARIABLESfortable_definition_cache - Counts tables via
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema != 'information_schema'. Themysql,performance_schemaandsystables take a slot in the cache as soon as they are used, so they are counted
Fact Sheet¶
| Fact | Value |
|---|---|
| Check Plugin Download | https://github.com/Linuxfabrik/monitoring-plugins/tree/main/check-plugins/mysql-table-definition-cache |
| Nagios/Icinga Check Name | check_mysql_table_definition_cache |
| Check Interval Recommendation | Every hour |
| Can be called without parameters | Yes |
| Runs on | Cross-platform |
| Compiled for Windows | No (runs with Python interpreter) |
| 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-definition-cache [-h] [-V] [--always-ok]
[--defaults-file DEFAULTS_FILE]
[--defaults-group DEFAULTS_GROUP]
[--no-perfdata] [--timeout TIMEOUT]
Checks the table definition cache size in MySQL/MariaDB against the number of
tables, views and system tables outside `information_schema`, which never
takes a slot in the cache. When `table_definition_cache` is smaller than the
table count, definitions get evicted and re-read on each access, which costs
`.frm` parses on disk-heavy workloads. Alerts when `table_definition_cache` is
below the table count.
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
--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)
Documentation:
https://linuxfabrik.github.io/monitoring-plugins/check-plugins/mysql-table-definition-cache/
Usage Examples¶
./mysql-table-definition-cache --defaults-file=/var/spool/icinga2/.my.cnf
OK output:
Everything is ok. `table_definition_cache` (2000) is greater than the number of tables (516).
WARN output:
`table_definition_cache` (400) is less than the number of tables (516) [WARNING].
Recommendations:
* Raise `table_definition_cache` above 516.
States¶
- WARN if
table_definition_cacheis below the number of tables outsideinformation_schema. - OK if
table_definition_cache >= total_tables. --always-oksuppresses all alerts and always returns OK.
Perfdata / Metrics¶
| Name | Type | Description |
|---|---|---|
| mysql_table_definition_cache | Number | Number of table definitions that can be cached. |
| mysql_total_tables | Number | Number of tables, views and system tables outside information_schema. |
Credits, License¶
- Authors: Linuxfabrik GmbH, Zurich
- License: The Unlicense, see LICENSE file.
-
Credits:
- heavily inspired by MySQLTuner (https://github.com/major/MySQLTuner-perl)