Hikaricp: About Pool Sizing in distributed environments / microservices

[billoneil]

Would it be possible to add an example or two to the About Pool Sizing wiki related to multiple app servers?

Guess what that means? Your little 4-Core i7 server with one hard disk should be running a connection pool of: 9 = ((4 * 2) + 1). Call it 10 as a nice round number.

If we have two app servers does that mean each should have ~5 connections? What about four app servers?

Here is a trend I have noticed. Years ago we had the two monolith apps pointing at a single DB each with a pool size of 50. Which means the db has 100 active connections.

Ideally we would have each app server would have 5-10 connections so the db would sit at 10-20 connections.

Since micro services are all the rage what we end up with is two instances each of 5 micro services all hitting the same db (they probably should be separate databases but unrelated). They read the pool sizing article and we end up with 10 services each with a pool size of 10. The DB now has
100 active connections again, full cycle back to the beginning. I personally disagree with this set up but I see it all the time. Often times 90% of the active connections just sit idle and do nothing.

When you have multiple applications hitting the same db how do you distribute the connection pool sizes?

[nunnr]

You have understood the documentation correctly I think. Sweet spot for best database throughput is tied to hardware resources of the database.

If you had 2 large apps on a server, happily running 10 connections total, then the same database work performed by 4 apps split from the original 2 would still suit 10 connections.

Deciding how to allocate those connections per micro service might be as simple as:

2 services, 5 connections each
4 services, 2-3 connections each
8 services, 1 connection each
...but best to do some profiling. A user authentication service would likely do more database work than a log writer service, and justify more connections, for instance.

Also, that example of 8 services with a single connection each doesn't sound nice. In my opinion, if a server only has the power to support 10 connections, 8 micro services is too many!

A system I work on has 4 app servers with 4 services (Spring Boot apps mostly) per server. The services have 2-5 connections each. I think that's fairly typical.

Finally, if you see 90% connections idle, adjust HikariCP's minimumIdle setting to 10% of your maximumPoolSize.

[brettwooldridge]

@billoneil @nunnr The pool sizing guideline is basically with respect to a single component (application, etc) and a single database.

It can get bit trickier when you are trying to size many pools accessing a single shared database. So, here are some ways to think about it.

Assume our hypothetical database running on a 4-core box, and our calculated maximum simultaneous query throughput of 10.

With respect to a single component, for example a monolithic application, running any more than 10 simultaneous queries will yield a lower throughput (higher latency). If the average query time is 2ms, then within a 2ms period we can satisfy 10 queries. If we allowed 20 queries (eg. a pool size of 20), then 20 queries run simultaneously could not be satisfied in 2ms, and would in fact likely be more than double due to the overhead of time-slicing by the scheduler.

But this also kind of assumes a constant load (continuous queries). So, maybe we need another way to think about it.

Let's imagine two micro-services, Service A and Service B. With respect to each service, 10 is still going to be the maximum pool size. Because, even if Service B is completely idle, Service A cannot do any better than 10 simultaneous queries, given the database capabilities. And the same is true for Service B, if Service A is completely idle. So, at least we know that the maximum still holds.

If Service A and Service B are generating continuous load, i.e. they are basically always running queries as fast as they can, then it would make sense to set each pool to a maximum of 5 connections. For N-services that are generating continuous load, the formula for the maximum pool size would be simple: (maximum simultaneous database queries) / N.

However, if Service A and Service B are "bursty", sometimes firing a bunch of queries, and the rest of the time idle (with respect to the database), then it probably makes sense to have maximum pool sizes somewhere between 5 and 10.

Because there is no coordination between Service A and Service B, this does mean that statistically, and somewhat unpredictably, the database can be temporarily over-committed. For example, if the pool size was 8, the stochastic distribution of load would generally mean that the database is probably running around 8 queries simultaneously. But it would also mean that occasionally both Service A and Service B would just happen to want to run 8 queries each, and the database would (sub-optimally) be required to run 16 simultaneous queries. However, there is not too much to be done in such a situation. If such occurrences are infrequent, it may never be noticed.

