Go 語言使用 MySQL 的常見故障分析和應對方法

語言: CN / TW / HK

圖片

導讀:很多同學在使用Go和資料庫打交道的過程中,經常會遇到一些異常不知道為什麼,本文從SQL連線池的原理進行分析,模擬了一些例子對異常的現象進行解讀分析,並給出一些常見的應對手段,期望能幫助到大家。

全文12795字,預計閱讀時間32分鐘

有很多同學遇到了 MySQL 查詢緩慢的問題,其可能表現為 SQL 語句很簡單,但是查詢耗時很長。可能是由於這樣一些原因所致。

1、資源未及時釋放

Go 的 sql 包使用的是長連線方式讓 Client 和 SQL Server 互動,為了避免 SQL Server 連結過多,一般會在 Client 端限定最大連線數。

下面是sql 的連線池的狀態圖(設定了最大開啟連線數的情況):

圖片

SQL Client 和 Server 互動後,有些結果返回的是一個流(Stream),此時的網路連線(Conn)是被 Stream 物件繼續使用的,Client 需要迭代讀取結果,讀取完成後應立即關閉流以回收資源(釋放 conn)。

比如最長用的DB.QueryContext 方法即是如此:

// QueryContext 查詢一些結果
// query:select * from test limit 10
func (db *DB) QueryContext(ctx context.Context, query string, args ...any) (*Rows, error)
type Rows struct{
    Close( ) error 
    ColumnTypes( ) ( [ ]*ColumnType, error) 
    Columns( ) ( [ ]string, error) 
    Err( ) error 
    Next( ) bool 
    NextResultSet( ) bool 
    Scan(dest ...any) error
}

當還有結果的時候(即Rows.Next()==true 時),說明還有結果未讀取出來,此時必須呼叫 Rows.Close() 方法來對流進行關閉以釋放連線(讓當前連線變為空閒狀態以 讓其他邏輯可以使用該連線)。

1.1 實驗1-不呼叫 Rows.Close()

若不呼叫 Close 又會怎樣呢?下面做一個實驗來觀察一下:

select * from user;
+----+-------+---------------------+----------+--------+
| id | email | register_time       | password | status |
+----+-------+---------------------+----------+--------+
|  2 | dw    | 2011-11-11 11:01:00 | d        |      0 |
+----+-------+---------------------+----------+--------+
1 row in set (0.03 sec)
package main
import (
   "context"
   "database/sql"
   "encoding/json"
   "fmt"
   "sync"
   "time"
   _ "github.com/go-sql-driver/mysql"
)
func main() {
   db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test")
   if err != nil {
      panic(err)
   }
   db.SetMaxOpenConns(1)
   // 啟動一個單獨的協程,用於輸出 DB 的狀態資訊
   go func() {
      tk := time.NewTicker(3 * time.Second)
      defer tk.Stop()
      for range tk.C {
         bf, _ := json.Marshal(db.Stats())
         fmt.Println("db.Stats=", string(bf))
      }
   }()
   // 啟動 10 個協程,同時查詢資料
   var wg sync.WaitGroup
   for i := 0; i < 10; i++ {
      wg.Add(1)
      go func(id int) {
         defer wg.Done()
         queryOne(id, db)
      }(i)
   }
   wg.Wait()
   fmt.Println("finish")
}
func queryOne(id int, db *sql.DB) {
   start := time.Now()
   rows, err := db.QueryContext(context.Background(), "select * from user limit 1")
   if err != nil {
      panic(err)
   }
   // defer rows.Close() 
   // 沒有從 Rows 裡讀取結果,也沒有呼叫 rows.Close
   fmt.Println("id=", id, "hasNext=", rows.Next(), "cost=", time.Since(start))
}

執行後將輸入如下內容:

id= 0 hasNext= true cost= 9.607371ms
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}

解讀一下狀態資料:

