Enable slow query logging#
You can identify inefficient or time-consuming queries by enabling slow query log in your MySQL service. In the following sections you can find how can you enable slow queries in your Aiven for MySQL service.
Prerequisites#
An Aiven account with an Aiven for MySQL service running.
Configure slow queries in the Aiven Console#
Follow these steps to enable your slow queries in your Aiven for MySQL service via the Aiven Console:
On the Overview page, scroll down to the Advanced configuration section and click Add configuration option.
Click Add configuration option and choose the
slow_query_log
.Enable
slow_query_log
by toggling it to On. By default,slow_query_log
is disabled.Click Add configuration option and choose the
long_query_time
.Set
long_query_time
according to your specific need. By default, the value is 10 seconds.Click Save advanced configuration
Your Aiven for MySQL can now log slow queries. If you want to simulate slow queries to check this feature, check the next section for that.
Simulate slow queries#
Connect to your Aiven for MySQL using your favorite tool. Make sure to have slow_query_log
enabled and set 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 this as 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;
Your output should look similar to this:
+-----------------+-----------------+
| slow_query | query_time |
+-----------------+-----------------+
| select sleep(3) | 00:00:03.000450 |
+-----------------+-----------------+
1 row in set, 1 warning (0.03 sec)
Warning
Disabling the slow_query_logging
setting will truncate mysql.slow_log table
. Make sure to back up the data from mysql.slow_log
table in case you need it for further analysis.