SQL查詢超時的設(shè)置方法(關(guān)于timeout的處理)
更新時間：2013年04月16日 16:21:15 作者：
為了優(yōu)化OceanBase的query timeout設(shè)置方式，特調(diào)研MySQL關(guān)于timeout的處理，下面與大家分享下處理記錄，感興趣的朋友可以參考下哈
為了優(yōu)化OceanBase的query timeout設(shè)置方式，特調(diào)研MySQL關(guān)于timeout的處理，記錄如下。
mysql> show variables like '%time%';
+----------------------------+-------------------+
| Variable_name | Value |
+----------------------------+-------------------+
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| flush_time | 1800 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 0 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 10.000000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1366027807 |
| wait_timeout | 28800 |
+----------------------------+-------------------+
21 rows in set, 1 warning (0.00 sec)
重點(diǎn)解釋其中幾個參數(shù)：
connect_timeout:
The number of seconds that the mysqld server waits for a connect packet before respondingwith 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.
解釋：在獲取鏈接時，等待握手的超時時間，只在登錄時有效，登錄成功這個參數(shù)就不管事了。主要是為了防止網(wǎng)絡(luò)不佳時應(yīng)用重連導(dǎo)致連接數(shù)漲太快，一般默認(rèn)即可。
interactive_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 alsowait_timeout.
解釋：一個持續(xù)SLEEP狀態(tài)的線程多久被關(guān)閉。線程每次被使用都會被喚醒為acrivity狀態(tài)，執(zhí)行完Query后成為interactive狀態(tài)，重新開始計時。wait_timeout不同在于只作用于TCP/IP和Socket鏈接的線程，意義是一樣的。
MySQL可以配置連接的超時時間，這個時間如果做得太長，甚至到了10min，那么很可能發(fā)生這種情況，3000個鏈接都被占滿而且sleep在哪，新鏈接進(jìn)不來，導(dǎo)致無法正常服務(wù)。因此這個配置盡量配置一個符合邏輯的值，60s或者120s等等。
說人話：
命令行下面敲一個命令后，直至下一個命令到來之前的時間間隔為interactive_time，如果這個時間間隔超過了interactive_timeout，則連接會被自動斷開，下一個命令失敗。不過一般的mysql客戶端都有自動重連機(jī)制，下一個命令會在重連后執(zhí)行。
mysql> set interactive_timeout = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show session variables like '%timeout%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| interactive_timeout | 1 |
| wait_timeout | 28800 |
+----------------------------+----------+
10 rows in set (0.00 sec)
mysql> set wait_timeout = 1;
Query OK, 0 rows affected (0.00 sec)
【去泡杯茶，等會兒】
mysql> show session variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| interactive_timeout | 28800 |
| wait_timeout | 28800 |
+----------------------------+----------+
10 rows in set (0.01 sec)
wait_timeout:
The number of seconds the server waits for activity on a noninteractive connection (連接上沒有活動命令，可能是客戶端喝咖啡去了。)before closing it. 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.
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
這里順帶解釋一下什么是non-interactive connection
> Non-Interactive Commands
Just do a quick look up on a table without logging into the client, running the query then logging back out again.
You can instead just type one line using the ' -e ' flag.
c:\mysql\bin\mysql -u admin -p myDatabase -e 'SELECT * FROM employee'
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.
解釋：這個參數(shù)只對TCP/IP鏈接有效，分別是數(shù)據(jù)庫等待接收客戶端發(fā)送網(wǎng)絡(luò)包和發(fā)送網(wǎng)絡(luò)包給客戶端的超時時間，這是在Activity狀態(tài)下的線程才有效的參數(shù)
JDBC setQueryTimeout函數(shù)：
為了避免查詢出現(xiàn)死循環(huán)，或時間過長等現(xiàn)象，而導(dǎo)致線程阻塞，在獲得Statement的實例后，stmt.setQueryTimeout(10); 避免因為查詢導(dǎo)致程序出現(xiàn)線程阻塞。
但昨天發(fā)現(xiàn)程序出現(xiàn)了，“ORA-01013: 用戶請求取消當(dāng)前的操作”的異常。手工執(zhí)行出錯SQL語句發(fā)現(xiàn)，這個語句耗時20多秒。因為setQueryTimeout(10)，所以還沒有執(zhí)行完查詢語句就拋出異常了。使用setQueryTimeout(10)時一定要把時間設(shè)置的長一些，如60秒以上。只要不導(dǎo)致線程長期阻塞，就可以。太短了容易拋出，“ORA-01013: 用戶請求取消當(dāng)前的操作”的異常
JDBC實現(xiàn)setQueryTimeout的原理：
class IfxCancelQueryImpl extends TimerTask
implements IfmxCancelQuery
{
IfxStatement stmt;
Timer t = null;
public void startCancel(IfxStatement paramIfxStatement, int paramInt)
throws Exception
{
this.stmt = paramIfxStatement;
this.t = new Timer(true);
this.t.schedule(this, paramInt * 1000);
}
public void run()
{
try
{
this.stmt.cancel();
this.t.cancel();
}
catch (SQLException localSQLException)
{
this.t.cancel();
throw new Error(localSQLException.getErrorCode() + ":" + localSQLException.getMessage());
}
}
}
可見，query timeout是通過客戶端解決方案來做的，服務(wù)器端無需知曉。通過一個timer線程來監(jiān)控執(zhí)行時間，如果執(zhí)行時間超時，則會schedule run()函數(shù)。
Reference:
http://wangwei.cao.blog.163.com/blog/static/10236252620111119115540534/
http://sls8204.blog.163.com/blog/static/62979632200741683453114/
OceanBase可以通過server端支持query timeout，可以設(shè)置query timeout并且不中斷當(dāng)前session。這一點(diǎn)比MySQL先進(jìn)。
復(fù)制代碼 代碼如下:
mysql> show variables like '%time%';
+----------------------------+-------------------+
| Variable_name | Value |
+----------------------------+-------------------+
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| flush_time | 1800 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 0 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 10.000000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1366027807 |
| wait_timeout | 28800 |
+----------------------------+-------------------+
21 rows in set, 1 warning (0.00 sec)
重點(diǎn)解釋其中幾個參數(shù)：
connect_timeout:
The number of seconds that the mysqld server waits for a connect packet before respondingwith 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.
解釋：在獲取鏈接時，等待握手的超時時間，只在登錄時有效，登錄成功這個參數(shù)就不管事了。主要是為了防止網(wǎng)絡(luò)不佳時應(yīng)用重連導(dǎo)致連接數(shù)漲太快，一般默認(rèn)即可。
interactive_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 alsowait_timeout.
解釋：一個持續(xù)SLEEP狀態(tài)的線程多久被關(guān)閉。線程每次被使用都會被喚醒為acrivity狀態(tài)，執(zhí)行完Query后成為interactive狀態(tài)，重新開始計時。wait_timeout不同在于只作用于TCP/IP和Socket鏈接的線程，意義是一樣的。
MySQL可以配置連接的超時時間，這個時間如果做得太長，甚至到了10min，那么很可能發(fā)生這種情況，3000個鏈接都被占滿而且sleep在哪，新鏈接進(jìn)不來，導(dǎo)致無法正常服務(wù)。因此這個配置盡量配置一個符合邏輯的值，60s或者120s等等。
說人話：
命令行下面敲一個命令后，直至下一個命令到來之前的時間間隔為interactive_time，如果這個時間間隔超過了interactive_timeout，則連接會被自動斷開，下一個命令失敗。不過一般的mysql客戶端都有自動重連機(jī)制，下一個命令會在重連后執(zhí)行。
復(fù)制代碼 代碼如下:
mysql> set interactive_timeout = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show session variables like '%timeout%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| interactive_timeout | 1 |
| wait_timeout | 28800 |
+----------------------------+----------+
10 rows in set (0.00 sec)
復(fù)制代碼 代碼如下:
mysql> set wait_timeout = 1;
Query OK, 0 rows affected (0.00 sec)
【去泡杯茶，等會兒】
mysql> show session variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| interactive_timeout | 28800 |
| wait_timeout | 28800 |
+----------------------------+----------+
10 rows in set (0.01 sec)
wait_timeout:
The number of seconds the server waits for activity on a noninteractive connection (連接上沒有活動命令，可能是客戶端喝咖啡去了。)before closing it. 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.
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
這里順帶解釋一下什么是non-interactive connection
> Non-Interactive Commands
Just do a quick look up on a table without logging into the client, running the query then logging back out again.
You can instead just type one line using the ' -e ' flag.
復(fù)制代碼 代碼如下:
c:\mysql\bin\mysql -u admin -p myDatabase -e 'SELECT * FROM employee'
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.
解釋：這個參數(shù)只對TCP/IP鏈接有效，分別是數(shù)據(jù)庫等待接收客戶端發(fā)送網(wǎng)絡(luò)包和發(fā)送網(wǎng)絡(luò)包給客戶端的超時時間，這是在Activity狀態(tài)下的線程才有效的參數(shù)
JDBC setQueryTimeout函數(shù)：
為了避免查詢出現(xiàn)死循環(huán)，或時間過長等現(xiàn)象，而導(dǎo)致線程阻塞，在獲得Statement的實例后，stmt.setQueryTimeout(10); 避免因為查詢導(dǎo)致程序出現(xiàn)線程阻塞。
但昨天發(fā)現(xiàn)程序出現(xiàn)了，“ORA-01013: 用戶請求取消當(dāng)前的操作”的異常。手工執(zhí)行出錯SQL語句發(fā)現(xiàn)，這個語句耗時20多秒。因為setQueryTimeout(10)，所以還沒有執(zhí)行完查詢語句就拋出異常了。使用setQueryTimeout(10)時一定要把時間設(shè)置的長一些，如60秒以上。只要不導(dǎo)致線程長期阻塞，就可以。太短了容易拋出，“ORA-01013: 用戶請求取消當(dāng)前的操作”的異常
JDBC實現(xiàn)setQueryTimeout的原理：
復(fù)制代碼 代碼如下:
class IfxCancelQueryImpl extends TimerTask
implements IfmxCancelQuery
{
IfxStatement stmt;
Timer t = null;
public void startCancel(IfxStatement paramIfxStatement, int paramInt)
throws Exception
{
this.stmt = paramIfxStatement;
this.t = new Timer(true);
this.t.schedule(this, paramInt * 1000);
}
public void run()
{
try
{
this.stmt.cancel();
this.t.cancel();
}
catch (SQLException localSQLException)
{
this.t.cancel();
throw new Error(localSQLException.getErrorCode() + ":" + localSQLException.getMessage());
}
}
}
可見，query timeout是通過客戶端解決方案來做的，服務(wù)器端無需知曉。通過一個timer線程來監(jiān)控執(zhí)行時間，如果執(zhí)行時間超時，則會schedule run()函數(shù)。
Reference:
http://wangwei.cao.blog.163.com/blog/static/10236252620111119115540534/
http://sls8204.blog.163.com/blog/static/62979632200741683453114/
OceanBase可以通過server端支持query timeout，可以設(shè)置query timeout并且不中斷當(dāng)前session。這一點(diǎn)比MySQL先進(jìn)。
相關(guān)文章
MySQL報錯ERROR?2002?(HY000):?Canot?connect?to?local?MyS
ERROR?2002是一個常見的錯誤,這可能會阻礙數(shù)據(jù)庫的正常使用,本文就來介紹一下該錯誤的解決方法,具有一定的參考價值,感興趣的可以了解一下2024-07-07
Mysql 切換數(shù)據(jù)存儲目錄的實現(xiàn)方法
這篇文章主要介紹了Mysql 切換數(shù)據(jù)存儲目錄的實現(xiàn)方法的相關(guān)資料,需要的朋友可以參考下2017-07-07
關(guān)于MySQL的體系結(jié)構(gòu)及存儲引擎圖解
這篇文章主要介紹了關(guān)于MySQL的體系結(jié)構(gòu)及存儲引擎圖解,MySQL整體的邏輯結(jié)構(gòu)可以分為4層,客戶層、服務(wù)層、存儲引擎層、數(shù)據(jù)層,需要的朋友可以參考下2023-05-05
Mysql查詢很慢卡在sending data的原因及解決思路講解
今天小編就為大家分享一篇關(guān)于Mysql查詢很慢卡在sending data的原因及解決思路講解，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-04-04
這篇文章主要介紹了MySQL source命令的使用簡介，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下2021-03-03
PHP學(xué)習(xí)散記 2012_01_07(MySQL)
數(shù)據(jù)庫是Web大多數(shù)應(yīng)用開發(fā)的基礎(chǔ)。如果你是用PHP，那么大多數(shù)據(jù)庫用的是MYSQL也是LAMP架構(gòu)的重要部分2012-01-01
范圍訪問方法使用單一索引去檢索表中的數(shù)據(jù)包含一個或者多個索引值的行記錄,下面這篇文章主要給大家介紹了關(guān)于MySQL范圍查詢優(yōu)化的相關(guān)資料,文中通過實例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-06-06
數(shù)據(jù)庫已成為企業(yè)和管理員們最為關(guān)注的核心組件之一,本文主要介紹了MySQL8.0 MGR的維護(hù)管理,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-08-08
Mysql自動備份腳本供大家參考，實現(xiàn)了定期備份Mysql數(shù)據(jù)庫，并且可以選在在每周的一天做指定目錄下文件的全面?zhèn)浞?，備份文件自動上傳到你指定的FTP上，保證了備份的可靠性。2009-04-04
這篇文章主要介紹了mysql生成隨機(jī)字符串函數(shù)分享,本文直接給出實現(xiàn)代碼,需要的朋友可以參考下2015-02-02