what is mysql connection pool

Hi,
I have a nodejs MVC project in which a myql connection pool is created. Although i am working on a similar Spring MVC java project in which i have used JDBC. wondering whats the use of creating a mysql connection pool and how to do the same in spring MVC.

Here is node js code which create connection pool :
var createConnection = function () {
    var pool = mysql.createPool({
        connectionLimit: 10,
        host: config.db.host,
        user: config.db.user,
        password: config.db.password
    });
    pool.on("connection", function (connection) {
        connection.query("USE " + config.db.database, function (error, result) {
            if (error) {
                util.log("Couldn't USE db because: " + error);
            }
        });
    });
    return pool;
}
var connection = createConnection();

Open in new window


whereas in my spring MVC application i am having the following code in root-context.xml which creates connection and i autowire the things and use it directly in my code :

 <context:property-placeholder
            location="classpath:properties/${FLOCK_APPS_CONFIG}/database.properties"
            ignore-unresolvable="true"/>

    <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="${snippet.jdbc.url}"/>
        <property name="username" value="${snippet.jdbc.username}"/>
        <property name="password" value="${snippet.jdbc.password}"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="ds"></property>
    </bean>

Open in new window


I want to create a connection pool in my spring MVC code .
Please let me know how to do it and what are the advantages ?

Thanks
Rohit BajajAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mccarlIT Business Systems Analyst / Software DeveloperCommented:
Add the following dependency to your POM...

        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>

Open in new window


And then change line 5 from your context.xml above to this...

    <bean id="ds" class="org.apache.commons.dbcp.BasicDataSource">

Open in new window



You will probably want to check out the documentation for Apache Commons DBCP to see if there are any other properties that you would like/need to set and to get an idea about what it does...

But in a nutshell, connection pools are there to save the application from having to constantly create new connections to the database. This is an expensive operation so once a connection is first opened by the application, when it is finished with it, the connection is kept open so that the next time a connection is required, the pool just hands this free, open connection back to the app. It also allows options to limit the total number of concurrent connections that the app can use, etc.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mccarlIT Business Systems Analyst / Software DeveloperCommented:
You can go for a higher version if you don't need to support older Java/JDBC versions. The main DBCP page that I linked to above gives the list of what versions are supported under which versions of Java/JDBC.
Rohit BajajAuthor Commented:
Here is what i understood please correct my understanding :
1) In my code  there is only one connection maintained to the mysql server... ?
If the connection gets broken will it try to reconnect automatically ?

2) In maintain connection pool  as i understand the application will maintain say 5 connections to the same mysql server and if there is a db hit it will check if a connection is alive otherwise it will give another alive connection from the pool ?
what happens if a connection in a pool of 5 connections get broken ? does it automatically get renewed in background ?

Thanks
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mccarlIT Business Systems Analyst / Software DeveloperCommented:
Ok...

1) It depends on how your app uses it. In your case, I see you have a JdbcTemplate instance, and so that works on a "connection per query/update" pattern, ie. get a connection to the DB, execute the query or update, then close the connection. If you subsequently execute another query via that same JdbcTemplate it will open a new connection, do that query, close the connection. Therefore, yes, this can be very expensive in terms of performance.

