Mysql connection timeout parameter settings _ MySQL

Recently, the system encountered several failures due to database connection timeout. I checked the configuration of my. cnf, and finally found a problem with the code design. All the timeout parameters are handled. First, check the database for timeouts: mysqlshowglobalvariabl has encountered several failures recently due to database connection timeout. I checked the configuration of my. cnf, and finally found a problem with the code design. All the timeout parameters are handled. First, check the database to see the timeouts:

Mysql> show global variables like "% timeout % ";

+ ----------------------------- + ---------- +

| Variable_name | Value |

+ ----------------------------- + ---------- +

| Connect_timeout | 10 |

| Delayed_insert_timeout | 300 |

| Innodb_flush_log_at_timeout | 1 |

| Innodb_lock_wait_timeout | 120 |

| Innodb_rollback_on_timeout | OFF |

| Interactive_timeout | 28800 |

| Lock_wait_timeout | 31536000 |

| Net_read_timeout | 30 |

| Net_write_timeout | 60 |

| Rpl_stop_slave_timeout | 31536000 |

| Slave_net_timeout | 3600 |

| Wait_timeout | 28800 |

+ ----------------------------- + ---------- +

12 rows in set (0.00 sec)

Let's analyze the meaning of each parameter:

Connect_timeout

Manual description:

The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds as of MySQL 5.1.23 and 5 seconds before that.

Increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at 'XXX', system error: errno.

Explanation: The timeout time for handshaking is only valid during logon when the link is obtained. the parameter "login successful" will be ignored. The main purpose is to prevent the number of connections from increasing too quickly due to application reconnection when the network is poor, which is generally the default value.

Delayed_insert_timeout

Manual description:

How many seconds an insert delayed handler thread shoshould wait for INSERT statements before terminating.

Explanation: This is a timeout parameter designed for MyISAM insert delay. it is the time to wait for the INSERT statement before the insert delay is aborted.

Innodb_lock_wait_timeout

Manual description:

The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. the default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this has seconds before issuing the following error:

1

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When a lock wait timeout occurs, the current statement is not executed. the current transaction is not rolled back. (To have the entire transaction roll back, start the server with the-innodb_rollback_on_timeout option, available as of MySQL 5.1.15. see also Section 13.6.12, "InnoDB Error Handling ".)

Innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.

InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.

For the built-in InnoDB, this variable can be set only at server startup. For InnoDB Plugin, it can be set at startup or changed at runtime, and has both global and session values.

Explanation: The description is very long. In short, it is the Query timeout time when the transaction encounters a Lock wait. Unlike deadlocks, once InnoDB detects a deadlock, it will immediately roll back the transaction with a low cost. when the lock wait is not deadlocked, the next transaction will hold the lock resources required by another transaction, the Query to be rolled back must be the Query to be locked.

Innodb_rollback_on_timeout

Manual description:

In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. if-innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1 ). this variable was added in MySQL 5.1.15.

Explanation: If this parameter is disabled or does not exist, only the last Query of the transaction will be rolled back when timeout occurs. if it is enabled, the entire transaction will be rolled back when timeout occurs.

Interactive_timeout/wait_timeout

Manual description:

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

Explanation: How long is a thread in SLEEP state closed. Every time a thread is used, it is awakened to the acriworkflow status. after the Query is executed, it becomes the interactive status and the timer starts again. Wait_timeout is different in that it only acts on the threads of TCP/IP and Socket links, and the meaning is the same. Generally, this parameter is set to 8 hours. generally, a website is accessed for a day, which usually takes more than 8 hours from night to morning. this problem occurs when you access the website again.

Net_read_timeout/net_write_timeout

Manual description:

The number of seconds to wait for more data from a connection before aborting the read. before MySQL 5.1.41, this timeout applies only to TCP/IP connections, not to connections made through Unix socket files, named pipes, or shared memory. 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.

On Linux, the NO_ALARM build flag affects timeout behavior as indicated in the description of the net_retry_count system variable.

Explanation: This parameter is only valid for TCP/IP links. it is the timeout time when the database waits for the receiving client to send the network packet and sends the network packet to the client, this parameter is valid only for threads in the Activity state.

Slave_net_timeout

Manual description:

The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. the first retry occurs immediately after the timeout. the interval between retries is controlled by the MASTER_CONNECT_RETRY option for the change master to statement or-master-connect-retry option, and the number of reconnection attempts is limited by the-master-retry-count option. the default is 3600 seconds (one hour ).

Explanation: This is the timeout setting for Slave to determine whether the host is suspended. if the Master response is still not obtained within the set time, the Master will be suspended.