登录
  • 人们都希望被别人需要 却往往事与愿违
  • 全中国只有一所学校, 就是党校 -- 其它的学校都是分校!@陈丹青 (艺术家)

Python长期连接数据库的最不佳实践:Lost connection to MySQL server during query

不懂编程 Benny小土豆 1065次浏览 5028字 4个评论
文章目录[显示]
这篇文章在 2018年06月12日13:24:10 更新了哦~

问题来源

前几天用Tornado做Web服务器起了一个简单的以表格显示数据库中内容的网页,然后把它放到lab服务器上做demo。卧病几天之后打开邮箱,发现mentor邮件说表格中不显示数据。赶紧登上服务器screen回去看下,发现抛了个这么异常:Lost connection to MySQL server during query

Python长期连接数据库的最不佳实践:Lost connection to MySQL server during query

当然也说不定会是MySQL server has gone away

Python:我这么爱你,你为什么要离开我?

MySQL:你丫八个小时都不跟俺说一句话,想起来了就冷不丁的冒一句,然后就消失,就这样还是俺真爱?哼!

Python:……(好吧,被你发现了,我就是玩玩而已你还认真了,真是的?)

以上内容纯属虚构,请勿对号入座(咋想也没办法对号入座啊)

问题原因

造成这个异常的原因其实非常简单,就是基本上没啥人访问我的demo link,导致连接对象超过了wait_timeout的默认28800秒(即8小时),然后MySQL就关闭了这个连接。由于TCP的特性,Python的驱动并不会知道被甩了,当有人访问demo link也就是进行一次查询的时候,就会触发异常,于是表格自然就是空白的啦。

为了更进一步的说明这个问题并提供若干最不佳实践,咱先简单的说说这段代码……GitHub可以戳下面的触手猫(commit为959ce78f854894397a75bad0efef4ef0de06b7ca)
触手猫十周年啦

开头

首先在开头引入几个库,然后新建两个全局的对象:一个是用于数据库连接的con,一个是查询游标cur

con = mysql.connector.connect(host='127.0.0.1', user='root', password='root', database='san')
cur = con.cursor()

路由

在make_app函数中我们可以看到定义了俩路由,/对应Index类,/list/对应Retrive类。

Retrive类重载了get方法,返回的是一个函数get_data,而get_data则是进行数据库相关操作的函数,代码如下:

def get_data():
    cur.execute('SHOW COLUMNS from switch_device')
    col_data = cur.fetchall()
    col_field = [i[0] for i in col_data]
    cur.execute('SELECT * FROM switch_device')
    data = cur.fetchall()
    
    bulk_dic = []
    for i in range(len(data)):
        es_dic = dict(zip(col_field, data[i]))
        bulk_dic.append(es_dic)
    return json.dumps(bulk_dic)

我不由得想起了我最爱的列表推导……反正就是查询了两次,根据列名和数据把表中的内容组成了一个json。可惜啊我不知道DictCursor

入口

入口非常简单,就是启动tornado而已。

总结

通篇可以看到有这么几个特点:

  1. cur和con是全局的,只要Python脚本在运行,那么这俩对象的id(内存地址)就不会改变,无论多少次的页面刷新(数据库查询操作)都是由cur这个全局对象执行的
  2. 并没有对connection进行close。脚本在收到相应的信号量时会执行对应的行为,比如SIGINT也即Ctrl+C,自然就退出了,连接自然也就被释放了。

再一次说明问题原因

再一次总结出错原因:demo太冷门没人看,连接闲置时间超过wait_timeout,MySQL关闭连接,然后再次触发查询的时候,自然而然就会报错了。

针对这个问题,我想了几种个人觉得不咋样的解决方法,所以被称之为“最不佳实践”。当然提前说一个比较好的办法,那就是用数据库框架啊,类似SQLAlchemy什么的,这类框架应该会处理这类问题的;还有就是,如果对应的驱动程序提供了类似automatic reconnection这种特性,那也好办多啦

注意:为了方便叙述,下面的get_data()就是指代数据库操作啦!

方法0:crontab定期重启程序,apscheduler定期新建连接

喂喂喂,别想了,这不就是为了解决一个问题,引入了更多更多非常复杂的、并且不可控、并且移植性兼容性差的问题了吗?

