MySQL - Persistent connection vs connection pooling

Question

In order to avoid the overhead of establishing a new connection each time a query needs fired against MySQL, there are two options available:

1. Persistent connections, whereby a new connection is requested a check is made to see if an 'identical' connection is already open and if so use it.
2. Connection pooling, whereby the client maintains a pool of connections, so that each thread that needs to use a connection will check out one from the pool and return it back to the pool when done.

So, if I have a multi-threaded server application expected to handle thousands of requests per second, and each thread needs to fire a query against the database, then what is a better option?

From my understanding, With persistent connections, all the threads in my application will try and use the same persistent connection to the database because they all are using identical connections. So it is one connection shared across multiple application threads - as a result the requests will block on the database side soon.

If I use a connection pooling mechanism, I will have all application threads share a pool of connections. So there is less possibility of a blocking request. However, with connection pooling, should an application thread wait to acquire a connection from the pool or should it send a request on the connections in the pool anyway in a round-robin manner, and let the queuing if any, happen on the database?

Answer 1

Having persistent connections does not imply that all threads use the same connection. It just "says" that you keep the connection open (in contradiction to open a connection each time you need one). Opening a connection is an expensive operation, so - in general - you try to avoid opening connections more often than necessary.

This is the reason why multithreaded applications often use connection pools. The pool takes care of opening and closing connections and every thread that needs a connection requests one from the pool. It is important to take care that the thread returns the connection as soon as possible to the pool, so that another thread can use it.

If your application has only a few long running threads that need connections you can also open a connection for each thread and keep this open.

Using just one connection (as you described it) is equal to a connection pool with the maximum size one. This will be sooner or later your bottleneck as all threads will have to wait for the connection. This could be an option to serialize the database operations (perform them in a certain order), although there are better options to ensure serialisation.

Answer 2

Regarding your question about should the application server wait for a connection, the answer is yes.

MySQL connections are blocking. When you issue a request from MySQL server over a connection, the connection will wait, idle, until a response is received from the server.

There is no way to send two requests on the same connection and see which returns first. You can only send one request at a time.

So, generally, a single thread in a connection pool consists of one client side connection (in your case, the application server is the client) and one server side connection (database).

Your application should wait for an available connection thread from the pool, allowing the pool to grow when it's needed, and to shrink back to your default number of threads, when it's less busy.