First, let's understand the properties of mysql.
interactive_timeout : interactive timeout for mysql shell sessions in seconds, for example, in mysqldump or mysql command line commands. connections are in a state of sleep. In most cases, this value is greater because you do not want it to shut down when you do something in mysql cli.wait_timeout : the number of seconds during inactivity that MySQL will wait before it closes the connection over a non-interactive connection in seconds. example: connected from java. connections are in a state of sleep.
Now let's understand the properties of c3po and this relationship with DB details (I'm just going to copy from your question)
maxIdleTime : (Default: 0) Seconds. The connection may remain empty, but unused before discarding. Zero means idle connections never expire.
This refers to how long the connection object can be used and will be available in the pool. Once the timeout is over, c3po will destroy it or recycle it.
Now the problem occurs when you have maxIdleTime higher than wait_timeout . let's say if mxIdleTime : 50 secs and wait_timeout : 40 s , then there is a chance you will get Connection time out exception: Broken Pipe if you try to perform any operation in the last 10 seconds. Therefore, maxIdelTime should always be less than wait_timeout .
Instead of maxIdleTime, you can get the following properties.
idleConnectionTestPeriod sets a limit on the duration of the connection; idle before testing. Without preferredTestQuery , DatabaseMetaData.getTables() is a database agnostic by default, and although a relatively expensive call is probably good for a relatively small database. If you're paranoid about performance, use a query specific to your database (ie preferredTestQuery="SELECT 1")maxIdleTimeExcessConnections will return connectionCount back up to minPoolSize after a burst of activity.
Please note that any property of the pool (for example, maxIdleTime ) affects only the connection that is in the pool , i.e. if hibernate has acquired a connection and keeps it inactive than maxIdleTime, and then tries to perform any operation, you will get a "Broken Pipe"
It is good to have a bottom wait_timeout on mysql, but this is not always correct when you already have the application installed. Before you reduce it, you must make sure that in your application you do not open the connection for even wait_time .
You should also consider that acquiring a connection is a costly task, and if the latency is too low, then it hits the whole goal of having a connection pool, as it often tries to get connections.
This is especially important if you are not manually managing the connection, for example, when using the cross-border Spring API. Spring starts the transaction when you introduce the annotated @Transaction method to get the connection from the pool. If you make any web service call or read some file that takes longer than wait_time, you will get an exception.
I ran into this problem once.
In one of my projects, I had a cron that would process orders for clients. To speed things up, I used batch processing. Now, as soon as I get the client package and do some processing (no db calls). When I try to save all the orders that I used to get a pipe failure. The problem was that my wait_timeout was 1 minute and order processing took longer. Therefore, we had to increase it to 2 minutes. I could reduce the batch size, but this slowed down the overall processing.