MySQL Connection Timeout Parameters
In this blog post we have explained about “MySQL Connection Timeout Parameters” , If your application is not efficiently handling MySQL connection management we strongly recommend to use MySQL Connection Timeout Parameters / System Variables ( note: please do not tune Connection Timeout system variables without clearly understanding how they work, The wrong assumptions can bring down you MySQL infrastructure seriously affecting your businesses negatively) to avoid situations where you can see so many connections to MySQL doing nothing. It’s worth spending time researching on MySQL Timeout System Variables for troubleshooting performance especially when application is not gracefully closing all the open connections. The following MySQL Connection Timeout Parameters / System Variables are discussed in this post:
- connect_timeout: Total number of seconds mysqld server waits for a connect packet before responding to a bad handshake, The default value is 10 seconds. You can increase connect_timeout system variable if the client is frequently facing Lost connection to MySQL server at ‘XXX’, system error: errno.
- innodb_flush_log_at_timeout: If you want to control (reduce) frequency flushing to tune performance of binary log group commit, Then please increase innodb_flush_log_at_timeout, The unit of this parameter is recorded in seconds and default setting is once per second
- innodb_lock_wait_timeout: When a transaction A tries to access a row that is locked by another InnoDB transaction, Transaction A waits at most this many seconds ( default is 50 seconds) for write access to the row before issuing the following error:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
innodb_lock_wait_timeout is applicable only to InnoDB row locks. Because, MySQL table lock does not happen inside InnoDB and vice versa, his timeout does not apply to waits for table locks. By default innodb_deadlock_detect is enabled because InnoDB detects deadlocks immediately and rolls back the deadlocked transactions so lock wait timeout value does not apply to deadlocks . But, If innodb_deadlock_detect is disabled, then InnoDB relies on innodb_lock_wait_timeout for transaction rollback when a deadlock occurs.
- innodb_rollback_on_timeout: By default, InnoDB rolls back only the last statement in the case of transaction timeout. If innodb_rollback_on_timeout is enabled, a transaction timeout causes InnoDB to abort and roll back the entire transaction. We strongly recommend you to enable the system variable innodb_rollback_on_timeout if you are seriously concerned about transaction durability, This is applicable for most of our customers in Fintech. domain.
- lock_wait_timeout: The lock_wait_timeout system variable handles the timeout in seconds for attempts to acquire metadata locks, i.e. timeout value applies separately for each metadata lock attempt. This includes all DML and DDL operations on tables, views, stored procedures and stored functions along with LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements. This timeout is not applicable for implicit access to system tables in mysql database, like GRANT or REVOKE statements or table logging statements. The lock_wait_timeout variable is applicable only for tables accessed directly with SELECT or UPDATE. Even in the case of MySQL backup, The lock_wait_timeout defines the amount of time that a LOCK INSTANCE FOR BACKUP statement waits for a lock before giving up.
- wait_timeout: If you notice most of the clients connected to MySQL are doing nothing but just stay connected, We strongly recommend to reduce wait_timeout variable for avoiding situations causing bottleneck for other client connection requests. This setting is applicable only for idle sessions and your running queries will never be interrupted. The wait_timeout is not applicable to any other state besides idle. So when wait_timeout is set optimally, It does more good than bad in situations where client is not closing connections gracefully.
Conclusion
In some cases, we have to manage connection handling in MySQL by setting timeout values for avoiding performance bottlenecks which will negatively impact your performance. But, setting timeout variables too aggressively is also not good so please do your research thoroughly before implementing same on MySQL production systems.