HowTo configure the C3P0 connection pool

Configuration

Here is a sample of C3P0 configuration. This is an extract of hibernate.cfg.xml:

<!-- configuration pool via c3p0--> 
<property name="c3p0.acquire_increment">1</property> 
<property name="c3p0.idle_test_period">100</property> <!-- seconds --> 
<property name="c3p0.max_size">100</property> 
<property name="c3p0.max_statements">0</property> 
<property name="c3p0.min_size">10</property> 
<property name="c3p0.timeout">100</property> <!-- seconds --> 
<!-- DEPRECATED very expensive property name="c3p0.validate>--> 

You also can set extra c3p0 properties using c3p0.properties. Put this file in the classpath (WEB-INF/classes for example), but be careful, the previous values will be overridden by Hibernate whether set or not (see below for more details). For more information on C3P0 configuration, please have a look at http://sourceforge.net/projects/c3p0 and unzip the download, there is a doc folder where you'll find everything you need.

For more on hibernate/c3p0 issues, see c3p0's docs at http://www.mchange.com/projects/c3p0/index.html#appendix_d

Parameters

c3p0 is very tunable, and offers a somewhat bewildering array of configuration parameters. For the complete list, see http://www.mchange.com/projects/c3p0/index.html#appendix_a

Here are a subset of the most important features:

initialPoolSize C3P0 default: 3

minPoolSize Must be set in hibernate.cfg.xml (or hibernate.properties), Hibernate default: 1

maxPoolSize Must be set in hibernate.cfg.xml (or hibernate.properties), Hibernate default: 100

idleTestPeriod Must be set in hibernate.cfg.xml (or hibernate.properties), Hibernate default: 0

If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds.

timeout Must be set in hibernate.cfg.xml (or hibernate.properties), Hibernate default: 0

The seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.

maxStatements Must be set in hibernate.cfg.xml (or hibernate.properties), Hibernate default: 0

The size of c3p0's PreparedStatement cache. Zero means statement caching is turned off.

propertyCycle Must be set in c3p0.properties, C3P0 default: 300

Maximum time in seconds before user configuration constraints are enforced. c3p0 enforces configuration constraints continually, and ignores this parameter. It is included for JDBC3 completeness.

acquireIncrement Must be set in hibernate.cfg.xml (or hibernate.properties), Hibernate default: 1

Determines how many connections at a time c3p0 will try to acquire when the pool is exhausted.

testConnectionOnCheckout Must be set in c3p0.properties, C3P0 default: false

Don't use it, this feature is very expensive. If set to true, an operation will be performed at every connection checkout to verify that the connection is valid. A better choice is to verify connections periodically using c3p0.idleConnectionTestPeriod.

autoCommitOnClose Must be set in c3p0.properties, C3P0 default: false

The JDBC spec is unfortunately silent on what should happen to unresolved, pending transactions on Connection close. C3P0's default policy is to rollback any uncommitted, pending work. (I think this is absolutely, undeniably the right policy, but there is no consensus among JDBC driver vendors.) Setting autoCommitOnClose to true causes uncommitted pending work to be committed, rather than rolled back on Connection close. [Note: Since the spec is absurdly unclear on this question, application authors who wish to avoid bugs and inconsistent behavior should ensure that all transactions are explicitly either committed or rolled-back before close is called.]

forceIgnoreUnresolvedTransactions Must be set in c3p0.properties, C3P0 default: false

Strongly disrecommended. Setting this to true may lead to subtle and bizarre bugs. This is a terrible setting, leave it alone unless absolutely necessary. It is here to work around broken databases / JDBC drivers that do not properly support transactions, but that allow Connections' autoCommit flags to be set to false regardless. If you are using a database that supports transactions "partially" (this is oxymoronic, as the whole point of transactions is to perform operations reliably and completely, but nevertheless, such databases exist), if you feel comfortable ignoring the fact that Connections with autoCommit == false may be in the middle of transactions and may hold locks and other resources, you may turn off c3p0's wise default behavior, which is to protect itself, as well as the usability and consistency of the database, by either rolling back (default) or committing (see c3p0.autoCommitOnClose above) unresolved transactions. This should only be set to true when you are sure you are using a database that allows Connections' autoCommit flag to go to false, but that it offers no other meaningful support of transactions. Otherwise setting this to true is just a bad idea.

numHelperThreads Must be set in c3p0.properties, C3P0 default: 3

c3p0 is very asynchronous. Slow JDBC operations are generally performed by helper threads that don't hold contended locks. Spreading these operations over multiple threads can significantly improve performance by allowing multiple operations to be performed simultaneously.

factoryClassLocation Must be set in c3p0.properties, C3P0 default: null

DataSources that will be bound by JNDI and use that API's Referenceable interface to store themselves may specify a URL from which the class capable of dereferencing a them may be loaded. If (as is usually the case) the c3p0 libraries will be locally available to the JNDI service, leave this set to null.  

以上參考網頁:http://www.hibernate.org/214.html

系統golive時這部份參數設置不當,結果導致DB Connection暴衝,一下子就給你從10、20給你一路衝到70以上;然後...再隔一陣子,DB就隔屁了...囧rz

後來查了一下原因,猜想除了參數設置不當之外,有很大一部份是某一段時間內的使用者量太高所導致;當c3p0要把連線從DB中release時,User又進系統了,結果就是連線給你一路追加。

下面這個是後來調整過的參數值。

1. Hibernate設定檔的設置方式
c3p0.min_size 設為 10
c3p0.max_size 設為 35
c3p0.acquire_increment(指達到max_size的用量後一次可以再追加多少的連線數) 設為 0
c3p0.max_statements 設為 0 (這個一定要設成0,不然Connection Pool會常出現DeadLock的Error,在Hibernate官方forum上查了一下,這問題"似乎"是c3p0本身的bug)
c3p0.timeout(單位為秒) 設為 300
c3p0.idle_test_period(單位為秒,指多久要去Check一次是否有connecion被閒置不用) 設為 100

2.如果只是單純的使用c3p0,不跟Hibernate綁在一起,參數設定則是下面這樣
minPoolSize 設為 10
maxPoolSize 設為 35
acquireIncrement 設為 0
maxStatements 設為 0
maxIdleTime 設為 300
idleConnectionTestPeriod 設為 100
設置這些參數的Reference Document是來自這裡(其一其二),有需要的人也可以去看看這些文件的說明...:)

參考網頁:http://cloudtu.blogspot.com/2007/05/c3p0-connection-pool.html

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 楊 bey kuen 的頭像
    楊 bey kuen

    楊 bey kuen的部落格

    楊 bey kuen 發表在 痞客邦 留言(0) 人氣()