Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted July 23, 2012

Handling Slow Processes in MySQL

By Rob Gravelle

Viewing the process list is a key step in debugging many common server problems, including bottlenecks, poor performance, deadlocks, connection issues, and many others. For that reason, knowing how to bring up relevant information on running processes is crucially important. In today's article, we're going to look at some ways to use the processlist to glean insight into potential server issues.

SHOW PROCESSLIST versus INFORMATION_SCHEMA.PROCESSLIST

Before version 5.1, the only way to get information on server processes was to execute the SHOW PROCESSLIST command. If you have the PROCESS privilege, you can see all threads. Otherwise, you can only see your own threads (that is, threads associated with the MySQL account that you are using). Here is some sample output:

SHOW PROCESSLIST;
+ -- -- + -- -- -- -- -- + -- -- -- -- -- -- -- -- + -- -- -- + -- -- -- -- -+ -- -- -- + -- -- -- -+ -- -- -- -- -- -- -- -- -- -+
| Id | User |Host | db | Command | Time | State | Info |
+ -- -- + -- -- -- -- -- + -- -- -- -- -- -- -- -- + -- -- -- + -- -- -- -- -+ -- -- -- + -- -- -- -+ -- -- -- -- -- -- -- -- -- -+
| 1 | root | localhost:1715 | | Sleep | 73 | | |
| 2 | root | localhost:1716 | test | Query | 0 | | show processlist |
| 5 | sonar | localhost:1835 | test | Sleep | 2359 | | |
 + -- -- + -- -- -- -- -- + -- -- -- -- -- -- -- -- + -- -- -- + -- -- -- -- -+ -- -- -- + -- -- -- -+ -- -- -- -- -- -- -- -- -- -+
In version 5.1 and later, we can query the INFORMATION_SCHEMA database for process information:
SELECT * FROM information_schema.processlist
Both show the exact same information on all the currently running processes, but with one noteworthy difference: the INFORMATION_SCHEMA processlist table can be joined to other tables and filtered through a WHERE clause to get more detailed and purpose-specific information.

The PROCESSLIST Fields

  • Id: a numeric connection identifier.
  • User: the MySQL user who issued the command.
  • Host: the hostname and port of the client issuing the command. The hostname will usually be "localhost" unless you are executing commands on a remote server.
  • db: the name of the database on which the command is being issued.
  • Command: These include pretty much all MySQL commands that you could issue. The two that will appear the most often are "Query" (which includes the "SHOW PROCESSLIST" command) and "Sleep," which means that the database connection is waiting for a new statement. There are many more commands, but I'm not going to list them all here. If you're interested, take a look at the MySQL documentation for the full command list.
  • Time: the amount of time elapsed in seconds from the command's start time to the time the processlist is viewed.
  • State: usually a very quick operation that a thread is currently executing. Like Commands, it could be one of hundreds of different values. One example is "After create", which occurs when a thread creates a table, at the end of the function that creates the table. Another is "Creating sort index", whereby the thread is processing a SELECT query that uses an internal temporary table. Once again, I'd like to direct you to the MySQL docs for the full list of thread states.
  • Info: shows the statement being run. If you issue a SHOW PROCESSLIST command without the FULL keyword, only the first 100 characters of each statement are shown.

Formatting Processlist Results

The processlist can conceivably be quite large, if your database is a large and/or highly active one. Rather than scroll through hundreds of rows, you may want to filter the results in some way, by running your queries against the INFORMATION_SCHEMA.PROCESSLIST table with a WHERE clause.

You may only want to see active threads (as opposed to dormant ones). That's easily done by excluding processes for the Sleep command:

SELECT * 
 FROM INFORMATION_SCHEMA.PROCESSLIST 
 WHERE COMMAND != 'Sleep';

From there you can sort active connections by time running:

SELECT * 
 FROM INFORMATION_SCHEMA.PROCESSLIST 
 WHERE COMMAND != 'Sleep' 
 ORDER BY TIME DESC;

Knowing how to interpret the PROCESSLIST fields will go a long way towards identifying and fixing possible issues.

Killing a Process

In the Understanding the MySQL Information Schema Database article, I described how to query the processlist to find long running queries:

SELECT * FROM information_schema.processlist
WHERE COMMAND = 'Query'
AND time > 60 * 10;

Remember that the Time field is in seconds, so we can fetch all the queries that are running for over 10 minutes by comparing it to the expression of sixty seconds times ten.

Now, suppose that the above query identified a query that was problematic and needed to be terminated. We can do that using the KILL command with the Process Id:

mysql> KILL 3; 
 Query OK, 0 rows affected (0.04 sec)
 

We can also kill the query thread while leaving the connection active by issuing the KILL QUERY command instead, followed by the thread Id.

If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated.

Killing Multiple Processes

Sometimes there may be many troublesome processes that meet the criteria for termination. In that case, you have to take a somewhat different approach.

The following query appends multiple KILL commands to a file that you can execute afterwards. Note that you have to be a lot more selective because you wouldn't want to terminate a slow-but-necessary process:

mysql> SELECT CONCAT('KILL ',ID,';')
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE USER = 'app'
AND COMMAND = 'Query'
AND TIME > 30
INTO OUTFILE '/tmp/slow_processes.txt';
Query OK, 12 rows affected (0.05 sec)
Here is the code to execute our slow_processes.txt file:
mysql> \. /tmp/slow_processes.txt
Query OK, 0 rows affected (0.00 sec)

Conclusion

Finding and terminating slow or otherwise troublesome processes can be done manually or automated using MySQL Events, Scheduled Jobs, scripts, or any combination thereof. Having said that, I would caution you to gradually transition into automating any functionality for terminating processes as that's not something that you want to be too cavalier about!

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM