為了優(yōu)化OceanBase的query timeout設(shè)置方式，特調(diào)研MySQL關(guān)于timeout的處理，記錄如下。

重點(diǎn)解釋其中幾個(gè)參數(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í)，等待握手的超時(shí)時(shí)間，只在登錄時(shí)有效，登錄成功這個(gè)參數(shù)就不管事了。主要是為了防止網(wǎng)絡(luò)不佳時(shí)應(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.
解釋：一個(gè)持續(xù)SLEEP狀態(tài)的線程多久被關(guān)閉。線程每次被使用都會(huì)被喚醒為acrivity狀態(tài)，執(zhí)行完Query后成為interactive狀態(tài)，重新開始計(jì)時(shí)。wait_timeout不同在于只作用于TCP/IP和Socket鏈接的線程，意義是一樣的。
MySQL可以配置連接的超時(shí)時(shí)間，這個(gè)時(shí)間如果做得太長(zhǎng)，甚至到了10min，那么很可能發(fā)生這種情況，3000個(gè)鏈接都被占滿而且sleep在哪，新鏈接進(jìn)不來，導(dǎo)致無法正常服務(wù)。因此這個(gè)配置盡量配置一個(gè)符合邏輯的值，60s或者120s等等。
說人話：
命令行下面敲一個(gè)命令后，直至下一個(gè)命令到來之前的時(shí)間間隔為interactive_time，如果這個(gè)時(shí)間間隔超過了interactive_timeout，則連接會(huì)被自動(dòng)斷開，下一個(gè)命令失敗。不過一般的mysql客戶端都有自動(dòng)重連機(jī)制，下一個(gè)命令會(huì)在重連后執(zhí)行。


wait_timeout:
The number of seconds the server waits for activity on a noninteractive connection (連接上沒有活動(dòng)命令，可能是客戶端喝咖啡去了。)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.

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.
解釋：這個(gè)參數(shù)只對(duì)TCP/IP鏈接有效，分別是數(shù)據(jù)庫等待接收客戶端發(fā)送網(wǎng)絡(luò)包和發(fā)送網(wǎng)絡(luò)包給客戶端的超時(shí)時(shí)間，這是在Activity狀態(tài)下的線程才有效的參數(shù)
JDBC setQueryTimeout函數(shù)：
為了避免查詢出現(xiàn)死循環(huán)，或時(shí)間過長(zhǎng)等現(xiàn)象，而導(dǎo)致線程阻塞，在獲得Statement的實(shí)例后，stmt.setQueryTimeout(10); 避免因?yàn)椴樵儗?dǎo)致程序出現(xiàn)線程阻塞。
但昨天發(fā)現(xiàn)程序出現(xiàn)了，“ORA-01013: 用戶請(qǐng)求取消當(dāng)前的操作”的異常。手工執(zhí)行出錯(cuò)SQL語句發(fā)現(xiàn)，這個(gè)語句耗時(shí)20多秒。因?yàn)閟etQueryTimeout(10)，所以還沒有執(zhí)行完查詢語句就拋出異常了。使用setQueryTimeout(10)時(shí)一定要把時(shí)間設(shè)置的長(zhǎng)一些，如60秒以上。只要不導(dǎo)致線程長(zhǎng)期阻塞，就可以。太短了容易拋出，“ORA-01013: 用戶請(qǐng)求取消當(dāng)前的操作”的異常
JDBC實(shí)現(xiàn)setQueryTimeout的原理：

可見，query timeout是通過客戶端解決方案來做的，服務(wù)器端無需知曉。通過一個(gè)timer線程來監(jiān)控執(zhí)行時(shí)間，如果執(zhí)行時(shí)間超時(shí)，則會(huì)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)。

最新評(píng)論