这么做的话,还是老老实实回家种田吧。

方法1:增加wait_timeout时间

既然数据库小姐默认的“生气时间”是28800秒,那咱简单的给它调大点不就好了。

Python:亲爱的,你以后要多点耐心,小不忍则乱大谋啊~
MySQL:好吧好吧,那我就多点耐心。
一年之后……
Python:亲爱的,干嘛呢?
MySQL:老娘不认识你,丨。
Python吸取教训,不能冷落小姐姐太久,于是再次通过自己的阴谋诡计取得小姐姐的好感与信任。
Python:亲爱的,你以后要多点耐心,小不忍则乱大谋啊~
MySQL:好吧好吧,那我就多点耐心。
十几天之后……
Python:亲爱的,干嘛呢?
MySQL:忙着呢,没空理你。
Python:…………(算了,干嘛一棵树上吊死)

简单的在得到游标之后来这么一句:

cur.execute('SET SESSION WAIT_TIMEOUT = 2147483')

注:

2147483(约24天)是Windows平台的MySQL的WAIT_TIMEOUT最大值,其他平台为31536000(一年)

如果有必要的话咱还可以SET GLOBAL WAIT_TIMEOUT = 2147483,不过你真的确定要global么,说不好DoS了,查完日志就该回家种田了啊。

但实际上这是一种想当然一拍脑门一跺脚的完全不现实的不经过仔细思考的解决方案。为啥捏,待我慢慢道来。

治标不治本

不,这个三级标题是错误的。这种解决方案不仅不治标,更不治本!甚至有损于数据库的性能。暂且不考虑平台兼容性(设置为31536000对Windows平台就是无效值了啊),如果真的就是连接限制了超过timeout的时间,然后突然有了一次访问,那还不是照样挂?

影响数据库性能

MySQL为什么要有这样一个参数?很简单啊,为了提高服务器性能啊。每维持一个连接都是需要耗费一定的内存和CPU资源的,关闭长期不使用的连接自然节约了资源,同时也避免了max_connections达到上限造成了DoS的结果。

即使我们把max_connections的值调到很高尽可能避免DoS,这对于服务器的性能也是不利的,需要更多内存,更多文件描述符。这同样也是一个既不治标也不治本的解决“解决方案”的方案。堆硬件?有那钱还不如打赏辛苦写作本文的俺呐!

注:max_connections默认值为151,上限为100000,同时还要受到文件描述符(用ulimit查看)的限制。

一句话总结

放弃吧,别用这种不太好的办法了。

方法2:每次刷新页面都新建连接-查询-关闭连接

既然问题在于连接限制,那咱就每次数据库相关的操作都新建连接-查询-关闭连接。代码也很简单,把全局的数据库连接移动到函数内:

con = mysql.connector.connect(host='127.0.0.1', user='root', password='root', database='san')
cur = con.cursor()
# 执行操作
con.close()
return some_value

这确实是一个非常简单的处理方式,鲁棒性很强,没有bug,绝对管用,不仅治标也治本。只是有一个问题,在某些场景下性能可能会有点差劲。

新建一次连接自然是比较费时、费资源的啦,说不定要0.1秒呢,那如果同时访问的人多了造成大并发的局面(tornado是多线程非阻塞的服务器,所以短时间内可能会创建很多个con对象),那可能速度就要稍微慢一点了。

不过这种方法确实好用,因为抓住了问题的本质:闲置连接被关闭

方法3:死循环捕获错误

既然上面的方法性能略差,那么我们就不要每次都新建连接,而是尽可能的用全局的con对象。那就配合一个死循环吧?代码大概如下:

while True:
    try:
        get_data()
    except mysql.connector.errors.OperationalError:
        con.reconnect()

看起来好像有点好,但是问题就是,这个While是阻塞的啊,你这阻塞了还咋让tornado运行啊,多线程还是多进程呗?

你看是吧,不仅没解决问题,反而引入了更高级的多线程多进程,要是多线程可能还得考虑线程安全的问题(这就得看驱动是怎么实现的connection对象了,不同语言、不同驱动未必相同哦)。