{
    "MaxOpenConnections": 1,  // 最大開啟連線數,和程式碼設定的一致,是 1
    "OpenConnections": 1,     // 已開啟的連線數    
    "InUse": 1,               // 正在使用的連線數
    "Idle": 0,                // 空閒連線數
    "WaitCount": 9,           // 等待連線數
    "WaitDuration": 0,        // 等待總耗時(在等待退出時才計數)
    "MaxIdleClosed": 0,       // 超過最大 idle 數所關閉的連線總數 
    "MaxIdleTimeClosed": 0,   // 超過追到 idle 時間所關閉的連線總數
    "MaxLifetimeClosed": 0    // 超過最大生命週期所關閉的連線總數
}

從上面的輸出可以看出,總共啟動了 10 個協程,只有一個協程的 queryOne 方法成功執行了,其他 9 個協程的都是處於等待狀態。

1.2 實驗2-呼叫 Rows.Close()

若將 queryOne 方法的,“// defer rows.Close()” 的註釋去掉,即變為:

func queryOne(id int, db *sql.DB) {    
    start := time.Now()    
    rows, err := db.QueryContext(context.
Background(), "select * from user limit 1")    
    if err != nil {       
       panic(err)    
    }    
    defer rows.Close() // 打開了此處的註釋,Close 方法會釋放資源    
    fmt.Println("id=", id, "hasNext=", rows.Next(), "cost=", time.Since(start)) 
}

執行後,會輸出如下內容:

# go run main.go
id= 9 hasNext= true cost= 4.082448ms
id= 3 hasNext= true cost= 5.670052ms
id= 8 hasNext= true cost= 5.745443ms
id= 5 hasNext= true cost= 6.238615ms
id= 6 hasNext= true cost= 6.520818ms
id= 7 hasNext= true cost= 6.697782ms
id= 4 hasNext= true cost= 6.953454ms
id= 1 hasNext= true cost= 7.1079ms
id= 0 hasNext= true cost= 7.3036ms
id= 2 hasNext= true cost= 7.464726ms
finish

上述輸出結果說明所有的 10 個協程都成功執行完成。

1.3 實驗3- 使用帶超時的 Context

補充,上述呼叫 QueryContext 方法的時候,使用的是context.Background(),所以是一致阻塞的效果。實際在使用的時候,傳入的 context 一般是有超時時間或者支援取消的,類似這樣:

func  queryOne(id int, db *sql.DB) { 
    start := time.Now() 
    ctx,cancel:=context.WithTimeout(context.Background(),time.Second) // 關鍵     
    defer cancel()  // 關鍵。若將此行替換為 _=cancel,又是另外一種結果了
    rows, err := db.QueryContext(ctx , "select * fro m user  limit 1") 
     if err != nil { 
       // panic (err) 
       fmt.Println("BeginTx failed:",err)        
       return 
    } 
    // defer rows.Close () // 打開了此處的注 釋,Close  方法會釋放資源     
    fmt.Println("id=" , id, "hasNext=", rows.Next(), "cost=", time.Since (start)) 
}

執行後可以觀察到,所有的 10 個協程也都執行成功了:

id= 9 hasNext= true cost= 1.483715ms
id= 3 hasNext= true cost= 175.675µs
id= 6 hasNext= true cost= 1.277596ms
id= 1 hasNext= true cost= 174.307µs
id= 7 hasNext= true cost= 108.061µs
id= 4 hasNext= true cost= 115.072µs
id= 2 hasNext= true cost= 104.046µs
id= 0 hasNext= true cost= 96.833µs
id= 8 hasNext= true cost= 123.758µs
id= 5 hasNext= true cost= 92.791µs
finish

由於 context 是帶超時的,而且執行完成後會呼叫 defer cancel() 將 ctx 取消,所以即使沒有使用 rows.Close 釋放資源,ctx 在被cancel後也會立即釋放資源。

若是將 defer cancel() 換為 _=cancel ,又是另外一種結果了,我們將看到的是:

d= 9 hasNext= true cost= 2.581813ms
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded

1.4 解決方案

