database connection pooling with c3p0
Post on 10-Feb-2017
233 Views
Preview:
TRANSCRIPT
Database Connection Pooling With
c3p0By Kasun Dinesh Madusanke
hSenid Lanka: Connection Pooling With c3p0
2
hSenid Lanka: c3p0
Topics What is a Connection?
What is a Connection Pool?
How Connection Pool Works?
Connection Life Cycle
Connection Poolers
3
hSenid Lanka: c3p0
Topics Cont.
Introduction to c3p0
Using c3p0
Configuring c3p0
Benefits of Connection Pooling
4
hSenid Lanka: c3p0
What is a Connection?• In computer science, a database connection is the means by which a database server and
its client software communicate with each
other.
• The term is used whether or not the client and the server are on different machines.
• Connections are built by supplying an underlying driver or provider with a
connection string.
5
hSenid Lanka: c3p0
What is a Connection? Cont.
• Connection string is used to address a specific database or server and to provide instance and
user authentication credentials.
• Connections are a key concept in data-centric programming.
• Since some DBMSs require considerable time to connect, connection pooling is used to improve
performance.
6
hSenid Lanka: c3p0
What is a Connection Pool?
• Database connections are finite and expensive and can take a long time to create relative to
the operations performed on them.
• It is very inefficient for an application to create and close a database connection whenever
it needs to update a database.
7
hSenid Lanka: c3p0
• The application server maintains a pool of ready to use connections to a data store.
o Application client requests a connections using a data source or a connection factory object.
o Connection is retrieved from the pool.
• Opening a connection only when needed, and
closing it as soon as the work is done, rather
than holding a connection open for the entire
life of the application.
What is a Connection Pool? Cont.
8
hSenid Lanka: c3p0
• In this manner, a relatively small number of connections can service a large number of requests.
• Using too many connections may just cause thrashing rather than get more useful work done.
• It is desirable to set some limit on the number of connections in the pool.
• In case all the connections are in use, the operation can block until a connection is returned to the pool, or an
error may be returned.
What is a Connection Pool? Cont.
9
hSenid Lanka: c3p0
How Connection Pool Works?
10
hSenid Lanka: c3p0
Connection Life Cycle• A managed connection object is always in one of three states.
DoesNotExist, InFreePool or InUse
• After a connection is created, it can be in
either,o InUse (if allocated to an application)o InFreePool state
11
hSenid Lanka: c3p0
Connection Life Cycle Cont.
• Between these three states are transitions. Examaple: If the application has called the data
source or connection factory getConnection()
method:
FreePoolState -> InUse state
• After a connection is closed with the close() method, it is either,
o Returned to the free pool
o Destroyed
12
hSenid Lanka: c3p0
Connection Poolers• Here are some of the connection poolers that we can use to create a pool of connections,
o Apache DBCP - Java Database Connection Pooling library
o BoneCP - The JDBC Connection Pool
o MongoPool - Manages MongoDB Connections
o SQL Relay - Database Connection Pool library with API available in all programming languages
o c3p0 - JDBC3 Connection and Statement Pooling
13
hSenid Lanka: c3p0
Introduction to C3P0• c3p0 is an easy-to-use library for augmenting traditional (DriverManager-based) JDBC drivers,
including DataSources that implement Connection and
Statement Pooling.
• In particular, c3p0 provides several useful services:
• A class which adapt traditional DriverManager-based JDBC drivers to the newer javax.sql.DataSource scheme for acquiring database Connections.
• Transparent pooling of Connection and PreparedStatements behind DataSources.
14
hSenid Lanka: c3p0
Using c3p0Creating/initialize a pooled data source
• There are three ways to create a c3p0 connection
pool.
i. Using a ComboPooledDataSource.
ii. DataSources factory class.
iii.Build our own pool-backed DataSource by directly
instantiating PoolBackedDataSource and setting its
ConectionPoolDataSource.
15
hSenid Lanka: c3p0
Using c3p0 Cont.1. Using the ComboPooledDataSource
Most user prefer using this method than the other methods.
16
hSenid Lanka: c3p0
Using c3p0 Cont.2. Using the DataSources factory class
• We can use the static factory class
com.mchange.v2.c3p0.DataSources to build unpooled DataSources
from traditional JDBC drivers, and to build pooled
DataSources from unpooled DataSources.
17
hSenid Lanka: c3p0
Using c3p0 Cont.
We can programmatically set the configuration parameters and
override the default parameters using map like in the code
above.
18
hSenid Lanka: c3p0
Using c3p0 Cont.Querying a PooledDataSource's current status
• Here are some of the methods that we can use to query/get the
information of the current status of the data sources. public int getNumConnectionsDefaultUser() public int getNumIdleConnectionsDefaultUser() public int getNumBusyConnectionsDefaultUser() public int getNumConnections(String username, String
password) public int getNumConnectionsAllUsers()
c3p0 maintains separate pools for Connections withdistinct authentications.
19
hSenid Lanka: c3p0
Using c3p0 Cont.Cleaning up PooledDataSources
o Using destroy method
20
hSenid Lanka: c3p0
Using c3p0 Cont.Cleaning up PooledDataSources Cont.o Using close method
Unreferenced instances of PooledDataSource that are not closed by clients
close() themselves prior to garbage collection in their finalize() methods.
21
hSenid Lanka: c3p0
Configuring c3p0• There are several ways to modify c3p0 properties: By changing the property values associated with a
particular DataSource in your code, or you can configure
c3p0 externally,i. via a simple Java properties file
ii.via HOCON (typesafe-config) files (if and only if
you bundle the typesafe-config library with your
application)
iii.via an XML configuration file
iv.via System properties
22
hSenid Lanka: c3p0
Configuring c3p0 Cont.
• DataSources must be configured before they are used (during or after the construction), c3p0 does support
property modifications midstream.
However if you obtain a DataSource by instantiating a
ComboPooledDataSource, configure it by simply calling
appropriate setter methods offered by that class before
attempting a call to getConnection().
23
hSenid Lanka: c3p0
Configuring c3p0 Cont.
If you obtain a DataSource by using factory methods of
the utility class com.mchange.v2.c3p0.DataSources, and
wish to use a non-default configuration, you can supply
a Map of property names.
24
hSenid Lanka: c3p0
• To override the library's built-in defaults, create a file called
c3p0.properties and place it at the "root" of your classpath or
classloader.
• The file must be available as a classloader resource under the name
/c3p0.properties, in the classloader that loaded c3p0's jar file.
• The format of c3p0.properties should be a normal Java Properties
file format, whose keys are c3p0 configurable properties.An example
c3p0.properties file is given in the next slide.
Configuring c3p0 Cont.Overriding c3p0 defaults via c3p0.properties
25
hSenid Lanka: c3p0
Configuring c3p0 Cont.
• Sample properties file
26
hSenid Lanka: c3p0
Configuring c3p0 Cont.
• HOCON (Human-Optimized Config Object Notation) is a format
for human-readable data, and a superset of JSON.
• You must specify ordinary config params explicitly inside a
c3p0 scope one way or another, even in a c3p0.conf file. "Dot
notation" can be used equivalently to scopes.
Overriding c3p0 defaults with "HOCON“ configuration files
27
hSenid Lanka: c3p0
Configuring c3p0 Cont.Overriding c3p0 defaults with "HOCON“ configuration files Cont.
• HOCON file
• properties file
28
hSenid Lanka: c3p0
Configuring c3p0 Cont.
• You can use the XML config file for all c3p0 configuration,
including configuration of defaults, named configurations,
per-user overrides, and configuration extensions.
• By default, c3p0 will look for an XML configuration file in
its classloader's resource path under the name "/c3p0-
config.xml".
Overriding c3p0 defaults via c3p0-config.xml
29
hSenid Lanka: c3p0
Configuring c3p0 Cont.Overriding c3p0 defaults via c3p0-config.xml Cont.
• Xml configuration file
30
hSenid Lanka: c3p0
Configuring c3p0 Cont.Overriding c3p0 defaults via c3p0-config.xml Cont.
• Xml configuration filewith Named configuration
31
hSenid Lanka: c3p0
Configuring c3p0 Cont.
• c3p0 properties can also be defined as System properties,
using the same "c3p0." prefix for properties specified in a
c3p0.properties file.
Overriding c3p0 defaults with System properties
32
hSenid Lanka: c3p0
Configuring c3p0 Cont.
• Under some circumstances, statement pooling can dramatically
improve application performance.
• Whether and how much statement pooling will help depends on
how much parsing, planning, and optimizing of queries your
databases does when the statements are prepared.
Configuring Statement Pooling
33
hSenid Lanka: c3p0
Configuring c3p0 Cont.
• We can configure statement pooling in c3p0 via the following configuration parameters:
• maxStatements-Defines the total number PreparedStatements a DataSource will cache.
• maxStatementsPerConnection-Defines how many statements each pooled Connection is allowed to own.
• statementCacheNumDeferredCloseThreads-If greater than zero, the Statement pool will defer physically closing cached Statements until its parent Connection is not in use by any client or internally by the pool itself.
Configuring Statement Pooling Cont.
34
hSenid Lanka: c3p0
Configuring c3p0 Cont.
• c3p0 DataSources are designed (and configured by default) to
recover from temporary database outages, such as those which
occur during a database restart or brief loss of network
connectivity.
• You can affect how c3p0 handles errors in acquiring
Connections via the configurable properties described in the
next slide.
Configuring Recovery From Database Outages
35
hSenid Lanka: c3p0
Configuring c3p0 Cont.
• acquireRetryAttempts-How much attempts to get a connection, default
is infinite.
• acquireRetryDelay-Delay between retry attempts.
• breakAfterAcquireFailure-If 'true', DataSource will consider itself
broken after a failed round of Connection
attempts, and future client requests
will fail immediately.
Configuring Recovery From Database Outages Cont.
36
hSenid Lanka: c3p0
Benefits of Connection Pooling• Performance-The connection cost is paid for once and amortized across all the consuming components.
• Diagnostics-If you have one sub-system responsible for connecting to the database, it becomes easier to diagnose and
analyze database connection usage.
• Maintainability-Again, if you have one sub-system responsible for handing out database connections, your code will be
easier to maintain than if each component connected to the
database itself.
Thank You!
By Kasun Dinesh Madusanke
hSenid Lanka: Connection Pooling With c3p0
top related