The slow query log feature gives the possibility to log queries that take more than x seconds to be executed. This is the first step when investigating a performance issue. To look at the current status, connect to your MariaDB instance and launch it:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%SLOW_QUERY%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/log/mysql/mariadb-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

Here, we can see the path of the slow query logs. To activate this on the fly, run that SQL command:

MariaDB [(none)]> SET GLOBAL SLOW_QUERY_LOG=1;
Query OK, 0 rows affected (0.00 sec)

The other option is to set in seconds the query delay to mark it as a long query. These long queries will be logged...

The explain SQL command provides information for a specific request. Most of the time, we get a query from the slow query logs to analyze the request. The explain command won't return the classical output of the query but will provide some information concerning the related SQL query.

The explain command can only be applied on a SELECT query. UPDATE and DELETE are supported in Version 10.0.5!

Let's take a query that you can have in your slow query logs. Here is an example with a working version of MediaWiki:

MariaDB [mediawiki]> explain select page_id, page_title, page_namespace, page_is_redirect, old_id, old_text from wiki_page, wiki_revision, wiki_text where rev_id=page_latest and old_id=rev_text_id\g;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wiki_page
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2005
        Extra:
*****...

Since you can directly have the query log the output of the explain command in MariaDB 10.0.5, this will help you save time. To make it active, you need to add this line in your MariaDB configuration file (/etc/mysql/my.cnf):

[mysqld]
log_slow_verbosity      = query_plan,explain

Then, restart MariaDB. To test it, simply force the creation of a long query. Here is a SQL script with a loop. Adapt the first line if the default time is not enough:

-- Change this value to a higher one if you need more time
-- This will insert x lines number in your database
SET @MAX_INSERT = 100000;

-- Vars
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- Create database
DROP DATABASE IF EXISTS chapter2;
CREATE DATABASE chapter2;
USE chapter2;

-- Create table and add index
CREATE TABLE IF NOT EXISTS `s_explain` (
  `id` int(11) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE...

The show explain feature is only available in MariaDB 10. It allows you to get an explanation directly from a running process, for example, if you use the loop.sql script once again. At the time of insertion, execute a show processlist command:

MariaDB [chapter2]> SHOW PROCESSLIST\G;
[...]
*************************** 2. row ***************************
      Id: 81
    User: root
    Host: localhost
      db: chapter2
 Command: Query
    Time: 0
   State: query end
    Info: INSERT INTO `s_explain`(`id`, `ts`) VALUES (FLOOR(RAND() * @MAX_INSERT), NOW())
Progress: 0.000
2 rows in set (0.00 sec)

We can see here the 81 ID, which is the INSERT command in the loop.sql script. We're going to analyze it with the show explain command:

MariaDB [chapter2]> SHOW EXPLAIN FOR 81\G;
*************************** 1. row ***************************
           id: 1
  select_type: INSERT
        table: s_explain
         type: ALL
possible_keys: NULL
          key: NULL
     ...

Profiling permits you to benchmark information that indicates resource usages during a session. This is used when we want to get information on a specified query. Here are the types of information:

First of all, you need to know that profiling on a production server is not recommended because of the performance degradation it can cause.

To enable profiling, use the following command:

MariaDB [none]> SET PROFILING=1;

Perform all the query tasks you want to profile and then list them:

MariaDB [none]> SHOW PROFILES;
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.30798532 | select * from s_explain |
|        2 | 0.25341312 | select * from s_explain |
+----------+------------+-------------------------+

In the preceding command-line output, you can see that we've two query IDs. To get information...

In Version 5.5.3, you can use the performance_schema monitoring feature of MariaDB to monitor performance. It has been implemented as an engine (that's why you can see it on a show engines command) with a database that stores data performance.

To activate the performance schema, add this line to your my.cnf configuration file:

performance_schema=on

You can then check whether it has been correctly activated:

MariaDB [(none)]> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

You can now list the complete table list to see the available monitoring features:

MariaDB [(none)]> USE PERFORMANCE_SCHEMA;
MariaDB [performance_schema]> show tables;
+----------------------------------------------------+
| Tables_in_performance_schema                       |
+-----------------------------------------------...

Since MariaDB 5.2, a patch from Google, Percona, and other companies has been implemented, which permits you to view the user statistics, client statistics, index statistics (and usage), and table statistics.

You can activate it on the fly using the following command:

MariaDB [(none)]> SET GLOBAL userstat=1;

Alternatively, you can make it persistent in the MariaDB configuration (my.cnf) using the following code:

[mysqld]
userstat = 1

You now have access to the new FLUSH and SHOW commands:

MariaDB [(none)]> FLUSH TABLE_STATISTICS
MariaDB [(none)]> FLUSH INDEX_STATISTICS
MariaDB [(none)]> FLUSH USER_STATISTICS
MariaDB [(none)]> FLUSH CLIENT_STATISTICS
MariaDB [(none)]> SHOW CLIENT_STATISTICS
MariaDB [(none)]> SHOW USER_STATISTICS
MariaDB [(none)]> SHOW INDEX_STATISTICS
MariaDB [(none)]> SHOW TABLE_STATISTICS

Here is an example of what the user statistics look like:

MariaDB [(none)]> SHOW USER_STATISTICS\G;
*************************** 1. row *****...

Sysbench is a benchmarking tool that has several modes to bench:

To install it, run this command:

> aptitude install sysbench

The common test is to use the Online Transaction Processing (OLTP) scenario with small transactions to hit an optimized database. We will pass arguments to the command to simulate application threads (the --num-threads argument).

You can run this OLTP test with two kinds of scenarios:

The available version in Debian Wheezy is 0.4. A newer version exists with more interesting results such as a reporting interval every x sec. In addition, you can...

Percona Toolkits is a suite of tools for MySQL and MariaDB. They are very useful in many situations and well documented (the main website is http://www.percona.com/software/percona-toolkit). To install them, you can add the repository:

> aptitude install python-software-properties
> apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
> add-apt-repository 'deb http://repo.percona.com/apt wheezy main'

Then, configure APT-Pining to avoid the Percona repository overriding MariaDB's repository and conflict some packages. So, create this file at /etc/apt/preferences.d/00percona.pref and add the following content to it:

Package: *
Pin: release o=Percona Development Team
Pin-Priority: 100

You're now ready for the installation of the package:

> aptitude update
> aptitude install percona-toolkit

That's it! Several binaries starting with pt- are now available on your system.