Springboot upgrade from 1.x to 2.3.x MySQL performance issue

Question

Trying to upgrade 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.

This line is added for the upgraded 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 

Any suggestions from experts to match DB CPU and connections with previous version?

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


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

DB process list have several "cleaned up" state too.

gradle dependencies

    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")

Logs says its picking right tomcat.datasource as

org.apache.tomcat.jdbc.pool.DataSource@35ee466f {ConnectionPool[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; timeBetweenEvictionRunsMillis=5000; numTestsPerEvictionRun=0; minEvictableIdleTimeMillis=30000; testWhileIdle=false; testOnConnect=false; password=********; url=jdbc:mysql:aurora://DB/schema?connectTimeout=2000; username=root; validationQuery=SELECT 1; validationQueryTimeout=5; validatorClassName=null; validationInterval=3000; accessToUnderlyingConnectionAllowed=true; removeAbandoned=true; removeAbandonedTimeout=60; logAbandoned=false; connectionProperties=null; initSQL=null; jdbcInterceptors=null; jmxEnabled=true; fairQueue=true; useEquals=true; abandonWhenPercentageFull=0; maxAge=0; useLock=false; dataSource=null; dataSourceJNDI=null; suspectTimeout=0; alternateUsernameAllowed=false; commitOnReturn=false; rollbackOnReturn=false; useDisposableConnectionFacade=true; logValidationErrors=false; propagateInterruptState=false; ignoreExceptionOnPreLoad=false; useStatementFacade=true; }

slow query log - after removing server/sensitive details [1]: https://i.stack.imgur.com/X5jbh.jpg

From comment:

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

For this query (from Comment):

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'

history may benefit from

INDEX(capi_id, major_revision, minor_revision, version)

(The order of the index's columns does not matter for this query.)

Please provide SHOW CREATE TABLE history.

It seems quite unusual for those INSERTs to be taking 2-4 seconds. Perhaps some of them are INSERT ... SELECT ... and I can't see the full query?

Answer 2

Solution:DB connection pool issue resolved by switching to hikari connection pool which is default for springboot 2.x. There were high latency and 504 gatewaytimeout(or nginx 499)in application load test which is resolved by reducing db & property reloads(PeriodicReloadingTrigger) VisualVM helped to connect to remote ec2 on load test to see waiting threads,which were hikari hits.