小結:

  • 我們應該使用QueryContext 這類支援傳入 context 的函式,並且傳入帶超時控制的 context,並且在邏輯執行完成後,應使用 defer 方法將 context 取消。

  • 對於返回一個流型別的結果,使用完成後一定需要呼叫 Close 方法以釋放資源。

  • 所有 *sql.DB、*sql.Tx、*sql.Stmt 的返回 *Conn、*Stmt、*Rows 這幾種型別的都需要 Close:

type DB/Tx/Stmt struct{
   Conn(ctx context.Context) (*Conn, error)
   Prepare(query string) (*Stmt, error)
   PrepareContext(ctx context.Context, query string) (*Stmt, error)
   Query(query string, args ...any) (*Rows, error)
   QueryContext(ctx context.Context, query string, args ...any) (*Rows, error)
}

要避免該問題出現,一般只需要如上例,新增上 defer rows.Close() 即可。

若是使用的 GDP 框架,讀取 Rows 結果,可以使用 mysql.ReadRowsClose 方法,在讀取完成後,會自動的 Close。比如:

type user struct {     
   ID           int64     `ddb:"id"`     
   Status       uint8     `ddb:"status"` 
}
func readUsers(ctx context.Context)([]*user,error)
    rows, err := cli.QueryContext(ctx, "select * from user where status=1 limit 5")     
    if err != nil {         
       return nil,err     
    }     
    var userList []*user     
    err=mysql.ReadRowsClose(rows, &userList)     
    return   userList,err
}

或者是 QueryWithBuilderScan:

b := &SimpleBuilder{
  SQL: "SELECT id,name from user where id=1",
 }
 type user struct{
  Name string `ddb:"name"`
  ID int `ddb:"id"`
 }
 var us []*user
 err = mysql.QueryWithBuilderScan(ctx, client, b, &us)

2、事務不完整

開啟一個事務(Tx)後,必須提交(Commit)或者回滾(Rollback),否則會事務不完整,也會導致 Client 端資源(連線)不釋放。

func (db *DB) BeginTx(ctx context.Context, opts *TxOptions) (*Tx, error)
type Tx 
func (tx *Tx) Commit() error    // 提交事務
func (tx *Tx) Rollback ( ) error  // 回滾事務
func (tx *Tx) Exec(query string, args ...any) (Result, error) 
func (tx *Tx) ExecContext(ctx context.Context, query string, args ...any) (Result, error) 
func (tx *Tx) Prepare(query string) (*Stmt, error) 
func (tx *Tx) PrepareContext(ctx context.Context, query string) (*Stmt, error) 
func (tx *Tx) Query(query string, args ...any) (*Rows, error) 
func (tx *Tx) QueryContext(ctx context.Context, query string, args ...any) (*Rows, error) 
func (tx *Tx) QueryRow(query string, args ...any) *Row 
func (tx *Tx) QueryRowContext(ctx context.Context, query string, args ...any) *Row 
func (tx *Tx) Stmt(stmt *Stmt) *Stmt 
func (tx *Tx) StmtContext(ctx context.Context, stmt *Stmt) *Stmt

2.1 和 PHP 的區別

另外需要注意的是,使用 Go標準庫的 DB.BeginTx 方法開啟一個事務後,會得到一個事務物件 Tx,要讓一批 SQL 在一個事務裡執行需要讓這些 SQL 在此 Tx 物件上執行。這點和 PHP 的是不一樣的,比如在 PHP 中是這樣使用事務:

  <?php
/* 開始一個事務,關閉自動提交 */
$dbh->beginTransaction(); 
 /* 在全有或全無的基礎上插入多行記錄(要麼全部插入,要麼全部不插入) */
$sql = 'INSERT INTO fruit(name, colour, calories) VALUES (?, ?, ?)';
$sth = $dbh->prepare($sql);
foreach ($fruits as $fruit) {
    $sth->execute(array(
        $fruit->name,
        $fruit->colour,
        $fruit->calories,
    ));
}
/* 提交更改 */
$dbh->commit();
// 此程式碼來自 https://www.php.net/manual/zh/pdo.commit.php

而使用 Go 的事務是這樣的:

