Check mysql-innodb-buffer-pool-size
Overview
Checks the size of the InnoDB buffer pool in MySQL/MariaDB. Logic is taken from MySQLTuner script:mysql_innodb(), v1.9.8.
Always take care of both innodb_buffer_pool_size
and innodb_log_file_size
when making adjustments. For that have a look at the following output example InnoDB buffer pool / data size: 36.0GiB / 49.4GiB [WARNING]. Set innodb_buffer_pool_size >= 49.4GiB. innodb_log_file_size * innodb_log_files_in_group / innodb_buffer_pool_size = 9.0GiB * 2 / 36.0GiB = 50.0% [WARNING] (should be 25%). Set innodb_log_file_size to 4.5GiB.
:
Here, buffer pool is 36 GB.
Data does not fit in, it needs 49 GB.
The check plugin complains and makes some suggestions on how to resize
innodb_buffer_pool_size
andinnodb_log_file_size
.If we adjust
innodb_buffer_pool_size
to 50 GB, andinnodb_log_files_in_group
is set to2
(deprecated and ignored from MariaDB 10.5.2), we should setinnodb_log_file_size
to6.25
to get the 25% log file versus pool size ratio. Then both warnings should change to OK.Attention: Assuming this is a database server with entirely/primarily InnoDB tables, you need at least 64 GB, following the rule that the InnoDB buffer pool size can be set up to 80% of the total memory in this case.
Hints:
Requires a user account with high privileges to access schemas like INFORMATION_SCHEMA. 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 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.
On RHEL 7+, one way to install the Python MySQL Connector is via
pip install pymysql
On MariaDB 10.2.2+,
innodb_buffer_pool_size
can be set dynamically..Compared to check_mysql / MySQLTuner this check currently:
supports only simple login with username/password (not via SSL/TLS)
does not support a connection via socket
Fact Sheet
Check Plugin Download |
|
Check Interval Recommendation |
Every 5 minutes |
Can be called without parameters |
Yes |
Available for |
Python 3, Windows |
Requirements |
Python module |
Help
usage: mysql-innodb-buffer-pool-size [-h] [-V] [--always-ok] [-H HOSTNAME]
[-p PASSWORD] [--port PORT]
[-u USERNAME]
Checks the size of the InnoDB buffer pool in MySQL/MariaDB.
options:
-h, --help show this help message and exit
-V, --version show program's version number and exit
--always-ok Always returns OK.
-H HOSTNAME, --hostname HOSTNAME
MySQL/MariaDB hostname. Default: 127.0.0.1
-p PASSWORD, --password PASSWORD
Use the indicated password to authenticate the
connection. Default:
--port PORT MySQL/MariaDB port. Default: 3306
-u USERNAME, --username USERNAME
MySQL/MariaDB username. Default: root
Usage Examples
./mysql-innodb-buffer-pool-size --hostname localhost --username root --password mypassword
Output:
InnoDB buffer pool / data size: 36.0GiB / 49.4GiB [WARNING]. Set innodb_buffer_pool_size >= 49.4GiB. innodb_log_file_size * innodb_log_files_in_group / innodb_buffer_pool_size = 9.0GiB * 2 / 36.0GiB = 50.0% [WARNING] (should be 25%). Set innodb_log_file_size to 4.5GiB.
States
WARN if size of data does not fit into the InnoDB buffer pool.
WARN if the InnoDB log file size versus the InnoDB pool size ratio is not in the range of 20 to 30%.
Perfdata / Metrics
Name |
Type |
Description |
---|---|---|
mysql_innodb_buffer_pool_size |
Bytes |
InnoDB buffer pool size in bytes. The primary value to adjust on a database server with entirely/primarily InnoDB tables, can be set up to 80% of the total memory. |
mysql_innodb_log_file_size |
Bytes |
Size in bytes of each InnoDB redo log file in the log group. The combined size can be no more than 512GB. Larger values mean less disk I/O due to less flushing checkpoint activity, but also slower recovery from a crash. |
mysql_innodb_log_files_in_group |
Number |
Number of physical files in the InnoDB redo log. Deprecated and ignored from MariaDB 10.5.2. |
mysql_innodb_log_size_pct |
Percentage |
innodb_log_file_size * innodb_log_files_in_group / innodb_buffer_pool_size * 100 |
Credits, License
Authors: Linuxfabrik GmbH, Zurich
License: The Unlicense, see LICENSE file.
Credits:
heavily inspired by MySQLTuner (https://github.com/major/MySQLTuner-perl)