Optimal number of connections in connection pool

Question

Currently we are using 4 cpu windows box with 8gb RAM with MySQL 5.x installed on same box. We are using Weblogic application server for our application. We are targeting for 200 concurrent users for our application (Obviously not for same module/screen). So what is optimal number of connections should we configured in connection pool (min and max number) (We are using weblogic AS' connection pooling mechanism) ?

Siliqua

This question probably belongs on serverfault. – Evocation

@Evocation - Its very much programming related question. H/W cofig description is for detailing problem. SO is perfect place for this question. – Siliqua

I would suggest 4 (CPUs) divided by the wait% time percent. I suggest MAX no more than 50 connections in parallel, probably less. Your CPU can only ever run 4 threads at a time, no amount of configuration can beat that. The only way it helps is handling IO waits, which depends on your app. – Deherrera

Additional information request. Post on pastebin.com and share the links. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report for server workload tuning analysis. - github.com/pmachapman by [email protected] for WINDOWS MySQLTuner 0.8.4 – Cent

Any chance you can post additional information requested on Mar 29, 2019 to allow analysis of current situation for your instance? – Cent

Answer 1

There is a very simple answer to this question:

The number of connections in the connection pool should be equal the number of the exec threads configured in WebLogic.

The rationale is very simple: If the number of the connections is less than the number of threads, some of the thread maybe waiting for a connection thus making the connection pool a bottleneck. So, it should be equal at least the number the exec threads (thread pool size).

Anadem

Answer 2

Did you really mean 200 concurrent users or just 200 logged in users? In most cases, a browser user is not going to be able to do more than 1 page request per second. So, 200 users translates into 200 transactions per second. That is a pretty high number for most applications.

Regardless, as an example, let's go with 200 transactions per second. Say each front end (browser) tx takes 0.5 seconds to complete and of the 0.5 seconds, 0.25 are spent in the database. So, you would need 0.5 * 200, or 100 connections in the WebLogic thead pool and 0.25 * 200 = 50 connections in the DB connection pool.

To be safe, I would set the max thread pool sizes to at least 25% larger than you expect to allow for spikes in load. The minimums can be a small fraction of the max, but the tradeoff is that it could take longer for some users because a new connection would have to be created. In this case, 50 - 100 connections is not that many for a DB so that's probably a good starting number.

Note, that to figure out what your average transaction response times are, along with your average DB query time, you are going to have to do a performance test because your times at load are probably not going to be the times you see with a single user.

Counselor

Answer 3

Sizing a connection pool is not a trivial thing to do. You basically need:

• metrics to investigate the connection usage
• failover mechanisms for when there is no connection available

FlexyPool aims to aid you in figuring out the right connection pool size.

Thissa

Answer 4

You should profile the different expected workflows to find out. Ideally, your connection pool will also dynamically adjust the number of live connections based on recent usage, as it's pretty common for load to be a function of the current time of day in your target geographical area.

Start with a small number and try to reach a reasonable number of concurrent users, then crank it up. I think it's likely that you'll find that your connection pooling mechanism is not nearly as instrumental in your scalability as the rest of the software.

Tisbe

+1 as I think I'm discovering "I think it's likely that you'll find that your connection pooling mechanism is not nearly as instrumental in your scalability as the rest of the software" – Alarick

Answer 5

The connection pool should be able to grow and shink based on actual needs. Log the numbers needed to do analysis on the running system, either through logging statements or through JMX surveillance. Consider setting up alerts for scenarios like "peak detected: more than X new entries had to be allocated in Y seconds", "connection was out of pool for more than X seconds" which will allow you to give attention to performance issues before they get real problems.

Mathis

Answer 6

It's difficult to get hard data for this. It's also dependent on a number of factors you don't mention -

• 200 concurrent users, but how much of their activity will generate database queries? 10 queries per page load? 1 query just on login? etc. etc.

• Size of the queries and the db obviously. Some queries run in milliseconds, some in minutes.

You can monitor mysql to watch the current active queries with "show processlist". This could give you a better sense of how much activity is actually going on in the db under peak load.

Sematic

Its almost 1 query per page with at most 3000 record per query. – Siliqua

Answer 7

This is something that needs to be tested and determined on an individual basis - it's pretty much impossible to give an accurate answer for your circumstances without intimately being familiar with them.

Croteau

Is there any formula/mechanism by which I can get rough number of connections in connection pool ? – Siliqua

You'd probably want to start any calculating by determining how many concurrent transactions (or users) you can actually expect – Croteau

Answer 8

Based on my experience on high transaction financial systems, if you want to handle for example 1K requests per seconds, and you have 32 CPU's, You need to have 1000/32 open connection polls to your database.

Here is my formula:

RPS / CPU_COUNT

If most cases, your database engine will be able to handle your requests even in much lower numbers, but your connections will be in waiting mode if the number is low.

I think it's pretty important to mention that your database should be able to handle those transactions (based on your disk speed, database configuration and server power).

Good luck.

Cotterell

this make sense :D – Branching

What are open connection polls? Do you mean you need approximate a maximum of 32 available number of connections in the pool for a 1000 concurrent requests? – Feverous

I have difficulty believing that this is accurate. By this formula, if I had 1 CPU I should configure 1000 connections in the pool. This would probably peg the CPU at 100% and nothing would get done. Meanwhile, on a hypothetical 128 core system, it would be less than 10 connections in the pool. This would probably result in most of the cores being idle in a transactional system where most queries don't go parallel. This formula yields a lower degree of concurrency the more cores you have in the system, which just can't be right. – Explosive

It's also worth noting that you can get 1k RPS on a single CPU system with one connection as long as your queries are running in 1ms or less. You don't need 1000 connections in that scenario, so this math really just doesn't add up to me – Explosive