HelloWorld
登录

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

京东云开发者

2024-03-26

789 0 0
  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使用率 实验结果 数据汇总 TPS autocommit=false 数据库CPU autocommit=true 数据库CPU 1000

114.3
2
310

2000

125.1
2
313.6

3000

135.8
2
319.4

4000

147
2
325.7

5000

158.1
2
330.8

6000

170.7
2
335.8

7000

181.4
2
340.4

8000

192.3
2
346.1

9000

197(8.5k)
2
351.2

10000

1
256.6

11000

1
262.6

12000

1
267.2

13000

1
274.2

14000

1
280.3

15000

1
286.4

16000

1
290.4

17000

1
293.9

18000

1
298.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

1SET autocommit=0
2
3将连接autocommit属性设置为false，所有sql手动提交
4
5PoolableConnectionFactory#activateObject

line: 704

dbcp

2

1select fields from task where id = 1
2
3执行业务sql
4
5
6

3

1commit
2
3提交事务，这个提交是mybatis框架执行的，前提条件是sqlSession不是spring管理的
4
5SqlSessionInterceptor#invoke

line:362

mybatis

4

1select @@session.transaction_read_only
2
3查询session是否只读
4
5PoolableConnectionFactory#passivateObject

line: 684

dbcp

5

1rollback
2
3回滚所有事务
4
5PoolableConnectionFactory#passivateObject

line: 685

dbcp

6

1SET autocommit=1
2
3将连接autocommit属性恢复为true
4
5PoolableConnectionFactory#passivateObject

line: 689

dbcp

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

1sql
2
3sql说明
4
5源码位置
6
7触发执行逻辑的框架

1

1select fields from task where id = 1
2
3执行业务sql
4
5
6

至此，终于破案了。因为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出现。

点赞
收藏

评论区

加载中...

相关推荐

面试字节跳动Java工程师该怎么准备？值得收藏！

性能调优影响MySQLServer性能的相关因素1.商业需求对性能的影响2.系统架构及实现对性能的影响3.Query语句对系统性能的影响4.Schema设计对系统的性能影响5.硬件环境对系统性能的影响MySQL数据库锁定机制1.MySQL锁定机制简介2.各种锁定机制分析3.合理利用锁机制优化MySQLMySQL数据库Que

MySQL数据库性能优化六大技巧

数据库表表面上存在索引和防错机制，然而一个简单的查询就会耗费很长时间。Web应用程序或许在开发环境中运行良好，但在产品环境中表现同样糟糕。如果你是个数据库管理员，你很有可能已经在某个阶段遇到上述情况。因此，本文将介绍对MySQL进行性能优化的技巧和窍门。1.存储引擎的选择如果数据表需要事务处理，应该考虑使用InnoDB，因为它完全符合ACI

MySQL批量SQL插入性能优化

前言对于一些数据量较大的系统，数据库面临的问题除了查询效率低下，还有就是数据入库时间长。特别像报表系统，每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此，优化数据库插入性能是很有意义的。经过对MySQLinnodb的一些性能测试，发现一些可以提高insert效率的方法，供大家参考参考。方法1\.一条SQL语句

万界星空科技工时管理系统功能介绍

万界星空科技工时管理系统的使用价值包括提高工作效率、控制成本和优化资源、提高管理水平、提供法律合规支持以及增强员工满意度和参与感。通过使用工时管理系统，企业能够更好地管理和规划员工的工作时间，并提高工作效率和准确性，从而为企业的发展和运营带来积极的影响。

营销权益平台春晚技术探究| 京东云技术团队

一、引言在当前快速发展的互联网环境中，许多企业和服务都面临着高并发场景的挑战。随着用户规模不断增长，对于同一时间内大量用户请求的处理能力、系统性能、稳定性和容错性的要求也日益提高。高并发场景对系统架构设计、数据库设计、缓存策略、自动化运维、安全防护、成本、

企业降低成本与提升竞争力的之软件项目体检

通过Codigger之软件项目体检，软件质量得到了显著提升。它优化了代码结构和逻辑，使得软件运行更加流畅高效；增强了性能和稳定性，减少了系统崩溃和卡顿的情况；同时极大地增强了用户体验，让客户更加满意。