If my tomcat is configured to accept 100 incoming requests concurrently, how many connections should be present in DB connection pool ? In other words, if there a known optimal ratio of number of connections / number of incoming requests ?
Is it possible that application servers have made more requests / transactions per second than the db is configured to handle ? If yes, then what happens ? Are transactions queued ? dropped ?
In general the max number of running sessions has a relation with the number of CPU cores on the database server. If you have 32 cores it does not make sense to allow more connections and expect them all to run concurrently.
Because in most of the times there will be some waiting, you could allow a few more but the database system itself also needs some CPU to work for you.
So your database connection pool should not have many more connections than there are available CPU cores to run them on.
When all CPU's are loaded, the application will have to wait for connections to return results and putting more stress on the database will slowdown the application more.
The optimal connection pool size can be smaller but depends on the transaction length and the number of concurrent transactions. If every transaction returns it's connection to the pool the system can handle a lot of work.