Database Reference
In-Depth Information
The
log_slow_rate_limit
variable is used to control how often long queries
are actually logged. For example, a setting of
20
would log every twentieth query,
or 5 percent of the slow queries. This is useful if our slow query log is growing too
fast. If this variable is not present, the default is for the slow query log to log every
slow query.
Lastly, the
log_slow_verbosity
variable controls what information is logged.
Possible values include:
•
microtime
: Makes the log to log the queries in microseconds
•
query_plan
: Logs query execution plan information
•
innodb
: Adds additional statistical information about queries that touch
XtraDB and InnoDB tables
•
standard
: Turns on both the
microtime
and
innodb
variables
•
full
: Turns on all options
•
profiling
: Allows the logged queries to be profiled
We can specify multiple options if we separate them with commas. Here is an
example which turns on the slow query log and sets some common options:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 0.05
log_slow_rate_limit = 30
log_slow_verbosity = query_plan,standard
More information about the slow query log can be found at
https://mariadb.com/
kb/en/slow-query-log/
.
Optimizing and tuning MariaDB
The subject of tuning and optimizing MariaDB, and the applications that connect
to it, for maximum performance is worthy of a topic in itself. We won't go into the
details of specific strategies here because it's generally not necessary when we're just
getting started with MariaDB. It is useful to know a little about the subject though,
which is explained in the following section.
The basic process of tuning and optimizing MariaDB starts with identifying the
choke points, that is, the places that are causing unnecessary slowdowns. Using the
slow query log to identify these choke points is a good place to start.
