11

We experienced an issue in which a read-only query, run through MySQL workbench, timed out from a user's UI perspective and remained running on the server (and apparently consuming more and more resources) until we had an outage.

Questions

  • Is there a standard way to deal with this sort of issue in MySQL?
  • Is there a fundamental cause that we need to avoid?

2 Answers 2

12

You need to look at what default values are in place for timeouts:

mysql> show variables like '%timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| connect_timeout            | 10    |
| delayed_insert_timeout     | 300   |
| innodb_lock_wait_timeout   | 50    |
| innodb_rollback_on_timeout | OFF   |
| interactive_timeout        | 60    |
| net_read_timeout           | 30    |
| net_write_timeout          | 60    |
| slave_net_timeout          | 3600  |
| table_lock_wait_timeout    | 50    |
| wait_timeout               | 60    |
+----------------------------+-------+
10 rows in set (0.00 sec)

Usually, I watch for several timeout variables. This is very imperative if you use MySQL remotely from MySQL Workbench, mysql client, or PHP app on an app server contacting MySQL on a DB Server.

Here is what the MySQL Documentation says one these settings:

  • wait_timeout (Default 28800 [8 hours]) : The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory. On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.
  • interactive_timeout (Default 28800 [8 hours]) : The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.
  • net_read_timeout (Default 30) : The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.
  • net_write_timeout (Default 60) : The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.

Please make sure these timeouts are set high enough to accommodate queries that may run for a very long time, which may include:

  • Mass UPDATEs
  • Mass DELETEs
  • ENABLE KEYS on a Large MyISAM

To deal with queries that keep running after you lose touch with it, you have to run KILL against the process ID of the long running query. Even with the KILL command, you will have to wait for any query that is in the middle of disk-intensive steps or have internal mutexes in progress.

3
  • Is there a standard, robust way to run KILL against processes that have been running for a long time, or is it usually done via a bash script/cron job?
    – asthasr
    Jan 13, 2012 at 0:22
  • I actually wrote a post back in May 2011 about how to launch a script using the information_schema to kill a bunch of DB Connections : dba.stackexchange.com/a/2637/877 Jan 13, 2012 at 3:00
  • "Please make sure these timeouts are set high enough to accommodate queries that may run for a very long time, which may include: Mass UPDATEs..." example: php+mysql i have to select every record from a table column, so fetch rows and then do something...then update another record with a new value. Put the case that this script requires about 5+ minutes. One SELECT at the beginning and one UPDATE to the end, fetch rows and do...in the middle. Can you explain what has to do the wait_timeout with this situation? Really it's not clear to me... what's a safe value for wait_timeout to free resource
    – user14967
    Nov 2, 2012 at 2:45
0

When timeout happens in UI there is a chance that query is still running in the DB and consuming resources. General approach to deal this is find the mysql connection id of the timed out UI query from the processlist and kill the query/connection. Suppose if we are using a "xx_ui_user" for running the queries then find the list of connection id whose time > "app time out time" and kill them.

select concat("kill ",id,";") 
from information_schema.processlist 
where user = "xx_ui_user" 
and time > "app time out time in seconds";

The above statement will generate the required kill commands.

If there is an option in the application when UI times out, find the current connection_id using SELECT CONNECTION_ID(); and send a kill command to the DB with connection_id will automatically solve the issue.

Not sure if this can be achieved from the application.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.