user60627 user60627 - 6 months ago 7
SQL Question

which one is the best for mysql connection?

Here is my question below.

case 1:

Always make a connection or disconnection for every

TCP
client.

case 2:

Only make one connection and share the connection for every
TCP
client.

Which one is best for performance? Thanks!

Answer

I assume that your architecture is n-tier, something like:
DB_SERVER <---> APPLICATION <----> MANY_CLIENTS
...and you consider a solution in the APPLICATION part?
Assuming so, it still depends:

  1. From the performance point of view it is better to have fewer connections. But the gain of squeezing multiple client requests into a single connection may be small if network has low latency between DB_SERVER and APPLICATION.
  2. The logic of your application to ensure a single (multiplexed) connection can be very simple, if every client only requires one query. But it may also be complex if the communication with clients requires doing many SQL queries, and if they intervene. So the cost (in terms of APLICATION complexity) may well out-weight small benefits in network performance.

So you have to consider the gain and the cost... the result depends heavily on both the above factors.

Of course, you can also hit some connection-table size limit if you consider really many concurrent connections. Or you may have some securitiy-specific connection limitation between DB_SERVER and APPLICATION. In such cases multiplexing client connection into one may be the only option.

Comments