数据库连接池
使用数据库连接池优化单数据服务器,提升其性能。以及Golang中的配置方式。
业务场景(单数据库服务器)
一个简单的业务系统架构:前端一台 Web 服务器运行业务代码,后端一台数据库服务器存储业务数据。当用户量不大时,系统运行平稳。
系统流量增大
当系统流量增大时,系统频繁报错。日志发现:“Can not connect to MySQL server. Too many connections”。
增加mysql的max_connections ,一番操作后错误消失。系统正常执行
系统流量继续增大
max_connections 已经调整的很大,继续调整仍然响应很慢,阻塞的数据库操作越来越多,系统响应越来越慢。
如何解决?
为什么需要连接池
数据库的调用方式是先获取数据库的连接,然后依靠这条连接从数据库中查询数据,最后关闭连接释放数据库资源。这种调用方式下,每次执行 SQL 都需要重新建立连接。
完成一次数据库操作的基本耗时:
- 建立连接 1-2ms (TCP三次握手)
- MySQL 服务端校验客户端密码的过程 4ms
- 执行数据库操作 1ms
也就是说相比于 SQL 的执行,MySQL 建立连接的过程是比较耗时的。
解决方法就是,使用连接池将数据库连接预先建立好,这样在使用的时候就不需要频繁地创建连接了。调整之后,你发现 1s 就可以执行 1000 次的数据库查询,查询性能大大的提升了
数据库连接池有两个最重要的配置:最小连接数和最大连接数, 它们控制着从连接池中获取连接的流程:
- 如果当前连接数小于最小连接数,则创建新的连接处理数据库请求;
- 如果连接池中有空闲连接则复用空闲连接;
- 如果空闲池中没有连接并且当前连接数小于最大连接数,则创建新的连接处理请求;
- 如果当前连接数已经大于等于最大连接数,则按照配置中设定的时间(C3P0 的连接池配置是 checkoutTimeout)等待旧的连接可用;
- 如果等待超过了这个设定时间则向用户抛出错误。
连接池大小如何设定
下面的公式是由PostgreSQL提供的,不过我们认为可以广泛地应用于大多数数据库产品。你应该模拟预期的访问量,并从这一公式开始测试你的应用,寻找最合适的连接数值。
连接数 = ((核心数 * 2) + 有效磁盘数)
核心数不应包含超线程(hyper thread),即使打开了hyperthreading也是。如果活跃数据全部被缓存了,那么有效磁盘数是0,随着缓存命中率的下降,有效磁盘数逐渐趋近于实际的磁盘数。这一公式作用于SSD时的效果如何尚未有分析。
按这个公式,你的4核i7数据库服务器的连接池大小应该为((4 * 2) + 1) = 9。取个整就算是是10吧。是不是觉得太小了?跑个性能测试试一下,我们保证它能轻松搞定3000用户以6000TPS的速率并发执行简单查询的场景。如果连接池大小超过10,你会看到响应时长开始增加,TPS开始下降。
笔者注: 这一公式其实不仅适用于数据库连接池的计算,大部分涉及计算和I/O的程序,线程数的设置都可以参考这一公式。我之前在对一个使用Netty编写的消> 息收发服务进行压力测试时,最终测出的最佳线程数就刚好是CPU核心数的一倍。
公理
公理: 你需要一个小连接池,和一个充满了等待连接的线程的队列
如果你有10000个并发用户,设置一个10000的连接池基本等于失了智。1000仍然很恐怖。即是100也太多了。你需要一个10来个连接的小连接池,然后让剩下的业务线程都在队列里等待。连接池中的连接数量应该等于你的数据库能够有效同时进行的查询任务数(通常不会高于2*CPU核心数)。
我们经常见到一些小规模的web应用,应付着大约十来个的并发用户,却使用着一个100连接数的连接池。这会对你的数据库造成极其不必要的负担。
注意
连接池优化主要用于单数据库的使用场景,当优化好连接池参数后系统响应仍然很慢。达到单台数据的瓶颈时,需要采用分库的设计。
配置Go sql.DB的连接池属性获得更好的性能
sql.DB对象是包含"open"和"idle"连接的许多数据库连接的池。 当您使用连接执行数据库任务(例如执行SQL语句或查询行)时,该连接被标记为打开。 任务完成后,连接变为空闲。
当您指示sql.DB执行数据库任务时,它将首先检查池中是否有可用的空闲连接。 如果有一个可用,那么Go将重用现有的连接并将其在任务期间标记为打开。 如果需要时池中没有空闲连接,则Go将创建一个新的附加连接并“打开”它。
SetMaxOpenConns/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 open connections 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)
// 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)
最后要指出的是,MaxIdleConns应该始终小于或等于MaxOpenConns。 Go会强制执行此操作,并在必要时自动减少MaxIdleConns。 这篇Stack Overflow帖子很好地描述了原因:
空闲连接数没有超过允许的最大打开连接数的任何意义,因为如果您可以立即获取所有允许的打开连接数,则其余空闲连接将始终保持空闲状态。 就像有一条四车道的桥梁,但只允许三辆车一次驶过它。
参考
- https://www.jianshu.com/p/a8f653fc0c54
- https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
- 高并发系统设计40问-07 | 池化技术:如何减少频繁创建数据库连接的性能损耗?
- https://www.alexedwards.net/blog/configuring-sqldb