Connection timeout parameters in MySQL
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"]