Introduction
- wait_timeout
- interactive_timeout
- net_read_timeout
- net_write_timeout
interactive_timeout
According to the MySQL manual, Interactive_timeout is only used for connections from interactive clients like the command line MySQL clients. The technical explanation is: "clients utilizing the "CLIENT_INTERACTIVE option to
mysql_real_connect()." Check the specific documentation to your favorite client to see whether it fits this description. If your client fits this behavior,
wait_timeout is set to the value in
interactive_timeout. The only benefit to this parameter is flexibility. If your command line or other interactive clients have a vastly different requirement than your application, feel free to set this differently than wait_timeout.
wait_timeout
Wait timeout is simply there to protect you in the common case of clients sitting there doing nothing but absorbing a connection. You want to set this to the lowest acceptable number of seconds in order to protect your server against an application malfunction or some other event that may cause too many connections to the database to be opened, crowding out other clients. If a client is doing nothing for wait_timeout seconds, the MySQL server will terminate the connection. The proper setting for this variable depends on the particular environment. An environment I worked in for 3+ years set this to 120 seconds, because jboss connection pooling (and associated parameters) took care of making sure the mission critical application in the environment had available connections. A common misconception and common misuse is to try to adjust wait_timeout for a situation in which a query has been interrupted. Wait timeout only applies to idle sessions. If the connection is in any other state besides idle, wait_timeout does not apply. This is why utilities like pt-kill exist. It does not help that an idle connection being interrupted by KILL produces the same error message. Example using my perl script and the MySQL processlist: jscott@js-trusty1:~$ ./test_timeouts.pl -K Doing Killable Wait Timeout Test ATTN: You have 20 seconds to kill the MySQL thread!! in another shell
jscott@js-trusty1:~$ mysql -e "show processlist" +----+------+-------------------+--------------+---------+-------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-------------------+--------------+---------+-------+-------+------------------+ | 36 | js | 172.19.73.2:58892 | NULL | Sleep | 28514 | | NULL | | 79 | root | localhost:43350 | timeout_test | Sleep | 4 | | NULL | | 80 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+------+-------------------+--------------+---------+-------+-------+------------------+ jscott@js-trusty1:~$ mysql -e "kill 79"; back to the perl script output.
DBD::mysql::st execute failed: MySQL server has gone away at ./test_timeouts.pl line 134. ------------- Err: 2006 ErrStr: MySQL server has gone away State: HY000 Note that we got MySQL error code 2006 "MySQL server has gone away". Now we'll allow wait_timeout to trigger instead of killing a process.
jscott@js-trusty1:~$ ./test_timeouts.pl -W Doing Wait Timeout Test DBD::mysql::st execute failed: MySQL server has gone away at ./test_timeouts.pl line 151. ------------- Err: 2006 ErrStr: MySQL server has gone away State: HY000 The fact that a killed idle process and a process receiving wait_timeout get the same error message and code (2006 "MySQL server has gone away") causes much confusion. Rest assured,
wait_timeout only kills idle connections, even if the error messages are the same. If there is a running query, you will typically get error code 2013: "Lost connection to MySQL server during query". The most common reason a query is killed, in my experience, is a backup. Many backup utilities for MySQL have the default behavior of killing long-running queries.
net_read_timeout
net_read_timeout is the number of seconds mysql will wait between bytes as you are sending it data. As mentioned above, I tried very hard to synthetically cause a net_read_timeout with a perl script and was unsuccessful. The best way to explain net_read_timeout is to use the example of an ETL (Extract, Transform, Load) job. At the beginning of an ETL job, you select data from one data source, then transform and load it into another data source, such as a MySQL data warehouse. Tools like "Pentaho Data Integration" (cited below) provide a GUI view of ETL. [caption id="attachment_85957" align="alignleft" width="500"]
Pentaho ETL Process[/caption] If your ETL job is complex, and you use something similar to
Pentaho Kettle, you have very little visibility into what is happening behind the scenes. If the steps in between the initial query in an ETL job and an output step
consume more than net_read_timeout seconds, then an error may occur due to incomplete statements being sent to the "output" steps. In
mid-transaction, if the MySQL server fails to receive data within the timeout interval, the connection will be terminated. Another way net_read_timeout can occur: Normally all database connections used in a Pentaho ETL transformation are opened at the beginning of the transformation. If you have "use result streaming cursor" turned off on the database connection used for "table input", the result set may take longer than "net_read_timeout" seconds to receive, causing an error on the database connection being used for the output steps. Have you ever executed a simple query (with a large result set) on the MySQL command line and wondered why there is a pause of several seconds or even minutes prior to beginning to receive the results? Your client actually has begun receiving the results, but they simply have not been presented to you. The reason for this is, it's generally better for a MySQL server to be "finished" with a query as soon as possible; therefore, the default behavior of the MySQL client libraries on a read-only query is to receive all the results first, before passing the results onto the program. The default behavior is called "
mysql_store_result".
The MySQL reference manual does a good job of explaining this in detail. As the manual states, you should not change the default behavior unless you are doing a minimal amount of processing on each result row. Homemade ETL programs in other languages can have the same issue. Take care to use auto_reconnect features and/or streaming cursors / "mysql_use_result", when appropriate. ETLs and scripts are good examples of clients which can be modified with SET commands. "SET SESSION net_read_timeout=<longer value>;" should be considered for jobs outside of your main application. Lastly, net_read_timeout is also often caused by
bad network connections. This is the default explanation offered in many blog posts on this topic. The Error returned to the client will be (2013) "Lost connection to MySQL server during query".
