亲身经历的痛--database/sql: Stmt的使用以及坑

前言

众所周知,golang操作数据库,是通过database/sql包,以及第三方的实现了database/sql/driver接口的数据库驱动包来共同完成的。

其中database/sql/driver中的接口Conn和Stmt,官方交给第三方实现驱动,并且是协程不安全的。官方实现的database/sql包中的DB和Stmt是协程安全的,因为内部实现是连接池。

如何使用

刚开始接触database/sql包,并且网上的很多使用例子,都类似下面这种方式:

db,err := sql.Open("mysql","test:test@tcp(127.0.0.1:3306)/abwork?charset=utf8")
rows,err := db.Query("select id,name,age from test limit 0,5")
res, err := db.Exec("INSERT test SET name=?,age =?", "xiaowei", 18)

或者下面这种:

stmt,err := db.Prepare("insert into test(name,age)values(?,?)")
defer stmt.Close()
stmt.Exec("张三",20)

其实仔细看database/sql的源码实现,上面两个例子的方式都是一样的。 首先看一下第一种方式的使用,以下是golang1.3版本database/sql包sql.go中,type DB的exec方法,第899-906行的实现:

dc.Lock()
si, err := dc.ci.Prepare(query)
dc.Unlock()
if err != nil {
    return nil, err
}
defer withLock(dc, func() { si.Close() })
return resultFromStatement(driverStmt{dc, si}, args...)

可以看到,dc.ci.Prepare(query)这句,会先创建一个预处理语句,然后调用resultFromStatement方法执行sql操作。而Query方法,最终的实现也是一样。

我们再看第二种方式,先调用Papare方法生成一个Stmt,在Prepare中,会调用dc.prepareLocked(query),请看sql.go中844行,而最终dc.prepareLocked(query)这个方法,还是会调用dc.ci.Prepare(query)创建预处理语句,请看251行。接下来,就是调用Stmt的Exec或者Query方法,而最终这两个方法还是会调用resultFromStatement方法去执行。

那么两种方式的相通之处,都是会预处理,不同的是使用db.Prepare会额外的创建Stmt,由Stmt实例在去处理具体的数据库操作。

那么大家也看出来了,如果不是批量的操作,是没必要使用db.Papare方法的,否则即多了Stmt创建和关闭的性能开销,又多写了两行代码,有点得不偿失。如果是批量的操作,那么毋庸置疑,肯定是db.Papare拿到Stmt,再由Stmt去执行sql,这样保证批量操作只进行一次预处理。

发现的问题

按照上文说的,在实际使用过程中,也发现了一些问题。

DB默认的最大open连接数是0,而最大空闲数是defaultMaxIdleConns = 2。在数据库操作很频繁的实际使用场景中,尤其是一波又一波访问高峰不间断来临的时候,数据库性能会不断的消耗在连接的创建和销毁上,这是很拖累数据和和机器的,所以我们根据mysql的max_user_connections参数,设置合理的值之后,这种现象很快的稳定下来。

而连接稳定,在使用db.Exec和Query时,每次都会向数据库创建预处理语句。虽说在批量操作时使用db.Papare拿到Stmt然后执行批量操作更好,但我们的实际业务场景中,涉及到批量操作的地方很少(可以说没有),那么问题来了。。。不是挖掘机哪家强,而是该如何合理的使用Stmt!

众所周知,我们的手游《暖暖环游世界》非常适合广大妹纸和宅男朋友们暖身解闷,ios版请点击此处下载,android版请点击此处下载。所以数据流量也非常的大。在大推期间,访问量不断的刷新最高值,mysql的压力爆表,经过分析,除了刚开始最大open连接数和最大空闲连接数设置不当,导致mysql连接方面极大的性能开销外,还有就是这第二个问题,不断的创建预处理语句又关闭销毁,也是性能杀手。(暖暖推广期间毛老师丁丁刘做了大量的mysql优化,详情请看《mysql在高内存、IO利用率上的几个优化点》)

虽说批量操作不多,但我们所有的操作都是相同的sql语句,没有在用户使用过程中,根据用户不同行为,生成不同sql语句的业务场景。

所以我们决定改变使用方式,直接在程序初始化的时候,通过db.Papare创建Stmt,在运行期间,总是由预先创建好的Stmt去执行sql,这样就可以达到预处理语句复用的效果,应该能够很大的减少mysql预处理语句上的性能开销。

而且Stmt的源码实现,也是使用的DB连接池,管理着一批已经创建预处理语句的连接,具体就是下面的struct:

type connStmt struct {
    dc *driverConn
    si driver.Stmt
}

可以看见,每一个连接driverConn对应着一个预处理driver.Stmt。

而Stmt在执行Exec和Query等方法时,会先执行connStmt方法(注意,这是及其重要的一个方法):

func (s *Stmt) connStmt() (ci *driverConn, releaseConn func(error), si driver.Stmt, err error)

