MariaDB Tips & Tricks: Where does my configuration variable value come from?

As you may already know, there are many places where a MariaDB system variable can get its value.

It can come from:

  • the compile-time default;
  • an option file;
  • the command line;
  • an automatic decision made by the server;
  • or a SET GLOBAL statement executed after startup.

And when several configuration files are involved, finding the effective source is not always obvious.

Fortunately, MariaDB provides this information in INFORMATION_SCHEMA.SYSTEM_VARIABLES.

Let’s check this in action with max_connections.

Checking the current value

First, let’s look at the value currently used by the server:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.001 sec)

This tells us the value.

But it does not tell us where that value came from.

For that, we can query INFORMATION_SCHEMA.SYSTEM_VARIABLES:

MariaDB [(none)]> SELECT
    VARIABLE_NAME,
    GLOBAL_VALUE,
    DEFAULT_VALUE,
    GLOBAL_VALUE_ORIGIN,
    GLOBAL_VALUE_PATH
FROM information_schema.SYSTEM_VARIABLES
WHERE VARIABLE_NAME = 'MAX_CONNECTIONS'\G
*************************** 1. row ***************************
        VARIABLE_NAME: MAX_CONNECTIONS
          GLOBAL_VALUE: 151
         DEFAULT_VALUE: 151
   GLOBAL_VALUE_ORIGIN: COMPILE-TIME
      GLOBAL_VALUE_PATH: NULL
1 row in set (0.001 sec)

Here we can see that the value comes from the MariaDB compile-time default.

The interesting columns are:

GLOBAL_VALUE_ORIGIN
GLOBAL_VALUE_PATH

GLOBAL_VALUE_ORIGIN explains how the effective global value was set.

GLOBAL_VALUE_PATH identifies the option file that supplied the value, when the value came from a configuration file. The path column was added in MariaDB 10.5.0.

Setting the value in an option file

Now let’s add the following option to a MariaDB configuration file:

[mariadb]
max_connections=200

After restarting MariaDB, we can run the same query:

MariaDB [(none)]> SELECT
    VARIABLE_NAME,
    GLOBAL_VALUE,
    DEFAULT_VALUE,
    GLOBAL_VALUE_ORIGIN,
    GLOBAL_VALUE_PATH
FROM information_schema.SYSTEM_VARIABLES
WHERE VARIABLE_NAME = 'MAX_CONNECTIONS'\G
*************************** 1. row ***************************
        VARIABLE_NAME: MAX_CONNECTIONS
          GLOBAL_VALUE: 200
         DEFAULT_VALUE: 151
   GLOBAL_VALUE_ORIGIN: CONFIG
      GLOBAL_VALUE_PATH: /etc/my.cnf.d/60-custom.cnf
1 row in set (0.001 sec)

This time we can immediately see that the value was read from a configuration file:

GLOBAL_VALUE_ORIGIN: CONFIG
GLOBAL_VALUE_PATH: /etc/my.cnf.d/60-custom.cnf

No need to search through every .cnf file manually.

MariaDB tells us which file supplied the effective value.

The SYSTEM_VARIABLES table contains the current global and session values as well as metadata such as the default value, scope, type, valid range, whether the variable is read-only, and how the global value was set.

And when we have multiple files?

MariaDB can read several option files.

On a Linux installation, you may have files such as:

/etc/my.cnf
/etc/my.cnf.d/my.cnf
/etc/my.cnf/mariadb.cnf
/etc/my.cnf.d/50-server.cnf
/etc/my.cnf.d/60-custom.cnf

You may also have !include and !includedir directives that load additional files.

Let’s imagine that max_connections is present in two files.

In the first file:

# cat /etc/my.cnf.d/60-custom.cnf
[mariadb]
max_connections=200

And in another file loaded later:

# cat /etc/mysql/mariadb.conf.d/99-local.cnf
[mariadb]
max_connections=500

After restarting MariaDB:

MariaDB [(none)]> SELECT
    GLOBAL_VALUE,
    GLOBAL_VALUE_ORIGIN,
    GLOBAL_VALUE_PATH
FROM information_schema.SYSTEM_VARIABLES
WHERE VARIABLE_NAME = 'MAX_CONNECTIONS'\G
*************************** 1. row ***************************
          GLOBAL_VALUE: 500
   GLOBAL_VALUE_ORIGIN: CONFIG
      GLOBAL_VALUE_PATH: /etc/my.cnf.d/99-local.cnf
1 row in set (0.001 sec)

MariaDB returns the file responsible for the effective value.

Option files are processed in order, and when the same option is specified more than once, the later setting overrides the earlier one. Files loaded by !includedir are processed alphabetically.

So GLOBAL_VALUE_PATH shows the winning file.

It does not return every file in which the option appeared.

That distinction is important.

What happens with SET GLOBAL?

Let’s now change the variable dynamically:

MariaDB [(none)]> SET GLOBAL max_connections=300;
Query OK, 0 rows affected (0.001 sec)

The current value is now:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 300   |
+-----------------+-------+
1 row in set (0.001 sec)

And the metadata becomes:

MariaDB [(none)]> SELECT
    GLOBAL_VALUE,
    DEFAULT_VALUE,
    GLOBAL_VALUE_ORIGIN,
    GLOBAL_VALUE_PATH
FROM information_schema.SYSTEM_VARIABLES
WHERE VARIABLE_NAME = 'MAX_CONNECTIONS'\G
*************************** 1. row ***************************
          GLOBAL_VALUE: 300
         DEFAULT_VALUE: 151
   GLOBAL_VALUE_ORIGIN: SQL
      GLOBAL_VALUE_PATH: NULL
