Can Tomcat container improve JDBC performance / scalability?

Question

I'm using JDBC to query postgres database in my WebApp deployed in Apache Tomcat. Priority for the project is performance, that's why I decided to use JDBC. The app will have to handle multiple requests at the same time.

I am creating my connection in Spring configuration:

@Bean
public Connection connection() throws SQLException, ClassNotFoundException
{
    LOG.info("Creating JDBC connection...");
    Class.forName(this.dbDriver);
    return DriverManager.getConnection(this.dbUrl, this.dbUsername, this.dbPassword);
}

which is then injected into DAO and reused everytime.

I also have second implementation of the DAO that uses Hibernate, but it is much slower.

Questions:
- Will this solution scale? I.e. will it be fast, when for multiple requests at the same time? Does JDBC connection support concurrent requests or will it block on each request?
- Should I use some Tomcat's connection pooling mechanism (which?) to support concurrency? Or can I implement some connection pooling myself?

EDIT: I forgot to add: DB is read-only and I don't want blocking.
EDIT2: To clarify: I'm not opening and closing connections. I keep 1 connection open all the time.

Answer 1

Here are some answers that you might find helpful:

1. Simply using JDBC isn't a guarantee of performance or scalability. Depends more on your schema, indexing, queries, data sets, etc.
2. This is a poor way to create a database connection. It's not even correct Spring. Read up on how to configure Spring properly. When you call new or use getConnection Spring is not involved. Let the bean factory do this.
3. A better idea is to set up a JNDI connection pool on Tomcat and configure Spring to use it.
4. JDBC will block on each request. That's a good thing.
5. Use what Tomcat will provide. You won't improve on existing connection pool implementations; it's likely you'll do far worse.

Answer 2

Will this solution scale? I.e. will it be fast, when for multiple requests at the same time? Does JDBC connection support concurrent requests or will it block on each request?

IT won't, if each request create db connection, query the database, close the connection, Then it would slow as making db connection is costly operation.

JDBC tries to create connection per request/thread which would be blocked but after creation requests would be concurrent. But This would surely get connection resource crunch on higher amount of concurrent request.

Should I use some Tomcat's connection pooling mechanism (which?) to support concurrency? Or can I implement some connection pooling myself?

There are plenty open source JDBC connection pool library available, no need to reinvented the same wheel. Example - BoneCP, C3p0, DBCP, HikariCP etc.

References:

• Connection Pool Comparison - c3p0 vs DBCP
• BoneCP vs DBPool vs c3p0

Answer 3

"Priority is performance" is vague, you should clarify what performance criteria are needed. "Multiple requests at the same time" doesn't sound extreme at all, and by itself isn't grounds for ruling out using a framework like Spring.

At some point Spring will incur some overhead. The most significant overhead would come from AOP, where the infrastructure can add a lot of layers of method calls to whatever you're doing. But this is a concern only for extremely high performance applications, you are a very long way from that point. A web application using Spring running in Tomcat should have no problem handling hundreds of concurrent connections.

Tomcat creates a pool of threads and allocates a thread to each request. When you setup Spring with a datasource that uses a connection pool, and you create DAOs that use JdbcTemplate, Spring assigns a database connection from the connection pool to the thread as a threadlocal. JDBC blocks, but only the thread making the query is affected, other requests can still execute independently, each on their own thread.

Using a single connection is a very bad idea. Typically connections are internally synchronized so that only one query can run at a time (if not for compliance with a spec, it would only make sense for them to be designed this way for protection from misuse by badly-behaved clients), your requests will have to wait to get access to the shared connection, your application won't be able to execute queries concurrently and performance will be miserable. If that's not enough, your application's resilience will be impacted, any network glitch or database downtime will cause your connection to go bad, and with no ability to renew the connection your application will stop working until it's restarted. Your first priority should be using a connection pool (an existing one). The connection pool will manage its connections, it can test them to make sure they're valid and replace stale ones with new connections, so you don't have to restart to recover from a network problem.

Next tune your queries, make sure they are efficient and are using indexes. There's no reason not to use spring-jdbc instead of raw JDBC, it reduces the cut-n-pasting that accompanies JDBC and adds great features like named query parameters and data access exception translation.

After that, introduce some kind of caching. There are a lot of ways to do this, depending on how up-to-date and consistent the data has to be, where you want the cache to live, and how extreme a performance benefit you really need. You can have caching going on at multiple levels, the database will cache results from recently-run queries, you can put a webserver in front of your application that caches GET requests. Spring introduces a cache abstraction so you can manage caching separately from application code.

Application-instance-level caching can be tricky, because it can interfere with resilience and scalability concerns. You may want to deploy your application in a cluster or otherwise as multiple instances in order to avoid making the server a single point of failure. In particular avoid using the HttpSession for anything but the bare minimum. Session changes either have to get propagated across cluster nodes, which creates more communication traffic as the number of nodes increases, or requests have to get pinned to a single server, reducing resilience (for these users the instance they're pinned to is now a SPOF again) and hurting load-balancing. If this is a read-only application then this will likely not be a concern, you should be able to do some aggressive caching. You could even consider having a dedicated database with tables that map to your views that are loaded from your original datasource, so the queries don't have to do joins and don't have any contention with what other applications may be doing.

Answer 4

You'd better use technology such as database connection pool. Open connection and close connection takes time and consume resources.

In the java world, there are several db pool implementation, you don't need to reinvent the wheels.

You can useApache DBCP.

Here is the some introduction you can read http://www.snaq.net/java/DBPool/