May 7, 2012

Connection pool tuning in JBoss to get rid of excessive connection validation calls

The issue was - there were too many "select 'x' from dual" calls in database. Let me describe how we got there and finally got rid of.

Here is a brief background -

When we moved from WebLogic to JBoss, we decided to use JBoss connection pool facilities built in. We were in c3p0 earlier.

So, we configured DataSource something like this for each four web server-


<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<datasources>
    <local-tx-datasource>
        <jndi-name>TxDS</jndi-name>
        <rar-name>jboss-local-jdbc.rar</rar-name>
        <use-java-context>true</use-java-context>
        <connection-definition>javax.sql.DataSource</connection-definition>
        <jmx-invoker-name>jboss:service=invoker,type=jrmp</jmx-invoker-name>
        <min-pool-size>80</min-pool-size>
        <max-pool-size>150</max-pool-size>
        <blocking-timeout-millis>60000</blocking-timeout-millis>
        <idle-timeout-minutes>15</idle-timeout-minutes>
        <prefill>false</prefill>
        <use-fast-fail>false</use-fast-fail>
        <statistics-formatter>org.jboss.resource.statistic.pool.JBossDefaultSubPoolStatisticFormatter</statistics-formatter>
        <isSameRM-override-value>false</isSameRM-override-value>
        <allocation-retry>0</allocation-retry>
        <allocation-retry-wait-millis>5000</allocation-retry-wait-millis>
        <metadata>
            <type-mapping>Oracle9i</type-mapping>
        </metadata>
        <type-mapping>Oracle9i</type-mapping>
        <local-transaction/>
        <user-name>prod</user-name>
        <password>*******</password>
        <check-valid-connection-sql>SELECT * FROM DUAL</check-valid-connection-sql>        

        <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
        <prepared-statement-cache-size>0</prepared-statement-cache-size>
        <share-prepared-statements>false</share-prepared-statements>
        <set-tx-query-timeout>false</set-tx-query-timeout>
        <query-timeout>0</query-timeout>
        <use-try-lock>60000</use-try-lock>
        <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
        <connection-url>jdbc:oracle:thin:@vdb1.xxx.net:1521:prod</connection-url>
    </local-tx-datasource>
</datasources>


Things had been running good but I noticed there were too many executions of SELECT * FROM DUAL - over a million in 30 min window for about 500 active connections. The overall execution of database increased about 30%. Well, this was not a good thing to look at and accept.

To reduce the excessive connection validation calls, we then reconfigured the datasource xml like this -

<!-- <check-valid-connection-sql>SELECT * FROM DUAL</check-valid-connection-sql> -->
<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
<background-validation>true</background-validation>
<background-validation-millis>600000</background-validation-millis>


We commented out our own validation checking sql and put Oracle's validation checker class. Also, introduced background validation.

This did not improve anything - rather we ended up with so many "select 'x' from dual" calls! The OracleValidConnectionChecker pingDatabase() method actually did the thing on our behalf.

Now, how to get rid of so many connection validation calls? I googled - did not find any resolution, became disappointed a bit. People had been saying that, this was an issue with JBoss connection pool. Then I decided to read documents by myself. I stared with this -

https://community.jboss.org/wiki/ConfigDataSources

I found the <validate-on-match> property interesting and was wondering if this had caused us so many connection validation calls. We don't need to validate on match! The default value is "true" for this. Document says - 

 <validate-on-match> - whether to validate the connection when the JCA layer matches a managed connection (i.e. when the connection is checked out of the pool). With the addition of <background-validation> this is not necessarily required.  Note: Specifying "true" for <validate-on-match> is typically not done in conjunction with specifying "true" for <background-validation> as this would be overkill in most scenarios.  Default is true.

In another document, I found -

<validate-on-match> - Prior to JBoss 4.0.5, connection validation occurred when the JCA layer attempted to match a managed connection. With the addition of <background-validation> this is no longer required. Specifying <validate-on-match> forces the old behavior. NOTE: this is typically NOT used in conjunction with <background-validation> 


Decided to do some testing using <validate-on-match>. I planned like this - 


* Alter AWR snap window to 10 min to monitor several things using AWR report. 


* Change the Datasource - added that <validate-on-match> property and made it false. 

<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>

<background-validation>true</background-validation>
<background-validation-millis>600000</background-validation-millis>
<validate-on-match>false</validate-on-match>



* Using JMeter scripts (testing team helped on this), did database activities for some specified amount of time. 


The test result was amazing. When we don't do any validate-on-match (meaning setting it false) - the calls for "select 'x' from dual" get reduced from 100,000+ to just few hundred!


When we put it in production, I saw the immediate improvement. For an off peak window of 30 min, the executions were over 65,000 before we disabled validate on match, and now this is just 1500!


***

2 comments:

Anonymous said...

You ought to take part in a contest for one of the most
useful blogs online. I am going to highly recommend this site!
Also visit my web site ; chicago pool table movers

Flamarion Jorge said...

Your post was great, but I really did a research and not found any place to view all of options that you setup in your datasource.

Could you show me where you found all that options?

I need to know the real meaning for those and apply.

Thank you and congrats for your great post.

Flamarion