1 row in set (0.001 sec)

The origin is now SQL.

This tells us that the current global value was changed while the server was running.

Notice that the configuration file still contains:

max_connections=200

But the running value is now 500.

After a restart, unless the option file is modified, MariaDB will read 500 again.

That is another useful reason to inspect GLOBAL_VALUE_ORIGIN.

It helps distinguish the startup configuration from a runtime change.

Listing all variables coming from configuration files

We can also list every system variable whose current value came from an option file:

SELECT
    VARIABLE_NAME,
    GLOBAL_VALUE,
    GLOBAL_VALUE_PATH
FROM information_schema.SYSTEM_VARIABLES
WHERE GLOBAL_VALUE_PATH IS NOT NULL
ORDER BY GLOBAL_VALUE_PATH, VARIABLE_NAME;

For example:

+--------------------+--------------+--------------------------+
| VARIABLE_NAME      | GLOBAL_VALUE | GLOBAL_VALUE_PATH        |
+--------------------+--------------+--------------------------+
| PERFORMANCE_SCHEMA | ON           | /etc/my.cnf.d/server.cnf |
+--------------------+--------------+--------------------------+
1 row in set (0.004 sec)

This is convenient when reviewing the server’s effective configuration.

Listing values that differ from their defaults

Another useful query is to compare the current global values with the compiled defaults:

SELECT
    VARIABLE_NAME,
    GLOBAL_VALUE,
    DEFAULT_VALUE,
    GLOBAL_VALUE_ORIGIN,
    GLOBAL_VALUE_PATH
FROM information_schema.SYSTEM_VARIABLES
WHERE GLOBAL_VALUE <> DEFAULT_VALUE
ORDER BY VARIABLE_NAME;

This gives a quick overview of the variables that have been changed.

However, there are two things to keep in mind.

First, some variables may be adjusted automatically by MariaDB.

Second, comparing values as strings is not always a perfect configuration audit. Some variables may have equivalent values represented differently, and some values depend on the environment or server initialization.

Still, it is a very practical starting point.

Checking a variable with one compact query

Most of the time, this is the query I need:

SELECT
    VARIABLE_NAME,
    GLOBAL_VALUE,
    DEFAULT_VALUE,
    GLOBAL_VALUE_ORIGIN,
    GLOBAL_VALUE_PATH
FROM information_schema.SYSTEM_VARIABLES
WHERE VARIABLE_NAME = 'INNODB_BUFFER_POOL_SIZE'\G

It answers several questions at once:

- What is the current global value?
- What is the compiled default?
- How was the current value set?
- Which configuration file supplied it?

And SYSTEM_VARIABLES contains much more information.

For example:

SELECT
    VARIABLE_NAME,
    GLOBAL_VALUE,
    DEFAULT_VALUE,
    VARIABLE_SCOPE,
    VARIABLE_TYPE,
    READ_ONLY,
    COMMAND_LINE_ARGUMENT,
    VARIABLE_COMMENT
FROM information_schema.SYSTEM_VARIABLES
WHERE VARIABLE_NAME = 'INNODB_BUFFER_POOL_SIZE'\G

This can help determine whether the variable is dynamic, global or session-scoped, accepted on the command line, and what range or type of value it expects.

Which option files does MariaDB read?

Sometimes you also need to know the full option file hierarchy.

The exact list depends on the operating system, package, build options, startup user, and arguments used to launch MariaDB.

The safest way to check is:

mariadbd --help --verbose

Near the beginning of the output, MariaDB displays the option files and option groups it reads:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

The following groups are read:
mysqld server mysqld-11.8 mariadb mariadb-11.8
mariadbd mariadbd-11.8 client-server galera

MariaDB documents this command as the way to obtain the exact files and groups read by a specific binary on a specific system.

You can also print the options assembled from the option files:

mariadbd --print-defaults

Remember that option-file processing arguments such as --defaults-file and --no-defaults must be placed first on the command line:

mariadbd --defaults-file=/tmp/test.cnf --print-defaults

--defaults-file causes MariaDB to read only the specified option file.

A small version detail

INFORMATION_SCHEMA.SYSTEM_VARIABLES has existed since MariaDB 10.1 and has already exposed metadata, including GLOBAL_VALUE_ORIGIN.

However, the very useful GLOBAL_VALUE_PATH column was introduced in MariaDB 10.5.0.

On an older MariaDB release, you may therefore be able to see that a value came from the configuration, but not the exact file:

GLOBAL_VALUE_ORIGIN: CONFIG

On MariaDB 10.5 and later, you can also get:

GLOBAL_VALUE_PATH: /etc/my.cnf.d/60-custom.cnf

Conclusion

MariaDB makes it very easy to determine where the current value of a system variable came from.

The query is:

SELECT
    VARIABLE_NAME,
    GLOBAL_VALUE,
    DEFAULT_VALUE,
    GLOBAL_VALUE_ORIGIN,
    GLOBAL_VALUE_PATH
FROM information_schema.SYSTEM_VARIABLES
WHERE VARIABLE_NAME = 'MAX_CONNECTIONS';

GLOBAL_VALUE_ORIGIN tells us whether the value came from the compiled default, the configuration, automatic server initialization, or an SQL statement.

GLOBAL_VALUE_PATH tells us which option file supplied the effective value.

This is especially useful on systems where multiple .cnf files and included directories are involved.

No more grepping every configuration file and wondering which occurrence won.

MariaDB can tell us directly.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Leave a Reply

Your email address will not be published. Required fields are marked *