Springboot从1. x升级到2.3. x MySQL性能问题

Question

正在尝试升级Springboot

from 1.x/java8  to 2.3.x/java 11(AWS Corretto 11)

MySQL: Aurora
Connectionpool: tomcat datasource

Issue: with the upgraded version DB CPU go very high and DB connections in 800-1000 where as 1.x with same connection pool was 200-300 connections and CPU in 40s
This is detected in performance test.

该行是为升级的2.3添加的。x

 "spring.datasource.type=org.apache.tomcat.jdbc.pool.DataSource"


## DB connection pool
    spring.datasource.type=org.apache.tomcat.jdbc.pool.DataSource
    spring.datasource.tomcat.max-wait=30000
    spring.datasource.tomcat.max-active=150
    spring.datasource.tomcat.test-on-borrow=true
    spring.datasource.tomcat.max-idle=150
    spring.datasource.tomcat.initialize-size=10
    spring.datasource.tomcat.min-idle=10
    spring.datasource.tomcat.validation-query=SELECT 1
    spring.datasource.tomcat.validation-query-timeout=5
    spring.datasource.tomcat.time-between-eviction-runs-millis=5000
    spring.datasource.tomcat.min-evictable-idle-time-millis=30000
    spring.datasource.tomcat.remove-abandoned=true
    spring.datasource.tomcat.remove-abandoned-timeout=60
    spring.datasource.tomcat.default-auto-commit=true 

专家有什么建议来匹配数据库CPU和与以前版本的连接？

springboot 1.x:
CPU:40-50%
Connections : 200-300


springboot 2.3.x:
CPU:100%
Connections : 800-1000

DB进程列表也有几个“清理”状态。

gradle依存关系

    implementation("org.springframework.boot:spring-boot-starter-data-jpa"){
    exclude group: 'org.elasticsearch.client', module: 'elasticsearch-rest-client'
    exclude group: 'com.zaxxer', module: 'HikariCP'
}
implementation('org.springframework.boot:spring-boot-starter-jdbc') {
    exclude group: 'com.zaxxer', module: 'HikariCP'
}
implementation('org.apache.tomcat:tomcat-jdbc')
implementation("org.springframework.boot:spring-boot-starter-data-rest")

日志上说它的采摘tomcat.datasource

组织。阿帕奇。公猫jdbc。水塘DataSource@35ee466f{连接池[defaultAutoCommit=true；defaultReadOnly=null；defaultTransactionIsolation=-1；defaultCatalog=null；driverClassName=org.mariadb.jdbc.Driver；maxActive=150；maxIdle=150；minIdle=10；initialSize=10；maxWait=30000；testOnBorrow=true；testOnReturn=false；TimebetweenvictionRunsMillis=5000；NumTestsPerviectionRun=0；MinevictableTimeMillis=30000；testWhileIdle=false；testOnConnect=false；密码=********; url=jdbc:mysql:aurora://DB/schema?connectTimeout=2000; 用户名=根；validationQuery=选择1；validationQueryTimeout=5；validatorClassName=null；validationInterval=3000；AccessToUnderlyingConnectionLowed=true；removeAbandoned=true；removeAbandonedTimeout=60；LOG放弃=false；connectionProperties=null；initSQL=null；jdbcInterceptors=null；jmxEnabled=true；fairQueue=true；useEquals=true；当百分比为0时放弃；最大年龄=0；useLock=false；数据源=null；数据源jndi=null；suspectTimeout=0；alternateUsernameAllowed=false；commitOnReturn=false；回滚返回=false；useDisposableConnectionFacade=true；logValidationErrors=false；传播中断状态=false；ignoreexceptionnonpreload=false；useStatementFacade=true；}

查询日志速度慢-删除服务器/敏感详细信息[1]后：https://i.stack.imgur.com/X5jbh.jpg

来自评论：

CREATE TABLE history (
    capi_id varchar(60) NOT NULL, 
    major_revision bigint(20) NOT NULL, 
    minor_revision bigint(20) NOT NULL, 
    version varchar(20) NOT NULL, 
    document_id varchar(60) DEFAULT NULL, 
    operation varchar(10) DEFAULT NULL, 
    status varchar(10) DEFAULT NULL, 
    transaction_id varchar(60) DEFAULT NULL, 
    PRIMARY KEY (capi_id,major_revision,minor_revision,version)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Answer 1

解决方案：DB连接池问题通过切换到hikari连接池来解决，hikari连接池是springboot 2的默认设置。x、 应用程序负载测试中存在高延迟和504网关超时（或nginx 499），这可以通过减少db来解决

Answer 2

对于此查询（来自评论）：

select  history0_.capi_id as capi_id1_9_0_,
        history0_.major_revision as major_re2_9_0_,
        history0_.minor_revision as minor_re3_9_0_,
        history0_.version as version4_9_0_,
        history0_.document_id as document5_9_0_,
        history0_.operation as operatio6_9_0_,
        history0_.status as status7_9_0_,
        history0_.transaction_id as transact8_9_0_
    from  history history0_
    where  history0_.capi_id='ID'
      and  history0_.major_revision=1485757299000
      and  history0_.minor_revision=0
      and  history0_.version='publish'

历史可能受益于

INDEX(capi_id, major_revision, minor_revision, version)

（索引列的顺序对于此查询并不重要。）

请提供“显示创建表历史记录”。

对于那些插入时间为2-4秒的插入来说，这似乎很不寻常。也许其中一些是插入。。。选择 我看不到完整的查询？