#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使用率

| TPS | 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明细

那么autocommit=true时，general_log如何显示呢？如下表，仅有一条业务sql！！！

| 序号 | sql | sql说明 | 源码位置 | 触发执行逻辑的框架 |
| — | — | — | — | — |
| 1 | select fields from task where id = 1 | 执行业务sql | | |

至此，终于破案了。因为autocommit的频繁开启关闭，会导致以下问题：

1.性能开销：每次改变autocommit的状态都需要执行额外的操作，这会增加CPU的工作负载。

2.事务管理：在autocommit关闭的情况下，MySQL会将后续的操作视为一个事务，直到显式地执行COMMIT或ROLLBACK。频繁切换autocommit模式意味着频繁地开始和结束事务，这可能会导致事务日志的增长和额外的磁盘I/O操作。

3.锁定资源：在事务处理期间，可能会锁定一些资源，直到事务提交或回滚。频繁切换autocommit模式可能会导致锁定时间变长，增加了死锁的可能性，影响并发性能。

4.网络开销：如果更改autocommit状态的操作是在应用程序与数据库服务器之间进行的，那么这也会增加网络通信的开销。

4. 结论与建议

结论

前提条件：在spring+mybatis+dbcp(autocommit=false)+mysql(autocommit默认true)的框架下

1.每次调用SqlSessionTemplate（属于mybatis-spring）的sql方法，应用程序与mysql之间会多出5次网络io，mysql多执行5个sql

2.在极端场景下，mysql 性能下降50%。

一般情况下应用层不需要开启事务的案例：
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出现。

原文链接: https://juejin.cn/post/7350214909056483366

文章标签

后端开发程序员

DBCP一个配置，浪费了MySQL 50%的性能！

研究背景

数据库性能的重要性

连接池在数据库性能中的作用

研究问题及目的

2. 实验设计与方法

实验环境说明