CODEWITHSUNDEEP
javadatabasepostgresql
Banana Tech
Banana Tech

Reputation: 337

Hikari DB Connection Maximum Pool Understanding and tuning

I would to understand more about the connection pool handling using Hikari.

The problem I'm facing is connection will hit the maximum which is 250 then back to 10. There are 2 things I would like to know/tune

  1. Why connection spike to maximum then back to 10? How to check what's happening in the back ground?
  2. How to make the connection more stable?

The scenario is as below

  1. Running 100 threads calling from soap-ui to application server concurrently for 30 minutes
  2. Each thread inserts into 4 tables and commit as transaction when exiting the transaction scope
  3. running PostgreSQL database
  4. When connection pool hits the max, then insertion will become slower.

Check out the attached picture. It's Hikari Mbean during runtime. The darker color of active connection means it's hitting the maximum. The lighter green color means active connection pool drop back to 10.

enter image description here

These are the database settings:

<Resource name="jdbc/maxpool" auth="Container"
        factory="com.zaxxer.hikari.HikariJNDIFactory"
        type="javax.sql.DataSource"
        poolName="ah pool"
        idleTimeout="0"
        registerMbeans="true"
        maximumPoolSize="250"
        maxLifetime="3000"
        minimumIdle="5"
        leakDetectionThreshold="90000"
        dataSourceClassName="org.postgresql.ds.PGSimpleDataSource"
        dataSource.url="jdbc:postgresql://10.1.1.1:5444/xxxx"
        dataSource.user="ereport"
        dataSource.password="zzzz"/>


#hibernate.hbm2ddl.auto=create
#hibernate.generate_statistics=true
#hibernate.cache.use_structured_entries=true
#hibernate.ejb.naming_strategy=my.mimos.jpa.naming.CustomImprovedNamingStrategy
javax.persistence.lock.timeout=5000
hibernate.id.new_generator_mappings=true
hibernate.id.optimizer.pooled.prefer_lo=true
hibernate.default_batch_fetch_size=20
hibernate.jdbc.fetch_size=50
hibernate.jdbc.batch_size=60
hibernate.jdbc.lob.non_contextual_creation=true
hibernate.order_inserts=true
#hibernate.connection.release_mode=after_statement
## uncomment the following 2 properties if batch update is required
#hibernate.order_updates=true
#hibernate.jdbc.batch_versioned_data=true

# Hibernate Envers (audit trail) properties
## There are 2 strategies: DefaultAuditStrategy and ValidityAuditStrategy
## ValidityAuditStrategy will store both the start revision and the end revision instead of storing start revision only
org.hibernate.envers.audit_strategy=org.hibernate.envers.strategy.DefaultAuditStrategy
org.hibernate.envers.audit_table_suffix=_h
org.hibernate.envers.do_not_audit_optimistic_locking_field=false
"efamily-persistence.properties" 40L, 2039C

Upvotes: 2

Views: 4616

Answers (2)

reinardhz
reinardhz

Reputation: 61

Don't set the minimumIdle I think.

minimumIdle
This property controls the minimum number of idle connections that HikariCP tries to maintain in the pool. If the idle connections dip below this value and total connections in the pool are less than maximumPoolSize, HikariCP will make a best effort to add additional connections quickly and efficiently. However, for maximum performance and responsiveness to spike demands, we recommend not setting this value and instead allowing HikariCP to act as a fixed size connection pool. Default: same as maximumPoolSize.

source: https://github.com/brettwooldridge/HikariCP

Upvotes: 0

Banana Tech
Banana Tech

Reputation: 337

to debug this issue, i found a SQL command quite useful. The issue is because some of the tables are locked. Run this when the insertion is slow, then can get some clues where the problem is.

SELECT * FROM pg_stat_activity

Upvotes: 1

Related Questions