Hikaricp: Maximum Connection Pool Size in production/Load testing

[MohanRamu]

Hi,

We do have Maximum connection pool size 30, When we do load testing with 600k requests that needs to have 2250k connections(hit to DB ) to be opened and released to pool with in 5 mins. It is not happening, we always get error(Could not acquire connection after waiting for 30000ms). When we increase the pool size to 300, 400 , 1000, it is working fine as expected. I need experts advice for increasing the pool size. What is the bench mark pool size for the following configuration .

[nitincchauhan]

If you missed, reading and watching video here may help you decide:
https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing

[brettwooldridge]

I translate "No of DB hits" to mean queries. 2250k queries in a 5 minute span = 7500 queries/sec. This part of the calculation is fixed, it is simple math.

Without considering the database and hardware, a next question might be: How long does each query take to execute? For example, if each query takes 5ms, then one thread using one connection can execute 200 queries/sec. In a perfect world, that would mean that ~38 threads/connections could execute 7500 queries/sec.

Now you need to do some homework. You need to figure out how long individual transactions are taking as the load scales up. One way to do this is to configure Dropwizard metrics, and watch the
<pool name>.pool.Usage metric. This metric is going to tell you the time between HikariDataSource.getConnection() and Connection.close() (times in milliseconds). Watching the
<pool name>.pool.Wait metric could also be interesting.

Add two more columns to your table above, and fill-in these observed values for the different "No of Requests". Lastly, run the loads (100k, 200k, ... 600k) using a single pool size, for example 200, and then run them all again with a lower value, 150, and then 100. You should start to get a "performance profile" of your application.

It is not particularly likely that your database can _actually_ process 1000 _simultaneous_ queries (unless you have ~512 CPU cores, or a DB cluster). I _would_ be interested to know what database and on what hardware you are testing.

[MohanRamu]

PFB the
RDS DB config: DBInstanceClassMemory=7.5GB

The query is taking 2ms.

[brettwooldridge]

Oh, Amazon RDS. You might want to read this slide stack:

http://www.pgcon.org/2014/schedule/attachments/315_Postgres%20in%20Amazon%20RDS.pdf

But, if your queries are taking 2ms on average, one connection should be able to run ~500 tps. Which suggests that 15 threads/connections should be able to run 7500 tps, if the backend can handle it. Of course that is just between the application and the DB, and does not account for other request processing such has HTTP handling etc.

When you talked about 600k requests in 5 minutes, you never mentioned how many "clients" that represents on the frontend. When you run the load tests, what does the backend load look like?

[brettwooldridge]

@rmdmohan Do you have any updated results from your profiling?

[MohanRamu]

Hi Brettwooldridge,

Can you please explain, how we can implement thread in Hikari for accommodate threads/connections.
I checked amazon RDS console 2000 iops are there and 200 GB size. Can you please explain about no of clients, threads and duration pg Bench Results, are they related to client requests. If it is that we have 7.5k clients and no threads. We have MySQL RDS.

[brettwooldridge]

Well, you're using MySQL so clearly pgbench (for PostgreSQL) doesn't apply. Looking at these graphs implies, at least to me, either one of two things:

• The database itself is not an issue. If you are experiencing timeouts under load, it is most likely the application servers (web-server, etc.) that are under-powered and/or over-provisioned.

_or_

• Even under load spikes when DB connections reached ~175, the CPU utilization was under 10% and read/write latency was barely affected. If your application experienced timeouts during these spikes it would indicate that there may be serious lock-contention in the database. This could be because of poor or missing indexes, or any of a dozen MySQL DB tuning parameters.

Amazon does have this to say:

---

System resources can constrain the throughput of a DB instance, but there can be other reasons for a bottleneck. If you find the following situation, your database could be the issue:

• The channel throughput limit is not reached
• Queue depths are consistently low
• CPU utilization is under 80%
• There is free memory available
• There is no swap activity
• There is plenty of free disk space
• Your application has dozens of threads all submitting transactions as fast as the database will take them, but there is clearly unused I/O capacity

_If there isn’t at least one system resource that is at or near a limit, and adding threads doesn’t increase the database transaction rate, the bottleneck is most likely contention in the database. The most common forms are row lock and index page lock contention, but there are many other possibilities. If this is your situation, you should seek the advice of a database performance tuning expert._

---

I think you are most likely to find useful answers on the Amazon RDS Discussion Forums. You question is less about connection pools in general and more about database and/or application tuning.

Increasing the number of connections or threads when there is high-contention or a misconfigured database can either make the problem worse, or even if it works could be far from an optimal solution. If you put in a big enough engine, even a car with square wheels can be driven.