> From: [Configuring sql.DB for Better Performance](https://www.alexedwards.net/blog/configuring-sqldb)
網上有很多教程介紹`sql.DB`, 以及如何使用它來執行SQL數據庫查詢和語句, 但是大部分都沒有介紹`SetMaxOpenConns()`、`SetMaxIdleConns()`和`SetConnmaxLifetime()`方法。事實上你可以使用這些方法來配置`sql.DB`的行為并改善其性能。
在這篇文章中,我想準確地解釋這些設置的作用,并演示它們可能產生的(正面和負面)影響。
## 打開和空閑連接
首先說一點背景知識。
sql.db對象是包含多個**open**和**idle**數據庫連接的連接池。當使用連接執行數據庫任務(如執行SQL語句或查詢數據)時,該連接被標記為**open**(打開)。任務完成后,連接將變為**idle**(空閑)。
當您指示`sql.db`執行數據庫任務時,它將首先檢查池中是否有空閑連接可用。如果有可用的連接,Go將重用現有連接,并在任務期間將其標記為打開。如果在需要連接時池中沒有空閑連接的話,go將創建一個新的附加連接并**打開**它。
## SetMaxOpenConns 方法
默認情況下,可以同時打開的連接數沒有限制。但您可以通過setMaxOpenConns()方法實現自己的限制,如下所示:
~~~
// 初始化一個新的連接池
db, err := sql.Open("postgres", "postgres://user:pass@localhost/db")
if err != nil {
log.Fatal(err)
}
// 設置最大的并發打開連接數為5。
// 設置這個數小于等于0則表示沒有顯示,也就是默認設置。
db.SetMaxOpenConns(5)
~~~
在此示例代碼中,池中最多有5個并發打開的連接。如果5個連接都已經打開被使用,并且應用程序需要另一個連接的話,那么應用程序將被迫等待,直到5個打開的連接其中的一個被釋放并變為空閑。
為了說明更改**MaxOpenConns**的影響,我運行了一個基準測試,將最大開放連接設置為1、2、5、10和無限制。基準測試在PostgreSQL數據庫上執行并行的insert語句,您可以在這個[gist](https://gist.github.com/alexedwards/5d1db82e6358b5b6efcb038ca888ab07)中找到代碼。結果如下:
~~~
BenchmarkMaxOpenConns1-8 500 3129633 ns/op 478 B/op 10
allocs/opBenchmarkMaxOpenConns2-8 1000 2181641 ns/op 470 B/op 10
allocs/opBenchmarkMaxOpenConns5-8 2000 859654 ns/op 493 B/op 10
allocs/opBenchmarkMaxOpenConns10-8 2000 545394 ns/op 510 B/op 10
allocs/opBenchmarkMaxOpenConnsUnlimited-8 2000 531030 ns/op 479 B/op 9 allocs/op
PASS
~~~
> 準確地說,此基準的目的不是模擬應用程序的“真實”行為。它只是幫助說明`SQL.DB`在幕后的行為,以及更改**MaxOpenConns**對該行為的影響。
對于這個基準,我們可以看到允許的開放連接越多,在數據庫上執行**插入**操作所花費的時間就越少(3129633 ns/op,其中1個開放連接,而無限連接為531030 ns/op,大約快6倍)。這是因為存在的開放連接越多,基準代碼等待開放連接釋放并再次空閑(準備使用)所需的時間(平均值)就越少。
## SetMaxIdleConns
默認情況下,`sql.DB`允許在連接池中最多保留**2**個空閑連接。您可以通過`SetMaxIdleConns()`方法進行更改,如下所示:
~~~
// 初始化連接池
db, err := sql.Open("postgres", "postgres://user:pass@localhost/db")
if err != nil {
log.Fatal(err)
}
// 設置最大的空閑連接數為5。
// 設置小于等于0的數意味著不保留空閑連接。
db.SetMaxIdleConns(5)
~~~
理論上,在池中允許更多的空閑連接將提高性能,因為這樣可以減少從頭開始建立新連接的可能性,從而有助于節省資源。
讓我們來看看相同的基準,最大空閑連接設置為無、1、2、5和10(并且開放連接的數量是無限的):
~~~
BenchmarkMaxIdleConnsNone-8 300 4567245 ns/op 58174 B/op 625
allocs/opBenchmarkMaxIdleConns1-8 2000 568765 ns/op 2596 B/op 3
allocs/opBenchmarkMaxIdleConns2-8 2000 529359 ns/op 596 B/op 11
allocs/opBenchmarkMaxIdleConns5-8 2000 506207 ns/op 451 B/op 9
allocs/opBenchmarkMaxIdleConns10-8 2000 501639 ns/op 450 B/op 9 allocs/op
PASS
~~~
當`MaxIdleConns`設置為none時,必須為每個插入操作創建新的連接,從基準中我們可以看到平均運行時間和內存分配相對較高。
只允許保留和重用一個空閑連接,在我們這個特定的基準測試中有很大的不同——它將平均運行時間減少了8倍左右,并將內存分配減少了20倍左右。繼續增加空閑連接池的大小會使性能更好,盡管這些改進不那么明顯。
那么我們應該維護一個大的空閑連接池嗎?答案是它取決于應用程序。
重要的是要認識到保持空閑連接的存活是要付出代價的——它會占用內存,否則這些內存可以同時用于應用程序和數據庫。
也有一種可能,如果一個連接空閑太久,那么它也可能會變得不可用。例如,MySQL的[wait\_timeout](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout)設置將自動關閉8小時內未使用的任何連接(默認情況下)。
當發生這種情況時,`sql.DB`會優雅地處理它。在放棄之前,將自動重試兩次壞連接,之后Go將從池中刪除壞連接并創建新連接。因此,將`MaxIdleConns`設置得太高實際上可能會導致連接變得不可用,并且使用的資源比使用較小的空閑連接池(使用的連接更少,使用頻率更高)的情況下要多。所以只有你很可能馬上再次使用浙西連接,你才會保持這些連接空閑。
最后要指出的一點是,`MaxIdleConns`應該始終小于或等于`MaxOpenConns`。Go會檢查并在必要時自動減少`MaxIdleConns` StackOverflow上的一個解釋很好地描述了原因:
> 設置比`MaxOpenConns`更多的空閑連接數是沒有意義的,因為你最多也就能拿到所有打開的連接,剩余的空閑連接依然保持的空閑。這就像一座四車道的橋,但是只允許三輛車同時通過。
## SetConnMaxLifetime 方法
現在讓我們來看一下`SetConnMaxLifetime()`方法,它設置了連接可重用的最大時間長度。如果您的SQL數據庫也實現了最大的連接生存期,或者(例如)您希望在負載均衡器后面方便地切換數據庫,那么這將非常有用。
您可以這樣使用它:
~~~
// 初始化連接池
db, err := sql.Open("postgres", "postgres://user:pass@localhost/db")
if err != nil {
log.Fatal(err)
}
// 設置連接的最大生命周期為一小時。
// 設置為0的話意味著沒有最大生命周期,連接總是可重用(默認行為)。
db.SetConnMaxLifetime(time.Hour)
~~~
在這個例子中,我們的所有連接將在第一次創建后1小時“過期”,并且在它們過期后無法重用。但是注意:
* 這并不能保證連接將在池中存在完整的一小時;很可能由于某種原因連接將變得不可用,并且在此之前自動關閉。
* 一個連接在創建后仍可以使用一個多小時,只是說一個小時后不能再被重用了。
* 這不是空閑超時。連接將在第一次創建后1小時后過期,而不是1小時后變成空閑。
* 每秒自動運行一次清理操作以便從池中刪除“過期”連接。
理論上,**ConnMaxLifetime**越短,從零開始創建連接的頻率就越高。
為了說明這一點,我運行了基準測試,將**ConnMaxLifetime**設置為100ms、200ms、500ms、1000ms和unlimited(永遠重復使用),默認設置為unlimited open connections和2個idle connections。這些時間段顯然比您在大多數應用程序中使用的要短得多,但它們有助于很好地說明連接庫的行為。
~~~
BenchmarkConnMaxLifetime100-8 2000 637902 ns/op 2770 B/op 34 allocs/op
BenchmarkConnMaxLifetime200-8 2000 576053 ns/op 1612 B/op 21 allocs/op
BenchmarkConnMaxLifetime500-8 2000 558297 ns/op 913 B/op 14 allocs/op
BenchmarkConnMaxLifetime1000-8 2000 543601 ns/op 740 B/op 12 allocs/op
BenchmarkConnMaxLifetimeUnlimited-8 3000 532789 ns/op 412 B/op 9 allocs/op
PASS
~~~
在這些特定的基準測試中,我們可以看到100毫秒的內存分配要比unlimited的內存分配多三倍,而且每個插入的操作的平均運行時間也稍長一些。
## 超出連接限制
最后,如果不提及超過了數據庫連接數的硬限制的話,那么本文就不算一個完整的教程了。
如圖所示,我將更改**postgresql.conf**文件,因此只允許總共5個連接(默認值為100)…
~~~
max_connections = 5
~~~
使用 unlimited open connections 的配置進行基準測試:
~~~
BenchmarkMaxOpenConnsUnlimited-8 --- FAIL: BenchmarkMaxOpenConnsUnlimited-8
main_test.go:14: pq: sorry, too many clients already
main_test.go:14: pq: sorry, too many clients already
main_test.go:14: pq: sorry, too many clients already
FAIL
~~~
一旦達到5個連接的硬限制,我的數據庫驅動程序(PQ)立即返回一條`sorry, too many clients already`錯誤信息,而不是完成插入操作。
為了避免這個錯誤,我們需要將`sql.DB`中打開和空閑連接的最大總數設置為5以下。像這樣:
~~~
// 初始化連接池
db, err := sql.Open("postgres", "postgres://user:pass@localhost/db")
if err != nil {
log.Fatal(err)
}
//設置open和idle的總連接數為3
db.SetMaxOpenConns(2)
db.SetMaxIdleConns(1)
~~~
現在,由`sql.DB`創建的連接數最多只能有3個,基準測試運行時應該沒有錯誤。
但是這樣也會給我們帶來一個很大的警示:當達到開放連接限制時,應用程序需要執行的任何新數據庫任務都將被強制等待,直到連接變為空閑。
對于某些應用程序,該行為可能很好,但對于其他應用程序,則可能不好。例如,在Web應用程序中,最好立即記錄錯誤消息并向用戶發送`500 Internal Server Error`,而不是讓他們的HTTP請求掛起,并可能在等待空閑連接時超時。