About Vlad Mihalcea

Vlad Mihalcea is a software architect passionate about software integration, high scalability and concurrency challenges.

Professional connection pool sizing

Introduction

I previously wrote about the benefits of connection pooling and why monitoring it is of crucial importance. This post will demonstrate how Flexy Pool can assist you in finding the right size for your connection pools.

Know your connection pool

The first step is to know your connection pool settings. My current application uses XA transactions, therefore we use Bitronix transaction manager, which comes with its own connection pooling solution.

Accord to the Bitronix connection pool documentation we need to use the following settings:

  • minPoolSize: the initial connection pool size
  • maxPoolSize: the maximum size the connection pool can grow to
  • maxIdleTime: the maximum time a connection can remain idle before being destroyed
  • acquisitionTimeout: the maximum time a connection request can wait before throwing a timeout. The default value of 30s is way too much for our QoS

Configuring Flexy Pool

Flexy Pool comes with a default metrics implementation, built on top of Coda Hale Metrics and offering two reporting mechanisms:

An enterprise system must use an central monitoring tool, such as Ganglia or Graphite and instructing Flexy Pool to use a different reporting mechanism is fairly easy. Our example will export reports to CSV files and this is how you can customize the default metrics settings.

Initial settings

We only have to give a large enough maxOverflow and retryAttempts and leave Flexy Pool find the equilibrium pool size.

NameValueDescription
minPoolSize0The pool starts with an initial size of 0
maxPoolSize1The pool starts with a maximum size of 1
acquisitionTimeout1A connection request will wait for 1s before giving up with a timeout exception
maxOverflow9The pool can grow up to 10 connections (initial maxPoolSize + maxOverflow)
retryAttempts30If the final maxPoolSize of 10 is reached and there is no connection available, a request will retry 30 times before giving up.

Metrics time

Our application is a batch processor and we are going to let it process a large amount of data so we can gather the following metrics:

    1. concurrentConnectionCount
      concurrentconnectioncount-initialsettings1
    2. concurrentConnectionRequestCount
      concurrentconnectionrequestcount-initialsettings1
    3. maxPoolSizeHistogram
      maxpoolsizehistogram-initialsettings1
    4. connectionAcquireMillis
      connectionacquiremillis-initialsettings1
    5. retryAttemptsHistogram
      retryattemptshistogram-initialsettings1
    6. overallConnectionAcquireMillis
      overallconnectionacquiremillis-initialsettings
    7. connectionLeaseMillisconnectionleasemillis-initialsettings

 

After analysing the metrics, we can draw the following conclusions:

  • The max pool size should be 8
  • For this max pool size there is no retry attempt.
  • The connection acquiring time has stabilized after the pool has grown to its max size.
  • There is a peak connection lease time of 50s causing the pool size to grow from 7 to 8. Lowering the time the connections are being held allows us to decrease the pool size as well.

If the database maximum connection count is 100 we can have at most 12 concurrent applications running.

Pushing the comfort zone

Let’s assume that instead of 12 we would need to run 19 such services. This means the pool size must be at most 5. Lowering the pool size will increase the connection request contention and the probability of connection acquire retry attempts.

We will change the maxOverflow to 4 this time while keeping the other settings unchanged:

NameValueDescription
maxOverflow4The pool can grow up to 10 connections (initial maxPoolSize + maxOverflow)

Metrics reloaded

These are the new metrics:

    1. concurrentConnectionCountconcurrentconnectioncount-contentionsettings
    2. concurrentConnectionRequestCountconcurrentconnectionrequestcount-contentionsettings
    3. maxPoolSizeHistogrammaxpoolsizehistogram-contentionsettings
    4. connectionAcquireMillisconnectionacquiremillis-contentionsettings
    5. retryAttemptsHistogramretryattemptshistogram-contentionsettings
    6. overallConnectionAcquireMillisoverallconnectionacquiremillis-contentionsettings
    7. connectionLeaseMillisconnectionleasemillis-contentionsettings

 

Analysing the metrics, we can conclude that:

  • For the max pool size of 5 there are at most 3 retry attempts.
  • The overall connection acquiring time confirms the retry attempts.
  • The peak connection lease time was replicated, even if it’s around 35s this time.

Conclusion

Flexy Pool eases connection pool sizing while offering a failover mechanisms for those unforeseen situations when the initial assumptions don’t hold up any more.

Alerts may be triggered whenever the number of retries exceeds a certain threshold, allowing us to step in as soon as possible.

 

Reference: Professional connection pool sizing from our JCG partner Vlad Mihalcea at the Vlad Mihalcea’s Blog blog.
Related Whitepaper:

Software Architecture

This guide will introduce you to the world of Software Architecture!

This 162 page guide will cover topics within the field of software architecture including: software architecture as a solution balancing the concerns of different stakeholders, quality assurance, methods to describe and evaluate architectures, the influence of architecture on reuse, and the life cycle of a system and its architecture. This guide concludes with a comparison between the professions of software architect and software engineer.

Get it Now!  

Leave a Reply


9 − eight =



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close