Optimal number of connections in connection pool
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) ?
Answer
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.