Why does hikari pool commit take so long?

Hello,

We are seeing that hikari.pool.commit takes quite long, sometimes more than 50 ms reaching up to 200 ms.

Commit takes around 25% of the transaction time.

Is it something to do with the Database (postgres) ? In my understanding, maybe not as SSL happens between the appln and DB. Any pointers as to how to start to troubleshoot this? What could be the issue ?
Please advice.

Finally the stack trace comes down to

That seems like a more appropriate question for the HikariCP project, no?

Seems to me that this is the database writing to the write ahead log. Maybe you misconfigured your connection pool? I think commit shouldn’t happen usually if you configure the connection pool properly. I think there should be a setting to tell it, that auto-commit is off by default, then it won’t have to run commit to reset the auto-commit mode.

Thanks for your reply beikov, can you pls elaborate a little as I am not an expert with HikariCP. Is it recommended to have auto-commit off or on ?
To add, there is no explicit commit, it is managed through spring transaction management.

Thanks sebersole, I have already posted the question here but no reply yet been a week.

You should always prefer to disable auto-commit and explicitly manage transactions.

People who say otherwise are trying to sell you something :laughing:

AFAIU you would just have to use this autoCommit=false setting to get the intended behavior: GitHub - brettwooldridge/HikariCP: 光 HikariCP・A solid, high-performance, JDBC connection pool at last.

Why does autocommit default to true if recommended is false ?
We have around 40-45 SQL modifications inside a transaction so not sure if it will be easy to manage the transaction explicitly. Even so, it will take some time to implement.
Is there any way we could counter this through other properties/configurations?

Current config:
hikari.connectionTimeout=4000
hikari.initializationFailTimeout=0
hikari.maximumPoolSize=10

No difference when we increase the pool size to 20.

Like we wrote before, this is not the place to ask questions about your connection pool implementation. There is nothing else we can say to help you. You will simply have to change your code to use explicit transactions.