Enabling the MySQL Slow Query Log
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.
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`;
