Speed comparison to C3P0

[Vojtěch Knyttl]

I am trying to drop C3P0 and replace it with its alternative. Hikari claims to be the fastest, but in my case it is twice as slower as C3P0.

C3P0:

                <prop key="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</prop>

                <prop key="hibernate.c3p0.min_size">5</prop>

                <prop key="hibernate.c3p0.max_size">30</prop>

                <prop key="hibernate.c3p0.timeout">300</prop>

                <prop key="hibernate.c3p0.max_statements">50</prop>

                <prop key="hibernate.c3p0.idle_test_period">600</prop>

Hikari:

                <prop key="hibernate.connection.provider_class">com.zaxxer.hikari.hibernate.HikariConnectionProvider</prop>

                <prop key="hibernate.hikari.minimumPoolSize">20</prop>

                <prop key="hibernate.hikari.maximumPoolSize">100</prop>

                <prop key="hibernate.hikari.idleTimeout">30000</prop>

                <prop key="hibernate.hikari.dataSourceClassName">com.mysql.jdbc.jdbc2.optional.MysqlDataSource</prop>

                <prop key="hibernate.hikari.dataSource.url">${database.connection}</prop>

                <prop key="hibernate.hikari.dataSource.user">${database.username}</prop>

                <prop key="hibernate.hikari.dataSource.password">${database.password}</prop>

The comparison maybe is not very scientific, but the results for me are obvious:

$ time curl -v http://127.0.0.1:92/en/ > /dev/null 

C3P0:

               0m1.617s 0m0.359s 0m0.342s 0m0.335s 0m0.334s 0m0.289s 0m0.316s 0m0.313s

Hikari:

               0m2.637s 0m0.903s 0m0.748s 0m0.617s 0m0.783s 0m0.554s 0m0.485s 0m0.489s

What could I be doing wrong? In this way, it seems to me that Hikari is way slower than C3P0.

[Brett Wooldridge]

You don't give the full pool C3P0 configuration, which I would like to see, but if that is the

core of the configuration, I can tell you what the primary difference is...

In addition to striving for speed, HikariCP also tries to ensure the highest reliability.  As such

HikariCP tests connections immediately before returning them to the user.  In order to match

that same reliability level with C3P0, and for the comparison to therefore be fair, you need to

enable the testConnectionOnCheckout option in C3P0.

The way your pool is configured, C3P0 will test idle connections every 10 minutes.  What

happens if you reboot your database backend?  If your connection pool has 30 connections,

then 30 broken connections will be given to your application.  I don't know of many applications

that handle that well in production, but use cases vary.

Additionally, you are using the C3P0 prepared statement cache (maxStatements=50).  HikariCP

does not include a prepared statement cache, instead relying on the driver's provided cache, if

configured.  The MySQL JDBC driver has such a cache, but you must configure it like so:

<prop key="hibernate.hikari.dataSource.prepStmtCacheSize">50</prop>

If your statements are longer than 256 characters, you also need to set prepStmtCacheSqlLimit.

Lastly, I would be interested in your test results if you run from the 'dev' branch of Hikari (1.2.7-SNAPSHOT),

we're going to be releasing it this week, with some improvements based on feedback such as this.

[Vojtěch Knyttl]

I am not sure how this was happening but now the difference seems to be not that big. Hikari seems to be slowlier only by 10 - 20%. Will be testing a little bit more.

 

You don't give the full pool C3P0 configuration, which I would like to see, but if that is the
core of the configuration, I can tell you what the primary difference is...

Well, I am not sure what you mean by "Full C3P0" conf. I don't have any other settings, apart from:

                <prop key="hibernate.dialect">${database.dialect}</prop>

                <prop key="hibernate.hbm2ddl.auto">${database.structure}</prop>

                <prop key="hibernate.connection.url">${database.connection}</prop>

                <prop key="hibernate.connection.username">${database.username}</prop>

                <prop key="hibernate.connection.password">${database.password}</prop>

                <prop key="hibernate.connection.driver_class">${database.driver}</prop>

                <prop key="hibernate.connection.shutdown">true</prop>

                <prop key="hibernate.connection.writedelay">0</prop>

                <prop key="hibernate.connection.characterEncoding">UTF-8</prop>

                <prop key="hibernate.connection.charSet">UTF-8</prop> 

The way your pool is configured, C3P0 will test idle connections every 10 minutes.  What
happens if you reboot your database backend?  If your connection pool has 30 connections,
then 30 broken connections will be given to your application.  I don't know of many applications
that handle that well in production, but use cases vary.

Well I try not to do restart the database, but you are right that it results in connection exceptions, but the connection is quickly restored thanks to "?autoReconnect=true". Maybe it is not the best solution, but I will be happy to configure it better.

If your statements are longer than 256 characters, you also need to set prepStmtCacheSqlLimit.

I am not sure about that :-) How do I find out?

 Lastly, I would be interested in your test results if you run from the 'dev' branch of Hikari (1.2.7-SNAPSHOT),

I can test it once it is placed in the maven repository :-) 

[Brett Wooldridge]

