What is best Configuration of HikariCP for Hibernate,Spring,Jsf Web application

[Subodh Joshi]

Can anyone tell me what will be best configuration of HikariCP in production server ? As i am using Spring and Hbernate i am trying below configuration 

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
        <property name="dataSourceProperties">
            <props>
                <prop key="dataSource.url">jdbc:mysql://ipaddress:3306/ccc?useUnicode=true&amp;characterEncoding=utf-8</prop>
                <prop key="dataSource.user">user</prop>
                <prop key="dataSource.password">password</prop>
            </props>
        </property>
        <property name="dataSourceClassName" value="com.mysql.jdbc.Driver" />
    </bean>

    <bean id="DataSource" class="com.zaxxer.hikari.HikariDataSource">
        <constructor-arg ref="hikariConfig" />
    </bean>
   

But i will want to use Best configuration of HikariCP ? Can anyoe tell what all properties can be use for production server so connection should be maximum error as today i tried HikariCP previously i am using C3P0 but i am plenty of time getting exception mentioned on this link http://stackoverflow.com/questions/25055756/communications-link-failure-hibernate-spring-application

But i am thinking to use HikariCP so i am looking what will be best configuration?

[Brett Wooldridge]

If you are getting same the error from C3P0 as in that stackoverflow question, and want to avoid the same in HikariCP, please read the first answer in the FAQ.  With respect to getting the best performance from MySQL and HikariCP, please read this entry in the wiki.

Except for matching the idleTimeout and maxLifetime timeouts to your MySQL timeouts (as described in the article linked from the first answer in the FAQ), I suggest trying HikariCP with all other settings at their defaults and see how it performs.  The defaults are good for a great many production environments.

EDIT:

Quoting a little from the article linked in the FAQ:

• Choose a reasonable wait_timeout value. Stateless PHP environments do well with a 60 second timeout or less. Stateful applications that use a connection pool (Java, .NET, etc.) will need to adjust wait_timeout to match their connection pool settings. The default 8 hours (wait_timeout = 28800) works well with properly configured connection pools.

• Configure the wait_timeout to be slightly longer than the application connection pool's expected connection lifetime. This is a good safety check.
  

If you set the MySQL wait_timeout = 28800 (seconds = 8 hours), you should set HikariCP idleTimeout and maxLifetime to the slightly shorter 28000000 (milliseconds = 7 hours 46 minutes).

[Brett Wooldridge]

Just another tip to make your configuration more readable/manageable, you can set the properties that you would normally pass in the URL as datasource properties, like this (includes your two properties, plus additional MySQL tuning):

<property name="dataSourceProperties">
   <props>
      <prop key="dataSource.url">jdbc:mysql://ipaddress:3306/ccc</prop>

      <prop key="dataSource.user">user</prop>
      <prop key="dataSource.password">password</prop>

      <prop key="dataSource.useUnicode">true</prop>

      <prop key="dataSource.characterEncoding">utf-8</prop>

      <!-- Performance options -->

      <prop key="dataSource.cachePrepStmts">true</prop>

      <prop key="dataSource.prepStmtCacheSize">250</prop>

      <prop key="dataSource.prepStmtCacheSqlLimit">2048</prop>

      <prop key="dataSource.useServerPrepStmts">true</prop>    <!-- use if your MySQL server is 5.1 or above -->

   </props>
 /property>

Oh, and your datasource is configured wrong:

<property name="dataSourceClassName" value="com.mysql.jdbc.Driver" />

Should be: 

<property name="dataSourceClassName" value="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" />

[Subodh Joshi]

Hi Brett I am using these config

But still i am getting

SEVERE: Exception performing authentication
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 39,036,106 milliseconds ago.  The last packet sent successfully to the server was 36 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3715)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3604)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4155)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2212)
    at org.apache.catalina.realm.JDBCRealm.getPassword(JDBCRealm.java:547)
    at org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:406)
    at org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:353)
    at org.apache.catalina.authenticator.AuthenticatorBase.doLogin(AuthenticatorBase.java:848)
    at org.apache.catalina.authenticator.AuthenticatorBase.login(AuthenticatorBase.java:830)
    at org.apache.catalina.connector.Request.login(Request.java:2637)
    at org.apache.catalina.connector.RequestFacade.login(RequestFacade.java:1064)
    at com.ccc.web.view.LoginBean.login(LoginBean.java:283)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.apache.el.parser.AstValue.invoke(AstValue.java:278)
    at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:273)
    at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
    at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87)
    at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
    at org.primefaces.application.DialogActionListener.processAction(DialogActionListener.java:45)
    at javax.faces.component.UICommand.broadcast(UICommand.java:315)
    at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
    at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
    at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:646)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:70)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at com.ocpsoft.pretty.PrettyFilter.doFilter(PrettyFilter.java:145)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:722)