拿到type connStmt struct中的driverConn和driver.Stmt,再由resultFromStatement方法去做具体的数据库操作。

Stmt的坑

上文所说的方法,经过实际使用,确实会降低mysql在预处理语句上的性能开销,但问题来了,Stmt的坑是啥呢。。。

回到connStmt方法,Stmt通过该方法获得driverConn和driver.Stmt,那么具体的逻辑是咋样的呢,请看以下源码分析:

func (s *Stmt) connStmt() (ci *driverConn, releaseConn func(error), si driver.Stmt, err error) {
    if err = s.stickyErr; err != nil {
        return
    }
    s.mu.Lock()
    if s.closed {
        s.mu.Unlock()
        err = errors.New("sql: statement is closed")
        return
    }

    // In a transaction, we always use the connection that the
    // transaction was created on.
    if s.tx != nil { // 这里如果有事务,则通过事务tx获取连接(不是本文重点)
        s.mu.Unlock()
        ci, err = s.tx.grabConn() // blocks, waiting for the connection.
        if err != nil {
            return
        }
        releaseConn = func(error) {}
        return ci, releaseConn, s.txsi.si, nil
    }
        // 重点开始
    var cs connStmt
    match := false
    for i := 0; i < len(s.css); i++ { // 如果你翻开Stmt源码,就会发现css是type connStmt的一个slice:css []connStmt
        v := s.css[i]
        _, err := s.db.connIfFree(v.dc) // Stmt首先会从css中找到当前空闲的连接
        if err == nil {
            match = true // 如果有空闲连接,将match设为true
            cs = v
            break
        }
        if err == errConnClosed { // 顺便还处理下关闭的连接,从css中remove掉
            // Lazily remove dead conn from our freelist.
            s.css[i] = s.css[len(s.css)-1]
            s.css = s.css[:len(s.css)-1]
            i--
        }

    }
    s.mu.Unlock()

    // Make a new conn if all are busy.
    // TODO(bradfitz): or wait for one? make configurable later?
    if !match { // 注意match,如果css中没有空闲连接
        dc, err := s.db.conn() // 就会从db的连接池中取
        if err != nil {
            return nil, nil, nil, err
        }
        dc.Lock()
        si, err := dc.prepareLocked(s.query) // 取到连接,这里加把锁去创建预处理语句(注意,这里是重点!!!)
        dc.Unlock()
        if err != nil {
            s.db.putConn(dc, err) // 将连接放到池中
            return nil, nil, nil, err
        }        s.mu.Lock()
        cs = connStmt{dc, si} // 创建connStmt
        s.css = append(s.css, cs) // 将connStmt加到css
        s.mu.Unlock()
    }

    conn := cs.dc
    return conn, conn.releaseConn, cs.si, nil
}

看完上面的源码,相信大家还是没太明白问题出在哪。

我们先明确一件事,那就是Stmt中css里的driveConn,和db连接池中的conn,有可能数量不同,状态不同,有可能db连接池中被干掉的连接,也有可能还在css中。

更有可能,s.db.connIfFree(v.dc)时还不是空闲的连接,在执行到if !match 时,已经是了,然后被dc, err := s.db.conn()从连接池中取出,接下来就到了源码分析中,重点的那句,那里不管连接有木有处于css中,都会创建一个新的connStmt,然后再存入css中。这么一来,导致的问题就是不断有driverConn创建新的driver.Stmt,然后存入css却不销毁。同一个driverConn,有可能会对应着成千上万不同的driver.Stmt。

而我们之所以会发现这个坑,就是在观察mysql状态时,发现Prepared_stmt_count的值,会随着时间的推移,不断的上涨,直到达到max_prepared_stmt_count的最大值,然后代码报错。而这个时候,我们只能通过杀掉进程重启,使Prepared_stmt_count归0,来缓解这个问题。

后续

发现这个问题后,我们分别在: https://code.google.com/p/go/issues/detail?id=8376 https://groups.google.com/forum/#!topic/golang-nuts/bbFX0qQvsB0 这两个地方提出了问题,然后也得到了热心朋友的支持。

ma…@joh.to(找不到对方的真实邮箱,有问题的朋友可以在golang-nuts,通过回复发送邮件联系)帮我们在: https://codereview.appspot.com/116930043 给官方提了codereview和临时解决方案,遗憾的是他的解决方案依然存在这个问题。

最终,database/sql的作者,Brad Fitzpatrick大神亲自回复,明确了问题,然后在: https://code.google.com/p/go/source/detail?r=fdb52a28028a 里做了解决,并且在go1.4中会得到修复。

以下是go1.4beta1中,Brad Fitzpatrick大神的修改,可以对比下旧的实现:

