mysql video tutorialThe column introduces the timeout variable
Recommended (free): mysql video tutorial
1. How much do you know about the timeout variable
Open mysql, use show variables like '%timeout%'
When you look at the command, you don’t know but you are shocked. The results are as shown below. There are so many timeout related variables, and you are scared to death. . It turns out that my understanding of mysql is so insufficient. Well, what do these timeouts mean? I spent an afternoon studying and did a few small experiments, and finally understood a thing or two. If there are any mistakes, please feel free to enlighten me. ah.
mysql> show variables like '%timeout%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | 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 | +-----------------------------+----------+
2. Analysis
Let’s find some of the more commonly used ones in timeout and analyze them one by one.
2.1 connect_timeout
connect_timeout refers to the handshake timeout during the connection process. The default is 10 seconds after 5.0.52, and the default is 5 seconds in previous versions. The official document says this:
connect_timeout: 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.0.52 and 5 seconds before that
The basic principle of mysql should be that there is a listening thread that receives requests in a loop. When a request comes, a thread is created (or taken from the thread pool) to process the request. Since the mysql connection uses the TCP protocol, a TCP three-way handshake must have been performed before. After the TCP three-way handshake is successful, the client will enter blocking and wait for messages from the server. At this time, the server will create a thread (or take a thread from the thread pool) to process the request. The main verification part includes host and username and password verification. We are familiar with host verification, because the host is specified when using the grant command to authorize users. For username and password authentication, the server first generates a random number and sends it to the client. The client uses the random number and password to perform multiple SHA1 encryptions and then sends them to the server for verification. If passed, the entire connection handshake process is completed. (The specific handshake process will be found and analyzed later)
It can be seen that there may be various possible errors in the entire connection handshake. So the connect_timeout value refers to the timeout time. You can simply test it by running the following telnet command and you will find that the client will time out and return after 10 seconds.
telnet localhost 3306
The connection status in mysql before timeout is as follows:
256 | unauthenticated user | localhost:60595 | NULL | Connect | NULL | Reading from net | NULL
2.2 interactive_timeout & wait_timeout
Let’s look at the official documentation first, from the documentation Both wait_timeout and interactive_timeout refer to the inactive connection timeout. When the connection thread starts, wait_timeout will be set to one of these two values depending on whether it is interactive mode or non-interactive mode. If we run the mysql -uroot -p
command to log in to mysql, wait_timeout will be set to the value of interactive_timeout. If we do not perform any operation within the wait_timeout time, a timeout will be prompted when we operate again, which means the mysql client will reconnect.
The number of seconds the server waits for activity on a noninteractive connection before closing it. On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()).
The test is as follows:
mysql> set global interactive_timeout=3; ##设置交互超时为3秒
Re-enter mysql, you can see:
mysql> show variables like '%timeout%'; ##wait_timeout已经被设置为3秒 +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 3 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 3 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 3600 | | wait_timeout | 3 | +-----------------------------+----------+
You can see that wait_timeout is set to the value of interactive_timeout, so, we have 3 seconds After executing other commands, the following prompt will appear:
mysql> show variables like '%timeout%'; ERROR 2006 (HY000): MySQL server has gone away ##超时重连 No connection. Trying to reconnect... Connection id: 50 Current database: *** NONE *** +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 3 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 3 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 3600 | | wait_timeout | 3 | +-----------------------------+----------+
2.3 innodb_lock_wait_timeout & innodb_rollback_on_timeout
It is better to refer to the official document first. From the document, this value is for innodb The engine is the waiting timeout for row locks in innodb. The default is 50 seconds. If it times out, the current statement is rolled back. If innodb_rollback_on_timeout is set, the entire transaction will be rolled back. Otherwise, only the statement in which the transaction is waiting for the row lock will be rolled back.
The length of time in seconds an InnoDB transaction waits 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 waits at most this many seconds for write access to the row before issuing the following error: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Let’s test it in the same way (first create a table test in the innodb engine, with only one column, and the column name is a):
mysql> CREATE TABLE `test` ( `a` int primary key) engine=innodb;
First insert three pieces of test data
mysql> select * from test; +---+ | a | +---+ | 1 | | 2 | | 3 |
Currently innodb_rollback_on_timeout=OFF, set innodb_lock_wait_timeout=1, we open two transactions
##事务1 加行锁 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test where a=2 for update; +---+ | a | +---+ | 2 | +---+ 1 row in set (0.01 sec)
##事务2,请求行锁 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from test where a=1; Query OK, 1 row affected (0.00 sec) mysql> delete from test where a=2; ##请求行锁超时 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from test; +---+ | a | +---+ | 2 | | 3 | +---+ 2 rows in set (0.00 sec) mysql> begin; ##这里我们直接开启另外的事务(或者直接commit当前事务),则原来的事务只会回滚第二条语句,最终结果就是test表中只剩下2和3.如果这里我们显示的rollback,则会回滚整个事务,保持1,2,3不变。
Then if innodb_rollback_on_timeout=ON, the same transaction 2 will time out, but at this time if we begin to open a new transaction, the request lock will be rolled back The entire transaction times out, instead of just rolling back the timed out statement like before.
2.4 lock_wait_timeout
The document describes it as follows. Simply put, lock_wait_timeout is the metadata lock waiting timeout. This timeout parameter will be used in any statement that locks metadata. The default for one year. Metadata locks can participate in mysql metadata lock. In order to ensure that transactions can be serialized, whether it is a table in myisam or innodb engine, as long as a transaction is opened in a session, the metadata lock of the operation table will be obtained. At this time, if If another session wants to modify the metadata of the table, it will block until timeout.
This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000. This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements
Test example:
We use a myisam engine table myisam_test to test. There is a record (1,1). Now we open a session and then execute a select statement. In addition, open a session and then perform metadata operations on the table, such as deleting the table. You will find that the operation is blocked until a timeout occurs after lock_wait_timeout seconds.
##第一个session,获取metadata lock mysql> show create table myisam_test; -----------------------------------------------------------+ | Table | Create Table | +----------------------------------------------------------- | myisam_test | CREATE TABLE `myisam_test` ( `i` int(11) NOT NULL, `j` int(11) DEFAULT NULL, PRIMARY KEY (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from myisam_test; +---+------+ | i | j | +---+------+ | 2 | 1 | +---+------+ 1 row in set (0.00 sec) ##另一个session,删除表提示超时 mysql> drop table myisam_test; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
其中更改表结构的元数据操作指令有如下这些:
DROP TABLE t; ALTER TABLE t ...; DROP TABLE nt; ALTER TABLE nt ...; LOCK TABLE t ... WRITE;
当然,多说一句,对于myisam表的加锁以及并发插入等,这篇博客myisam表锁非常详细,有兴趣的可以看看。
2.5 net_read_timeout & net_write_timeout
文档中描述如下,就是说这两个参数在网络条件不好的情况下起作用。比如我在客户端用load data infile的方式导入很大的一个文件到数据库中,然后中途用iptables禁用掉mysql的3306端口,这个时候服务器端该连接状态是reading from net,在等待net_read_timeout后关闭该连接。同理,在程序里面查询一个很大的表时,在查询过程中同样禁用掉端口,制造网络不通的情况,这样该连接状态是writing to net,然后在net_write_timeout后关闭该连接。slave_net_timeout类似。
The number of seconds to wait for more data from a connection before aborting the read. 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
测试:
我创建一个120M的数据文件data.txt。然后登陆到mysql。
mysql -uroot -h 127.0.0.1 -P 3306 --local-infile=1
导入过程设置iptables禁用3306端口。
iptables -A INPUT -p tcp --dport 3306 -j DROP iptables -A OUTPUT -p tcp --sport 3306 -j DROP
可以看到连接状态为reading from net,然后经过net_read_timeout秒后关闭。
3.总结
经过几个实验可以发现,connect_timeout在握手认证阶段(authenticate)起作用,interactive_timeout 和wait_timeout在连接空闲阶段(sleep)起作用,而net_read_timeout和net_write_timeout则是在连接繁忙阶段(query)或者网络出现问题时起作用。
The above is the detailed content of Exploring the mysql timeout variable. For more information, please follow other related articles on the PHP Chinese website!

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

Dreamweaver CS6
Visual web development tools

Dreamweaver Mac version
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

WebStorm Mac version
Useful JavaScript development tools
