CODEWITHSUNDEEP
mysqlspringhibernatec3p0
Zoidberg
Zoidberg

Reputation: 10323

What is the best maximum connections setting for MySQL when using c3p0 connection pooling

To being the question, I will describe our current production environment.

I have configured c3p0 with a minimum connection pool size of 1 with an increment value of 3 and a maximum of 20 connections. So my question is, what should my maximum connections to MySQL be? Should it be the max pool size times the number of clients (20 * 300 = 6000)? Or should it be less? Will an error occur if c3p0 has say, 3 connections already and tries to obtain another and MySQL is at it's max?

I do not think that all clients will need their maximum number all at the same time, but I do want to prevent any errors from happening if a fringe case occurs.

Upvotes: 0

Views: 1388

Answers (1)

Steve Waldman
Steve Waldman

Reputation: 14073

In theory, as you say, your MySQL could see up to 6000 Connections, so to be safe, that's the answer.

But you really don't want it to have 6000 Connections open. If each pool has minPoolSize of 1 and maxPoolSize of 20, it sounds as though you expect clients to often be quiescent, but to occasionally spike in usage. Unless the spikes are likely to be highly correlated in time, your usual load should be much, much lower.

By default, c3p0 Connection pools will grow quickly with spikes in load, but not decay. If you set an aggressive maxIdleTime, or better yet maxIdleTimeExcessConnections on your c3p0 pools, you can ensure that quiescent pool hold few Connections and reduce the likelihood that you will ever approach the theoretical max of 6K.

As to the MySQL setting, you can set it to 6K to be safe, or set it much lower so that you see errors rather than sluggishness if you are overtaxing the DBMS. It might be best to estimate the peak use you expect, set the MySQL max to maybe double that, and see whether your load expectations are dramatically violated (i.e. if errors occur because the DBMS refuses Connections).

With 300 distinct databases, that implies 300 c3p0 DataSources, which may lead to a high overhead in Threads and thread management. c3p0's numHelperThreads defaults to 3, and you don't want to go lower than that. So, that's something to think about.

Upvotes: 1

Related Questions