Chapter 3. Optimizing and Tuning MariaDB

In this chapter, we will cover the following recipes:

• Using SHOW STATUS to check whether a feature is being used

• Controlling MariaDB optimizer strategies

• Using extended Keys with InnoDB and XtraDB

• Configuring the Aria two-step deadlock detection

• Configuring the MyISAM segmented key cache

• Configuring threadpool

• Configuring the Aria pagecache

• Optimizing queries with the subquery cache

• Optimizing semijoin subqueries

• Creating an index

• Creating a full-text index

• Removing an index

• Using JOINs

• Using microseconds in DATETIME columns

• Updating the DATETIME and TIMESTAMP columns automatically

Introduction

This chapter contains recipes for configuring and using various optimization and tuning-related features of MariaDB. This chapter is not meant as a complete or even a partial MariaDB optimization and tuning guide; it only contains recipes related to the topic.

Using SHOW STATUS to check if a feature is being used

The SHOW STATUS command shows information about the server. This includes things such as the number of bytes of data received and sent by the server, the number of connections served, the number of rows read, and so on. The command can also be used to check whether a feature has been enabled or is being used.

How to do it...

1. Launch the mysql command-line client and connect to our MariaDB database server.

2. Uninstall the Cassandra storage engine with:

   UNINSTALL SONAME 'ha_cassandra.so';
   

3. MariaDB will either respond with a Query OK message (if the Cassandra storage engine was installed and has now been uninstalled) or it will give the SONAME ha_cassandra.so does not exist error (if the Cassandra storage engine was not installed). Either of the messages is ok.

4. Issue the following SHOW STATUS command to see if the Cassandra storage engine is installed. The result will be an Empty set, which means that it is not installed:

   SHOW STATUS LIKE 'Cassandra...

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

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

2. Show the current status of all optimizer_switch flags with the following command:

   SELECT @@optimizer_switch\G
   

3. The output of the previous command will be similar to the following screenshot. There may be some differences depending on our local server settings...

Using extended keys with InnoDB and XtraDB

When creating an execution plan for a query, the MariaDB optimizer makes a cost-based decision about whether or not to use one or more indexes instead of reading through a table row-by-row. Indexes are often, but not always the faster choice. The extended key's optimization improves the index lookups for InnoDB and XtraDB tables.

How to do it...

1. Launch the mysql command-line client application and connect it to our MariaDB server as the root user or as a user with the SUPER privilege.

2. Enable the extended keys optimization with the following command:

   SET GLOBAL optimizer_switch='extended_keys=on';
   

3. Add the following code to our my.cnf or my.ini file (or to an existing [mysqld] section):

   [mysqld]

4. optimizer_switch = 'extended_keys=on' verifies that extended_keys is set to on with the following command:

   SHOW VARIABLES LIKE 'optimizer_switch'\G
   

How it works...

In this recipe, we turn on the extended_keys optimization globally for the running server and we...

Configuring the Aria two-step deadlock detection

A deadlock is when there are two competing actions and both are waiting for the other to finish and so neither of them ever finish. The Aria storage engine is able to automatically detect and deal with deadlocks. To make effective use of this feature, we should configure it with the settings that work well for our needs.

How to do it...

1. Run the following command to show the current settings for Aria's two-step deadlock detection:

   SHOW VARIABLES LIKE 'deadlock_%'\G
   

2. If our settings are set to the default values, the output of the previous command will be as shown in the following screenshot:

   

3. Change the variables to our desired values, as follows:

   SET GLOBAL deadlock_search_depth_short = 3;
   SET GLOBAL deadlock_search_depth_long = 10;
   SET GLOBAL deadlock_timeout_long = 10000000;
   SET GLOBAL deadlock_timeout_short = 5000;
   

