本篇內(nèi)容介紹了“MySQL中參數(shù)wait_timeout和interactive_timeout以及空閑超時(shí)的實(shí)現(xiàn)方法是什么”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
我們提供的服務(wù)有：成都網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)、微信公眾號(hào)開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、南京ssl等。為近千家企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的南京網(wǎng)站制作公司
這里簡(jiǎn)單解釋一下兩個(gè)參數(shù)含義如下：
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()
wait_timeout：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())
他們都是session/global級(jí)別的，簡(jiǎn)單的說(shuō)前者用于描述交互式的客戶端的空閑超時(shí)，后者用于非交互式的客戶端的空閑超時(shí)，但是這里也揭示了，如果是交互式客戶端連接的session那么wait_timeout將被interactive_timeout覆蓋掉，換句話說(shuō)如果是非交互式的客戶端連接的session將不會(huì)使用interactive_timeout覆蓋掉wait_timeout，也就是interactive_timeout沒(méi)有任何作用了。
interactive_timeout：
static Sys_var_ulong Sys_interactive_timeout( vio_io_wait "interactive_timeout", "The number of seconds the server waits for activity on an interactive " "connection before closing it", SESSION_VAR(net_interactive_timeout), CMD_LINE(REQUIRED_ARG), VALID_RANGE(1, LONG_TIMEOUT), DEFAULT(NET_WAIT_TIMEOUT), BLOCK_SIZE(1));
wait_timeout：
static Sys_var_ulong Sys_net_wait_timeout( "wait_timeout", "The number of seconds the server waits for activity on a " "connection before closing it", SESSION_VAR(net_wait_timeout), CMD_LINE(REQUIRED_ARG), VALID_RANGE(1, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT)), DEFAULT(NET_WAIT_TIMEOUT), BLOCK_SIZE(1));
我們可以看到內(nèi)部而言參數(shù)interactive_timeout表示為net_interactive_timeout，wait_timeout表示為net_wait_timeout。
實(shí)際上這個(gè)操作只會(huì)在用戶登陸的時(shí)候才出現(xiàn)函數(shù)對(duì)應(yīng)server_mpvio_update_thd，如下：
重慶分公司
重慶分公司
server_mpvio_update_thd(THD *thd, MPVIO_EXT *mpvio) do_command
{
thd->max_client_packet_length= mpvio->max_client_packet_length; if (mpvio->protocol->has_client_capability(CLIENT_INTERACTIVE)) //這里做判斷
thd->variables.net_wait_timeout= thd->variables.net_interactive_timeout;//這里覆蓋這里我們可以明確看到有覆蓋操作，并且我們也能看到這里的if條件是如果是CLIENT_INTERACTIVE類型的客戶端連接才會(huì)做覆蓋。
棧幀如下：
#0 server_mpvio_update_thd (thd=0x7ffe7c012940, mpvio=0x7fffec0f6140) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/auth/sql_authentication.cc:2014#1 0x0000000000f01787 in acl_authenticate (thd=0x7ffe7c012940, command=COM_CONNECT, extra_port_connection=false) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/auth/sql_authentication.cc:2246#2 0x0000000001571149 in check_connection (thd=0x7ffe7c012940, extra_port_connection=false) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_connect.cc:1295#3 0x00000000015712dc in login_connection (thd=0x7ffe7c012940, extra_port_connection=false) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_connect.cc:1352#4 0x0000000001571bfe in thd_prepare_connection (thd=0x7ffe7c012940, extra_port_connection=false) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_connect.cc:1516#5 0x000000000170e642 in handle_connection (arg=0x6781c30) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:306
那么我們這里可以得到一個(gè)結(jié)論，只在登陸的時(shí)候會(huì)判斷連接是否是交互式的，如果是則覆蓋掉參數(shù)wait_timeout，但是一旦連接后將不會(huì)發(fā)生覆蓋操作，即便我們?cè)俅涡薷膇nteractive_timeout的值也不會(huì)覆蓋，后面我們會(huì)看到實(shí)際上生效的參數(shù)只有wait_timeout。
實(shí)際上每次執(zhí)行任何一個(gè)命令都會(huì)做一次wait_timeout值的重新檢查和賦值給網(wǎng)絡(luò)read_timeout值。在函數(shù)do_command中我們可以發(fā)現(xiàn)my_net_set_read_timeout(net, thd->get_wait_timeout());步驟，這個(gè)步驟就是將我們的wait_timeout賦值給網(wǎng)絡(luò)read_timeout值，其中包含片段
if (net->read_timeout == timeout) //如果read_timeout和wait_timeout相等 DBUG_VOID_RETURN;//不需要做操作直接return net->read_timeout= timeout;//否則進(jìn)行賦值。 if (net->vio) vio_timeout(net->vio, 0, timeout);//這里會(huì)進(jìn)行net->vio.read_timeout的賦值
執(zhí)行完這個(gè)步驟后wait_timeout就生效了，然后就會(huì)執(zhí)行命令，執(zhí)行完命令后，整個(gè)線程會(huì)再次回到do_command函數(shù)，再做一次my_net_set_read_timeout函數(shù)生效其中的wait_timeout參數(shù)，中并且堵塞接受命令(后面可以看到是poll實(shí)現(xiàn)的)，這個(gè)時(shí)候wait_timeout就起作用了。整個(gè)棧幀如下：
#0 vio_io_wait (vio=0x7ffe7c015520, event=VIO_IO_EVENT_READ, timeout=10000) at /root/mysqlall/percona-server-locks-detail-5.7.22/vio/viosocket.c:1119#1 0x0000000001e4d5f6 in vio_socket_io_wait (vio=0x7ffe7c015520, event=VIO_IO_EVENT_READ) at /root/mysqlall/percona-server-locks-detail-5.7.22/vio/viosocket.c:116#2 0x0000000001e4d6d2 in vio_read (vio=0x7ffe7c015520, buf=0x7ffe7c061c10 "\001", size=4) at /root/mysqlall/percona-server-locks-detail-5.7.22/vio/viosocket.c:171#3 0x00000000014c6ceb in net_read_raw_loop (net=0x7ffe7c028440, count=4) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/net_serv.cc:672#4 0x00000000014c6ec2 in net_read_packet_header (net=0x7ffe7c028440) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/net_serv.cc:756#5 0x00000000014c6fcb in net_read_packet (net=0x7ffe7c028440, complen=0x7fffec0c5c58) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/net_serv.cc:822#6 0x00000000014c715e in my_net_read (net=0x7ffe7c028440) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/net_serv.cc:899#7 0x00000000014de010 in Protocol_classic::read_packet (this=0x7ffe7c027bf8) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/protocol_classic.cc:808#8 0x00000000014de514 in Protocol_classic::get_command (this=0x7ffe7c027bf8, com_data=0x7fffec0c5d70, cmd=0x7fffec0c5d98) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/protocol_classic.cc:965#9 0x00000000015c5699 in do_command (thd=0x7ffe7c0268e0) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:960
最終會(huì)調(diào)入vio_io_wait函數(shù)，如下是其中的部分片段，我們可以清楚看到實(shí)際上所謂的空閑超時(shí)實(shí)際上就是我們的pool實(shí)現(xiàn)的。
switch ((ret= poll(&pfd, 1, timeout)))
{ case -1: /* On error, -1 is returned. */
break; case 0: /*
Set errno to indicate a timeout error.
(This is not compiled in on WIN32.)
*/
errno= SOCKET_ETIMEDOUT; break; default: /* Ensure that the requested I/O event has completed. */
DBUG_ASSERT(pfd.revents & revents); break;
}因此整個(gè)步驟就是
loop
做wait_timeout參數(shù)檢查并且賦值。
堵塞接受命令由poll函數(shù)實(shí)現(xiàn)，通過(guò)poll函數(shù)的超時(shí)參數(shù)也實(shí)現(xiàn)了空閑等待超時(shí)。(如果不發(fā)送命令就堵塞在這里)
命令來(lái)到退出堵塞。
再次做wait_timeout參數(shù)檢查并且賦值。
執(zhí)行命令。
goto loop
我這里就用mysql客戶端和pymysql進(jìn)行交互和非交互連接的測(cè)試。
交互式mysql客戶端會(huì)話interactive_timeout 參數(shù)覆蓋wait_timeout參數(shù)
mysql> show variables like 'wait_timeout%'; +---------------+-------+| Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 |+---------------+-------+1 row in set (0.02 sec) mysql> show variables like 'interactive_timeout'; +---------------------+-------+| Variable_name | Value | +---------------------+-------+ | interactive_timeout | 28800 |+---------------------+-------+1 row in set (0.01 sec) mysql> set global interactive_timeout = 20; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye [root@gp1 log]# /mysqldata/mysql3340/bin/mysqlWelcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6Server version: 5.7.22-22-debug-log Source distribution Copyright (c) 2009-2018 Percona LLC and/or its affiliates Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like 'interactive_timeout'; +---------------------+-------+| Variable_name | Value | +---------------------+-------+ | interactive_timeout | 20 |+---------------------+-------+1 row in set (0.01 sec) mysql> show variables like 'wait_timeout'; +---------------+-------+| Variable_name | Value | +---------------+-------+ | wait_timeout | 20 |+---------------+-------+1 row in set (0.02 sec)
交互式mysql客戶端會(huì)話登陸期間修改interactive_timeout不生效，更改wait_timeout生效。
mysql> show variables like 'interactive_timeout'; +---------------------+-------+| Variable_name | Value | +---------------------+-------+ | interactive_timeout | 28800 |+---------------------+-------+1 row in set (0.02 sec) mysql> show variables like 'wait_timeout'; +---------------+-------+| Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 |+---------------+-------+1 row in set (0.02 sec) mysql> set interactive_timeout=5; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'wait_timeout'; +---------------+-------+| Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 |+---------------+-------+1 row in set (0.01 sec) mysql> show variables like 'interactive_timeout'; +---------------------+-------+| Variable_name | Value | +---------------------+-------+ | interactive_timeout | 5 |+---------------------+-------+1 row in set (0.02 sec) 等待5秒，并未生效 mysql> select sysdate(); +---------------------+| sysdate() |+---------------------+| 2019-02-28 17:24:29 |+---------------------+1 row in set (0.00 sec) mysql> set wait_timeout=5; Query OK, 0 rows affected (0.00 sec) 等待5秒 發(fā)現(xiàn)斷開了 mysql> show variables like 'wait_timeout'; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 10
使用python連接非交互式客戶端interactive_timeout 參數(shù)不會(huì)覆蓋wait_timeout參數(shù)
我們可以簡(jiǎn)單的寫一個(gè)python腳本如下：
import socketimport pymysql.cursorsimport psutilimport subprocess
mysql_con = {"host":"192.168.99.95","port":3340,"user":"pycon","passwd":"gelc123","db":"test"}def main():
sqlwait = "show variables like 'wait_timeout'"
sqlinter = "show variables like 'interactive_timeout'"
sql_c_inter = "set global interactive_timeout=10"
connect = pymysql.Connect(host=mysql_con["host"], port=mysql_con["port"], user=mysql_con["user"],
passwd=mysql_con["passwd"], db=mysql_con["db"])
cursor = connect.cursor() ##查看初始值
cursor.execute(sqlwait)
ret_wait = cursor.fetchone()
cursor.execute(sqlinter)
ret_inter = cursor.fetchone()
print("before change: {}".format(ret_wait+ret_inter)) ##更改值
cursor.execute(sql_c_inter)
connect.close()##關(guān)閉連接
##重新登陸開啟連接
connect = pymysql.Connect(host=mysql_con["host"], port=mysql_con["port"], user=mysql_con["user"],
passwd=mysql_con["passwd"], db=mysql_con["db"])
cursor = connect.cursor()
cursor.execute(sqlwait)
ret_wait = cursor.fetchone()
cursor.execute(sqlinter)
ret_inter = cursor.fetchone()
print("after change: {}".format(ret_wait+ret_inter)) ##恢復(fù)值
sql_c_inter = "set global interactive_timeout=28800"
cursor.execute(sql_c_inter)
connect.close()#關(guān)閉連接##程序開始if __name__ == '__main__':
main()得到的測(cè)試結(jié)果如下：
before change: ('wait_timeout', '28800', 'interactive_timeout', '28800')
after change: ('wait_timeout', '28800', 'interactive_timeout', '10')如果是交互是客戶端會(huì)話的話wait_timeout也應(yīng)該是10。
“MySQL中參數(shù)wait_timeout和interactive_timeout以及空閑超時(shí)的實(shí)現(xiàn)方法是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
