Chapter 8. Performance and Usage Statistics

In this chapter, we will cover the following recipes:

• Installing the Audit Plugin

• Using the Audit Plugin

• Using engine-independent table statistics

• Using extended statistics

• Enabling the performance schema

• Using the performance schema

Introduction

There are several ways of tracking and measuring our usage of MariaDB. Some, such as the MariaDB Audit Plugin, come from third parties. Others, such as the performance schema, are built in. All of this helps us know what is happening on our server so that we can track our current usage better, analyze long term performance trends, and plan for our future needs.

The recipes in this chapter introduce several auditing and tracking features that we can enable in MariaDB.

Installing the Audit Plugin

There are many third-party tools that can enhance our MariaDB server. The Audit Plugin from SkySQL is one of them. This plugin is used by organizations to comply with government regulations that require the tracking and auditing of access to sensitive data.

Getting ready

Locate the plugin directory on your local machine. This can be done by connecting to MariaDB using the mysql command-line client and then running the following command:

SHOW GLOBAL VARIABLES LIKE 'plugin_dir';

The directory displayed is our local MariaDB plugin directory.

How to do it...

1. Download the appropriate version of the MariaDB Audit Plugin (either Windows or Linux) from the SkySQL downloads page available at http://www.skysql.com/downloads/.

2. On Windows, navigate to the location where we downloaded the plugin ZIP file and unpack it by right-clicking on the file in Windows Explorer and choosing Extract all....

3. On Linux, navigate to the location where we downloaded the plugin TAR file and unpack...

Using the Audit Plugin

Installing the MariaDB Audit Plugin, as described in the previous recipe, doesn't do a whole lot for us. In order to get the most out of this plugin, we need to configure it. That is what this recipe is about.

Getting ready

Complete the Installing the Audit Plugin recipe described earlier in this chapter to install the Audit Plugin, prior to using the Audit Plugin.

How to do it...

1. Connect to MariaDB using the mysql command-line client with a user that has the SUPER privilege.

2. Show the Audit Plugin variables with the following command:

   SHOW GLOBAL VARIABLES LIKE 'server_audit%';
   

   The preceding command displays an output similar to the following screenshot:

   

3. Turn off the audit logging with the following command:

   SET GLOBAL server_audit_logging=OFF;
   

4. Turn on the audit logging with the following command:

   SET GLOBAL server_audit_logging=ON;
   

5. Force the audit logfile to rotate immediately with the following command:

   SET GLOBAL server_audit_file_rotate_now=ON;
   

6. Show the location of the...

Using engine-independent table statistics

MariaDB includes a facility to gather statistics on all tables, no matter what storage engine those tables use. The MariaDB optimizer can use these statistics to better calculate the optimum query plans.

How to do it...

1. Connect to MariaDB using the mysql command-line client with a user that has the SUPER privilege.

2. Run the following command:

   SET GLOBAL use_stat_tables=complementary;
   

3. Force an update of the table statistics for a table with the following command (change table_name to the name of an existing table):

   ANALYZE TABLE table_name;
   

4. View the collected table, index, and column statistics with the following commands:

   SELECT * FROM mysql.table_stats;
   SELECT * FROM mysql.index_stats;
   SELECT * FROM mysql.column_stats;
   

How it works...

How MariaDB uses the engine-independent table statistics is controlled by the use_stat_tables variable. There are three valid values: never means that MariaDB will not use the statistics, complementary means that MariaDB...

Using extended statistics

MariaDB includes a powerful feature for collecting extended user statistics. These statistics can be used to better understand how our server is behaving and to locate and identify the sources of our server's load.

How to do it...

1. Connect to MariaDB using the mysql command-line client with a user that has the SUPER privilege.

2. Enable statistics collection with the following command:

   SET GLOBAL userstat=1;
   

3. Run the following commands to show the statistics collected since collection was enabled:

   SHOW CLIENT_STATISTICS;
   SHOW INDEX_STATISTICS;
   SHOW TABLE_STATISTICS;
   SHOW USER_STATISTICS;
   

4. Run the following commands to flush the statistics by resetting the statistics counters to zero:

   FLUSH CLIENT_STATISTICS;
   FLUSH INDEX_STATISTICS;
   FLUSH TABLE_STATISTICS;
   FLUSH USER_STATISTICS;
   

How it works...

Extended statistics collection is off by default so that it does not cause unnecessary or unwanted load on the server. Enabling it is easy though and if desired, statistics gathering...

Enabling the performance schema

The performance schema is a tool that we can use to monitor our server performance. It is disabled by default, but it can easily be enabled.

How to do it...

1. Open our my.cnf or my.ini file and add the following code to the [mysqld] section:

   performance_schema

2. Restart MariaDB.

3. Connect to MariaDB using the mysql command-line client.

4. Run the SHOW ENGINES; command and verify that PERFORMANCE_SCHEMA is listed. The performance schema entry will look similar to the following screenshot:

   

5. Switch to the performance_schema database using the following command:

   USE performance_schema;
   

6. Show the performance schema tables using the following command:

   SHOW TABLES;
   

How it works...

The performance schema is implemented as a storage engine. This is why it shows up alongside other storage engines when we use the SHOW ENGINES; command. However, it is not a storage engine for storing data. The purpose of the performance schema is to help us monitor server performance and when it is enabled...

Using the performance schema

Using the performance schema is similar to querying a table or set of tables.

Getting ready

Enable the performance schema as described in the Enabling the performance schema recipe earlier in this chapter.

How to do it...

1. Connect to MariaDB using the mysql command-line client. List how many current connections the users have and how many connections they had in total (we might want to log in and out a few times with example users to populate this table):

   SELECT * FROM performance_schema.users;
   

   The preceding commands display an output similar to the following screenshot:

   

2. Look up for the detailed information on all of the currently running user connection threads:

   SELECT * FROM performance_schema.threads 
       WHERE type="foreground"\G 
   

   The preceding commands display an output similar to the following screenshot:

   

3. Add the following code to the [mysqld] section of our my.cnf or my.ini file and then restart MariaDB to disable the collection of user connection statistics...