[DISCUSS] CloudStack Connection Pools

2018-03-12 Thread Nicolas Vazquez 
Hi all,


I would like to introduce a topic for discussion, regarding DB connection pools 
used in CloudStack, currently Apache Commons DBCP 1.4 
(http://commons.apache.org/) is used. I've been investigating this topic as we 
are having complains of random issues on MySQL connection pool on large 
environments. Please let me know if this topic has already been discussed 
before.


First of all, DBCP 1.4 has been released on 2010 
(https://commons.apache.org/proper/commons-dbcp/changes-report.html), and no 
minor/patch version has been released since then. It seems to work in high 
performance with relatively low traffic and low load applications. However, it 
is single threaded, and in order to be thread-safe, the entire pool needs to be 
locked. It is also reported that an CPU and concurrent threads increases, the 
performance gets affected. This is a serious issue on highly concurrent 
systems, such as CloudStack.


I've been investigating some options to replace it:
- The first option can be upgrading to version 2.x. Issues on performance and 
concurrency could be solved using this version.
- Tomcat JDBC Connection Pool. Please check: 
https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html.

- Other replacement options found: BoneCP, C3P0, HikariCP


Given these options, I've been looking for benchmarks to compare them (*). 
Looks like HikariCP (http://brettwooldridge.github.io/HikariCP/) could be the 
best replacement, improving performance and stability. Another good replacement 
option could be Tomcat.


I've been also examining the codebase, data source initialization is done on 
TransactionLegacy class under the cloud-framework-db project. Replacement work 
should be done on this class. Instead of pure replacement, a global setting can 
be introduced to make the admins able to select which connection pool to use.

What do you think? Any possitive/negative feedback is welcome as well as new 
ideas. As mentioned before, I don't know if it has been discussed before, sorry 
in advance if it has.

Kind regards,
Nicolas

(*) Links to benchmarks and comparissons:
https://www.wix.engineering/single-post/how-many-threads-does-it-take-to-fill-a-pool
https://www.wix.engineering/single-post/how-does-hikaricp-compare-to-other-connection-pools
https://beansroasted.wordpress.com/2017/07/29/connection-pool-analysis/
https://beansroasted.wordpress.com/tag/connection-pool-comparison/
https://github.com/brettwooldridge/HikariCP/wiki/%22My-benchmark-doesn't-show-a-difference.%22
http://www.trustiv.co.uk/2014/06/battle-connection-pools

nicolas.vazq...@shapeblue.com 
www.shapeblue.com
,   
@shapeblue

Re: [DISCUSS] CloudStack Connection Pools

2018-03-12 Thread Khosrow Moossavi 
Hi Nicolas

>From my past experiences, I prefer 1) HikariCP 2) Tomcat Pool 3) C3P0 4)
DBCP in that order. Although I don't have
any benchmark of my own to provide, and the ones you mentioned are really
informative anyway.

To me the broader subject is the _one_ who uses the pool, I mean if the
transactions are handled in a faster way and
released sooner and with shorter locks, generally speaking if it's more
efficient, I don't think from ACS point of view
there won't be much difference between the above mentioned options.

On the same subject, it might be more interesting to use Spring Boot in
general and Spring Boot Data in particular
rather than only changing the CP functionality, and slowly refactor/retire
the DAO layer in favor of Spring Boot equivalent
implementation.


Khosrow Moossavi

CloudOps



On Mon, Mar 12, 2018 at 9:32 PM, Nicolas Vazquez <
nicolas.vazq...@shapeblue.com> wrote:

> Hi all,
>
>
> I would like to introduce a topic for discussion, regarding DB connection
> pools used in CloudStack, currently Apache Commons DBCP 1.4 (
> http://commons.apache.org/) is used. I've been investigating this topic
> as we are having complains of random issues on MySQL connection pool on
> large environments. Please let me know if this topic has already been
> discussed before.
>
>
> First of all, DBCP 1.4 has been released on 2010 (
> https://commons.apache.org/proper/commons-dbcp/changes-report.html), and
> no minor/patch version has been released since then. It seems to work in
> high performance with relatively low traffic and low load applications.
> However, it is single threaded, and in order to be thread-safe, the entire
> pool needs to be locked. It is also reported that an CPU and concurrent
> threads increases, the performance gets affected. This is a serious issue
> on highly concurrent systems, such as CloudStack.
>
>
> I've been investigating some options to replace it:
> - The first option can be upgrading to version 2.x. Issues on performance
> and concurrency could be solved using this version.
> - Tomcat JDBC Connection Pool. Please check: https://tomcat.apache.org/
> tomcat-7.0-doc/jdbc-pool.html.
>
> - Other replacement options found: BoneCP, C3P0, HikariCP
>
>
> Given these options, I've been looking for benchmarks to compare them (*).
> Looks like HikariCP (http://brettwooldridge.github.io/HikariCP/) could be
> the best replacement, improving performance and stability. Another good
> replacement option could be Tomcat.
>
>
> I've been also examining the codebase, data source initialization is done
> on TransactionLegacy class under the cloud-framework-db project.
> Replacement work should be done on this class. Instead of pure replacement,
> a global setting can be introduced to make the admins able to select which
> connection pool to use.
>
> What do you think? Any possitive/negative feedback is welcome as well as
> new ideas. As mentioned before, I don't know if it has been discussed
> before, sorry in advance if it has.
>
> Kind regards,
> Nicolas
>
> (*) Links to benchmarks and comparissons:
> https://www.wix.engineering/single-post/how-many-threads-
> does-it-take-to-fill-a-pool
> https://www.wix.engineering/single-post/how-does-hikaricp-
> compare-to-other-connection-pools
>  compare-to-other-connection-pools>https://beansroasted.
> wordpress.com/2017/07/29/connection-pool-analysis/
> https://beansroasted.wordpress.com/tag/connection-pool-comparison/
> 
> https://github.com/brettwooldridge/HikariCP/wiki/%22My-benchmark-
> doesn't-show-a-difference.%22
> http://www.trustiv.co.uk/2014/06/battle-connection-pools
>
> nicolas.vazq...@shapeblue.com
> www.shapeblue.com
> ,
> @shapeblue
>
>
>
>