Caused by: java.net.SocketException: Software caused connection abort: recv failed
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(SocketInputStream.java:150)
    at java.net.SocketInputStream.read(SocketInputStream.java:121)
    at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:112)
    at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:159)
    at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:187)
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3158)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3615)
    ... 61 more

Any Idea what thing i have to change to fix this issue

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">

        <property name="dataSourceProperties">
            <props>

                <prop key="url">jdbc:mysql://ipaddress3306/ccc?useUnicode=true&amp;characterEncoding=utf-8</prop>
                <prop key="user">userName</prop>
                <prop key="password">password</prop>
                <prop key="useUnicode">true</prop>
                <prop key="characterEncoding">utf-8</prop>
                <prop key="cachePrepStmts">true</prop>
                <prop key="prepStmtCacheSize">250</prop>
                <prop key="prepStmtCacheSqlLimit">2048</prop>
                <prop key="useServerPrepStmts">true</prop>
            </props>
        </property>
        <property name="dataSourceClassName" value="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" />

    </bean>

    <bean id="DataSource" class="com.zaxxer.hikari.HikariDataSource">
        <constructor-arg ref="hikariConfig" />
    </bean>

On Fri, Aug 1, 2014 at 12:27 AM, Subodh Joshi <subodhc...@gmail.com> wrote:

Thanks I have done this i am checking if any issue will ask you

On Thu, Jul 31, 2014 at 7:06 PM, Brett Wooldridge <brett.wo...@gmail.com> wrote:

Just another tip to make your configuration more readable/manageable, you can set the properties that you would normally pass in the URL as datasource properties, like this (includes your two properties, plus additional MySQL tuning):

<property name="dataSourceProperties">
   <props>
      <prop key="dataSource.url">jdbc:mysql://ipaddress:3306/ccc</prop>

      <prop key="dataSource.user">user</prop>
      <prop key="dataSource.password">password</prop>

      <prop key="dataSource.useUnicode">true</prop>

      <prop key="dataSource.characterEncoding">utf-8</prop>

      <prop key="dataSource.cachePrepStmts>true</prop>

      <prop key="dataSource.prepStmtCacheSize">250</prop>

      <prop key="dataSource.prepStmtCacheSqlLimit">2048</prop>

      <prop key="dataSource.useServerPrepStmts">true</prop>  <!-- if your MySQL is 5.1 or above -->

   </props>
 /property>

--
You received this message because you are subscribed to a topic in the Google Groups "HikariCP" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/hikari-cp/qGTURJNztTg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to hikari-cp+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

-- 

[Brett Wooldridge]

It looks like the Tomcat (catalina) JDBC-based authentication is actually not going through the HikariCP pool at all.  When I look at the stacktrace you provided, this part:

    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2212)
    at org.apache.catalina.realm.JDBCRealm.getPassword(JDBCRealm.java:547)

should have a com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.executeQuery() call between the org.apache.catalina stack entry and the com.mysql.jdbc.PreparedStatement.executeQuery() entry.  That indicates to me that while Spring and Hibernate might be going through HikariCP, the Tomcat instance is not configured to use the HikariCP DataSource for the JDBC authentication component.  You'll have to read the Tomcat documentation or maybe search stackoverflow to see how that should be configured.

[Subodh Joshi]

Yes you are i am using context.xml like this

    

   <Realm localDataSource="true" debug="99"
                className="org.apache.catalina.realm.JDBCRealm" connectionName="connetName"
                connectionPassword="password" connectionURL="jdbc:mysql://ipaddress:3306/ccc"
                digest="md5" driverName="com.mysql.jdbc.Driver" roleNameCol="umul_role_name"
                userCredCol="umul_password" userNameCol="umul_email_id"
                userRoleTable="um_users_list" userTable="um_users_list" />

What changes i have to do in this so it will use Hikari?

[Brett Wooldridge]

I am not really familiar with Tomcat configuration.  You're going to have to read their documentation or maybe ask on stackoverflow.