Scripts / Enabling the MySQL Slow Query Log shell
Description

When troubleshooting API performance issues, it's important to consider all contributors to the API request and response cycle. For instance, the issue may be related to poor code execution performance, but it's also worth considering the data source. If the API data source is a relational database, you should confirm that the respective tables have been correctly indexed, and that the database itself has been allocated appropriate system resources (CPU, RAM, etc).

MySQL users can quickly determine whether queries are performant by enabling the slow query log. When enabled, the slow query log will log any query which takes longer than a predetermined number of seconds to execute.

To enable the MySQL slow query log, you'll need root-level access to the database server.

Code
Step 1. Log into the MySQL database server
$ mysql -u root -p
Enter password:

Step 2. Enable the slow query log
mysql> set global slow_query_log = 'ON';

Step 3. Define the execution time limit (in seconds)
mysql>  set global long_query_time = 2;

Step 4. Define the log location
mysql> set global slow_query_log_file = '/var/log/mysql/slow_query_log';

Step 5. Monitor the log
$ tail -f /var/log/mysql/slow_query_log
/usr/sbin/mysqld, Version: 5.7.31-0ubuntu0.16.04.1 ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2020-09-14T17:49:56.033857Z
# [email protected]: dreamfactory[dreamfactory] @ ec2-12.34.56.78.compute-1.amazonaws.com [12.34.56.78]  Id:  7060
# Query_time: 0.304755  Lock_time: 0.000046 Rows_sent: 1  Rows_examined: 300030
use dreamfactory;
SET timestamp=1600105796;
select count(1) as aggregate from `jason`.`employees`;

Need API Advice?

Our team has advised thousands of companies around the world on API projects. Go to market faster by talking to the API experts.

jeanie

Ready to get started?