Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
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? –  syrion Jan 13 '12 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 –  RolandoMySQLDBA Jan 13 '12 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 '12 at 2:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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