You are here
MariaDB configuration analysis
If we do customers database configuration analysis we check on one side if the most important MariaDB server system variables (innodb_buffer_pool_size
, ...) are set appropriately but also if some MariaDB server system variables are configured completely wrong.
Fortunately MariaDB introduced in MariaDB 10.1 the INFORMATION_SCHEMA.SYSTEM_VARIABLES
view where you can find all the relevant information. But one!
Since MariaDB 10.5 we can also see from which file the MariaDB server system variable configuration is coming from. This makes it easier to find and fix wrong configurations.
MariaDB server system variables which are NOT default
A general assumption is that the defaults set by MariaDB are in most cases OK and if you change the defaults you need a good justification for the changes. "I do not know." is NOT a good justification!
SQL> SELECT VARIABLE_NAME, GLOBAL_VALUE, DEFAULT_VALUE FROM information_schema.SYSTEM_VARIABLES WHERE GLOBAL_VALUE != DEFAULT_VALUE AND GLOBAL_VALUE NOT LIKE '%home%' AND VARIABLE_NAME LIKE 'INNODB%' ORDER BY VARIABLE_NAME; +--------------------------------+--------------+----------------------+ | VARIABLE_NAME | GLOBAL_VALUE | DEFAULT_VALUE | +--------------------------------+--------------+----------------------+ | INNODB_BUFFER_POOL_INSTANCES | 1 | 0 | | INNODB_FLUSH_LOG_AT_TRX_COMMIT | 2 | 1 | | INNODB_IO_CAPACITY_MAX | 2000 | 18446744073709551615 | | INNODB_LOG_BUFFER_SIZE | 8388608 | 16777216 | | INNODB_LOG_FILE_SIZE | 268435456 | 100663296 | | INNODB_LOG_GROUP_HOME_DIR | ./ | | | INNODB_OPEN_FILES | 2000 | 0 | | INNODB_PAGE_CLEANERS | 1 | 0 | | INNODB_PRINT_ALL_DEADLOCKS | ON | OFF | | INNODB_UNDO_DIRECTORY | ./ | | +--------------------------------+--------------+----------------------+
If we look at the results, we see, that they are not 100% accurate yet. But it is already a big help.
We did NOT configure innodb_buffer_pool_instances
for example! And also not innodb_io_capacity_max
or innodb_page_cleaners
as can be shown here:
SQL> SELECT VARIABLE_NAME, GLOBAL_VALUE, DEFAULT_VALUE, GLOBAL_VALUE_PATH FROM information_schema.SYSTEM_VARIABLES WHERE GLOBAL_VALUE != DEFAULT_VALUE AND GLOBAL_VALUE NOT LIKE '%home%' AND VARIABLE_NAME LIKE 'INNODB%' AND GLOBAL_VALUE_PATH IS NULL ORDER BY VARIABLE_NAME; +------------------------------+--------------+----------------------+-------------------+ | VARIABLE_NAME | GLOBAL_VALUE | DEFAULT_VALUE | GLOBAL_VALUE_PATH | +------------------------------+--------------+----------------------+-------------------+ | INNODB_BUFFER_POOL_INSTANCES | 1 | 0 | NULL | | INNODB_IO_CAPACITY_MAX | 2000 | 18446744073709551615 | NULL | | INNODB_LOG_GROUP_HOME_DIR | ./ | | NULL | | INNODB_OPEN_FILES | 2000 | 0 | NULL | | INNODB_PAGE_CLEANERS | 1 | 0 | NULL | | INNODB_UNDO_DIRECTORY | ./ | | NULL | +------------------------------+--------------+----------------------+-------------------+
According to MariaDB documentation the default of innodb_io_capactiy_max
is 2000. But this is a detail.
MariaDB server system variables taken from which configuration file
Sometimes we do not know and also customer does not know from which MariaDB configuration file a variable is coming from. So the following query helps finding this out. Caution: This only works since MariaDB 10.5!
SQL> SELECT VARIABLE_NAME, GLOBAL_VALUE, GLOBAL_VALUE_PATH FROM information_schema.SYSTEM_VARIABLES WHERE GLOBAL_VALUE_PATH is NOT NULL ORDER BY VARIABLE_NAME LIMIT 5; +------------------------+--------------+--------------------------------------------------+ | VARIABLE_NAME | GLOBAL_VALUE | GLOBAL_VALUE_PATH | +------------------------+--------------+--------------------------------------------------+ | BINLOG_CACHE_SIZE | 1048576 | /home/mysql/database_slow/mariadb-105/etc/my.cnf | | BINLOG_FORMAT | ROW | /home/mysql/database_slow/mariadb-105/etc/my.cnf | | BINLOG_STMT_CACHE_SIZE | 1048576 | /home/mysql/database_slow/mariadb-105/etc/my.cnf | | EXPIRE_LOGS_DAYS | 5 | /home/mysql/database_slow/mariadb-105/etc/my.cnf | | GENERAL_LOG | OFF | /home/mysql/database_slow/mariadb-105/etc/my.cnf | +------------------------+--------------+--------------------------------------------------+
MariaDB server system variables which where set dymamically
Some customers change MariaDB server system variables dynamically because they want to test something. Typically short before they call us. And sometimes they forget about those changes or did not restart the database instance or did not persist those changes into their my.cnf
configuration file. To find those changes the following query will help:
SQL> SELECT VARIABLE_NAME, GLOBAL_VALUE, GLOBAL_VALUE_ORIGIN, DEFAULT_VALUE, GLOBAL_VALUE_PATH FROM information_schema.SYSTEM_VARIABLES WHERE GLOBAL_VALUE_ORIGIN = 'SQL' ORDER BY VARIABLE_NAME; +-----------------+--------------+---------------------+---------------+-------------------+ | VARIABLE_NAME | GLOBAL_VALUE | GLOBAL_VALUE_ORIGIN | DEFAULT_VALUE | GLOBAL_VALUE_PATH | +-----------------+--------------+---------------------+---------------+-------------------+ | KEY_BUFFER_SIZE | 16777216 | SQL | 134217728 | NULL | +-----------------+--------------+---------------------+---------------+-------------------+
MariaDB server system variables which cannot be set dynamically
Sometimes it is good to know which MariaDB server system variables can be set dynamically and which MariaDB server system variables require a database instance restart:
SQL> SELECT VARIABLE_NAME, READ_ONLY, GLOBAL_VALUE, GLOBAL_VALUE_ORIGIN, DEFAULT_VALUE, VARIABLE_SCOPE, NUMERIC_MIN_VALUE, NUMERIC_MAX_VALUE FROM information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME IN ('innodb_log_file_size', 'innodb_buffer_pool_size') ORDER BY VARIABLE_NAME; +-------------------------+-----------+--------------+---------------------+---------------+----------------+-------------------+----------------------+ | VARIABLE_NAME | READ_ONLY | GLOBAL_VALUE | GLOBAL_VALUE_ORIGIN | DEFAULT_VALUE | VARIABLE_SCOPE | NUMERIC_MIN_VALUE | NUMERIC_MAX_VALUE | +-------------------------+-----------+--------------+---------------------+---------------+----------------+-------------------+----------------------+ | INNODB_BUFFER_POOL_SIZE | NO | 134217728 | CONFIG | 134217728 | GLOBAL | 5242880 | 9223372036854775807 | | INNODB_LOG_FILE_SIZE | YES | 268435456 | CONFIG | 100663296 | GLOBAL | 1048576 | 18446744073709551615 | +-------------------------+-----------+--------------+---------------------+---------------+----------------+-------------------+----------------------+
Thanks to Elena Stepanova from MariaDB for pointing me to the right place (MDEV-25034). I was blind!
- Shinguz's blog
- Log in or register to post comments