>> If your statements are longer than 256 characters, you also need to set prepStmtCacheSqlLimit. 

> I am not sure about that :-) How do I find out?

<prop key="hibernate.show_sql">true</prop>

This should log the Hibernate SQL statements.  Find the longest one and check how many characters it is,

then set the MySQL prepStmtCacheSqlLimit property to something slightly larger.  For example:

<prop key="hibernate.hikari.dataSource.prepStmtCacheSqlLimit">1024</prop>

[Brett Wooldridge]

I've been experimenting with the MySQL driver.  It seems you also must set this property:

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

Otherwise, the driver isn't really caching anything in spite of prepStmtCacheSize being set.  In my test, this cut the test runtime from 283ms to 12ms.

[Brett Wooldridge]

HikariCP 1.2.7 has been published to the Maven Central Repository, if you would like to try it (along with the above suggestions, esp. the cachePrepStmts=true setting).

[Vojtěch Knyttl]

With 1.2.7 I am getting:

Caused by: java.lang.ArrayStoreException

at java.lang.System.arraycopy(Native Method)

at com.zaxxer.hikari.util.FastStatementList.add(Unknown Source)

at com.zaxxer.hikari.proxy.ConnectionProxy.trackStatement(Unknown Source)

at com.zaxxer.hikari.proxy.ConnectionProxy.prepareStatement(Unknown Source)

at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162)

at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)

at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160)

at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.prepareQueryStatement(AbstractLoadPlanBasedLoader.java:256)

at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:200)

[Vojtěch Knyttl]

Apache benchmark with C3P0:

Processing:  1556 1714  72.1   1730    1826

Waiting:     1553 1709  72.1   1728    1823

Total:       1556 1714  72.1   1730    1826

Percentage of the requests served within a certain time (ms)

  50%   1730

  66%   1756

  75%   1780

  80%   1793

  90%   1803

  95%   1826

  98%   1826

  99%   1826

 100%   1826 (longest request)

Apachebenchmark for hikari 1.2.6:

              min  mean[+/-sd] median   max

Connect:        0    0   0.0      0       0

Processing:  1663 2048 259.7   2012    2549

Waiting:     1661 2043 258.3   2012    2542

Total:       1663 2048 259.7   2012    2549

Percentage of the requests served within a certain time (ms)

  50%   2012

  66%   2060

  75%   2330

  80%   2381

  90%   2494

  95%   2549

  98%   2549

  99%   2549

 100%   2549 (longest request)

The prepStmp cache has absolutely no effect, still the same results.

[Brett Wooldridge]

Can you run the benchmark with HikariCP 1.2.8?  And can you post your current C3P0 and HikariCP configurations?

[Vojtěch Knyttl]

Still the same results, Hikari +- 200 ms slower.

The configuration is still the same as I mentioned above. Changing the values of hibernate.hikari.dataSource.prepStmtCacheSize/limit has no effect.

[Brett Wooldridge]

When you say "same as I mentioned above", I'm still not clear.  The original configurations you posted does not have equivalent configurations.  The C3P0 pool has min_size=5, max_size=30, while HikariCP pool has minimumSize=20, maximumSize=100.  These are not equivalent.

The original configuration does not configure C3P0 with testConnectionOnCheckout=true, so if "same as I mentioned above" means that is still true, they are not equivalent.

In your tests, I don't know anything about your environment, how many test threads, are you performing warm-up runs for both C3P0 and HikariCP to ensure the JVM has compiled the methods?  The JVM needs at least 10,000 traversals of a method before it will JIT compile it.  What JVM parameters are you running with?  At a minimum you need "-server -XX:+UseParallelGC".  How many threads is the apachebench using?  If the number of threads is more than 30, and C3P0 has a max_size=30 while HikariCP has a maximumSize=100, you are actually creating more load on the MySQL database when testing HikariCP.

Benchmarking is extremely difficult to get right, especially when performing comparative tests.  That is why nearly every comparative benchmark of X vs. Y (Ruby vs. Python, MySQL vs. PostgreSQL, Hibernate vs. EclipseLink) that is posted on the internet results in a storm of "these tests were not equal" claims.

[Vojtěch Knyttl]

 

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.

[Brett Wooldridge]

It is unfortunate that C3P0 recommends that.  Only testing connections based on an idle period means that if the database is rebooted you application will be given invalid connections from the pool until all they have all been consumed and replaced with valid connections.  It is possible for this to continue until the idle test period has passed (by default 30 seconds).  Very few applications are coded to handle SQL connection failures in this way.

HikariCP will by default test your connections using the JDBC4 Connection.isValid() method, which for many databases is much faster than a query-based test, if properly implemented.  Additionally, HikariCP has an optimization such that when the system is under constant load, a connection that was used within the last 1 second is considered valid and the test is bypassed.

However, if you want to make the sacrifice of safety, HikariCP pool is not for you.  But I would recommend BoneCP instead of C3P0.  If you look at the latest benchmarks on the project page, you'll see that BoneCP is the next fastest pool and C3P0 is in last place by a huge margin.