MariaDB - Tuning - Setting Server Variables
by Steve R. from LinuxQuestions.org on (#5KX0V)
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:
Solution suggested by Advisor:
Quote:
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


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. |
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. |
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