于是乎,为了解决连接对象的线程安全问题,我们引入了连接池……算了算了回家养猪吧(丁磊:来啊!跟我养猪啊)。

方法4:数据库的操作之前con.reconnect()

从方法3中我们发现了con.reconnect()这个神奇的小东西,那么我们在数据库操作之前执行这个,这样每次都会重新连接,那就可以啦。

con.reconnect()
get_data()

这个方法大概相当于方法2的改进版,每次操作没有重新新建con对象而是无论连接是否被MySQL关闭,都重新连接。没毛病,问题完美解决,只是也有一点点性能损失:

连接关闭了reconnect倒好,但是没关闭却reconnect了那不就浪费了嘛?

虽说如此,但是这个方法的性能会比方法2稍好一些。

方法5:数据库操作时try...except,异常重连

针对方法4,很容易就可以进行一下改进,那咱先毫无顾虑的执行,出问题了捕获异常就好了。

try:
    get_data()
except mysql.connector.errors.OperationalError:
    con.reconnect()
    get_data()

两次get_data()有点不雅啊,唉灵机一动:

try:
    cur.execute('select version()')
except mysql.connector.errors.OperationalError:
    con.reconnect()
finally:
    get_data()

或者不用finally直接开始写新的语句块,也是可以的!

随便执行个查询操作来看看连接有没有被关闭,然后finally无论怎么都会被执行的,好办法好办法,拍案叫绝!

只是也稍微有那么一丁点不好,白白的让数据库执行了一个没有用的查询,多多少少还是浪费了一丢丢资源啦。

于是我又灵机一动,诞生了方法6.

方法6:数据库操作之前进行con.ping(),异常重连

用一次con.ping()检测连接是否还在,不在的话就重新连接,finally里执行查询,哇(o゜▽゜)o☆

try:
    con.ping()
except mysql.connector.errors.InterfaceError:
    con.reconnect()
finally:
    get_data()

同上,不用finally直接开始写新的语句块,也是可以的!

这似乎是比较完美的解决方案啦。con.ping()的性能开销应该比较理想的。

需要注意的是,究竟要保持连接还是每次都新建连接是需要看具体的业务情况的。如果本来访问就比较少的话,每次新建也无妨;如果访问量很大,那也用不到考虑连接被关闭的问题了,当然那就该考虑每次建立连接的性能问题了……

需要注意的是,不同的库可能有一点不同,比如pymysql的con.ping()包含一个reconnect的参数,默认为True的时候就会自动重连。所以如果你使用pymysql,直接在数据库操作之前使用con.ping()就可以了。

后记

在写这篇博文之前我与某蜗牛童鞋尝试着讨论了一下这个问题,但是可能是我的语言表达能力不够强,造成她没理解我的问题,浪费了人家不少宝贵的时间,也没讨论出什么有用的结果,在此深表歉意

同时在StackOverflow上也没有什么好的参考资料,于是闭门造车搞出来了这么一篇“最不佳实践”。如果有啥更好的办法,欢迎评论补充哦~


文章版权归原作者所有丨本站默认采用CC-BY-NC-SA 4.0协议进行授权|
转载必须包含本声明,并以超链接形式注明原作者和本文原始地址:
https://www.bennythink.com/python-longpoll-db.html
喜欢 (2)
分享:-)
Benny小土豆
关于作者:
If you have any further questions, feel free to contact me in English or Chinese.
发表我的评论(代码和日志请使用Pastebin或Gist)
取消评论

                     

去你妹的实名制!

  • 昵称 (必填)
  • 邮箱 (必填,不要邮件提醒可以随便写)
  • 网址 (选填)
(4)个小伙伴在吐槽
  1. 连接池啊 小伙计 DBUtils
    Kios2018-04-16 10:36 回复
    • Benny小土豆
      ??请看方法3
      Benny小土豆2018-04-16 10:39 回复
  2. 居然这么长时间没个评论……我来水一下
    (后院有PR!)(另外烟花效果在我的 Chromium Build 里有渲染问题)
    布偶君2018-04-15 07:43 回复
    • Benny小土豆
      没评论还不是因为本站要黄啦~~~ 渲染问题嘛(这个可怎么办
      Benny小土豆2018-04-15 19:29 回复