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
}

本文来自:猎豹移动技术博客

感谢作者:郝冠伟

查看原文:database/sql: Stmt的使用以及坑

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。