[SOLVED] MariaDB - Tuning

Steve R. · 07-05-2021, 10:46 AM

I appear not to be comprehending how system (server) variables in MariaDB are set. Using Mint 20.1, MariaDB server version: 10.3.29-MariaDB-0ubuntu0.20.04.1 - Ubuntu 20.04

When looking at the results of "Advisor" under the "Status" tab, "Advisor" suggests some revisions. I will only focus on one for now, as once this concern is resolved, solving the rest should follow the same pattern.

Advisor reports:

Quote:

{long_query_time} is set to 10 seconds or more, thus only slow queries that take above 10 seconds are logged.

Solution suggested by Advisor:

Quote:

It is suggested to set long_query_time to a lower value, depending on your environment. Usually a value of 1-5 seconds is suggested.

To implement the solution suggested above, I went to the "Variables" tab under "More" and edited that value to set it to 1. After restarting MariaDB (sudo service mysql restart), I reexamined Advisor. Nothing changed, changes did not take.

After looking around a bit, it appears that many configuration settings are in the file: etc/mysql/mariadb.conf.d/50-server.cnf

A small portion of that file is shown below, where you will see my revisions. None of those revisions had any effect on changing what Advisor was reporting after restarting MariaDB. What have I done wrong?

Code:

# Enable the slow query log to see queries with especially long duration
# Revised 7/5/2021 in response to advisor.
#slow_query_log_file    = /var/log/mysql/mariadb-slow.log
slow_query_log_file     = /var/log/mysql/mariadb-slow.log
slow_query_log          = ON
#long_query_time        = 10
long_query_time         = 1
#log_slow_rate_limit    = 1000
#log_slow_verbosity     = query_plan
#log-queries-not-using-indexes

Steve R. · 07-05-2021, 12:48 PM

Since posting, as it usually happens, I made some progress, I moved some of the setting to the bottom of the file: /etc/mysql/mariadb.cnf. They took except for "query_cache_limit" (apparently the variable "Qcache_free_memory" no longer exists.). Results do not look promising at this time: "The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. Deprecation includes query_cache_limit.". So why is Advisor making a recommendation for fixing it?

/etc/mysql/mariadb.cnf

Code:

# This is the "replacement" for the MySQL my.cnf file.
# The MariaDB configuration file.
# Planning to use the "/etc/mysql/mariadb.conf.d/50-server.cnf" file to set global options (July 5, 2021).
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

# Configuration values added based on the suggestions of the "Advisor". July 5, 2021
slow_query_log_file     = /var/log/mysql/mariadb-slow.log
slow_query_log          = ON
long_query_time         = 1
#query_cache_limit       = 4.000

astrogeek · 07-05-2021, 01:12 PM

Thanks for posting your progress!

Most of those system variables were set in /etc/my.cnf for many years, but much of that is now under /etc/my.cnf.d/ or similar paths on different distributions such as your /etc/mysql/mariadb.cnf.d/.

The confusion arises from settings in one file overriding those in another, so it is necessary to find the "top" file in the stack which defines some given variable - /etc/mysql/mariadb.cnf in you present case. Check the distro documentation for the rationale, but you might think of those inder /etc/mysql/mariadb.cnf.d/... as the defaults, and /etc/mysql/mariadb.cnf as being the local master which provides a single place to override specific defaults.

Keep us posted!

Steve R. · 07-05-2021, 02:55 PM

I have several MariaDB databases for personal use, all relatively small. MariaDB acted very quickly, so I had not perceived of any concerns until I looked "under-the-hood" with "Advisor". Sometimes, you shouldn't look.

I'm also starting to think that some of the issues that are being reported by "Advisor", such as the deprecated "Query cache" are actually artifacts (false positives) resulting from other issues. One reported issue by "Advisor": "There are too many joins without indexes.". Solving that will require going through the tables to improve them and may "resolve" "Query cache".

I ran across one post that seems to have some good relevant advice. Suggestions to consider for your my.cnf [mysqld] section in case anyone is interested.

I think, as you point out: "The confusion arises from settings in one file overriding those in another, so it is necessary to find the "top" file in the stack which defines some given variable .."; that I finally found the file where the variables are not being overridden by another subsequent file. Since that has happened, this thread is probably solved, but I will let that go for a few days to allow for additional comments. Thanks.