Enable slow query logging
You can identify inefficient or time-consuming queries by enabling slow query log in your Aiven for MySQL® service.
Since the output of the slow query log is written to the
mysql.slow_log
table on a particular server (of the service or its
replica), the slow query logging is not supported on read-only replicas,
which don't allow any writes.
Prerequisites
You need an Aiven organization with an Aiven for MySQL service running.
Configure slow queries in Aiven Console
Enable your slow queries in your Aiven for MySQL service via Aiven Console:
- Log in to Aiven Console.
- In the Services page, select your Aiven for MySQL service.
- In the Service settings page of your service, scroll down to the Advanced configuration section and select Configure.
- In the Advanced configuration window
- Select Add configuration options. From the unfolded list,
choose
mysql.slow_query_log
. Enablemysql.slow_query_log
by toggling it toOn
. By default,mysql.slow_query_log
is disabled. - Select Add configuration options. From the unfolded list,
choose
mysql.long_query_time
. Setmysql.long_query_time
according to your specific need. - Select Save configuration.
- Select Add configuration options. From the unfolded list,
choose
Your Aiven for MySQL service can now log slow queries. To simulate slow queries to check this feature, check the next section.
Simulate slow queries
Connect to your Aiven for MySQL using your favorite tool. Make sure you
have mysql.slow_query_log
enabled and set mysql.long_query_time
to
2
seconds. Now, you can run the following query to simulate a slow
query of 3 seconds.
select sleep(3);
You should see the following output:
+----------+
| sleep(3) |
+----------+
| 0 |
+----------+
1 row in set (3.03 sec)
Now, you can check the logs of your slow query:
select convert(sql_text using utf8) as slow_query, query_time from mysql.slow_log;
You can expect to receive an output similar to the following:
+-----------------+-----------------+
| slow_query | query_time |
+-----------------+-----------------+
| select sleep(3) | 00:00:03.000450 |
+-----------------+-----------------+
1 row in set, 1 warning (0.03 sec)
Disabling the mysql.slow_query_log
setting truncates the
mysql.slow_query_log
table. Make sure to back up the data from the
mysql.slow_query_log
table in case you need it for further analysis.