Mysql的timeout以及python重連方式

更新時(shí)間：2024年07月18日 08:59:37 作者：有人找你

這篇文章主要介紹了Mysql的timeout以及python重連方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

問題

近期使用多線程對(duì)數(shù)據(jù)庫(kù)操作報(bào)錯(cuò)：

(2013, 'Lost connection to MySQL server during query')

解決方案

1. 更改timeout參數(shù)

出現(xiàn)這個(gè)問題順便就了解了一下mysql中的timeout都有什么：

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 28800    |
+-----------------------------+----------+
13 rows in set (0.06 sec)

很多參數(shù)，網(wǎng)上對(duì)于connection lost這個(gè)問題有說改wait_timout也有說net_read_timeout和net_write_timeout的。

那么他們之間到底有什么區(qū)別呢？

簡(jiǎn)單來說：

wait_timeout代表的是前一個(gè)sql操作和下一個(gè)sql操作的間隔。
net_read_timeout和net_write_timeout涉及到網(wǎng)絡(luò)傳輸中的包的問題。通常情況下，一個(gè)sql語(yǔ)句由多個(gè)包組成?？蛻舳藢⒁粋€(gè)一個(gè)包傳給服務(wù)端，然后在服務(wù)端再將包組合起來變?yōu)閟ql語(yǔ)句執(zhí)行。如果網(wǎng)絡(luò)情況太差，導(dǎo)致包遲遲不全，無法組成一個(gè)完整的可執(zhí)行語(yǔ)句，mysql服務(wù)端就會(huì)放棄這個(gè)語(yǔ)句的執(zhí)行。而這個(gè)等待時(shí)間就是net_read/write_timeout。
read是服務(wù)器從客戶端讀取；write是服務(wù)器向客戶端寫。
這兩個(gè)參數(shù)都是session level的，可以在每次連接的時(shí)候?qū)ζ溥M(jìn)行更改。
此外，需要注意的是，如果問題出在傳輸執(zhí)行一個(gè)sql的過程中時(shí)，使用ping是無法解決的。Mysql協(xié)議不允許客戶端在一次性數(shù)據(jù)沒有傳輸完畢的時(shí)候，向服務(wù)器傳輸其他的請(qǐng)求。（python里面的處理就是，如果使用了ping就自動(dòng)斷開了本次的執(zhí)行操作）

This for example typical cause of aborted connections while using Sphinx with large data sets and large buffers. While indexing Sphinx performs sorts and flushes buffers to the disk every so often which can take long enough to trigger default net_write_timeout on the server side.

You could ask why server does not do any flow control and can’t find out client just is just busy and it is not network issue – well this comes from simplicity of MySQL protocol which does not allow client to talk to the server until full result set is fetched. This is also the reason why you can’t stop fetching for ordinary statements and mysql_free_result will have to complete the fetch internally.

Is there any way to tell the server you need more time besides increasing net_write_timeout ? Not what I know of. You can’t use something like mysql_ping because connection is in the stage of getting the data. You can’t even fetch couple of rows every few seconds to show you’re fetching data because there is buffering happening inside MySQL client library so you never know when real network read will happen.

具體改什么還要視自己的情況而定：

詳細(xì)的net write timeout和wait timeout的比較