Enabling Performance Monitoring for MariaDB and MySQL

Enabling performance monitoring for MariaDB and MySQL works the same way.

Performance monitoring for databases is beneficial when you have records in the hundreds of thousands using complex joins. It also helps when you want to do essential performance monitoring overall.

In the past, you could use general_log, log_slow_queries and SHOW PROFILES. Since MySQL 5.5 you have access to much more detail through performance_schema.

To see if you already have performance monitoring enabled check your performance_schema variables using your database console. If it is set to OFF, you need to enable it.

I’m using MariaDB as my database.

MariaDB [(none)]> SHOW VARIABLES LIKE 'perf%';
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema                                     | OFF   |
| performance_schema_accounts_size                       | 10    |
| .......                                                | ...   |
| performance_schema_users_size                          | 5     |
+--------------------------------------------------------+-------+

When you enable performance monitoring do so only on your development environment. You will not see a significant speed reduction but on production, you can.

Enabling Performance Monitoring

After you install MariaDB using Homebrew create a hidden my.cnf file in your home directory. For MySQL, you may need to locate an already existing my.cnf.

> cd ~
> touch .my.cnf

Open your favorite editor and edit the file. Add performance_schema to my.cnf, set performance_schema_events_statements_history_long_size to 10000, be sure you have [mysqld] set at the top, and save the file.

[mysqld]
performance_schema
performance_schema_events_statements_history_long_size=10000

You can also set other configurations in my.cnf file if you like. Alternatively, you can set server system variables in the command line or the MySQL client.

Restart MariaDB or MySQL server.

> mysql.server restart

Enter the database console again and see if performance_scheme is enabled.

MariaDB [(none)]> SHOW VARIABLES LIKE 'perf%';
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema                                     | ON    |
| performance_schema_accounts_size                       | 10    |
| .......                                                | ...   |
| performance_schema_users_size                          | 5     |
+--------------------------------------------------------+-------+

events_statements_history_long

Now you can start enabling the features you want to use. I personal use the events_statements_history_long table introduced in MySQL 5.6.

events_statements_history_long will give you access to a detailed report on the last 10,000 queries run.

To enable events_statements_history_long monitoring open the MariaDB console and set your setup_consumers to YES. You must do this each time you start MariaDB, even after OS X launch.

MariaDB [(none)]> UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_history_long';

From here you can run SELECT queries on the performance_schema.events_statements_history_long table to review and profile your results.

When you make changes to your my.cnf file and restart your database, you will need to re-enable setup_customers.

You can review all the setup_customers using SELECT.

MariaDB [(none)]> SELECT * FROM performance_schema.setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | NO      |
| events_stages_history          | NO      |
| events_stages_history_long     | NO      |
| events_statements_current      | YES     |
| events_statements_history      | NO      |
| events_statements_history_long | YES     |
| events_waits_current           | NO      |
| events_waits_history           | NO      |
| events_waits_history_long      | NO      |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |
+--------------------------------+---------+

MySQL also has a quick start guide for the performance schema.