如何配置 sql.DB 才能获取更好的性能？

点击上方蓝色“Golang来啦”关注我哟

加个“星标”，天天 15 分钟，掌握 Go 语言

via:
https://www.alexedwards.net/blog/configuring-sqldb
作者：Alex Edwards

四哥水平有限，如有翻译或理解错误，烦请帮忙指出，感谢！

Go 语言常见的坑 提到了 sql.DB，那你知道如何配置这些参数才能获取更高的性能吗？我们一起来看下今天的文章。

原文如下：

---

现在已经有很多很好的教程介绍什么是 sql.DB 类型以及如何用它执行数据库查询。但是其中大部分都没有提及 SetMaxOpenConns()、SetMaxIdleConns() 和 SetConnMaxLifetime() 方法，通过这些函数可以配置 sql.DB 参数以获取更好的性能。

这篇文章中，我们将详细地讨论这些配置参数的作用并演示它们会产生的影响(正面或负面的)。

打开和空闲连接

文章开始时我们先介绍点背景知识。

sql.DB 对象是数据库连接池，包含 in-use 和 idle 连接。当你使用它执行数据库查询等任务时，一个连接就会被标记成 in-use；任务执行完成后该连接又会被标记成 idle。

当使用 sql.DB 执行数据库任务时，先会检查连接池里有没有空闲连接。如果有一个连接可用，则会重用次连接并在任务执行期间会被标记成 in-use；如果没有空闲连接将会创建新的连接。

SetMaxOpenConns() 方法

默认情况下，同时打开（包括 in-use 和 idle）的连接数是没有限制的。但你可以通过 SetMaxOpenConns() 方法配置连接数限制，就像下面这样：

// Initialise a new connection pool
db, err := sql.Open("postgres", "postgres://user:pass@localhost/db")
if err != nil {
    log.Fatal(err)
}

// Set the maximum number of concurrently open connections (in-use + idle)
// to 5. Setting this to less than or equal to 0 will mean there is no 
// maximum limit (which is also the default setting).
db.SetMaxOpenConns(5)

上面的例子中，连接池里打开的连接数限制是 5。如果连接都被标记成 in-use，应用程序再需要连接就只能等待直到有连接空出（被标记成 idle）。

为了演示更改 MaxOpenConns 的影响，我进行了基准测试，分别将最大连接数设置为 1, 2, 5, 10 和不限制。测试内容是在 PostgreSQL 数据库并发执行 INSERT 语句。【公众号后台回复 基准测试 获取测试脚本地址】

下面是测试结果：

BenchmarkMaxOpenConns1-8                 500       3129633 ns/op         478 B/op         10 allocs/op
BenchmarkMaxOpenConns2-8                1000       2181641 ns/op         470 B/op         10 allocs/op
BenchmarkMaxOpenConns5-8                2000        859654 ns/op         493 B/op         10 allocs/op
BenchmarkMaxOpenConns10-8               2000        545394 ns/op         510 B/op         10 allocs/op
BenchmarkMaxOpenConnsUnlimited-8        2000        531030 ns/op         479 B/op          9 allocs/op
PASS

我们从基准测试结果可以看出，打开连接数越多，执行 INSERT 语句花费时间越少（一个打开连接时，3129633 ns/op，没有限制连接数时是 531030 ns/op，快了 6 倍左右）。这是因为打开连接数越多，越多的数据库操作就能并发地执行。

SetMaxIdleConns() 方法

默认情况下，sql.DB 允许连接池中最多保留 2 个空闲连接。你可以通过 SetMaxIdleConns() 配置不同的数值。

// Initialise a new connection pool
db, err := sql.Open("postgres", "postgres://user:pass@localhost/db")
if err != nil {
    log.Fatal(err)
}

// Set the maximum number of concurrently idle connections to 5. Setting this
// to less than or equal to 0 will mean that no idle connections are retained.
db.SetMaxIdleConns(5)

理论上来说，池中空闲连接数越多将提高性能，因为这样可以减少从头开始建立新连接的可能性，因此有助于节省资源。

我们一起来看下基准测试结果，空闲连接数分别设置成 0、1、2、5 和 10（打开连接数没有限制）。

BenchmarkMaxIdleConnsNone-8          300       4567245 ns/op       58174 B/op        625 allocs/op
BenchmarkMaxIdleConns1-8            2000        568765 ns/op        2596 B/op         32 allocs/op
BenchmarkMaxIdleConns2-8            2000        529359 ns/op         596 B/op         11 allocs/op
BenchmarkMaxIdleConns5-8            2000        506207 ns/op         451 B/op          9 allocs/op
BenchmarkMaxIdleConns10-8           2000        501639 ns/op         450 B/op          9 allocs/op
PASS

当空闲连接数设置成 0 时，每次执行 INSERT 操作时都需要从头开始创建新连接，从基准测试结果可以看出，此时平均运行时间和内存使用相对较高。

仅保留一个空闲连接时基准测试结构产生了极大影响，相对于 0 空闲连接数时，此时平均运行时间只有原来的 1/8，内存消耗上只有原来的 1/20。继续增加连接池中的空闲连接数可以使性能更好，尽管改进的效果并不明显。

so，连接池中需要维护数量较多的空闲连接吗？答案是，不过数量取决于具体的应用。

重要的是需要意识到保留空闲连接是需要代价的，比如占用内存，而这些内存是本可以用于其他用途。

