雑なメモ書き

気楽にいきます

go-mysqlの接続あたりをみてみる(1)

Ping付きのServerを立てる

package main

import (
    "context"
    "database/sql"
    "io"
    "log"
    "net/http"
    "time"

    _ "github.com/go-sql-driver/mysql"
)

var ctx = context.Background()

func main() {
    db, err := sql.Open("mysql", "root:mysql@/mysql")
    if err != nil {
        log.Fatal(err)
    }

    ctx, cancel := context.WithTimeout(ctx, 1*time.Second)
    defer cancel()

    if err := db.PingContext(ctx); err != nil {
        log.Fatal(err)
    }
    helloHandler := func(w http.ResponseWriter, req *http.Request) {
        io.WriteString(w, "Hello, world!\n")
    }

    http.HandleFunc("/hello", helloHandler)
    log.Fatal(http.ListenAndServe(":8080", nil))
}

tcpdumpしてみる

  • server -> mysql: SYN-SENT
    • フラグ: S
    • シーケンス番号: 957900894
  • mysql -> server: SYN-RECEIVED
    • フラグ: [S.]
    • シーケンス番号: 957900895
  • server -> mysql: ESTABLISH
    • フラグ: [.]
    • ack: 1
> sudo tcpdump -i any  port 3306

17:39:23.147871 IP localhost.52841 > localhost.mysql: Flags [S], seq 957900894, win 65535, options [mss 16344,nop,wscale 6,nop,nop,TS val 1063241208 ecr 0,sackOK,eol], length 0
17:39:23.147915 IP localhost.mysql > localhost.52841: Flags [S.], seq 3498215316, ack 957900895, win 65535, options [mss 16344,nop,wscale 6,nop,nop,TS val 1063241208 ecr 1063241208,sackOK,eol], length 0
17:39:23.147925 IP localhost.52841 > localhost.mysql: Flags [.], ack 1, win 6379, options [nop,nop,TS val 1063241208 ecr 1063241208], length 0

wiresharkpingをみる

request

Frame 11: 61 bytes on wire (488 bits), 61 bytes captured (488 bits) on interface 0
Null/Loopback
Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1
Transmission Control Protocol, Src Port: 53783, Dst Port: 3306, Seq: 91, Ack: 90, Len: 5
MySQL Protocol
    Packet Length: 1
    Packet Number: 0
    Request Command Ping
        Command: Ping (14)

response

Frame 13: 67 bytes on wire (536 bits), 67 bytes captured (536 bits) on interface 0
Null/Loopback
Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1
Transmission Control Protocol, Src Port: 3306, Dst Port: 53783, Seq: 90, Ack: 96, Len: 11
MySQL Protocol
    Packet Length: 7
    Packet Number: 1
    Affected Rows: 0
    Server Status: 0x0002
    Warnings: 0

Pingのソースを追う

  • ここを見ると
  • cachedOrNewConn
  • alwaysNewConn
  • 2種類があることがわかる
// PingContext verifies a connection to the database is still alive,
// establishing a connection if necessary.
func (db *DB) PingContext(ctx context.Context) error {
    var dc *driverConn
    var err error

    for i := 0; i < maxBadConnRetries; i++ {
        dc, err = db.conn(ctx, cachedOrNewConn)
        if err != driver.ErrBadConn {
            break
        }
    }
    if err == driver.ErrBadConn {
        dc, err = db.conn(ctx, alwaysNewConn)
    }
    if err != nil {
        return err
    }

    return db.pingDC(ctx, dc, dc.releaseConn)
}
  • 更に追うと
  • connメソッドの中でfreeConnを使い回していることが分かる
   // Prefer a free connection, if possible.
    numFree := len(db.freeConn)
    if strategy == cachedOrNewConn && numFree > 0 {
        conn := db.freeConn[0]
        copy(db.freeConn, db.freeConn[1:])
        db.freeConn = db.freeConn[:numFree-1]
        conn.inUse = true
        db.mu.Unlock()
        if conn.expired(lifetime) {
            conn.Close()
            return nil, driver.ErrBadConn
        }