配置 sql.DB 獲得更好的性能 · golang

> 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請求掛起，并可能在等待空閑連接時超時。