如果一个连接空闲时间太长将会无法使用，例如默认情况下，MySQL 将自动关闭超 8 小时没被使用的连接，超时时间还可以通过 wait_timeout 参数设置。

如果发生这种情况，sql.DB 也能很好地处理。丢弃之前，错误的连接将会自动重试两次，之后会从连接池中删除并创建新的连接。因此，将 MaxIdleConns 设置得过高实际上可能会导致连接变得不可用，并且与空闲连接池较小的情况相比，会占用更多资源
。空闲连接数更少时，连接能更频繁地被使用。所以只有你很可能马上再次使用这些连接，你才会保持这些连接空闲。

有一点需要指出，MaxIdleConns 数值始终需要小于等于 MaxOpenConns，这一点是强制的。

SetConnMaxLifetime() 方法

现在，让我们看一下 SetConnMaxLifetime() 方法，该方法设置可重用连接的最大时间长度。如果你的 SQL 数据库还实现了最大连接生存周期，这将很有用，比如，你想方便地在负载均衡器后轻松地交换数据库。

像下面这样使用：

// Initialise a new connection pool
db, err := sql.Open("postgres", "postgres://user:pass@localhost/db")
if err != nil {
    log.Fatal(err)
}

// Set the maximum lifetime of a connection to 1 hour. Setting it to 0
// means that there is no maximum lifetime and the connection is reused
// forever (which is the default behavior).
db.SetConnMaxLifetime(time.Hour)

上面这个例子中，所有的数据库连接在第一次创建之后将存活 1 小时，到期之后将变得不可用。但是需要注意的是：

• 这并不一定保证连接一定会在池子里存活一小时，可能在这之前因为某些原因连接变得不可用；

• 一小时到了之后，连接有可能还继续存在池子里，因为此时连接可能还在执行任务，不过任务执行完成之后将变得不可用；

• 这并不是空闲过期时间，是从连接被创建算起而不是从最近一次变成空闲开始；

• 每秒执行一次清理操作，以自动删除池中过期的连接；

从理论上讲，ConnMaxLifetime 越短，连接失效的频率就越高，因此，需要从头开始创建连接的频率就越高。

为了说明这一点，我们来执行要基准测试，ConnMaxLifetime 分别设置成 100ms、200ms、500ms、1000ms 和不限（可以永久重用），SetMaxOpenConns 和 SetMaxIdleConns 都是默认值：不限和 2 个。

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

从基准测试结果可以看出，在内存使用上，100ms 大约是不限制时的 3 倍，平均执行时间也更长。

如果你在代码中设置了 ConnMaxLifetime，重要的一点：请务必记住连接过期的频率（随后连接会重建）。比如，总共有 100 个连接且 ConnMaxLifetime 是一分钟，你的应用有可能每秒钟清除和创建 1.67 个连接。你肯定不想这么频繁，因为这会影响性能，而不是提升性能。

超出连接数限制

最后，这篇文章如果不讲“如果连接数超出数据库限制会发生”是不完整的。

作为演示，我将修改配置文件 postgresql.conf 并将连接数设置成 5（默认是 100）：

max_connections = 5

接着我们将 SetMaxOpenConns 设置成不限，执行基准测试：

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，数据库驱动立马报错：sorry, too many clients already。

防止这种报错，我们需要将 sql.DB 的 SetMaxOpenConns 设置成比 5 小的数值，比如：

// Initialise a new connection pool
db, err := sql.Open("postgres", "postgres://user:pass@localhost/db")
if err != nil {
    log.Fatal(err)
}

// Set the number of open connections (in-use + idle) to a maximum total of 3.
db.SetMaxOpenConns(3)

现在，在任何时候，sql.DB 最多只能创建 3 个连接，基准测试应该可以顺利执行。

但是这样做需要警惕，当连接数达到限制且连接都被标记成 in-use，任何新增的数据库执行任务只能被迫等待直到有连接空闲。例如，在 Web 应用程序中，用户的 HTTP 请求都会“挂起”，等待数据库任务完成，期间有可能会超时。

为了兼容这种情况，在调用数据库时，你应当使用可以通过 context.Context  对象传递超时时间的方法，比如 ExecContext() 等，具体的可以看基准测试代码。【公众号后台回复 基准测试 获取代码】。

总结

1. 根据经验，我们应当显示地设置 MaxOpenConns，这个值应当低于数据库配置文件或其他配置设置的值；

2. 通常情况下，MaxOpenConns 和 MaxIdleConns 的值越大可以获得更高的性能。但是我们也需要意识到，当空闲连接数超过某一值时（连接没有被重用并最终变为坏连接），性能反而会下降；

3. 为了降低第 2 点的风险，我们可能需要设置相对较短的 ConnMaxLifetime。但这个值又不能设置的太短，不然会导致连接被频繁删除和创建；

4. MaxIdleConns 需要小于等于 MaxOpenConns；

对于中小型 Web 应用程序，项目刚起步的时候我通常采用如下配置，然后根据负载测试（具有真实的吞吐量）结果进一步优化。

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(5*time.Minute)

希望这篇文章你给你带来帮助！

推荐阅读：

Go 语言常见的坑

不要使用 Go 默认的 HTTP 客户端（在生产环境中）

---

这是持续翻译的第 16/100 篇优质文章。
如果你有想交流的话题，欢迎留言。

---

如果我的文章对你有所帮助，点赞、转发都是一种支持！

给个[在看]，是对四哥最大的支持