Configuring MySQL CONNECTION TIMEOUT PARAMETERS 

---

Introduction

If the application is not handling connection management optimally and not closing connections it is no longer using, MySQL Server will be closing those connections due to idle-timeout ( Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000] [2002] Connection timed out), Most often connection timeout error happens because of an application bug, a network timeout issue ( on a router, firewall, switch etc. ) or may be due to MySQL restarting. If application is not gracefully closing connection it is no longer using, a low wait_timeout value can help to avoid connection errors due to hitting limit of max_connections  ( Note: The ” sleeping / idle connections” which are not participating in a transaction are not reused ). In MySQL ” *_timeout ” system variables exist to troubleshoot proactively expensive connection management issues, There is absolutely no benefit of several clients connected to your MySQL server doing nothing.  This blog post is about MySQL CONNECTION TIMEOUT variables ( connect_timeout, interactive_timeout, lock_Wait_timeout, wait_timeout, net_read_timeout, net_retry_count and net_write_timeout ) and how to configure those for Database performance, scalability and reliability.

Configuring MySQL ” connection timeout ” System Variables for performance, scalability and reliability

MySQL connect timeout System Variable	How the connect timeout system variables impact thread handling / connection management in MySQL
connect_timeout	No. of seconds mysqld waits to connect back after a bad handshake from the client. You can keep this value high if clients frequently encounter errors of the form Lost connection to MySQL server
interactive_timeout	The number of seconds the server waits for activity on an interactive connection before closing it. Setting this value same as wait_timeout helps because in several cases aggressive interactive_timeout creates unpleasant connection management experiences as some applications by design demands higher intervals between query execution.
wait_timeout	The number of seconds the server waits for activity on a non-interactive connection before closing it. Every application demands unique thread / connection management requirements so sizing wait_timeout optimally does good if you have long intervals between SQL executions originating from same thread.
lock_Wait_timeout	The timeout in seconds for attempts to acquire metadata locks. If you are performing MySQL backup on a Master instance in replication, setting this value high helps because it defines defines the amount of time statement ( LOCK INSTANCE FOR BACKUP) waits for a lock before giving up.
net_read_timeout	No. of seconds to wait before aborting the read from the client.
net_write_timeout	No. of seconds to wait before aborting the write to the client.
net_retry_count	Number of retry attempts before giving up If a read or write on a communication port is interrupted, Please set this value high on FreeBSD because internal interrupts are sent to all threads.