Help MySQL Slow Query Logs
The MySQL database server can log queries that take longer to execute than the configured threshold in the MySQL slow query logs, meaning these logs help troubleshoot issues related to slow applications easily. In general, one second is considered the threshold for MySQL database queries, and queries that take more than one second for execution are registered as slow.
Site24x7's comprehensive log management solution offers support for monitoring MySQL slow query logs by default. It categorizes the logs based on fields including the host, query time, lock time, rows sent, and rows examined so you can troubleshoot at a glance using these logs. You can also view the top 10 slow queries based on different parameters like query time, lock time, rows examined, and rows returned on a dashboard.
Log in to the machine in which your MySQL database is installed. Open the Terminal from your Linux machine or the Command Prompt from your Windows machine, and execute the following commands.
For MySQL version 5.1.6 and above:
For MySQL versions below 5.1.6:
Edit the /etc/my.cnf file, and add the following lines to the [mysqld] section.
You can edit the file directly from the Terminal using the vi command.
This is the default pattern defined by Site24x7 for parsing MySQL slow query logs based on the sample mentioned below.
# User@Host: test[test] @ [192.168.1.2]<NewLine># Query_time: 31.896695 Lock_time: 0.000065 Rows_sent: 1023 Rows_examined: 196182<NewLine>use db100db;<NewLine>SET timestamp=1487758318;<NewLine>select USER_ID, EMAIL_ID from USER_ACCOUNT where USER_ID IN ( select USER_ID from USER_MAPPER where ID IN ( select RESOURCEID from appdetails where environment regexp '(.*)Microsoft Windows NT 5.2|Microsoft Windows NT 5.1|Microsoft Windows NT 5.0|Microsoft Windows NT 6.0(.*)'));
# User@Host: test[test] @ [192.168.1.2]<NewLine># Query_time: 31.896695 Lock_time: 0.000065 Rows_sent: 1000 Rows_examined: 196182<NewLine>use db100db;<NewLine>SET timestamp=1487758318;<NewLine>select USER_ID, EMAIL_ID from USER_ACCOUNT where USER_ID IN ( select USER_ID from USER_MAPPER where ID IN ( select RESOURCEID from appdetails where environment regexp '(.*)Microsoft Windows NT 5.2|Microsoft Windows NT 5.1|Microsoft Windows NT 5.0|Microsoft Windows NT 6.0(.*)'));
# User@Host: test[test] @ [192.168.1.2]<NewLine># Query_time: 31.896695 Lock_time: 0.000065 Rows_sent: 560 Rows_examined: 196182<NewLine>use db100db;<NewLine>SET timestamp=1487758318;<NewLine>select USER_ID, EMAIL_ID from USER_ACCOUNT where USER_ID IN ( select USER_ID from USER_MAPPER where ID IN ( select RESOURCEID from appdetails where environment regexp '(.*)Microsoft Windows NT 5.2|Microsoft Windows NT 5.1|Microsoft Windows NT 5.0|Microsoft Windows NT 6.0(.*)'));
The sample log above can be separated into the following fields, each of which will take its respective value from here and will then be uploaded to Site24x7.
Field name | Field value |
---|---|
User | test[test] |
Host | 192.168.1.2 |
Query time | 31.896695 |
Lock time | 0.000065 |
Rows sent | 1023 |
Rows examined | 196182 |
Database used | 100db |
Timestamp | 1487758318 |
Query | select USER_ID, EMAIL_ID from USER_ACCOUNT where USER_ID IN ( select USER_ID from USER_MAPPER where ID IN ( select RESOURCEID from appdetails where environment regexp '(.*)Microsoft Windows NT 5.2 |Microsoft Windows NT 5.1|Microsoft Windows NT 5.0|Microsoft Windows NT 6.0(.*)')) |
Help MySQL Slow Query Logs