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!