導讀:1建站知識為了優(yōu)化OceanBase的query timeout設置方式，特調(diào)研MySQL關于timeout的處理，記錄如下。 復制代碼 代碼如下: mysql show網(wǎng)站seo優(yōu)化軟件個業(yè)網(wǎng)站建設公司。

為了優(yōu)化OceanBase的query timeout設置方式，特調(diào)研MySQL關于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)      重點解釋其中幾個參數(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)絡不佳時應用重連導致連接數(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 opti建設網(wǎng)站on to mysql_real_connect(). See alsowait_timeout.  解釋：一個持續(xù)SLEEP狀態(tài)的線程多久被關閉。線程每次被使用都會被喚醒為acrivity狀態(tài)，執(zhí)行完Query后成為interactive狀態(tài)，重新開始計時。wait_timeout不同在于只作用于TCP/IP和Socket鏈接的線程，意義是一樣的。  MySQL可以配置連接的超時時間，這個時間如果做得太長，甚至到了10min，那么很可能發(fā)生這種情況，3000個鏈接都被占滿而且sleep在哪，新鏈接進不來，導致無法正常服務。因此這個配置盡量配置一個符合邏輯的值，60s或者120s等等。  說人話：  命令行下面敲一個命令后，直至下一個命令到來之前的時間間隔為interactive_time，如果這個時間間隔超過了interactive_timeout，則連接會被自動斷開，下一個命令失敗。不過一般的mysql客戶端都有自動重連機制，下一個命令會在重連后執(zhí)行。  復制代碼 代碼如下:     mysql> set interactive_timeout = 1;  Query OK, 0 rows affected (0.00 sec)  mysql> show session variables like '%timeout%';  +----------------------------+----------+  | Variable_name | Value |個業(yè)網(wǎng)站建設公司  +----------------------------+----------+  | 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 網(wǎng)站建設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)絡包和發(fā)送網(wǎng)絡包給客戶端的超時時間，這是在Activity狀態(tài)下的線程才有效的參數(shù)  JDBC setQueryTimeout函數(shù)：  為了避免查詢出現(xiàn)死循環(huán)，或時間過長等現(xiàn)象，而導致線程阻塞，在獲得Statement的實例后，stmt.setQueryTimeout(10); 避免因為查詢導致程序出現(xiàn)線程阻塞。  但昨天發(fā)現(xiàn)程序出現(xiàn)了，“ORA-01013: 用戶請求取消當前的操作”的異常。手工執(zhí)行出錯SQL語句發(fā)現(xiàn)，這個語句耗時20多秒。因為setQueryTimeout(10)，所以還沒有執(zhí)行完查詢語句就拋出異常了。使用setQueryTimeout(10)時一定要把時間設置的長一些，如60秒以上。只要不導致線程長期阻塞，就可以。太短了容易拋出，“ORA-01013: 用戶請求取消當前的操作”的異常  JDBC實現(xiàn)setQueryTimeout的原理： 相關網(wǎng)站seo優(yōu)化軟件個業(yè)網(wǎng)站建設公司。

關鍵詞標簽: 標題 標簽 SQL