BoneCP(Javadatabaseconnectionpool) 설정및사용-Spring의AppContext

BoneCP 설정 및 사용

* 예제
http://jolbox.com/index.html?page=http://jolbox.com/configuration-spring.html

* 설정

<default-config>
<!-- Sets the name of the pool for JMX and thread names. -->
<!-- <property name="poolName">(null or no default value)</property> -->

<!-- Sets the minimum number of connections that will be contained in every partition.
     Also refer  to {@link #setPoolAvailabilityThreshold(int)}. -->
<property name="minConnectionsPerPartition">0</property>

<!-- Sets the maximum number of connections that will be contained in every partition.
      Setting this to 5 with 3 partitions means you will have 15
     unique connections to the database.  Note that the connection pool will not
     create all these connections in one go but rather start off  with
     minConnectionsPerPartition and gradually increase connections as required. -->
<property name="maxConnectionsPerPartition">10</property>

<!-- Sets the acquireIncrement property.   When the available connections are about to
     run out, BoneCP will dynamically create new ones in batches.  This property
     controls how many new connections to create in one go (up to a
     maximum of maxConnectionsPerPartition).  Note: This is a per partition setting. -->
<property name="acquireIncrement">2</property>

<!-- Sets number of partitions to use.   In order to reduce lock
     contention and thus improve performance,  each incoming connection request picks off a
     connection from a pool that has thread-affinity,  i.e. pool[threadId % partition_count]. The
     higher this number, the better your performance will be for the case
     when you have plenty of short-lived threads. Beyond a certain threshold (approx 4),
     maintenance of these pools will start  to have a negative effect on
     performance (and only for the case when connections on a partition start running
     out). Has no effect in a CACHED strategy.   Default: 1, minimum:
     1, recommended: 2-4 (but very app specific) -->
<property name="partitionCount">1</property>

<!-- Sets the JDBC connection URL. -->
<!-- <property name="jdbcUrl">(null or no default value)</property> -->

<!-- Sets username to use for connections. -->
<!-- <property name="username">(null or no default value)</property> -->

<!-- Sets username to use for connections. Just delegates to setUsername for clients hardcoded
     with "setUser" instead. -->
<!-- <property name="user">(null or no default value)</property> -->

<!-- Sets password to use for connections. -->
<!-- <property name="password">(null or no default value)</property> -->

<!-- Sets the idleConnectionTestPeriod.  This sets the time (in minutes), for a connection
     to remain idle before sending  a test query to the DB. This
     is useful to prevent a DB from timing out connections  on its
     end. Do not use aggressive values here!    Default: 240 min,
     set to 0 to disable -->
<property name="idleConnectionTestPeriodInMinutes">240</property>

<!-- Sets the idleConnectionTestPeriod.  This sets the time (in seconds), for a connection
     to remain idle before sending  a test query to the DB. This
     is useful to prevent a DB from timing out connections  on its
     end. Do not use aggressive values here!    Default: 240 min,
     set to 0 to disable -->
<property name="idleConnectionTestPeriodInSeconds">14400</property>

<!-- Sets Idle max age (in min).  The time (in minutes), for a
     connection to remain unused before it is closed off. Do not use aggressive
     values here!   Default: 60 minutes, set to 0 to disable. -->
<property name="idleMaxAgeInMinutes">60</property>

<!-- Sets Idle max age (in seconds).  The time (in seconds), for a
     connection to remain unused before it is closed off. Do not use aggressive
     values here!   Default: 60 minutes, set to 0 to disable. -->
<property name="idleMaxAgeInSeconds">3600</property>

<!-- Sets the connection test statement.  The query to send to the DB
     to maintain keep-alives and test for dead connections.  This is database specific
     and should be set to a query that consumes the minimal amount of
     load on the server.  Examples: MySQL: "/* ping *\/ SELECT 1", PostgreSQL:
     "SELECT NOW()".  If you do not set this, then BoneCP will issue
     a metadata request instead that should work on all databases but is probably
     slower.  (Note: In MySQL, prefixing the statement by /* ping *\/ makes
     the driver issue 1 fast packet instead. See  http://blogs.sun.com/SDNChannel/entry/mysql_tips_for_java_developers ) Default: Use
     metadata request -->
<!-- <property name="connectionTestStatement">(null or no default value)</property> -->

<!-- Sets statementsCacheSize setting.  The number of statements to cache. -->
<property name="statementsCacheSize">0</property>

<!-- Sets the connection hook.  Fully qualified class name that implements the ConnectionHook
     interface (or extends AbstractConnectionHook).  BoneCP will callback the specified class according to
     the connection state (onAcquire, onCheckIn, onCheckout, onDestroy). -->
<!-- <property name="connectionHook">(null or no default value)</property> -->

<!-- Specifies an initial SQL statement that is run only when a connection is
     first created. -->
<!-- <property name="initSQL">(null or no default value)</property> -->

<!-- Instruct the pool to create a helper thread to watch over connection acquires
     that are never released (or released twice).  This is for debugging purposes
     only and will create a new thread for each call to getConnection().
     Enabling this option will have a big negative impact on pool performance. -->
<property name="closeConnectionWatch">false</property>

<!-- If enabled, log SQL statements being executed. This will also "fill in" the
     parameters  for prepared statements so that you can see exactly what values
     where bound at the time  you executed the statement. You will also
     need to set your log4j settings ("com.jolbox.bonecp") to DEBUG -->
<property name="logStatementsEnabled">false</property>

<!-- Sets the number of ms to wait before attempting to obtain a connection
     again after a failure. -->
<property name="acquireRetryDelayInMs">7000</property>

<!-- Set to true to force the connection pool to obtain the initial connections
     lazily. -->
<property name="lazyInit">false</property>

<!-- Set to true to enable recording of all transaction activity and replay the
     transaction automatically in case of a connection failure. -->
<property name="transactionRecoveryEnabled">false</property>

<!-- After attempting to acquire a connection and failing, try to connect these many
     times before giving up. Default 5. -->
<property name="acquireRetryAttempts">5</property>

<!-- Sets the connection hook class name. Consider using setConnectionHook() instead. -->
<!-- <property name="connectionHookClassName">(null or no default value)</property> -->

<!-- Set to true to disable JMX. -->
<property name="disableJMX">false</property>

<!-- If set, use datasourceBean.getConnection() to obtain a new connection instead of Driver.getConnection(). -->
<!-- <property name="datasourceBean">(null or no default value)</property> -->

<!-- Queries taking longer than this limit to execute are logged. -->
<property name="queryExecuteTimeLimitInMs">0</property>

<!-- Sets the Pool Watch thread threshold.  The pool watch thread attempts to
     maintain a number of connections always available (between minConnections and maxConnections). This value
     sets the percentage value to maintain. For example, setting it to 20 means
     that if the following condition holds: Free Connections / MaxConnections < poolAvailabilityThreshold
     new connections will be created. In other words, it tries to keep at
     least 20% of the pool full of connections. Setting the value to zero
     will make the pool create new connections when it needs them but it
     also means your application may have to wait for new connections to be
     obtained at times.   Default: 0. -->
<property name="poolAvailabilityThreshold">0</property>

<!-- If set to true, the pool will not monitor connections for proper closure.
     Enable this option if you only ever obtain your connections via a mechanism
     that is guaranteed to release the connection back to the pool (eg Spring's
     jdbcTemplate,  some kind of transaction manager, etc). -->
<property name="disableConnectionTracking">false</property>

<!-- Sets the maximum time (in milliseconds) to wait before a call to getConnection
     is timed out.   Setting this to zero is similar to setting
     it to Long.MAX_VALUE  Default: 0 ( = wait forever ) -->
<property name="connectionTimeoutInMs">0</property>

<!-- Sets the no of ms to wait when close connection watch threads are
     enabled. 0 = wait forever. -->
<property name="closeConnectionWatchTimeoutInMs">0</property>

<!-- Sets the maxConnectionAge in seconds. Any connections older than this setting will be
     closed off whether it is idle or not. Connections currently in use will
     not be affected until they are returned to the pool. -->
<property name="maxConnectionAgeInSeconds">0</property>

<!-- Sets the configFile. If configured, this will cause the pool to initialise using
     the config file in the same way as if calling new BoneCPConfig(filename). -->
<!-- <property name="configFile">(null or no default value)</property> -->

<!-- Sets the queue serviceOrder. Values currently understood are FIFO and LIFO. -->
<property name="serviceOrder">FIFO</property>

<!-- If set to true, keep track of some more statistics for exposure via
     JMX. Will slow down the pool operation. -->
<property name="statisticsEnabled">false</property>

<!-- Sets the defaultAutoCommit setting for newly created connections. If not set, use driver
     default. -->
<property name="defaultAutoCommit">true</property>

<!-- Sets the defaultReadOnly setting for newly created connections. If not set, use driver
     default. -->
<property name="defaultReadOnly">false</property>

<!-- Sets the defaultCatalog setting for newly created connections. If not set, use driver
     default. -->
<!-- <property name="defaultCatalog">(null or no default value)</property> -->

<!-- Sets the defaultTransactionIsolation. Should be set to one of: NONE, READ_COMMITTED, READ_UNCOMMITTED,
      REPEATABLE_READ or SERIALIZABLE. If not set, will use driver default. -->
<!-- <property name="defaultTransactionIsolation">(null or no default value)</property> -->

<!-- If set to true, no attempts at passing in a username/password will be
     attempted when trying to obtain a raw (driver) connection. Useful for cases when
     you already have another mechanism on authentication eg NTLM. -->
<property name="externalAuth">false</property>

<!-- Sets the classloader to use to load JDBC driver and hooks (set to
     null to use default). -->
<!-- <property name="classLoader">(null or no default value)</property> -->

<!-- If set to true, try to unregister the JDBC driver when pool is
     shutdown. -->
<property name="deregisterDriverOnClose">false</property>

<!-- Sets the nullOnConnectionTimeout.    If true, return null on connection timeout
     rather than throw an exception. This performs better but must be handled differently
     in your application. This only makes sense when using the connectionTimeout config option.
     -->
<property name="nullOnConnectionTimeout">false</property>

<!-- If true, issue a reset (rollback) on connection close in case client forgot
     it. -->
<property name="resetConnectionOnClose">false</property>

<!-- If true, and resetConnectionOnClose is also true, the pool will print out a
     stack  trace of the location where you had a connection that specified
     setAutoCommit(false) but then forgot to call commit/rollback before closing it off. This feature
     is intended  for debugging only. -->
<property name="detectUnresolvedTransactions">false</property>

<!-- Sets the poolStrategy. Currently supported strategies are DEFAULT and CACHED. This is an
     experimental feature!  DEFAULT strategy operates in a manner that has been used
     in the pool since the very first version: it tries to obtain a
     connection from a queue.   CACHED stores each connection in a thread-local
     variable so that next time the same thread asks for a connection, it
     gets the same one assigned to it (if it asks for more than
     one, it will be allocated a new one). This is very fast but
     you must ensure that the number of threads  asking for a connection
     is less than or equal to the number  of connections you have
     made  available. Should you exceed this limit, the pool will switch back
     (permanently) to the DEFAULT  strategy which will cause a one-time performance hit.
     Use this strategy if your threads are  managed eg in a Tomcat
     environment where you can limit the number of threads that it can
     handle. A typical use case would be a web service that always requires
     some form of database access,  therefore a service would have little point
     in accepting a new incoming socket connection if it  still has to
     wait in order to obtain a connection.   Essentially this means that
     you are pushing back the lock down to the socket or thread layer.
      While the first few thread hits will be slower than in the
     DEFAULT strategy, significant performance  gains are to be expected as the thread
     gets increasingly re-used (i.e. initially you should expect the first few rounds to
     be measurably slower than the DEFAULT strategy but once the caches get more
     hits you should get >2x better performance).  Threads that are killed off
     are detected during the next garbage collection and result in  their allocated
     connections from being taken back though since GC timing is not guaranteed you
     should ideally set your minimum pool size to be equal to the maximum
     pool size.  Therefore for best results, make sure that the configured minConnectionPerPartition
     = maxConnectionPerPartition = min Threads = max Threads. -->
<property name="poolStrategy">DEFAULT</property>

<!-- If true, track statements and close them if application forgot to do so.
     See also:  {@link BoneCPConfig#detectUnclosedStatements}. Do not set if your connections are managed
     eg via Spring jdbcTemplate or hibernate since those frameworks will always automatically close
     off your statements. This option has a negative performance hit. -->
<property name="closeOpenStatements">false</property>

<!-- Sets the detectUnclosedStatements. If true, print out a stack trace of where a
     statement was opened but not closed before the connection was closed. See also:
     {@link BoneCPConfig#closeOpenStatements}. -->
<property name="detectUnclosedStatements">false</property>

* 성능비교

	DBCP	C3P0	Proxool	BoneCP
Analog 5 threads 10 cycles of concurrent access to the database	Time 1181ms	Time 860ms	Time 1563ms	With 31ms
Of analog 10 threads 10 cycles concurrent access to the database	Time 1188ms	Time 953ms	Time 1625ms	With 63ms
10 concurrent access to the database to simulate 30 thread loop	Time 1250ms	Time 1047ms	Time 1657ms	Time 156ms
10 concurrent access to the database to simulate 50 thread loop	Time 1406ms	Time 1343ms	Time 1843ms	Time 172ms
Simulate 100 threads 10 cycles of concurrent access to the database	Time 1641ms	2703ms	2031ms	Time 532ms
Simulate 200 threads 10 cycles of concurrent access to the database	2093ms	Time 4891ms	2406ms	Time 936ms
Simulate 500 threads 10 cycles of concurrent access to the database	Time 3219ms	Time 11703ms	Time 3343ms	Time 1922ms
10 concurrent access to the database to simulate 800 thread loop	Time 4688ms	Time 12063ms	Time 4141ms	2859ms
Analog 1000 thread 10 cycles of concurrent access to the database	Time 5187ms	Time 12563ms	With 4703m	Time 3610ms
Of analog 3000 thread 10 cycles of concurrent access to the database	Time 14094ms	Time 16297ms	Time 11344ms	Time 11391ms
Of analog 5000 thread 10 cycles of concurrent access to the database	23610ms	22032ms	20125ms	Time 17125ms

* 주의사항

conㅜection failer 에러가 발생할 경우 

mysql config (/etc/my.cnf 수정)

wait_timeout=3600 (MySQL parameter);

bonecp parameter 추가
maxConnectionAgeInSeconds=3000 (BoneCP parameter)