If you were to use a TransactionManager (not that I've seen any evidence of you using that in your code), it would become a "connection per transaction" pattern.

Not that because of the above, there is NO connection maintained anywhere and therefore NO reconnection logic at all. The connection isn't even open for that long, but if something happens that breaks the connection while a query is executing, then all that happens is that thejdcbTemplate  method that you called would throw an Exception and you would handle it however you want (eg. retrying the query if it is a TransientDataAccessException perhaps)

Also, note that you could change your use of DriverManagerDataSource to use a SingleConnectionDataSource, and this would then hold one connection and keep it open between queries, but it IS NOT THREADSAFE and so is unlikely to be useful in most situations.

2) Your general understanding of the connection pool sounds about right. This and the reconnection behaviour is implementation dependant and in most cases is quite configurable, so it's not something that anyone could answer a black & white YES or NO. Check the docs linked to above for all that stuff.
Rohit BajajAuthor Commented:
Thanks I will go through the link.
Also in my case where i am using DriverManagerDataSource and JdbcTemplate .. Is this ThreadSafe ?
What if two threads try to upate the same row ... In this case each will be opening a new connection... and then performing an update...
I guess this case will be handled by Database itself ?
Rohit BajajAuthor Commented:
When you say SingleConnectionDataSource is not ThreadSafe and it maintains a single connection...
what i understand is that during multiple hits to web Application There will be multiple DB hits and for each user there will be a different thread performing it. so different threads can cause same row to update... may result in conflict....
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Also in my case where i am using DriverManagerDataSource and JdbcTemplate .. Is this ThreadSafe ?

Yes, this is thread safe. Because each thread would get a separate Connection object from the datasource, the Java code in each thread is operating on different objects, so this is fine.

What if two threads try to upate the same row ... In this case each will be opening a new connection... and then performing an update...
I guess this case will be handled by Database itself ?

Yes, this one would be handled by the DB, just the same as if you had two users connecting to MySQL via an app such as MySQLQueryBrowser.


When you say SingleConnectionDataSource is not ThreadSafe   ..........    so different threads can cause same row to update... may result in conflict....

No, that's not what we mean by thread safe. In this context, I am not really talking specifically about any database operation. We are talking about the actual Java object level here.

The problem is that if two threads are using the SingleConnectionDataSource, then when they go to get the Connection object from that data source, it will return the exact same Connection object to both threads. Now, technically, the JDBC spec says that Connection objects SHOULD be thread-safe but it is totally up to the JDBC driver to implement it in that way. Some may, some may not. Even though the spec says that, it is still recommended to only use one thread to access a connection at any one time.
Rohit BajajAuthor Commented:
Hi,
Is there any way to verify like in my current codebase that each time i am making a query a new connection is made and then closed ?
something like i use hashcode of the connection object..and see it changes with every request.
mccarlIT Business Systems Analyst / Software DeveloperCommented:
No, not easily. The connection object is buried deep inside the code of JdbcTemplate and is not exposed in anyway for you to get it's hashcode, etc.

There may be ways around this, either write your own DataSource that delegates to a real DataSource and then you can examine the Connection object on the way through. Or you may be able to do something with a NativeJdbcExtractor but I have never done anything like that so I don't know exactly what would be necessary.

The other way would be to debug a running application with Eclipse (or some IDE) and put breakpoints where the connection is used, and then you can inspect the value of the Connection for each query.

Anyway, the code is what it is and I know that it will always return a new Connection object for each query. You can double check this in the Javadoc for DriverManagerDataSource.
Rohit BajajAuthor Commented:
Hi,
I am using the dbcp2 like :

 <bean id="ds" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="${snippet.jdbc.url}"/>
        <property name="username" value="${snippet.jdbc.username}"/>
        <property name="password" value="${snippet.jdbc.password}"/>
        <property name="initialSize" value="10"/>
    </bean>

Open in new window


included the following in pom.xml

 <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.1.1</version>
        </dependency>

Open in new window


I need to understand what other parameters i should put in the bean apart from initialSize ?
As per my understanding initialSize=10 will create 10 connection objects.
If i see on https://commons.apache.org/proper/commons-dbcp/apidocs/index.html
it says - The pool is initialized the first time one of the following methods is invoked: getConnection, setLogwriter, setLoginTimeout, getLoginTimeout, getLogWriter

* So does that mean that the connection wont be ready beforehand and pool of 10 connections will be made after the first query to database ?

* what are borrows connection as mentioned in        setMaxTotal

* How do i set Max number of connections top limit ? Will just setting initial size to 10 mean maximum connection objects will be 10 and does dbcp automatically renews a connection object if the connection gets broken ?

* what exactly is abandoned connection do i need to specify something to remove it ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.