The more services you add, the tricker it gets to determine maximum (and minimum) pool sizes. Metrics are your friend and an essential tool for tuning such a deployment.

Start by picking a pool size for each service, based on a best guess about its needs, without considering other services. And collect metrics for each service:

• Active connections
• Idle connections
• Waiting thread count
• Usage (how long each connection is out of the pool)

If a service never maxes out its pool, and the waiting count is always zero, find the maximum number of active connections during some period (1 hour, 1 day, etc.) and use that as the maximum pool size (for that service).

Find the services with the highest "usage" time (time each connection is out of the pool), and try to determine why. Is it a slow query? Is a thread actually doing work unrelated to the database while holding a Connection (try to release back to the pool ASAP after a query)?

Put everything into a spreadsheet, for example Active Connections in the example below:

Active Connections

| Timestamp | Service A | Service B | Service C | Total |
|:----------- |:---------:|:----------:|:---------:|:-----:|
| 16:04:00 | 5 | 0 | 2 | 7 |
| 16:04:30 | 3 | 2 | 5 | 10 |
| 16:05:00 | 8 | 2 | 5 | 15 |
| 16:05:30 | 3 | 0 | 0 | 3 |
| 16:06:00 | 1 | 2 | 5 | 8 |
| 16:06:30 | 4 | 2 | 1 | 7 |

Look for points where the total active exceed the database capacity. Actually double-check that the CPU load on the DB corresponds with these peaks. If the DB load does not correspond with these peaks, it implies that service(s) are doing non-DB related work while holding a connection.

If these peaks are are infrequent, don't worry about them. If they are frequent, start pairing down pool sizes slowly, guided by the collected metrics. Then observe the system again, and repeat the process.

[johnou]

@brettwooldridge great tips but the current metrics (at least CodahaleMetricsTracker) does not really provide enough "resolution" for fine tuning. In bursty workloads it's easy for sample metrics to miss important information, we extended CodahaleMetricsTracker to include a high-water mark for active connections metric which resets when polled in the mbean https://gist.github.com/johnou/6e909f167b4bf59e9e18152e10f4b3c7

If you are interested I would be happy to create a pull request with the changes to CodahaleMetricsTracker.

[brettwooldridge]

@johnou How frequently do you poll for metrics?

[johnou]

@brettwooldridge we poll every 30 - 60 seconds, the more often you sample, the better the data is going to be, but you still can't know how high it goes between samples (for something like this where each connection is only used for a few ms at a time). As long as the polling interval is significantly higher than the avg query duration, the data is not going to be great.

[brettwooldridge]

If the active “high-water mark” is reset at every poll, how is it different than the actual active count collected at the time of the poll? Are the two metrics polled at different rates?

I can investigate using a LongAdder to track the actual active high-water mark, captured at borrow/return. I’ll have to benchmark the effect on performance, but my experience with LongAdder leads me to believe it to be nearly immeasurable.

This is exactly the use case it was designed for — metrics that may be in/decremented thousands of times per second but measured at an order of magnitude less frequency. Prior experience has shown increment to be within one order of magnitude (if not the same magnitude) as a raw integer increment. Polling that value obviously incurring somewhat more overhead.

[johnou]

@brettwooldridge high-water mark is calculated every borrow, if the current active count > high-water mark then the high-water mark is set to the current (highest) active count. After the high-water mark metric is polled it is "reset" to the current active count. I actually tried implementing an active connection counter based on borrow / return using a volatile int and an AtomicIntegerFieldUpdater but it got out of sync and I haven't investigated yet.

[jimforcode]

@brettwooldridge
i have a problem.
condition:
mysql server : 2core,4G ,max_connection: 2000
we have two simple application (without complicated business logic ).each application set connection 300 . totally ,600 connections. they running well,have good performance. but what if i set the connection lower. the performance goes down.

[brettwooldridge]

@jimforcode That seems extremely suspicious to me. Only 2 CPU cores, but 600 connections? Can you provide some actual measurements showing that performance is worse with fewer connections?