import (
  "context"
  "database/sql"
  "log"
)
var (
  ctx context.Context
  db  *sql.DB
)
func main() {
  tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
  if err != nil {
    log.Fatal(err)
  }
  id := 37
    // 使用 Tx 執行 Update 語句,而不是繼續使用 db.Exec
  _, execErr := tx.Exec(`UPDATE users SET status = ? WHERE id = ?`, "paid", id)
  if execErr != nil {
    _ = tx.Rollback()
    log.Fatal(execErr)
  }
  if err := tx.Commit(); err != nil {
    log.Fatal(err)
  }
}
// 此程式碼來自於:https://pkg.go.dev/database/[email protected]#example-DB.BeginTx

2.2 實驗

下面繼續實驗事務不完整的影響,主體部分和上述一樣,queryOne 方法變成如下這樣:

func queryOne(id int, db *sql.DB) {
   tx,err:=db.BeginTx(context.Background(),nil)
   if err!=nil{
      panic(err)
   }
   // defer tx.Rollback()
   start := time.Now()
   rows, err := tx.QueryContext(context.Background(), "select * from user limit 1")
   if err != nil {
      panic(err)
   }
   defer rows.Close()
   // 事務沒有回滾、提交
   fmt.Println("id=", id, "hasNext=", rows.Next(), "cost=", time.Since(start))
}

執行後輸入和上述沒有 rows.Close 類似:

id= 9 hasNext= true cost= 11.670369ms
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}

同樣,總共啟動了 10 個協程,只有一個協程的 queryOne 方法成功執行了,其他 9 個協程的都是處於等待狀態。

若將上述queryOne 方法中的 // defer tx.Rollback() 的註釋開啟,則所有 10 個協程都可以成功執行完成。

2.3 解決方案

要避免事務不完整,要保證事務要麼被 Commit,要麼被 Rollback。

若是使用的 GDP 框架,可以使用 mysql.BeginTx 方法來使用事務。該方案可以更安全的使用事務,會自動的依據 函式返回值來決定是 Commit 還是 Rollback,若業務函數出現了 panic 也會自動的 Rollback。

// 業務邏輯函式的定義,在此函式內實現事務內的增刪改查
// 返回 error==nil 則 tx.Commit(),否則 tx.Rollback()
type doFunc func(ctx context.Context, qe QueryExecuto r) error 
func BeginTx(ctx context.Context, cli CanBeginTx, opts *sql.TxOptions, do doFunc) error
var cli mysql.Client
updateUserNameByID := func(ctx context.Context, id uint64, name string) error {
   //  使用 BeginTx 方法,能更省心的處理事務
   err := mysql.BeginTx(ctx, cli, nil, func(ctx context.Context, qe mysq.QueryExecutor) error {
      // 其他的資料庫更新邏輯略
      b1 := &mysql.SimpleBuilder{}
      b1.Append("select name from user where uid=?", id)
      var oldName string
      if err := mysql.QueryRowWithBuilderScan(ctx, qe, b1, &oldName); err != nil {
         return err
      }
      if oldName == "諸葛亮" || oldName == name {
         // 返回 err,mysql.BeginTx 方法將會回滾事務
         return fmt.Errorf("不需要更新,事務整體回滾")
      }
      b2 := &mysql.SimpleBuilder{}
      b2.Append("update user set name=? where id=?", name, id)
      _, err := mysql.ExecWithBuilder(ctx, qe, b2)
      if err != nil {
         return err
      }
      // 返回 nil,mysql.BeginTx 方法將會提交事務
      return nil
   })
   return err
}

3、其他原因

3.1 不支援預處理

預設一般會使用預處理的方式來提升 SQL 的安全性,避免產生 SQL 注入的問題。

若是在廠內使用叢集版MySQL:DDBS(DRDS),其對 prepare 支援的並不好,使用後會導致效能特別差。可能表現為,本應該幾毫秒返回的查詢,實際上要數百毫秒甚至數秒才能返回。此時需要在引數中新增上配置項 interpolateParams=true ,關閉 prepare 功能來解決。

