后台用了一年多,现在查询报表变得很卡,大概要15~180秒,运营经常反映报表加载不出来,我去服务器一看日志,原来查询花了200多秒,nginx直接超时了……
先优化一下 SQL
我的想法是优化报表的 SQL 语句,因为我发现查个 sql 居然加起来要 10几秒。
我们的 sql 分为2条,一条是先查总数,这是为了下一步分页用。另一条是查详情。
查总数的 sql 我看了下,index 都有用到,但是在 explain 中多出了 using temporary; using file sort; 这2项。于是我把 group by date 去掉,这2项就没了。
于是吭哧吭哧地把数据都加载出来,然后在代码里 group by,虽然比较费内存和 CPU,但总比让数据库做好啊对不对?
后来想着对第二条也这样优化,但是发现不行,因为第二条有个分页,不 group by 的话,就没法分页了……这个暂时没想到替代方法。可怜代码已经写了2小时了,只好先不用了。
代码写完了,本地测试一下,发现查询速度从 9s 降到 1s,果然有效! 于是赶紧弄上服务器,然后一试,变成 32s 了!而且 cpu 、内存狂涨! 无语了,赶紧回滚了。
事后分析,大概是因为数据量太大,服务器也处理不过来了……
试试缓存?
这事只好作罢,心里想,慢点就慢点吧,反正也是自己人用。 但是渠道那边又来找了,说是我们的 api 拉取超时了。这下就比较难办了,渠道那边超时,可能会影响我们正常的业务。况且这种现象如果听之任之,最终可能引发雪崩效应,到时就麻烦了。
但是现在数据库优化很蛋疼,怎么办呢?这时还是应该从业务角度考虑,渠道要的数据并不是实时数据,所以完全可以用缓存顶一下嘛。
django 的话,直接用 django-cacheops 就行了,挺好用的,直接在 queryset 后面加个 .cache() 就可以启用缓存了,当然配置里面要设置一下,不然它默认对所有查询都缓存就不好了,毕竟运营要看到的是实时数据。
代码改好了,部署上去,加载时间立即缩短了10倍!而且不是光 api 接口,连报表的查询也大大缩短了!真是意外的惊喜啊。
所以从这件事可以总结出2个道理: 1. sql 查询变慢了,不一定是这条 sql 本身,可能是慢查询太多,导致它被拖累了 2. 缓存一定要用上,减少数据库的压力是非常有必要的。
redis 的问题
不过,刚高兴没多久呢,也就1、2分钟吧,接口就报 500 错误了,异常信息提示 Connection closed by server
,郁闷,赶紧回滚。然后就在本地测,结果一点问题没有。我猜可能是配置不一样,但具体是哪个参数我也不知道了。
然后就是上网查资料,发现一个参数 timeout 貌似有用。分别在本地和服务器端看了下,原来本地 timeout = 0,服务器是 timeout = 60 。把它改过来之后,终于正常了。
不过呢,最近遇到的 redis 的问题挺多的,比如服务器上经常遇到 use of closed network connection
,这个就挺郁闷的,完全不知道怎么下手。
现在疏理一下,timeout 是 redis 用来控制客户端连接的。如果 timeout > 0 的话,如果客户端持续 timeout 秒没有活动,redis 就会关闭这个连接。
如果设置成 timeout = 0,就表示永远不关闭。
问题在于,admin 服务器请求量小,确实可能 60s 里没有活动,redis 可以关闭它,但是广告服务器请求量很大,为什么还是会关闭呢?