Tracking long running processes in MySQL is not difficult. A process can be:
- SQL query
There are different ways of tracking both type of processes.. First we would look at some of the available methods to identify long running queries:
- slow query log
- performance schema
- packet inspection
Slow query log - This is the common method of identifying slow queries. You would need to check if its already enabled:
In this example, it is not. To enable slow query log, issue following commands:
I took the opportunity to reduce long_query_time value from 10 seconds to just 1 second as well. The idea is to capture all queries that take at least 1 second. To make those changes persistent you would have to modify MySQL configuration file (e.g. /etc/my.cnf or my.ini)
Next you would like to summarize slow query log, you have two famous methods to choose from:
mysqldumpslow - classic method that have been used since mysql is born, on-line docs explain it very well, click here
pt-query-digest - This method has been widely adopted by the community, click here to learn more about it.
If you are still using older release of MySQL e.g. 5.0 or earlier. It is not possible to enable slow query logging without rebooting MySQLd process. If you cannot restart MySQL process, you can still track slow queries using pt-kill utility:
Remember NOT to specify --kill option, as it would start killing your queries
Performance schema - It was introduced in version 5.5, by default it is enabled in 5.6+ release. It provides a way to inspect internal server events (such as IO activity, locks, query profiling etc). It can also be used to track timing on long running processes. Below are few examples of the commands that can be used to track long running queries, queries that use temporary table and/or the ones that perform full-table scan:
Packet inspection - this can be achieved using tcpdump utility, not covered in this article. This is due to its known limitations:
- you can only observe queries sent over the network.
- you don’t see queries sent through Unix sockets or via replication.
- you also can’t see queries run from stored procedures.
- security concern, this method requires root access.
In the next article, I would blog about how to identify long running transactions...