Connection pooling is a
technique used to improve performance in applications with dynamic
database-driven content. Opening and closing database connections may not seem
like a costly expense, but it can add up rather quickly. Let's assume it takes
5ms to establish a connection and 5ms to execute your query (completely made up
numbers), 50% of the time is establishing the connection. Extend this to
thousands or tens of thousands of requests and there is a lot of wasted network
time. Connection pools are essentially a
cache of open database connections. Once you open and use a database
connection instead of closing it you add it back to the pool. When you go to
fetch a new connection, if there is one available in the pool, it will use that
connection instead of establishing another.
Why Use a
Connection Pool?
- Constantly
opening and closing connections can be expensive. Cache and reuse.
- When
activity spikes you can limit the number of connections to the database.
This will force code to block until a connection is available. This is
especially helpful in distributed environments.
- Split
out common operations into multiple pools. For instance, you can have a
pool designated for OLAP connections and a pool for OLTP connections each
with different configurations.
Reducing the connection pool size alone, in
the absence of any other change, decreased the response times of the
application from ~100ms to ~2ms -- over 50x improvement.
But why?
We seem to have understood in other parts of computing recently
that less is more. Why is it that with only 4-threads an nginx web-server
can substantially out-perform an Apache web-server with 100
processes? Isn't it obvious if you think back to Computer Science 101?
Even a computer with one CPU core can "simultaneously"
support dozens or hundreds of threads. But we all [should] know that this is
merely a trick by the operating system though the magic of time-slicing.
In reality, that single core can only execute one thread at a
time; then the OS switches contexts and that core executes code for another
thread, and so on. It is a basic Law of Computing that given a single CPU
resource, executing A and B sequentially will always be
faster than executing A and B "simultaneously"
through time-slicing. Once the number of threads exceeds the number of CPU
cores, you're going slower by adding more threads, not faster.
That is almost true...
package connectionPooling;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/** Copyright (c), ParasChawla javdadsalgo.blogspot.in */
public class
PreparedStatementUseConnectionPooling {
public static void main(String... arg) throws SQLException {
ConnectionPool connectionPool = new ConnectionPool("oracle.jdbc.driver.OracleDriver",
"jdbc:oracle:thin:@localhost:1521:orcl", "ankit", "Oracle123", 5, 10, true);
Connection con = connectionPool.getConnection();
System.out.println("We have got connection from ConnectionPool class");
PreparedStatement prepStmt = con.prepareStatement("select ID, NAME from
EMPLOYEE");
ResultSet rs = prepStmt.executeQuery();
while (rs.next()) {
System.out.print(rs.getInt("ID") + " ");
System.out.println(rs.getString("NAME"));
}
if (rs != null)
rs.close(); //
close resultSet
if (prepStmt != null)
prepStmt.close(); // close PreparedStatement
connectionPool.free(con);
System.out.println("We have free/released connection to ConnectionPool
class");
}
}
/*
* OUTPUT
*
* We have got connection
from ConnectionPool class
* 7 ankit
* 8 rohit
* We have free/released
connection to ConnectionPool class
*
*/
Implementation of own Connection Pool
package connectionPooling;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Vector;
public class ConnectionPool implements Runnable {
private String driver, url, username, password;
private int maxConnections;
private boolean waitIfBusy;
public Vector<Connection> availableConnections, busyConnections;
private boolean connectionPending = false;
public ConnectionPool() {
}
/**
* Constructor of ConnectionPool class
*/
public ConnectionPool(String driver, String url, String username, String password, int initialConnections,
int maxConnections, boolean waitIfBusy) throws SQLException {
this.driver = driver;
this.url = url;
this.username = username;
this.password = password;
this.maxConnections = maxConnections;
this.waitIfBusy = waitIfBusy;
if (initialConnections > maxConnections) {
initialConnections = maxConnections;
}
availableConnections = new Vector<Connection>(initialConnections);
busyConnections = new Vector<Connection>();
for (int i = 0; i < initialConnections; i++) {
availableConnections.addElement(makeNewConnection());
}
}
/**
* Method to return Connections
*/
public synchronized Connection getConnection() throws SQLException {
if (!availableConnections.isEmpty()) {
Connection existingConnection =
(Connection) availableConnections.lastElement();
int lastIndex = availableConnections.size() - 1;
availableConnections.removeElementAt(lastIndex);
// If connection on available list is closed (e.g.,
// it timed out), then remove it from available list
// and repeat the process of obtaining a connection.
// Also wake up threads that were waiting for a
// connection because maxConnection limit was reached.
if (existingConnection.isClosed()) {
notifyAll();
// Freed up a spot for anybody waiting
return (getConnection());
} else {
busyConnections.addElement(existingConnection);
return (existingConnection);
}
} else {
// Three possible cases:
// 1) You haven't reached maxConnections limit. So
// establish one in the background if there isn't
// already one pending, then wait for
// the next available connection (whether or not
// it was the newly established one).
// 2) You reached maxConnections limit and waitIfBusy
// flag is false. Throw SQLException in such a case.
// 3) You reached maxConnections limit and waitIfBusy
// flag is true. Then do the same thing as in second
// part of step 1: wait for next available connection.
if ((totalConnections() < maxConnections) && !connectionPending) {
makeBackgroundConnection();
} else if (!waitIfBusy) {
throw new SQLException("Connection limit reached");
}
// Wait for either a new connection to be established
// (if you called makeBackgroundConnection) or for
// an existing connection to be freed up.
try {
wait();
} catch (InterruptedException ie) {
}
// Someone freed up a connection, so try again.
return (getConnection());
}
}
/**
* You can't just make a new connection in the
foreground // when none are
* available, since this can take several //
seconds with a slow network
* connection. Instead, // start a thread that
establishes a new connection,
* // then wait. You get woken up either when
the new connection // is
* established or if someone finishes with an
existing // connection.
*/
private void makeBackgroundConnection() {
connectionPending = true;
try {
Thread connectThread = new Thread(this);
connectThread.start();
} catch (OutOfMemoryError oome) {
// Give up on new connection
}
}
public void run() {
try {
Connection connection = makeNewConnection();
synchronized (this) {
availableConnections.addElement(connection);
connectionPending = false;
notifyAll();
}
} catch (Exception e) { //
SQLException or OutOfMemory
// Give up on new connection and wait for existing one
// to free up.
e.printStackTrace();
}
}
/**
* // Method explicitly makes a new connection.
Called in // the foreground
* when initializing the ConnectionPool, // and
called in the background
* when running.
*/
private Connection makeNewConnection() throws SQLException {
try {
// Context ctx = new InitialContext();
// Context envContext = (Context)
ctx.lookup("java:/comp/env");
// javax.sql.DataSource ds = (javax.sql.DataSource)
// envContext.lookup ("jdbc/connectionPool");
// Load database driver if not already loaded
Class.forName(driver);
// Establish network connection to database
Connection connection = DriverManager.getConnection(url, username, password);
// Connection connection = ds.getConnection();
return (connection);
} catch (Exception cnfe) {
// Simplify try/catch blocks of people using this by
// throwing only one exception type.
cnfe.printStackTrace();
throw new SQLException("ConnectionPool:: SQLException
encountered:: " + cnfe.getMessage());
}
}
/**
* Method to free the Connections
*/
public synchronized void free(Connection connection) {
busyConnections.removeElement(connection);
availableConnections.addElement(connection);
// Wake up threads that are waiting for a connection
notifyAll();
}
public synchronized int totalConnections() {
return (availableConnections.size() + busyConnections.size());
}
/**
* Method to Close all the connections. Use
with caution: be sure no
* connections are in use before calling. Note
that you are not
* <I>required</I> to call this when done with a ConnectionPool, since
* connections are guaranteed to be closed when
garbage collected. But this
* method gives more control regarding when the
connections are closed.
*/
public synchronized void closeAllConnections() {
closeConnections(availableConnections);
availableConnections = new Vector<Connection>();
closeConnections(busyConnections);
busyConnections = new Vector<Connection>();
}
private void
closeConnections(Vector<Connection> connections) {
try {
for (int i = 0; i < connections.size(); i++) {
Connection connection = (Connection) connections.elementAt(i);
if (!connection.isClosed()) {
connection.close();
}
}
} catch (SQLException sqle) {
sqle.printStackTrace();
// Ignore errors; garbage collect anyhow
}
}
/**
* String form of ConnectionPool class.
*/
public synchronized String toString() {
String info = "ConnectionPool(" + url + "," + username + ")" + ", available=" + availableConnections.size()
+ ", busy=" + busyConnections.size() + ", max=" + maxConnections;
return (info);
}
}
No comments:
Post a Comment