Controlling MariaDB optimizer strategies_MariaDB Cookbook-QQ阅读女频现言网

上QQ阅读APP看书，第一时间看更新

Controlling MariaDB optimizer strategies

Starting with MariaDB 5.3 and continuing with all major releases since, various optimizations have been introduced that improve the core performance of MariaDB. To keep upgrades as compatible and as trouble-free as possible or because it is only useful in certain limited instances, many of these optimizations are turned off by default. This recipe is about enabling optimizations which are turned off by default.

In this recipe, we will enable the Multi-Range Read optimizations, but the basic concepts apply to control any of the optimizer_switch flags.

How to do it...

Launch the mysql command-line client application and connect to our MariaDB server as the root user or as a user with the SUPER privilege.
Show the current status of all optimizer_switch flags with the following command:
```
SELECT @@optimizer_switch\G
```
The output of the previous command will be similar to the following screenshot. There may be some differences depending on our local server settings.
In the output, the mrr, mrr_cost_based, and mrr_sort_keys flags are all set to off. Enable them with the following command:
```
SET optimizer_switch="mrr=on";
SET optimizer_switch="mrr_cost_based=on";
SET optimizer_switch="mrr_sort_keys=on";
```
Run the SELECT command from step 2 and confirm that the three mrr flags are now set to on.

How it works...

The optimizer_switch variable is basically a list of flags, which shows the status of the various available optimization strategies. When we use the SET command, we can turn various individual flags off and on. Any flags that we do not name in the SET command remain as they are.

There's more...

By default, the SET command only sets the variables for our current session. If we quit the client or we are disconnected for some reason and then we reconnect, the flags will be set to what they were before the changes were made.

To make our changes until MariaDB is shut down or restarted, add GLOBAL to the command as follows:

SET GLOBAL optimizer_switch="mrr=on";

If we want to make the change permanent, so that an optimization is either on or off permanently, we need to add it to our my.cnf or my.ini file. For example, to turn on all the mrr optimizations, add the following lines of code to the end of the file (or to an existing [mysqld] section):

[mysqld]
optimizer_switch = "mrr=on, mrr_cost_based=on,mrr_sort_keys=on"

Restart MariaDB to activate the changes.