Given that thread and connections in the MySQL Server
have been so intertwined, it is easy to confuse the
purpose of the MySQL Thread Pool and the purpose of
a Connection Pool.
The aim of a Connection Pool is that the MySQL
clients should not be forced to constantly do connect and
disconnect. Thus it is possible to cache a connection in
the MySQL client when a user of the connection no longer
needs it. Thus another user that needs a connection to the
same MySQL Server can reuse this cached connection later on.
This saves execution time in both the client and the server.
It does however not change the dynamics of how many queries
are executed in parallel in the MySQL Server. This means that
the likelihood of too many concurrent queries to execute in
the MySQL Server is the same with or without a Connection
Pool.
Also a Connection Pool operates on the client side. This
means that it doesn't see the state of the MySQL Server when
deciding whether to send a query to the MySQL Server or not. Thus
it doesn't have the required information to decide whether to
queue a query or not. Only the MySQL Server have this information
and thus the MySQL Thread Pool has to operate in the MySQL Server.
It cannot perform its task on the client side.
Thus it is easy to see that the MySQL Thread Pool and a
Connection Pool are orthogonal and can be used independent of
each other.
If I understand this right:
ReplyDelete- I connect to MySQL server
- execute some queries
- call MySqlConnection.Close()
- at this point connection is not closed, it is kept in connection pool of the client (application). When I execute again MySqlConnection.Open(connection string), connection will be created from the connection pool of the application (connection poll just returns instance of MySqlConnection class).
Once the connection from the client to MySQL server has been established it means that as long as application is running socket is opened at client side and another socket and a thread are used on MySQL server. This means that MySQL server keeps socket and thread reserved for this client. Or is it better when client finishes its "sql work" that MySQL server closes socket and terminates client's thread, so this socket can become available/free for other clients. Also if client application does some other work (not sql), MySQL server just waits for client (idle process).
The first paragraph describes the connection pool. Here a connection is put into a pool of connections when it is closed.
ReplyDeleteThe thread pool is about how connections are treated on the MySQL server side. Traditionally the connections have had a separate thread. With thread pool the connections grabs a thread from the pool of threads when a query needs to be executed.