MariaDB Cookbook (2014)
Chapter 2. Diving Deep into MariaDB
In this chapter, we will cover the following recipes:
· Importing the data exported by mysqldump
· Using SHOW EXPLAIN with running queries
· Using LIMIT ROWS EXAMINED
· Using INSTALL SONAME
· Producing HTML output
· Producing XML output
· Migrating a table from MyISAM to Aria
· Migrating a table from MyISAM or Aria to InnoDB or XtraDB
Introduction
Now that we've got our feet wet with MariaDB, it's time to dive deeper and experiment with some of the useful features of MariaDB.
Importing the data exported by mysqldump
Importing data from a mysqldump backup is easy and quick. In this recipe, we'll import a backup of the Internet Speculative Fiction Database (ISFDB). This database is licensed under the Creative Commons Attribution license (CC BY) which allows us to use this database for this recipe and many of the other recipes in this book.
How to do it...
1. Go to http://www.isfdb.org/wiki/index.php/ISFDB_Downloads and download the latest MySQL 5.5 compatible file. The file will be almost 80 megabytes and will be named with the date the backup was made. In this recipe, we'll be using the backup-MySQL-55-2014-02-22.zip file. We can download that file or a more recent one. If we do use a more recent file, we'll need to update the names in the following steps.
2. After the download finishes, we unzip the file using the following command:
3. unzip backup-MySQL-55-2014-02-22.zip
4. When unzipped, the file will be over 300 megabytes.
5. Our next step is to launch the mysql command-line client and create a database to import into:
6. CREATE DATABASE isfdb;
7. After creating the database, quit the mysql command-line client.
8. Lastly, we import the file into MariaDB using the following command:
9. mysql isfdb < backup-MySQL-55-2014-02-22
10. Depending on the speed of our computer processor, the size of the memory we have, and the speed of our hard drive, the file will be imported in a time span of a few seconds to a couple of minutes, and the isfdb database will be full of data tables. We can now go ahead and take a look at it if we're interested.
Tip
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
How it works...
The special character < sends the contents of the backup-2014-02-22 file to the mysql command. The mysql command, in turn, is set to connect to the isfdb database we just created, so that is where the data goes. In addition to the data, the backup file contains the necessary commands to create all of the necessary tables.
There's more...
To keep the recipe short, I didn't put down any of the usual options for the mysql command-line client. Depending on how we have things set up, we may need to specify the user (-u), password (-p), host (-h), or other options. We just need to be sure to put them before the name of the database (isfdb in the recipe).
See also
· The full documentation of the mysqldump command can be found at https://kb.askmonty.org/en/mysqldump/
Using SHOW EXPLAIN with running queries
The SHOW EXPLAIN feature was introduced in MariaDB 10.0. It enables us to get an EXPLAIN (that is, a description of the query plan) of the query running in a given thread.
Getting ready
Import the ISFDB database as described in the Importing the data exported by mysqldump recipe of this chapter.
How to do it...
1. Open a terminal window and launch the mysql command-line client and connect to the isfdb database.
2. mysql isfdb
3. Next, we open another terminal window and launch another instance of the mysql command-line client.
4. Run the following command in the first window:
5. ALTER TABLE title_relationships DROP KEY titles;
6. Next, in the first window, start the following example query:
7. SELECT titles.title_id AS ID,
8. titles.title_title AS Title,
9. authors.author_legalname AS Name,
10. (SELECT COUNT(DISTINCT title_relationships.review_id)
11. FROM title_relationships
12. WHERE title_relationships.title_id = titles.title_id)
13. AS reviews
14.FROM titles,authors,canonical_author
15.WHERE
16. (SELECT COUNT(DISTINCT title_relationships.review_id)
17. FROM title_relationships
18. WHERE title_relationships.title_id = titles.title_id)>=10
19. AND canonical_author.author_id = authors.author_id
20. AND canonical_author.title_id=titles.title_id
21. AND titles.title_parent=0 ;
22. Wait for at least a minute and then run the following query to look for the details of the query that we executed in step 4 and QUERY_ID for that query:
23.SELECT INFO, TIME, ID, QUERY_ID
24.FROM INFORMATION_SCHEMA.PROCESSLIST
25.WHERE TIME > 60\G
26. Run SHOW EXPLAIN in the second window (replace id in the following command line with the numeric ID that we discovered in step 5):
27.SHOW EXPLAIN FOR id
28. Run the following command in the second window to kill the query running in the first window (replace query_id in the following command line with the numeric QUERY_ID number that we discovered in step 5):
29.KILL QUERY ID query_id;
30. In the first window, reverse the change we made in step 3 using the following command:
31.ALTER TABLE title_relationships ADD KEY titles (title_id);
How it works...
The SHOW EXPLAIN statement allows us to obtain information about how MariaDB executes a long-running statement. This is very useful for identifying bottlenecks in our database.
The query in this recipe will execute efficiently only if it touches the indexes in our data. So for demonstration purposes, we will first sabotage the title_relationships table by removing the titles index. This causes our query to unnecessarily iterate through hundreds of thousands of rows and generally take far too long to complete. The output of steps 3 and 4 will look similar to the following screenshot:
While our sabotaged query is running, and after waiting for at least a minute, we switch to another window and look for all queries that have been running for longer than 60 seconds. Our sabotaged query will likely be the only one in the output. From this output, we get ID and QUERY_ID. The output of the command will look like the following with the ID and QUERY_ID as the last two items:
Next, we use the ID number to execute SHOW EXPLAIN for our query. Incidentally, our query looks up all titles in the database that have 10 or more reviews and displays the title, author, and the number of reviews that the title has. The EXPLAIN for our query will look similar to the following:
Note
An easy-to-read version of this EXPLAIN is available at https://mariadb.org/ea/8v65g.
Looking at rows 4 and 5 of EXPLAIN, it's easy to see why our query runs for so long. These two rows are dependent subqueries of the primary query (the first row). In the first query, we see that 117044 rows will be searched, and then, for the two dependent subqueries, MariaDB searches through 83389 additional rows, twice. Ouch.
If we were analyzing a slow query in the real world at this point, we would fix the query to not have such an inefficient subquery, or we would add a KEY to our table to make the subquery efficient. If we're part of a larger development team, we could send the output ofSHOW EXPLAIN and the query to the appropriate people to easily and accurately show them what the problem is with the query. In our case, we know exactly what to do; we will add back the KEY that we removed earlier.
For fun, after adding back the KEY, we could rerun the query and the SHOW EXPLAIN command to see the difference that having the KEY in place makes. We'll have to be quick though, as with the KEY there, the query will only take a few seconds to complete (depending on the speed of our computer).
There's more...
The output of SHOW EXPLAIN is always accompanied by a warning. The purpose of this warning is to show us the command that is being run. After running SHOW EXPLAIN on a process ID, we simply issue SHOW WARNINGS\G and we will see what SQL statement the process ID is running:
This is useful for very long-running commands that after their start, takes a long time to execute, and then returns back at a time where we might not remember the command we started.
Note
In the examples of this recipe, we're using "\G" as the delimiter instead of the more common ";" so that the data fits the page better. We can use either one.
See also
· Some long-running queries can consume more resources than they are worth, and in those cases, the Using LIMIT ROWS EXAMINED recipe is helpful
· The full documentation of the KILL QUERY ID command can be found at https://mariadb.com/kb/en/data-manipulation-kill-connection-query/
· The full documentation of the SHOW EXPLAIN command can be found at https://mariadb.com/kb/en/show-explain/
Using LIMIT ROWS EXAMINED
The LIMIT ROWS EXAMINED clause is a good way to minimize the overhead of a very large or otherwise expensive query if we don't necessarily want or need to search every row in a large table or set of tables.
Getting ready
Import the ISFDB database as described in the Importing the data exported by mysqldump recipe, earlier in this chapter.
How to do it...
1. Open a terminal window and launch the mysql command-line client and connect to the isfdb database.
2. Run the following query from the Using SHOW EXPLAIN with running queries recipe, with one small addition at the end:
3. SELECT titles.title_id AS ID,
4. titles.title_title AS Title,
5. authors.author_legalname AS Name,
6. (SELECT COUNT(DISTINCT title_relationships.review_id)
7. FROM title_relationships
8. WHERE title_relationships.title_id = titles.title_id) AS reviews
9. FROM titles,authors,canonical_author
10.WHERE
11. (SELECT COUNT(DISTINCT title_relationships.review_id)
12. FROM title_relationships
13. WHERE title_relationships.title_id = titles.title_id) >= 10
14. AND canonical_author.author_id = authors.author_id
15. AND canonical_author.title_id=titles.title_id
16. AND titles.title_parent=0
17.LIMIT ROWS EXAMINED 10000;
How it works...
The LIMIT clause allows us to reduce the output of a SELECT query, but the full query is still run. On very large tables, because the full query is still being run, it may consume more resources than we would like. In MariaDB, we can use LIMIT ROWS EXAMINED to specify the number of rows we want the server to examine when executing our statement, thus minimizing the resources the query needs to use during execution.
This feature was designed to benefit queries running in something like a production web application where speed is critically important, possibly more so than having a complete answer.
LIMIT ROWS EXAMINED is also useful when testing a new query that we suspect will take a long time to run and consume a lot of resources. It's like testing on a subset of our full data without having to actually export and set up such a set. Instead, we can test on a full copy of our data, but with limits so that our testing is faster.
There's more...
Just because we are limiting the number of rows examined doesn't mean we can't also limit the output. We also need to be aware of the warnings this command gives.
Using LIMIT with LIMIT ROWS EXAMINED
When using LIMIT ROWS EXAMINED, we can still LIMIT the output to a specific number of rows. For example, we can examine 10000 rows and limit the output to the first 100 using the following command line:
LIMIT 100 ROWS EXAMINED 10000
Warning of incomplete results
With the limit set to 10000 rows, the query in this recipe completes quickly, but it comes with the following warning:
Query execution was interrupted. The query examined at least 10002 rows, which exceeds LIMIT ROWS EXAMINED (10000). The query result may be incomplete.
This warning is understandable. We told the server we only wanted it to examine 10000 rows, and so it did, and then quit. But the full query needs to examine many more rows than that, and so the results we received are incomplete, and the server is letting us know.
Using INSTALL SONAME
The INSTALL SONAME command is used to install plugins in MariaDB. In this recipe, we'll install the Cassandra storage engine.
How to do it...
1. Connect to MariaDB using the mysql command-line client with a user that has the INSERT privilege on the mysql.plugins table. The root user has this privilege, but other users might as well.
2. Install the Cassandra storage engine plugin using the following command line:
3. INSTALL SONAME 'ha_cassandra';
4. Issue the SHOW plugins; command and look for the following text:
5. | CASSANDRA | ACTIVE | STORAGE ENGINE | ha_cassandra.so | GPL |
6. Next, issue the SHOW STORAGE ENGINES; command and look for the following text:
7. | CASSANDRA | YES | Cassandra storage engine| NO | NO | NO |
8. The preceding output indicates that the Cassandra storage engine is installed and ready to go. The three NO columns are about transactions, distributed XA transactions, and savepoints, respectively. All three are features that the Cassandra storage engine does not support.
How it works...
When this command is run, the server looks in the configured plugin directory and loads the plugin with that name. We do not need to specify the file extension. The actual name of the ha_cassandra file will either be ha_cassandra.dll or ha_cassandra.so on Windows and Linux respectively.
There's more...
Installing plugins is not very difficult in MariaDB but there are some things that can trip us up if we're not careful.
Plugin names versus filenames
The name of a given plugin is defined in the data structures inside the plugin. The filename is the name of the file that contains the plugin. The two are similar, but they are not the same. For example, the name of the Cassandra storage engine plugin is CASSANDRA and the filename is ha_cassandra.so. The name is case insensitive, so when referring to it we can use CASSANDRA, Cassandra, cassandra, or even CaSsAnDrA if we want. The filename, on the other hand, is case sensitive if our underlying filesystem is case sensitive.
INSTALL SONAME versus INSTALL PLUGIN
The INSTALL SONAME command is just a variation of the INSTALL PLUGIN command. The main difference is that INSTALL PLUGIN requires two sets of information, the name and the filename, and INSTALL SONAME just needs the filename. The filename must be quoted. Here is the recipe using INSTALL PLUGIN:
INSTALL PLUGIN Cassandra SONAME 'ha_cassandra';
Apart from being shorter, INSTALL SONAME is no different from INSTALL PLUGIN, functionality-wise.
See also
· The complete documentation of the INSTALL SONAME command can be found at https://mariadb.com/kb/en/install-soname/
· The complete documentation of the Cassandra storage engine can be found at https://kb.askmonty.org/en/cassandra/
Producing HTML output
The mysql command-line client has several different output options. One of these is HTML.
Getting ready
Import the ISFDB database as described in the Importing the data exported by mysqldump recipe in this chapter. Create a file called isfdb-001.sql using the following command line:
SELECT * FROM authors LIMIT 100;
We could put whatever commands we want in this file, or give it a different name, but this works for the purposes of this recipe.
How to do it...
1. Open a terminal and navigate to where we saved the isfdb-001.sql file.
2. Issue the following command on the command line (not from within the mysql command-line client, but by calling the client with some special options):
3. mysql --html isfdb < isfdb-001.sql > isfdb-001.html
4. Execute either a dir or ls command and we'll see that now there is a file named isfdb-001.html in the directory.
5. We can now either open the newly created isfdb-001.html file in our favorite text editor, or view it in a web browser, such as Firefox, Chrome, or Opera.
How it works...
When the --html flag is passed on the command line, the mysql command-line client will spit out an HTML table instead of a regular output; no headers, footers, or DOCTYPE, just a table with the results as one long string.
On the command line, we use the < and > redirectors to read in the isfdb-001.sql file and then to direct the output to the isfdb-001.html file, respectively.
There's more...
The HTML output that the mysql command-line client produces is not pretty. What's more, it's not a completely valid HTML file as there's no DOCTYPE, no <head> section, no <body> section, no <title>, and so on. The file we created begins with a <TABLE> tag and ends with a closing </TABLE> tag. And yes, all the tags use the old uppercase style of writing HTML code.
In Linux, there is an easy way to remedy this using the Tidy program. If it is not already installed, it is easy to do so using our package manager. To clean up our HTML, add spaces and indentation, change the case of the tags to lowercase, and add a DOCTYPE and all the necessary sections. We will simply modify our recipe to the following command line:
mysql --html isfdb < isfdb-001.sql | tidy -q -i -o isfdb-001.html
Tidy will detect the appropriate DOCTYPE, change the case of the tags, indent the code, and add in the missing sections.
Of course, even tidied up, HTML output is of limited use. It is, however, something the mysql command-line client can do.
See also
· The full documentation of the mysql command-line client can be found at https://kb.askmonty.org/en/mysql-command-line-client/
Producing XML output
The mysql command-line client has several different output options. One of these is XML.
Getting ready
Import the ISFDB database as described in the Importing the data exported by mysqldump recipe in this chapter. Create a file called isfdb-001.sql using the following command line:
SELECT * FROM authors LIMIT 100;
We could put whatever commands we want in this file, or give it a different name, but this works for the purposes of this recipe. This file has the same name and contents as the file used in the previous recipe. If we've already completed that recipe, we can just reuse the same file.
How to do it...
1. Open a terminal and navigate to where we saved the isfdb-001.sql file.
2. Issue the following command on the command line (not from within the mysql command-line client, but by calling the client with some special options):
3. mysql --xml isfdb < isfdb-001.sql > isfdb-001.xml
4. Execute either a dir or ls command and we'll see that there is now a file named isfdb-001.xml in the directory.
5. To see the contents of the file, open it with our favorite text editor or an XML viewer.
How it works...
When the --xml flag is passed on the command line, the mysql command-line client will output a well-formed XML file, instead of a regular output.
On the command line we use the < and > redirectors to read in the isfdb-001.sql file and then to direct the output to the isfdb-001.xml file, respectively.
See also
· The full documentation of the mysql command-line client can be found at https://kb.askmonty.org/en/mysql-command-line-client/
Migrating a table from MyISAM to Aria
MariaDB ships with the MyISAM and Aria storage engines, among many others. The main difference between these two is that Aria is crash safe, whereas MyISAM is not. Being crash safe means that an Aria table can recover from catastrophic power loss or other unexpected failures in a much better way than a MyISAM table can. If we use MyISAM tables, an easy upgrade is to convert them to Aria tables.
Getting ready
Import the ISFDB database as described in the Importing the data exported by mysqldump recipe in this chapter.
How to do it...
1. Open the mysql command-line client and connect to the isfdb database.
2. Run the following command line:
3. ALTER TABLE authors ENGINE=Aria;
4. The ALTER command will then change the table so that it uses the Aria storage engine.
5. After it has finished, a message similar to the following will be displayed:
6. Query OK, 110829 rows affected (3.14 sec)
7. Records: 110829 Duplicates: 0 Warnings: 0
8. If our system is older or is under heavy load and it takes longer than 5 seconds for ALTER TABLE to complete, we'll see a progress message letting us know how much of the task has been completed, updated every 5 seconds, until the task is finished.
How it works...
The ALTER TABLE command changes a table in two stages. First, it creates a new table identical in every way to the old table, except that the new table has the changes specified by the command. In our case, the only change is to use the Aria storage engine instead of the MyISAM storage engine. Then, the command copies all of the data to the new table.
In the second stage, ALTER TABLE removes the old table and renames the new table with the name of the old table.
On a table like authors that only has around a hundred thousand rows, the conversion is quick and easy; however, on a table with several billion rows, the conversion process will take significantly longer.
See also
· The full documentation of the ALTER TABLE command can be found at https://kb.askmonty.org/en/alter-table/
Migrating a table from MyISAM or Aria to InnoDB or XtraDB
The default storage engine of MariaDB is XtraDB, which is an enhanced version of InnoDB.
Getting ready
Import the ISFDB database as described in the Importing the data exported by mysqldump recipe in this chapter.
How to do it...
1. Open the mysql command-line client and connect to the isfdb database.
2. Run the following command line:
3. ALTER TABLE awards ENGINE=InnoDB;
4. After the command line gets executed, a message similar to the following will be displayed:
5. Query OK, 33102 rows affected (5.37 sec)
6. Records: 33102 Duplicates: 0 Warnings: 0
7. If our system is older or is under heavy load and it takes longer than 5 seconds for the ALTER TABLE command line execution to complete, we'll see a progress message letting us know how much of the task has been completed. The message gets updated every 5 seconds, until the task is finished.
How it works...
The ALTER TABLE command converts the table over in two stages. First, it creates a new table, identical in every way to the old table, except that the new table uses the InnoDB or XtraDB storage engine (whichever we have configured as the active one) and copies all of the data to the new table.
In the second stage, ALTER TABLE removes the old table and renames the new table with the name of the old table.
On a table like awards that only has thirty thousand or more rows, the conversion is quick and easy; however, on a table with several billion rows, the conversion process will take significantly longer.
There's more...
Before converting all of the tables in a database from MyISAM or Aria to InnoDB/XtraDB, be aware that InnoDB/XtraDB uses more memory when running for the same amount of activity. Make sure our server has the memory capacity to handle it.
See also
· The full documentation for InnoDB and XtraDB can be found at https://kb.askmonty.org/en/xtradb-and-innodb/
· Full documentation of the ALTER TABLE command can be found at https://kb.askmonty.org/en/alter-table/