I learned something interesting while examining behavior in a web application that uses a database connection pool. It was discovered that as the maximum number of connections in the pool was increased, the application performance decreased (response latency increased and CPU utilization decreased). My speculation as to why can be illustrated by analogy the to bank teller line metaphor.
Imagine a busy bank at lunch hour. Let’s say there are two teller windows open. Each teller can handle one customer at a time, in serial fashion. Now imagine that access to a teller is gated by a worker giving out tickets. Every customer has to get a ticket before getting to one of the teller lines. After doing business with the teller, the customer hands back the ticket, so that it can be used by another customer. There is a limited number of tickets, smaller than the line of customers, but greater than the number of tellers. In this analogy the customers are application threads, the tellers are CPUs, the ticket manager is the connection pool, and the tickets are connections. What happens if everything else stays the same, but the number of available tickets is varied?
The number of tickets (available connections) controls the size of the teller queue, i.e. the number of blocked threads awaiting a CPU. Customers (threads) have to first wait for a ticket (a connection) and then they have to wait for a teller (CPU). It seems that handing out a lot of tickets doesn’t speed things up. Once a thread has a ticket, it is still blocked from doing business until a CPU is available. It’s likely that threads proceed more slowly to accomplish their work when a lot of them are waiting to run. On the other hand, if a thread has to wait for a ticket, the number of threads trying to get CPU is reduced. There is less thread swapping and, even though more people are made to wait for a ticket, overall everyone gets their banking done and out of the bank faster.
This algorithm might be effectively applied in a real world bank. The ticket manager could be automated. Come to think of it, some delicatessens make customers take a ticket, but that is only to create a virtual queue, so people don’t have to stand in a row to keep their place. If the number of tickets were limited and recycled, everyone might be serviced more quickly, as non-intuitive as it might seem.