DBCP一个配置，浪费了MySQL 50%的性能！
1. 引言
研究背景
数据库性能的重要性
数据库性能优化对于保证应用的响应速度和处理大量数据的能力至关重要。它可以显著减少查询时间，提高事务处理效率，降低硬件成本，并确保系统稳定性与可扩展性。优化后的数据库能够更好地服务于用户需求，增强客户满意度，对企业的长期发展和竞争力具有深远影响。
连接池在数据库性能中的作用
1. 降低连接开销：连接池预先创建并管理一组数据库连接，避免了频繁建立和关闭连接的开销，提高了应用程序的响应速度。
2. 提高资源利用率：通过复用已存在的连接，连接池使得数据库资源（如内存和连接数）得到更高效的利用。
3. 管理连接生命周期：连接池能够监控连接的健康状态，自动剔除失效的连接，并根据需要创建新的连接，确保连接的可用性。
4. 事务管理：连接池可以协助管理数据库事务，保证在同一连接中执行的操作能够满足事务的原子性、一致性、隔离性和持久性（ACID属性）。
5. 配置灵活性：连接池提供多种配置选项，如最小/最大连接数、连接超时时间等，帮助开发人员根据具体应用需求调整资源分配策略。
研究问题及目的
在应用压测过程中，发现数据库的TPS不高的情况下， 数据库CPU就很高，而且有个数据库的事务指标跟应用的特点不匹配。因此经过不断的试验、研究，最终发现是数据库连接池的autocommit配置导致的。因此，本篇文章的主要探讨：
通过实验验证autocommit=false的性能影响
通过源码分析解释性能影响的原因
2. 实验设计与方法
注：mysql服务端的autocommit默认值是ON，后续章节若无特殊说明，autocommit指应用侧dbcp的配置
实验环境说明
硬件配置：MySQL 5.7 4C16G
软件版本和配置：spring 4.1.3.RELEASE + mybatis 3.2.7 + mybatis-spring 1.2.2 + dbcp 1.4 + mydql 5.7
数据库连接池配置参数
#jdbc
jdbc.mysql.driver=com.mysql.jdbc.Driver
jdbc.mysql.url=jdbc:mysql://host:port/my_db?connectTimeout=1000&socketTimeout=1000&serverTimezone=Asia/Shanghai
jdbc.mysql.connectionProperties=useUnicode=true;characterEncoding=utf8;rewriteBatchedStatements=true;autoReconnectForPools=true;failOverReadOnly=false;roundRobinLoadBalance=true;allowMultiQueries=true;useLocalSessionState=true
#dbcp
dbcp.initialSize=4
dbcp.maxActive=12
dbcp.maxIdle=12
dbcp.minIdle=4
dbcp.maxWait=6000
dbcp.defaultAutoCommit=true
dbcp.timeBetweenEvictionRunsMillis=60000
dbcp.numTestsPerEvictionRun=16
dbcp.minEvictableIdleTimeMillis=180000
dbcp.testWhileIdle=true
dbcp.testOnBorrow=false
dbcp.testOnReturn=false
dbcp.validationQuery=select 1
dbcp.removeAbandoned=true
dbcp.removeAbandonedTimeout=180
dbcp.logAbandoned=true
实验方法
实验方法：设计一个查询接口，根据主键ID查询一条数据。表中一共12000条数据，查询id的范围为[1,10000]。其中数据库表、sql如下
表结构如下
CREATE TABLE `task` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
`cluster` varchar(100) NOT NULL DEFAULT '',
`system` varchar(50) NOT NULL COMMENT '系统',
`app_name` varchar(50) NOT NULL COMMENT '应用',
部分字段省略....
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=77 DEFAULT CHARSET=utf8 COMMENT='任务';
sql语句
select field1,field2,...,fieldN from task where id = ${id}
实验分组
在其它变量一致的情况下，开启autocommit与关闭autocommit 分别进行测试。通过压力机对接口进行梯度发压，对比mysql CPU使用率
实验结果
数据汇总
| autocommit=false 数据库CPU | autocommit=true 数据库CPU | |
| 1000 | 14.3 | 10 |
| 2000 | 25.1 | 13.6 |
| 3000 | 35.8 | 19.4 |
| 4000 | 47 | 25.7 |
| 5000 | 58.1 | 30.8 |
| 6000 | 70.7 | 35.8 |
| 7000 | 81.4 | 40.4 |
| 8000 | 92.3 | 46.1 |
| 9000 | 97(8.5k) | 51.2 |
| 10000 | 56.6 | |
| 11000 | 62.6 | |
| 12000 | 67.2 | |
| 13000 | 74.2 | |
| 14000 | 80.3 | |
| 15000 | 86.4 | |
| 16000 | 90.4 | |
| 17000 | 93.9 | |
| 18000 | 98.8 |
autocommit=false
数据库性能监控
应用侧接口性能监控
autocommit=true
数据库性能监控
应用侧性能监控
实验结论
autocommit=true(默认配置）支持的TPS是18K，此时CPU使用率在98%左右，而autocommit=false能支持的TPS是8.5K，此时CPU使用率也在98%左右。
明显看出，autocommit=false的配置，导致数据库性能下降了一倍。
3. 源码分析与讨论
根据上文的实验看出，连接池的autocommit属性对于性能的消耗是巨大的，接下来我们一步一步深究一下其原因。
注：没有特殊说明，流程图、时序图等，都是基于autocommit=false画出的
mybatis执行sql流程图
源码位于 org.mybatis.spring.SqlSessionTemplate.SqlSessionInterceptor#invoke
由下图可知，mybatis封装的sql执行步骤，还是离不开原生jdbc的三段式：获取连接、执行sql、关闭连接。不过由于框架的封装，很多细节隐藏到了其它中间件，比如获取连接、关闭连接，底层都是由dbcp处理的。因此autocommit如何作用，我们还要继续深入dbcp的源码。
源码分析autocommit=false如何起作用的
连接的管理，都是由dbcp实现的，而dbcp依赖了commons-pool框架。
获取连接from dbcp
在获取连接时，执行GenericObjectPool#borrowObject方法，即从连接池中获取一个可用的连接对象（可以是新建，也可以是从队列中获取闲置的），获取连接之后需要激活连接，代码为_factory.activateObject，这里的_factory是org.apache.commons.dbcp.PoolableConnectionFactory，其activateObject方法如下。conn.getAutoCommit()是获取连接的autocommit（默认true），_defaultAutoCommit是连接池的配置项，被项目配置为false。由于二者不一致，需要将连接的autocommit设置为true，此时mysql服务器远端也会被设置为false。
public void activateObject(Object obj) throws Exception {
if(obj instanceof DelegatingConnection) {
((DelegatingConnection)obj).activate();
}
if(obj instanceof Connection) {
Connection conn = (Connection)obj;
// autocommit=false 起作用的地方
if (conn.getAutoCommit() != _defaultAutoCommit) {
conn.setAutoCommit(_defaultAutoCommit);
}
if ((_defaultTransactionIsolation != UNKNOWN_TRANSACTIONISOLATION)
&& (conn.getTransactionIsolation() !=
_defaultTransactionIsolation)) {
conn.setTransactionIsolation(_defaultTransactionIsolation);
}
if ((_defaultReadOnly != null) &&
(conn.isReadOnly() != _defaultReadOnly.booleanValue())) {
conn.setReadOnly(_defaultReadOnly.booleanValue());
}
if ((_defaultCatalog != null) &&
(!_defaultCatalog.equals(conn.getCatalog()))) {
conn.setCatalog(_defaultCatalog);
}
}
}
关闭连接 to dbcp（实际上是将连接归还给线程池）
在归还连接时，调用链路为 GenericObjectPool#returnObject > GenericObjectPool#addObjectToPool ，然后执行PoolableConnectionFactory#passivateObject的方法，有两个核心步骤进行数据库连接的配置：
1、如果连接不是自动提交且不是只读的，回滚
2、如果连接不是自动提交的，将其设置为自动提交
public void passivateObject(Object obj) throws Exception {
if(obj instanceof Connection) {
Connection conn = (Connection)obj;
// 判断是否需要rollback
if(!conn.getAutoCommit() && !conn.isReadOnly()) {
conn.rollback();
}
conn.clearWarnings();
// 如果连接不是autocommit，设置autocommit=true
if(!conn.getAutoCommit()) {
conn.setAutoCommit(true);
}
}
if(obj instanceof DelegatingConnection) {
((DelegatingConnection)obj).passivate();
}
}
为什么autocommit=false会消耗一半的性能？
我们先来看一下，应用程序执行一条sql在mysql general_log的显示。下表是dbcp的autocommit=false，执行一条查询语句时，mysql general_log显示的sql明细
| 序号 | sql | sql说明 | 源码位置 | 触发执行逻辑的框架 |
| 1 | SET autocommit=0 | 将连接autocommit属性设置为false，所有sql手动提交 | PoolableConnectionFactory#activateObject line: 704 | dbcp |
| 2 | select fields from task where id = 1 | 执行业务sql | ||
| 3 | commit | 提交事务，这个提交是mybatis框架执行的，前提条件是sqlSession不是spring管理的 | SqlSessionInterceptor#invoke line:362 | mybatis |
| 4 | select @@session.transaction_read_only | 查询session是否只读 | PoolableConnectionFactory#passivateObject line: 684 | dbcp |
| 5 | rollback | 回滚所有事务 | PoolableConnectionFactory#passivateObject line: 685 | dbcp |
| 6 | SET autocommit=1 | 将连接autocommit属性恢复为true | PoolableConnectionFactory#passivateObject line: 689 | dbcp |
那么autocommit=true时，general_log如何显示呢？如下表，仅有一条业务sql！！！
| 序号 | sql | sql说明 | 源码位置 | 触发执行逻辑的框架 |
| 1 | select fields from task where id = 1 | 执行业务sql |
至此，终于破案了。因为autocommit的频繁开启关闭，会导致以下问题：
autocommit的状态都需要执行额外的操作，这会增加CPU的工作负载。autocommit关闭的情况下，MySQL会将后续的操作视为一个事务，直到显式地执行COMMIT或ROLLBACK。频繁切换autocommit模式意味着频繁地开始和结束事务，这可能会导致事务日志的增长和额外的磁盘I/O操作。autocommit模式可能会导致锁定时间变长，增加了死锁的可能性，影响并发性能。autocommit状态的操作是在应用程序与数据库服务器之间进行的，那么这也会增加网络通信的开销。4. 结论与建议
结论
前提条件：在spring+mybatis+dbcp(autocommit=false)+mysql(autocommit默认true)的框架下
一般情况下应用层不需要开启事务的案例：
1、单条select
2、多条select
3、单条insert
4、单条update
5、单条delete
极端场景，就是以上5种案例占数据库所有sql的比例100%，那么你的数据库有50%的CPU是浪费的。
我的应用就属于以上极端场景，因此在调整autocommit后，配置不变的情况下，承担了原来翻倍的业务增长，为公司节省了数据库成本10w元/年
建议
1、dbcp连接池，将配置autocommit设置为true，与数据库保持一致。在需要事务控制的业务逻辑上，使用spring的@Transactional注解，或者使用mybatis原生的SqlSession管理事务等等。
2、其它连接池中间件如C3P0、HikariCP、BoneCP等都支持自定义配置autocommit，也可能存在本文实验的问题。验证方法很简单：将mysql数据库general_log开启，然后找一个接口调用一下，看看是不是有多余的5条sql出现。
