I am using C3P0 (0.9.5.2) connection pool to connect to to MySQL DB. I have set default statement timeout of 1 seconds. I see that during high load, some of the connection request timing out (checkoutTimeout is 1 sec), though the max pool capacity was not reached. On analyzing thread stack, I saw 'MySQL Cancellation timer' threads in runnable state. Probably there is bulk timeout which is causing a non responsive DB and not creating new connection within 1 sec.
Is there a way to minimize the cancellation timer impact and to ensure client timeout is not happened if the max pool capacity is not reached?
Even if the pool is not maxPoolSize, checkout attempts will time out if checkoutTimeout is set and new connections cannot be acquired within the timeout. checkoutTimeout is just that — a timeout — and will enforce a time limit regardless of cause.
If you want to prevent timeouts, you have to ensure connections can be made available within the time alotted. If something is making the database nonresponsive to connection requests, the most straightforward solution obviously is to resolve that. Other approaches might include setting a larger acquireIncrement (so that connections are more likely to be prefetched) or a larger minPoolSize (same).
Alternatively, you can choose a longer timeout (or set no timeout at all).