I am having an Spring MVC + Mysql (JDBC 4) + c3p0 0.9.2 project.
In c3p0 maxIdleTime value is 240 (i.e 4 mins.) and wait_timeout in my.ini of Mysql to 30 seconds.
According to c3p0
maxIdleTime:
(Default: 0)
Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.
According to Mysql
wait_timeout: The number of seconds the server waits for activity on a
noninteractive connection before closing it.
Now i am having some douts on this:(some answers are known to me,Just wated to be sure I am correct or not)
- unused connection means the connection which are in
sleep state according to mysql(?)
- What is interactive and noninteractive connections?
- Is unused connections and noninteractive coonections are same? because my DBA set
wait_timeout to 30 seconds (he come to this value by observing DB server so that very less amount of connections be in sleep mode) this means an connection can be in sleep mode for 30 seconds after that it will be closed but at the otherhand c3p0's maxIdleTime is set to 240 seconds so whats this maxIdleTime setting playing role in this case.
- What is
interactive_timeout?
First Let's understand the mysql properties.
interactive_timeout : interactive time out for mysql shell sessions
in seconds like mysqldump or mysql command line tools. connections are in sleep state. Most of the time this is set to higher value because you don't want it to get disconnected while you are doing something on mysql cli.wait_timeout
: the amount of seconds during inactivity that MySQL will wait before
it will close a connection on a non-interactive connection in
seconds. example: connected from java. connections are in sleep state.
Now let's understand c3po properties and it's relation with DB props.(I am just gonna copy from your question)
maxIdleTime: (Default: 0) Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never
expire.
This refers to how long a connection object can be usable and will be available in pool. Once the timeout is over c3po will destroy it or recycle it.
Now the problem comes when you have maxIdleTime higher then the wait_timeout.
let's say if the mxIdleTime : 50 secs and wait_timeout : 40 s then there is a chanse that you will get Connection time out exception: Broken Pipe if you try to do any operation in last 10 seconds. So maxIdelTime should always be less then wait_timeout.
Instead of maxIdleTime you can you the following properties.
idleConnectionTestPeriod sets a limit to how long a connection will
stay idle before testing it. Without preferredTestQuery, the default
is DatabaseMetaData.getTables() - which is database agnostic, and
although a relatively expensive call, is probably fine for a
relatively small database. If you're paranoid about performance use a
query specific to your database (i.e. preferredTestQuery="SELECT 1")maxIdleTimeExcessConnections will bring back the connectionCount back
down to minPoolSize after a spike in activity.
Please note that any of the pool property(eg. maxIdleTime) only affects to connection which are in pool i.e if hibernate has acquired a connection and keeps it idle for than maxIdleTime and then tries to do any operation then you will get "Broken Pipe"
It is good to have lower wait_timeout on mysql but It's not always right when you have an application already built.
You have to make sure before reducing it that in your application you are not keeping connection open for more that wait_time out.
You also have to consider that acquiring a connection is expensive task and if have wait time out too low then it beats the whole purpose of having connection pool, as it will frequently try to acquire connections.
This is especially important when you are not doing connection management manually for example when you use Spring transnational API. Spring starts transaction when you enter an @Transaction annotated method so it acquires a connection from pool. If you are making any web service call or reading some file which will take more time than wait_time out then you will get exception.
I have faced this issue once.
In one of my projects I had a cron which would do order processing for customers. To make it faster I used batch processing. Now once I retrieve a batch of customers and do some processing(no db calls). When I try to save all the orders I used to get broken pipe exception. The problem was my wait_timeout was 1 minute and order processing was taking more time then that. So We had to increase it to 2 minutes. I could have reduced the batch size but that was making the overall processing slower.
unused connection means the connection which are in sleep state according to mysql(?)
According to mysql, this simply means that a connection was established with mysql/db, but there has been no activity here for the past amount of time and due to configuration / settings of mysql(which can be changed), the connection was destroyed.
What is interactive and noninteractive connections?
Interactive connections are when your input hardware(keyboard) interacts using command line with mysql. In short where you write the queries
Non interactive or rather wait_timeout queries are those for which your code establishes connection with mysql.
Is unused connections and noninteractive coonections are same? because my DBA set wait_timeout to 30 seconds (he come to this value by observing DB server so that very less amount of connections be in sleep mode) this means an connection can be in sleep mode for 30 seconds after that it will be closed but at the otherhand c3p0's maxIdleTime is set to 240 seconds so whats this maxIdleTime setting playing role in this case.
MaxIdleTime is done by your code at hibernateJpa Configuration where you ask your code itself to close a hibernate connection(for example) after a connection is unused. You have ownership of this as a coder.
Wait_timeout on other hand is from mysql side. So it is upon the DB administrator to set it up and change.
What is interactive_timeout?
Again, interactive timeout is when you are writing queries after connecting to mysql from keyboard on command line and that time conf in mysql gets up.
If you want to know more about how to change these values, go through this link:
http://www.serveridol.com/2012/04/13/mysql-interactive_timeout-vs-wait_timeout/
Hope now it is clear to you.:)
