Hack 98. Find and Stop Long-Running Queries

It is all too easy to set off a query that will run for several hours, consuming system resources and disturbing other users. Spot long-running queries and kill them off.

Long-running queries are surprisingly easy to produce. It is not always clear how long a query will take to run, and even correct queries may take a long time to execute. Incorrectly written queries, such as ones with JOIN conditions missing, may take a considerable time to solve. Oracle, SQL Server, MySQL, and PostgreSQL each support ways to detect long-running queries. They also offer ways to terminate running queries. The commands to manage queries have been implemented differently for each of these database systems.

In Oracle and SQL Server, you can monitor activity by querying a system view. This means that you can use SELECT statements to choose the columns and rows that you want to be shown.

12.3.1. Oracle

In Oracle, you can find a long-running database session (which could have been involved with many different transactions). The query to find long-running sessions uses the system view V$SESSION:

SELECT username,sid, serial#,
 TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS') "CURRENT",
 TO_CHAR(logon_time,'YYYY-MM-DD HH24:MI:SS') "LOGON",
 (sysdate - logon_time)*24*60 "MINS"
FROM V$SESSION
WHERE (sysdate - logon_time)*24*60 > 1 
AND username is not NULL;

USERNAME SID SERIAL# CURRENT LOGON MINS
----------------------------------------------------------------------------
GRUSSELL 246 52683 2006-06-16 14:38:22 2006-06-16 14:30:51 7.51
DBRW 251 49308 2006-06-16 14:38:22 2006-06-15 17:33:48 1264.56

You can subtract the logon_time from sysdate (the current time) to get the length of time each session has been running. This floating-point value is in units of days; so you can multiply by 24 to get this in hours and multiply again by 60 to get the number of minutes.

To kill a session use:

ALTER SYSTEM KILL SESSION 'sid,serial'

To kill off user DBRW's query use:

ALTER SYSTEM KILL SESSION '251,49308'

 

12.3.2. SQL Server

SQL Server maintains a list of all database processes in a table called sysprocesses. A sysprocess may be a running query. You can find all sysprocesses running in the server via a simple query:

USE master;
SELECT spid , nt_username,
 datediff(s,login_time,GETDATE( ))
 FROM sysprocesses;

Spid nt_username 
--------------------------
1 80000 
2 80000
200 andrew 60

Once the right spid is identified, kill the process:

EXEC ('kill 200');

 

12.3.3. MySQL

MySQL has some extensions to SQL that handle the tracking of processes:

mysql> SHOW PROCESSLIST;
+-------+-------+-----+---------+------+--------+-----------------------+
| Id | User | ... | Command | Time | State | Info |
+-------+-------+-----+---------+------+--------+-----------------------+
| 13122 | scott | | Query | 0 | NULL | SHOW PROCESSLIST |
| 13123 | scott | | Query | 10 | Send...| SELECT t.x+u.x FRO ...|
+-------+-------+-----+---------+------+--------+-----------------------+
2 rows in set (0.00 sec)

To terminate a process use the KILL command:

KILL 13123;

This will terminate the session as well as the query.

12.3.4. PostgreSQL

In PostgreSQL, you can query the system table pg_stat_activity:

scott=> SELECT datid, datname, usename, procpid,
scott->  backend_start FROM pg_stat_activity;
 datid | datname | usename | procpid | backend_start
-------+---------+---------+----------------------------------------
 16386 | scott | scott | 12347 | 2006-09-08 14:26:36.930312+01
 16388 | gisq | scott | 12248 | 2006-09-08 14:28:05.972587+01

To terminate the query being executed in a particular session you can send the INT signal to the procpid shown. You can do this as the root user from the operating system shell:

# kill -INT 12248

 

12.3.5. Hacking the Hack

You can prevent long-running queries from running in the first place. This stops the CPU hogs before they get started. Each database system has a different way of handling this.

12.3.5.1. Oracle

Oracle has a flexible approach to quotas, and can assign them to what it refers to as a profile. These, in turn, can be assigned to multiple users; so, for instance, you can have a "manager" profile, and if one manager needs an increase to his limit, you can change it in the profile and all managers will automatically get the new limit:

ALTER SYSTEM SET resource_limit = true;
CREATE PROFILE quick;
ALTER PROFILE quick LIMIT connect_time 1;
ALTER PROFILE quick LIMIT cpu_per_session 1000;
ALTER USER andrew PROFILE quick;
ALTER USER andrew quota 0 on SYSTEM quota 0 on SYSAUX

This limits connection time to 1 minute and 10 seconds of CPU time. Oracle has lots of different limits to choose from, including different CPU measures and idle times. You can set different quotas, including limits on index sizes and TEMP space usage.

12.3.5.2. MySQL

MySQL provides two mechanisms for limiting queries. It can limit the total number of rows that a query can output, and it can limit the number of table rows accessed during a query. You can set these globally or on a per-session basis:

SET SQL_SELECT_LIMIT=1000, SQL_MAX_JOIN_SIZE=1000000;

SQL_SELECT_LIMIT limits the number of rows that a SELECT statement can return. You can override it using the LIMIT clause in a query.

SQL_MAX_JOIN_SIZE is the limit on the number of rows that will be used in a JOIN. So, if you were joining three tables, X, Y, and Z, and they were 100 rows each, an unconstrained JOIN would use 100 * 100 * 100, or 1 million rows. With indexes and other optimizations, no query should realistically need the worst-case number of rows, and 1 million rows can actually be done quite quickly. As such, you could start at 1 million and see whether the things your users will want to do can be done within that limit.

You can configure the MySQL command prompt using the flag --safe-updates (also known as --i-am-a-dummy), which in turn executes the following:

SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=1000,SQL_MAX_JOIN_SIZE=1000000;

SQL_SAFE_UPDATES is a safety mechanism which prevents UPDATEs and DELETEs where no primary key is used in the WHERE clause. This flag may be useful for beginner users who may make CPU-expensive mistakes quite often.

12.3.5.3. SQL Server

SQL Server can use the optimizer to work out the likely time needed (in seconds) to execute a given query. You can instruct SQL Server to compare this prediction to a limit:

sp_configure 'SHOW ADVANCED OPTIONS', 1
reconfigure
GO
sp_configure 'QUERY GOVERNOR COST LIMIT', 1
GO
sp_configure 'SHOW ADVANCED OPTIONS', 0

If the optimizer predicts that the query will take more than the limit (in this case, 1 second) the query will not be executed. You can set this limit for every session, or globally for all connections. You need to have the right permissions to use this limit feature, which by default means you have to be in the sysadmin server role.

You can set it globally for all queries using:

sp_configure QUERY governor cost limit 1

The great thing about this is that it is predictive. It does not use the resource and then give up. It gives up before it even starts the query. Of course, if you are using loops in stored procedures, this is not going to work because it limits each query in the loop individually.

12.3.5.4. PostgreSQL

You can set a query timeout on your session using the STATEMENT_TIMEOUT parameter. To ensure that no query takes longer than 10 seconds you can use:

SET STATEMENT_TIMEOUT TO 10;