五分鐘搞懂 Golang 數(shù)據(jù)庫連接管理
Go 的 database/sql 軟件包提供了自動化數(shù)據(jù)庫連接池,能夠幫助開發(fā)人員有效管理連接。通常情況下,開發(fā)人員會請求某個打開的連接,執(zhí)行查詢,然后關(guān)閉連接以確保連接返回到池中。
開發(fā)人員常犯的一個錯誤是長時間持有數(shù)據(jù)庫連接,從而導(dǎo)致性能瓶頸。新請求不得不等待可用連接,造成連接池的效率受到影響。
本文將探討如何避免這一問題,并通過確定常見問題域和學(xué)習(xí)解決方法,優(yōu)化 Go 應(yīng)用以提高吞吐量。
基本示例
我們以一個返回雇員記錄的基本 HTTP 處理程序為例:
func GetEmployeesHandler(w http.ResponseWriter, r *http.Request) {
rows, err := db.Query(`SELECT id, name, email FROM employee`)
if err != nil {
http.Error(w, fmt.Sprintf("error querying database: %v", err), http.StatusInternalServerError)
return
}
defer rows.Close()
var employees []Employee
for rows.Next() {
var e Employee
if err := rows.Scan(&e.ID, &e.Name, &e.Email); err != nil {
http.Error(w, fmt.Sprintf("Error scanning row: %v", err), http.StatusInternalServerError)
return
}
decorateEmployee(&e)
employees = append(employees, e)
}
if err = rows.Err(); err != nil {
http.Error(w, fmt.Sprintf("error during row iteration: %v", err), http.StatusInternalServerError)
return
}
w.Header().Set("Content-Type", "application/json")
if err := json.NewEncoder(w).Encode(employees); err != nil {
http.Error(w, "Error encoding response", http.StatusInternalServerError)
return
}
}
在這個處理程序中:
- 查詢數(shù)據(jù)庫中的雇員記錄。
- 通過 defer rows.Close() 確保在處理完結(jié)果集后關(guān)閉連接。
- 掃描每一行,并用從外部獲取的數(shù)據(jù)對其進(jìn)行裝飾。
- 將最終結(jié)果追加到數(shù)組中。
- 檢查迭代過程中的任何錯誤,并以 JSON 格式返回結(jié)果。
乍一看,似乎沒有什么特別的地方。不過,你會期待在壓力測試的時候獲得更好的性能。
初步性能結(jié)果
使用 Vegeta[2] 等壓力測試工具,可以模擬該端點的負(fù)載情況。在每秒 10 個請求(RPS,requests per second)的初始速率下,應(yīng)用在 30 秒的測試運行中表現(xiàn)相對較好:
$ echo "GET http://localhost:8080/employees" | vegeta attack -duration=30s -rate=10 | tee results.bin | vegeta report
Requests [total, rate, throughput] 300, 10.03, 5.45
Duration [total, attack, wait] 52.095s, 29.9s, 22.196s
Latencies [min, mean, 50, 90, 95, 99, max] 2.318s, 11.971s, 8.512s, 26.222s, 30.001s, 30.001s, 30.001s
Bytes In [total, mean] 2290991, 7636.64
Bytes Out [total, mean] 0, 0.00
Success [ratio] 94.67%
Status Codes [code:count] 0:16 200:284
然而,當(dāng)我們將負(fù)載增加到 50 RPS 時,就會發(fā)現(xiàn)吞吐量大幅下降,請求失敗率急劇上升:
$ echo "GET http://localhost:8080/employees" | vegeta attack -duration=30s -rate=50 | tee results.bin | vegeta report
Requests [total, rate, throughput] 1500, 50.03, 4.20
Duration [total, attack, wait] 59.981s, 29.981s, 30s
Latencies [min, mean, 50, 90, 95, 99, max] 2.208s, 27.175s, 30.001s, 30.001s, 30.001s, 30.002s, 30.002s
Bytes In [total, mean] 2032879, 1355.25
Bytes Out [total, mean] 0, 0.00
Success [ratio] 16.80%
Status Codes [code:count] 0:1248 200:252
(上述狀態(tài)代碼為 0 表示測試運行過程中出現(xiàn)客戶端超時)
定位瓶頸
當(dāng) RPS 為 50 時,成功率急劇下降,吞吐量降至每秒僅 4.2 個請求。為什么會這樣?其中一個可能的原因是,考慮到當(dāng)前資源,50 RPS 是一個不合理的目標(biāo)。為了確認(rèn)代碼是否可以通過修改獲得更好的性能,我們可以研究收集一些指標(biāo)。其中一個指標(biāo)來源是裝飾過程,但在本文中,我們將重點關(guān)注數(shù)據(jù)庫連接池統(tǒng)計數(shù)據(jù)。
Go 的 database/sql 軟件包可通過 DBStats 函數(shù)查看應(yīng)用的數(shù)據(jù)庫池性能,會返回我們感興趣的統(tǒng)計信息:
- InUse: 當(dāng)前正在使用的連接數(shù)。
- Idle:空閑連接數(shù)。
- WaitCount:等待的連接總數(shù)。
可以通過添加另一個端點處理程序來輸出這些值:
func GetInfoHandler(w http.ResponseWriter, r *http.Request) {
w.Header().Set("Content-Type", "application/json")
if err := json.NewEncoder(w).Encode(db.Stats()); err != nil {
http.Error(w, "Error encoding response", http.StatusInternalServerError)
return
}
}
重新運行上述壓力測試,并對 /info 端點進(jìn)行監(jiān)控:
$ while true; do curl -s http://localhost:8080/info; sleep 2; done
...
{"MaxOpenConnections":15,"OpenConnections":15,"InUse":15,"Idle":0,"WaitCount":1434,"WaitDuration":1389188829869,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
{"MaxOpenConnections":15,"OpenConnections":15,"InUse":15,"Idle":0,"WaitCount":1485,"WaitDuration":1582086078604,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
{"MaxOpenConnections":15,"OpenConnections":15,"InUse":15,"Idle":0,"WaitCount":1485,"WaitDuration":1772844971842,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
...
上述結(jié)果表明連接池已達(dá)到最大值(InUse: 15, Idle: 0),每個新請求都被迫等待(WaitCount 不斷增加)。換句話說,連接池基本上處于停滯狀態(tài),從而導(dǎo)致了之前觀察到的延遲和超時問題!
優(yōu)化連接使用
查看原始代碼,我們發(fā)現(xiàn)問題要么出在查詢本身性能不佳,要么出在遍歷結(jié)果集時每次調(diào)用裝飾函數(shù)都需要很長時間才能返回??梢試L試在 rows.Next() 循環(huán)之外裝飾記錄,并將其移至數(shù)據(jù)庫連接使用之下,從而找出問題所在。
以下是更新后的代碼:
func GetEmployeesHandler(w http.ResponseWriter, r *http.Request) {
rows, err := db.Query(`SELECT id, name, email FROM employee`)
if err != nil {
http.Error(w, fmt.Sprintf("error querying database: %v", err), http.StatusInternalServerError)
return
}
var employees []Employee
for rows.Next() {
var e Employee
if err := rows.Scan(&e.ID, &e.Name, &e.Email); err != nil {
http.Error(w, fmt.Sprintf("Error scanning row: %v", err), http.StatusInternalServerError)
return
}
employees = append(employees, e)
}
if err = rows.Err(); err != nil {
http.Error(w, fmt.Sprintf("error during row iteration: %v", err), http.StatusInternalServerError)
return
}
rows.Close()
for i := range employees {
decorateEmployee(&employees[i])
}
w.Header().Set("Content-Type", "application/json")
if err := json.NewEncoder(w).Encode(employees); err != nil {
http.Error(w, "Error encoding response", http.StatusInternalServerError)
return
}
}
在這個重構(gòu)的處理程序中,我們:
- 將所有行掃描到內(nèi)存中。
- 掃描后立即關(guān)閉連接,將其釋放回池。
- 在內(nèi)存中裝飾雇員記錄,而無需保持連接打開。
優(yōu)化后的性能
優(yōu)化后以 50 RPS 運行相同的 Vegeta 測試,結(jié)果如下:
$ echo "GET http://localhost:8080/employees" | vegeta attack -duration=30s -rate=50 | tee results.bin | vegeta report
Requests [total, rate, throughput] 1500, 50.03, 45.78
Duration [total, attack, wait] 32.768s, 29.98s, 2.788s
Latencies [min, mean, 50, 90, 95, 99, max] 2.045s, 2.502s, 2.499s, 2.692s, 2.741s, 2.856s, 2.995s
Bytes In [total, mean] 11817000, 7878.00
Bytes Out [total, mean] 0, 0.00
Success [ratio] 100.00%
Status Codes [code:count] 200:1500
...
{"MaxOpenConnections":15,"OpenConnections":1,"InUse":0,"Idle":1,"WaitCount":0,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
{"MaxOpenConnections":15,"OpenConnections":1,"InUse":0,"Idle":1,"WaitCount":0,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
{"MaxOpenConnections":15,"OpenConnections":1,"InUse":0,"Idle":1,"WaitCount":0,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
...
可以看到,不僅吞吐量和延遲得到了 100% 的大幅改善,而且 OpenConnections 的總數(shù)也沒有超過 1,甚至還有閑置連接處于待機狀態(tài),從而使 WaitCount 始終為零!
總結(jié)
通過優(yōu)化連接的處理方式,先將所有行獲取到內(nèi)存中,然后立即關(guān)閉連接,而不是在執(zhí)行其他 I/O 綁定操作(如裝飾記錄)時保持連接打開。這樣,數(shù)據(jù)庫連接就能盡快返回到池中,為其他傳入請求釋放資源,從而提高吞吐量和并發(fā)性。