4. Make the changes permanent by adding the following lines of code to the bottom of our my.cnf or my.ini file (or to an existing ...

Configuring the MyISAM segmented key cache

We can dramatically improve the performance of our MyISAM tables by splitting the key cache into multiple segments. This is useful if we have high concurrency in our database usage (meaning there are lots of threads trying to access the key cache).

How to do it...

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

2. View the current number of segments with the following command:

   SHOW VARIABLES LIKE 'key_cache_segments'\G
   

3. Set the number of segments to 64 with the following command:

   SET GLOBAL key_cache_segments = 64;
   

4. Make the setting permanent by adding the following lines of code to the end of our my.cnf or my.ini file (or to an existing [mysqld] section):

   [mysqld]
   key_cache_segments = 64

How it works...

Whenever a MyISAM thread accesses the key cache, it needs to first acquire a lock. Lots of threads trying to get a lock on a single, monolithic key cache is a big choke...

Configuring threadpool

Pool-of-threads, or threadpool, is a MariaDB feature that improves performance by pooling active threads together instead of the old one thread per client connection method, which does not scale well for typical web-based workloads with many short-running queries.

How to do it...

1. To enable threadpool on Linux, add the following line of code to our my.cnf file (or to an existing [mysqld] section) and then restart MariaDB:

   [mysqld]
   thread_handling = pool-of-threads

2. To enable threadpool on Windows, we don't have to do anything as it is set by default and uses the native Windows thread pooling.

3. To disable threadpool on Windows, add the following to our main my.ini file and then restart MariaDB:

   [mysqld]
   thread_handling = one-thread-per-connection
   

4. To disable threadpool on Linux, either change the thread_handling line to one-thread-per-connection, as on Windows, or remove the thread_handling line from our system's my.cnf file, and then restart MariaDB.

How it works...

When threadpool...

Configuring the Aria pagecache

One difference between the Aria and MyISAM storage engines is Aria's PAGE row format. This is the default row format for Aria tables and must be used to take advantage of the crash-safe features of this storage engine.

A primary advantage of this row format is that rows are efficiently cached, which gives better performance. They also do not fragment as easily as the alternative DYNAMIC row format, and updates to them are quicker. The Aria pagecache is controlled by three variables.

How to do it...

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

2. View the current Aria pagecache settings with the following command:

   SHOW VARIABLES LIKE 'aria_pagecache%';
   

3. The output will look like the following screenshot:

   

4. Add the following lines of code to the end of our system's my.cnf or my.ini file (or to an existing [mysqld] section in the file), and then restart MariaDB:

   [mysqld]
   aria_pagecache_buffer_size...

Optimizing queries with the subquery cache

The subquery cache is one of the several methods utilized by MariaDB to improve the performance of statements with subqueries. This is a feature unique to MariaDB and makes subqueries in MariaDB much faster than competing databases.

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe in Chapter 2, Diving Deep into MariaDB.

How to do it...

1. Restart MariaDB to clear the subquery cache.

2. Launch the mysql command-line client application and connect to the isfdb database on our MariaDB server.

3. Run the following command to show our usage of the subquery cache:

   SHOW STATUS LIKE 'subquery%';
   

4. Because we just restarted MariaDB and cleared the subquery cache, the output will look like the following screenshot:

   

5. Run the following query:

   SELECT titles.title_id AS ID, 
     titles.title_title AS Title, 
     authors.author_legalname AS Name, 
     (SELECT COUNT(DISTINCT title_relationships.review_id) 
       FROM title_relationships...

Optimizing semijoin subqueries

MariaDB includes several optimizations specifically targeted at semijoin subqueries. Semijoin subqueries are primarily the ones in which the subquery is an IN subquery located in the WHERE clause of our SQL statement. An example will be something like the following from the popular DBT3 benchmarking dataset:

SELECT * FROM part
WHERE p_partkey IN
    (SELECT l_partkey FROM lineitem
    WHERE l_shipdate between '1997-01-01' and '1997-02-01')
ORDER BY p_retailprice DESC LIMIT 10;

How to do it...

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

2. Run the following command to enable the exists_to_in optimization:

   SET GLOBAL optimizer_switch='exists_to_in=on';
   

3. Make the change permanent by adding the following lines of code to the end of our my.cnf or my.ini file (or by adding it to an existing [mysqld] section):

   [mysqld]
   optimizer_switch = 'exists_to_in=on';

How it works...

MariaDB has...

Creating an index

An index helps MariaDB (or any database, really) to quickly locate often looked-for data that it will otherwise have to search for by reading through our tables row by row. Creating indexes of often-queried columns in large tables is a basic, but very useful optimization.

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe in Chapter 2, Diving Deep into MariaDB.

How to do it...

1. Launch the mysql command-line client application and connect to the isfdb database on our MariaDB server.

2. Create an index on the email_address column of the emails table:

   CREATE INDEX email ON emails(email_address(50));
   

3. Show the indexes on the emails table with the following command:

   SHOW INDEX FROM emails\G
   

4. The output will look similar to the following screenshot:

   

How it works...

The emails table already has an index, the primary key. This is the most common type of index, but if we rarely search in a large table for a record matching a primary key...

Creating a full-text index

A full-text index is a special type of index optimized to search through the text-based columns. They can only be created for columns of the type CHAR, VARCHAR, and TEXT.

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe from Chapter 2, Diving Deep into MariaDB.

How to do it...

1. Launch the mysql command-line client application and connect to the isfdb database on our MariaDB server.

2. Create a FULLTEXT index on the note_note column of the notes table using the following command:

   CREATE FULLTEXT INDEX note ON notes(note_note);
   

3. When MariaDB has finished creating the index, we will get an output similar to the following:

   Query OK, 246719 rows affected (11.08 sec) 
   Records: 246719  Duplicates: 0  Warnings: 0 
   

4. Show the indexes on the notes table with the following command:

   SHOW INDEX FROM notes\G
   

5. The output of the SHOW command will look like the following screenshot:

   

How it works...

A FULLTEXT index enables us to search...

Removing an index

If an index is not used, the only thing it is doing is wasting space and slowing down our INSERT and UPDATE statements. So if an index is not being used, we should remove it.

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe from Chapter 2, Diving Deep into MariaDB.

How to do it...

1. Launch the mysql command-line client application and connect to the isfdb database on our MariaDB server.

2. Drop the full-text index on the note_note column of the notes table, which we created in the previous recipe using the following command:

   DROP INDEX note ON notes;
   

3. After the command's execution is complete, verify that the full-text index is removed with the following command:

   SHOW INDEX FROM notes;
   

How it works...

The DROP INDEX command actually creates and runs an ALTER TABLE statement to remove an index. So when removing an index on a large table, we'll see the standard ALTER TABLE progress messages.

There's more...

The last thing we want...

Using JOINs

Joining data from two or more tables is how we unlock the power of a relational database such as MariaDB. There are three basic JOIN types: INNER, CROSS, and LEFT (or OUTER).

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe from Chapter 2, Diving Deep into MariaDB.

How to do it...

1. Launch the mysql command-line client application and connect to the isfdb database on our MariaDB server.

2. Perform an INNER JOIN of the authors and emails tables to show us a list of authors and their corresponding e-mail addresses using the following command:

   SELECT author_canonical, email_address 
     FROM authors INNER JOIN emails 
       ON authors.author_id = emails.author_id;
   

3. Perform a LEFT JOIN of the emails and authors tables to show us a list of authors and their corresponding e-mail addresses using the following command:

   SELECT author_canonical, email_address 
     FROM emails LEFT JOIN authors 
       ON authors.author_id = emails.author_id;
   

4. Perform...

Using microseconds in the DATETIME columns

There was a time when measuring dates and times accurately to within a single second were as precise as we needed it to be. However, those days are gone. Users expect their apps to have response times of well under a second, and so our databases must be able to track those times as well.

How to do it...

1. Launch the mysql command-line client application and connect it to our MariaDB server.

2. Create a test database if it doesn't already exist and switch to it using the following command:

   CREATE DATABASE IF NOT EXISTS test;
   USE test;
   

3. Create a simple two-column table named times using the following command:

   CREATE TABLE times (
     id int NOT NULL AUTO_INCREMENT,
     dt datetime(6),
     PRIMARY KEY (id)
   );
   

4. Run the following INSERT statements at least four times and add some sample data to our table using the following command:

   INSERT INTO times (dt) VALUES (NOW()), (NOW(6));
   

5. Select all of the data from our table with the following SELECT command:

   SELECT * FROM...

Updating DATETIME and TIMESTAMP columns automatically

If our database has a DATETIME or TIMESTAMP column that we want to be updated whenever the record is updated, there is no need for us to put that logic in our application. MariaDB can take care of it for us.

How to do it...

1. Launch the mysql command-line client application and connect to our MariaDB server.

2. Create a test database if it doesn't already exist and switch to it using the following command:

   CREATE DATABASE IF NOT EXISTS test;
   USE test;
   

3. Create a simple table named dtts using the following commands:

   CREATE TABLE dtts ( 
     id int(11) NOT NULL AUTO_INCREMENT, 
     name varchar(25), 
     dt datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), 
     ts timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) 
       ON UPDATE CURRENT_TIMESTAMP(3), 
     PRIMARY KEY (id) 
   );
   

4. Insert some data into our new table using the INSERT command:

   INSERT INTO dtts (name) VALUES 
     ('Thomass'),('Gordon'),('Howard'),('Ezra'); 
   

5. Fix the misspelling of Thomas:

   UPDATE dtts...