Name = "demo"
# 其他配置項略
[MySQL] 
Username     = "example"
# 其他引數略
DSNParams ="charset=utf8&timeout=90s&collation=utf8mb4_unicode_ci&parseTime=true&interpolateParams=true"

4、如何排查

我們可以利用 DB 的 Stats() 介面返回的資料來分析是否存在上述問題。在上述章節中,我們就是列印此資料來觀察 Client 的狀態資訊。

{    
"MaxOpenConnections" : 1 ,  // 最大開啟連線數,和程式碼設定的一致,是 1    
"OpenConnections" : 1 ,     // 已開啟的連線數        
"InUse" : 1 ,               // 正在使用的連線數    
"Idle" : 0 ,                // 空閒連線數    
"WaitCount" : 9 ,           // 等待連線數    
"WaitDuration" : 0 ,        // 等待總耗時(在等待退出時才計數)    
"MaxIdleClosed" : 0 ,       // 超過最大 idle 數所關閉的連線總數     
"MaxIdleTimeClosed" : 0 ,   // 超過追到 idle 時間所關閉的連線總數    
"MaxLifetimeClosed" : 0    // 超過最大生命週期所關閉的連線總數
}

若使用的是 GDP 框架,我們可以通過如下幾種手段來觀察此資料。

4.1 整合 GDP 應用面板

在百度廠內,GDP 框架(百度內部的  Go Develop Platform,具有易用性好、易擴充套件、易觀察、穩定可靠的特點,被數千模組使用)提供了一個叫做"GDP應用面板"的功能模組,該模組提供了視覺化的 UI 讓我們可以非常方便的檢視、觀察應用的各種狀態資訊。比如可以檢視系統資訊、檔案系統資訊、網路狀態資訊、編譯資訊、go runtime資訊、框架裡各種元件的狀態資訊(如服務發現的運轉狀態、MySQL、Redis 等 各種 Client 的連線池資訊等)。

整合該功能非常簡單,只需要新增 2 行配置性程式碼。

完成整合後,可以通過 http://ip:port/debug/panel/?tab=servicer 來訪問此面板,找到對應的 servicer 後(頁面的地址是 /debug/panel/?tab=servicer&key={servicer_name} ),頁面上的 “MySQL ClientStats”段落即為當前 MySQL Client 的 Stats 資訊。比如:

圖片


4.2 整合監控

GDP 框架的標準化指標監控能力已經將所有 MySQL Client 的 Stats 資訊進行了採集輸出。可以以 prometheus 或者 bvar 格式輸出。

完成整合後,訪問 http://ip:port/metrics/service 即可檢視到對應的指標項,大致是這樣的:

client_connpool{servicer="demo_mysql",stats="ConnType"} 1
client_connpool{servicer="demo_mysql",stats="IPTotal"} 1
client_connpool{servicer="demo_mysql",stats="InUseAvg"} 0
client_connpool{servicer="demo_mysql",stats="InUseMax"} 0
client_connpool{servicer="demo_mysql",stats="InUseTotal"} 0
client_connpool{servicer="demo_mysql",stats="NumOpenAvg"} 0
client_connpool{servicer="demo_mysql",stats="NumOpenCfg"} 100
client_connpool{servicer="demo_mysql",stats="NumOpenMax"} 0
client_connpool{servicer="demo_mysql",stats="NumOpenTotal"} 0

可以對上述指標新增報警,以幫我們更快發現並定位到問題。

4.3 輸出到日誌

若不採用上述 2 種方案,還可以採用啟動一個非同步協程,定期將 Stats 資訊輸出到日誌的方案,以方便我們分析定位問題。

————————END————————

推薦閱讀

百度交易中臺之錢包系統架構淺析

基於寬表的資料建模應用

百度評論中臺的設計與探索

基於模板配置的資料視覺化平臺

如何正確的評測影片畫質

小程式啟動效能優化實踐

我們是如何穿過低程式碼 “⽆⼈區”的:amis與愛速搭中的關鍵設計

移動端異構運算技術-GPU OpenCL 程式設計(基礎篇)