MySQL的timeout那点事

转载 2015年07月09日 15:07:47
转载网址: http://www.penglixun.com/tech/database/mysql_timeout.html

因为最近遇到一些超时的问题,正好就把所有的timeout参数都理一遍,首先数据库里查一下看有哪些超时:

root@localhost : test 12:55:50> show global variables like "%timeout%";
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| connect_timeout            | 10     |
| delayed_insert_timeout     | 300    |
| innodb_lock_wait_timeout   | 120    |
| innodb_rollback_on_timeout | ON     |
| interactive_timeout        | 172800 |
| net_read_timeout           | 30     |
| net_write_timeout          | 60     |
| slave_net_timeout          | 3600   |
| table_lock_wait_timeout    | 50     | # 这个参数已经没用了
| wait_timeout               | 172800 |
+----------------------------+--------+

我们一个个来看

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.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.
解释:在获取链接时,等待握手的超时时间,只在登录时有效,登录成功这个参数就不管事了。主要是为了防止网络不佳时应用重连导致连接数涨太快,一般默认即可。

delayed_insert_timeout

手册描述:
How many seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating.
解释:这是为MyISAM INSERT DELAY设计的超时参数,在INSERT DELAY中止前等待INSERT语句的时间。

innodb_lock_wait_timeout

手册描述:
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 many seconds before issuing the following error:

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.
解释:描述很长,简而言之,就是事务遇到锁等待时的Query超时时间。跟死锁不一样,InnoDB一旦检测到死锁立刻就会回滚代价小的那个事务,锁等待是没有死锁的情况下一个事务持有另一个事务需要的锁资源,被回滚的肯定是请求锁的那个Query。

innodb_rollback_on_timeout

手册描述:
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.
解释:这个参数关闭或不存在的话遇到超时只回滚事务最后一个Query,打开的话事务遇到超时就回滚整个事务。

interactive_timeout/wait_timeout

手册描述:
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
解释:一个持续SLEEP状态的线程多久被关闭。线程每次被使用都会被唤醒为acrivity状态,执行完Query后成为interactive状态,重新开始计时。wait_timeout不同在于只作用于TCP/IP和Socket链接的线程,意义是一样的。

net_read_timeout / net_write_timeout

手册描述:
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.
解释:这个参数只对TCP/IP链接有效,分别是数据库等待接收客户端发送网络包和发送网络包给客户端的超时时间,这是在Activity状态下的线程才有效的参数

slave_net_timeout

手册描述:
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).
解释:这是Slave判断主机是否挂掉的超时设置,在设定时间内依然没有获取到Master的回应就人为Master挂掉了

MySQL的timeout那点事

网址: http://www.penglixun.com/tech/database/mysql_timeout.html 因为最近遇到一些超时的问题,正好就把所有的timeout参数都理一遍,首先...
  • xtjsxtj
  • xtjsxtj
  • 2013年11月14日 10:25
  • 1162

MySQL的timeout那点事

以下文章来自:http://www.penglixun.com/tech/database/mysql_timeout.html,为P.Linux所写。放在此处 仅为保存和方便查询。 =======...

MySql定时器的那点事

由于项目需要创建定时器(evevt),所以就百度了一下,发现基本都是来源于一个模板,有些功能还不全,现在自己总结一下。 注:mysql版本是从5.16开始才支持event的。如果你的版本低于...

PHP+MySQL 分页那点事

分页技术随处可见,这可以算得上是最为基础的网站功能了。于是今天尝试着用PHP来实现一个分页的小例子。 准备工作 环境准备 Apache MySQL PHP 工作环境 数据库准备 建库 建表 预存数据 ...

MySQL那点事之查询数据

SELECT 属性列表        FROM [WHERE 条件表达式1]        [GROUP BY 属性名1 [HAVING条件表达式]]        [ORDER BY 属性名2 [A...

关于Mysql的那点事!

原文地址:关于Mysql的那点事! - lamb7758的博客 - 博客频道 - CSDN.NET http://blog.csdn.net/lamb7758/article/details/53...

毕业设计那点事 — 源码安装Mysql

安装依赖包 apt-get install bison # [下载](http://www.gnu.org/software/bison/) apt-get install cmake #[下载](h...

有关binlog的那点事(二)(mysql5.7.13)

http://www.cnblogs.com/onlyac/p/6043595.html 上次,我们仅仅把binlog做了一个概述,并没有去深入探索(1)binlog file究...

Mysql那点事之数据类型

Mysql常见的数据类型分为整数类型、浮点型类型、字符串类型、时间和日期类型、二进制类型。 其中整数类型(int),日期和时间类型和字符串类型是数据库中使用最频繁的数据类型。定点数类型 、二进制数...

MySQL那点事之常见操作

查看表结构 DESCRIBE(或者DESC) 表名: 可查看表的基本定义,其中包括:字段名,字段数据类型,是否为主键和默认值等。 SHOW CREATE TABLE 表名 :(后面+“\G"可以...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL的timeout那点事
举报原因:
原因补充:

(最多只允许输入30个字)