Configuring the initial number of connections in BoneCP

I use BoneCP as connection manager for my application because it is supposed to be fast and deadlock free.

Configuring it in a way that makes it predictable proves to be somewhat of a problem though. This is not really a problem of BoneCP itself but rather one of great configurability and the interaction of the various parameters.

The issue arose because I blindly copied the guide for using Spring+Hibernate. Profiling showed that this did not work in our configuration as we explicitly use a datasource confugration. In this case, Hibernate does not manage the connections itself thus making the Hibernate setting hibernate.connection.provider_class unused.

I started using the following configuration (excluding the actual connection parameters):

<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource"
            lazy-init="true" destroy-method="close">
    .....
    <property name="idleConnectionTestPeriodInMinutes" value="1"/>
    <property name="idleMaxAgeInMinutes" value="4"/>
    <property name="maxConnectionsPerPartition" value="60"/>
    <property name="minConnectionsPerPartition" value="1"/>
    <property name="partitionCount" value="3"/>
    <property name="acquireIncrement" value="3"/>
    <property name="statementsCacheSize" value="50"/>
    <property name="releaseHelperThreads" value="3"/>
</bean>

I expected this to allocate 3 connections at startup (3 partions with one connection each), but this turned out to reserve 39 connections.
It seems there are various setting which are relevant which were not present and some interactions between the settings which are not clear.

  • partitionCount: connections are grouped in partitions as this decreases contention when you open a new connection.
  • lazyInit: should connections immediately be allocated or a little later?
  • maxConnectionsPerPartition: upper limit of the amount of connections in one partition. If the limit is reached you will have to wait when a new connection is requested.
  • minConnectionsPerPartition: number of connections which are immediately allocated when the the pool is created. This is not used when lazyInit is true.
  • acquireIncrement: number of connections which are allocated at once when a connections are added in the pool.
  • poolAvailabilityThreshold: the percentage of available connections which should be available in each partition. The default value is 20%. Additional connections are acquired when there are too less than this percentage of available connections. This reduces the overhead of allocating new connections when needed.
  • connectionTestStatement: SQL statement which is used to verify that a connection is still alive. When not specified, a metadata request is used.

From this list, it is especially the poolAvailabilityThreshold which caused an unexpected number of connections to be initially allocated.

In the end I am now using this configuration:

<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource"
            lazy-init="true" destroy-method="close">
    .....
    <property name="idleConnectionTestPeriodInMinutes" value="1"/>
    <property name="idleMaxAgeInMinutes" value="4"/>
    <property name="maxConnectionsPerPartition" value="30"/>
    <property name="minConnectionsPerPartition" value="1"/>
    <property name="poolAvailabilityThreshold" value="5"/> 
    <property name="partitionCount" value="1"/>
    <property name="acquireIncrement" value="3"/>
    <property name="statementsCacheSize" value="50"/>
    <property name="releaseHelperThreads" value="3"/>
    <property name="connectionTestStatement" value="SELECT 1"/>
    <property name="lazyInit" value="true"/>
</bean>

4 Comments

  1. KK says:

    Thanks a lot.
    This solved my problem from getting “too many connections” in mysql.
    A very good article. Cheers.

  2. Touche. Outstanding arguments. Ҝeep up thе gooɗ effort.

    Feel free tο visit my page: bad credit student loans (http://www.erefinancestudentloans.com)

  3. Issac says:

    Hey! І know thiѕ is ҡind օf off topic but I waѕ wondering ԝhich blog patform aare уou usijg fօr this site?
    I’m ǥetting sick аnd tired of WordPress Ьecause I’ve
    had issues witɦ hackers and I’m loօking at options for another platform.
    I wߋuld bee great if you couuld poiոt me in tҺе direction of a good platform.

    mү web-site … guaranteed payday loans – Issac,

  4. I am in fact grateful to the holder of this web site who has
    shared this great post at at this time.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

question razz sad evil exclaim smile redface biggrin surprised eek confused cool lol mad twisted rolleyes wink idea arrow neutral cry mrgreen

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>