func (s *Stmt) connStmt() (ci *driverConn, releaseConn func(error), si driver.Stmt, err error) {
    if err = s.stickyErr; err != nil {
        return
    }
    s.mu.Lock()
    if s.closed {
        s.mu.Unlock()
        err = errors.New("sql: statement is closed")
        return
    }

    // In a transaction, we always use the connection that the
    // transaction was created on.
    if s.tx != nil {
        s.mu.Unlock()
        ci, err = s.tx.grabConn() // blocks, waiting for the connection.
        if err != nil {
            return
        }
        releaseConn = func(error) {}        return ci, releaseConn, s.txsi.si, nil
    }

    for i := 0; i < len(s.css); i++ {
        v := s.css[i]
        _, err := s.db.connIfFree(v.dc)
        if err == nil {
            s.mu.Unlock()
            return v.dc, v.dc.releaseConn, v.si, nil
        }
        if err == errConnClosed {            // Lazily remove dead conn from our freelist.
            s.css[i] = s.css[len(s.css)-1]
            s.css = s.css[:len(s.css)-1]
            i--
        }

    }
    s.mu.Unlock()

    // If all connections are busy, either wait for one to become available (if
    // we've already hit the maximum number of open connections) or create a
    // new one.
    //
    // TODO(bradfitz): or always wait for one? make configurable later?
    dc, err := s.db.conn()
    if err != nil {
        return nil, nil, nil, err
    }

    // Do another pass over the list to see whether this statement has
    // already been prepared on the connection assigned to us.
    s.mu.Lock()
    for _, v := range s.css { // 这里又做了一次css的检查,看连接是否已经预处理过了
        if v.dc == dc {
            s.mu.Unlock()
            return dc, dc.releaseConn, v.si, nil
        }
    }
    s.mu.Unlock()

    // No luck; we need to prepare the statement on this connection
    dc.Lock()
    si, err = dc.prepareLocked(s.query)
    dc.Unlock()
    if err != nil {
        s.db.putConn(dc, err)
        return nil, nil, nil, err
    }
    s.mu.Lock()
    cs := connStmt{dc, si}
    s.css = append(s.css, cs)
    s.mu.Unlock()

    return dc, dc.releaseConn, si, nil
}

原文发布于微信公众号 - Golang语言社区(Golangweb)

原文发表时间:2016-03-23

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏程序员宝库

购物网站的 redis 相关实现(Java)

本文主要内容: 登录cookie 购物车cookie 缓存数据库行 测试 必备知识点: WEB应用就是通过HTTP协议对网页浏览器发出的请求进行相应的服务器或者...

51414
来自专栏程序员的知识天地

使用 JS 实现一个本地数据库

前端很多时候还是需要保存一些数据的,这里的保存指的是长久的保存。以前的思想是把数据保存在 Cookie 中,或者将 key 保存在 Cookie 中,将其他数据...

4272
来自专栏C/C++基础

Google C++编程风格指南(一)之头文件的相关规范

一个良好的编程规范和风格是一名程序猿成熟的标志。规范的编码可以减少代码冗余,降低出错概率,便于代码管理和代码交流等等,事实上,其作用远不止这些,我们要牢记编码规...

1631
来自专栏java架构师

BAT美团滴滴java面试大纲(带答案版)之三:多线程synchronized

继续面试大纲系列文章。   从这一篇开始,我们进入ava编程中的一个重要领域---多线程!多线程就像武学中对的吸星大法,理解透了用好了可以得道成仙,俯瞰芸芸众生...

30910
来自专栏一枝花算不算浪漫

[Redis]Redis 概述及基本使用规范.

5118
来自专栏Java Web

Java 面试知识点解析(二)——高并发编程篇

在遨游了一番 Java Web 的世界之后,发现了自己的一些缺失,所以就着一篇深度好文:知名互联网公司校招 Java 开发岗面试知识点解析 ,来好好的对 Jav...

5337
来自专栏玄魂工作室

Python爬虫之urllib模块1

Python爬虫之urllib模块1 本文来自网友投稿。作者PG,一个待毕业待就业二流大学生。玄魂工作室未对该文章内容做任何改变。 因为本人一直对推理悬疑比较感...

3316
来自专栏Python中文社区

OpenStack中的RESTful API是如何实现的?

OpenStack作为一个开源的IaaS平台,各个组件和服务之间的消息传递都是通过RESTfulAPI和RPC传递,这里主要讲讲它是如何实现REST的。由于大家...

3388
来自专栏Java技术交流群809340374

史上最全Java面试266题:算法+缓存+TCP+JVM+搜索+分布式+数据库

以上是总结出的最全Java面试题目,以下是最新总结出的BAT面试java必考题目和答案。

3040
来自专栏嵌入式程序猿

Polyspace不认识Interrupt,肿么办?

曾经在公众号中介绍过优秀的软件验证工具Polyspace,有好多猿友在交流群里咨询这个软件的问题,今天我们就典型的如何处理中断来给大家介绍下。 ...

3084

扫码关注云+社区

领取腾讯云代金券