Using persistent connections in MySQL but still many open connections

user_2963652 0
Viewed 1259

I have a chatbot that works on a callback api, which sends a request on my server every time someone writes a message

Recently, I have read of so-called persistent connections, that made me think that I can avoid reconnecting to database each time I get a request on my server, because the database loading takes some time and I would like to speed up that process

So, I've changed any connection in my script to have a p: prefix, like this

$conn = new mysqli("p:".$servername, $username, $password, $dbname);

As I've understood, this way mysqli finds an existing connection with same parameters or creates one if it doesn't exist, instead of opening a new connection every time

But still, a couple of hours later I've checked open connections and I've noticed bunch of similar connections, different only by their ID, like this

  ID      | USER | HOST      | DB       | COMMAND | TIME | STATE | INFO |
+---------+------+-----------+----------+---------+------+-------+------+
| 5248403 | user | localhost | database | Sleep   |   24 |       | NULL |
| 5248609 | user | localhost | database | Sleep   |  113 |       | NULL |
| 5247822 | user | localhost | database | Sleep   |    1 |       | NULL |
| 5248652 | user | localhost | database | Sleep   |   79 |       | NULL |

(with user and database masking actual user and database)

Is there something that I've misunderstood about persistent connections? What can I do to avoid similar connections?

persistent mysqli mysql php
4 Answers

Persistent connections can be reused by subsequent requests, but if your site is handling multiple concurrent requests, they each need their own connection.

In other words, multiple concurrent PHP requests cannot use the same connection to the database at the same time.

It's not a problem for MySQL to handle multiple connections. The default max_connections limit is set to 151 in MySQL 8.0, but it can be higher if your server is strong enough. In my job, we use high-end database servers, so we raised max_connections to 4096. But we caution the developers that they probably don't want it going over 1024. Typically we see the number of connections stay at a few hundred.

Bill_Karwin_208600

A textbook XY problem

the database loading takes some time and I would like to speed up that process

I have a feeling that a persistent connection would have been the last option to solve your problem, if any.

As I've understood, this way mysqli finds an existing connection with same parameters or creates one if it doesn't exist, instead of opening a new connection every time

It is actually far from that. A persistent connection in PHP is bound to a number of threads PHP executes in. And each thread which happen to open a connection will hold it open further, providing this already opened connection for the scripts it is going to execute. Means a persistent connection is persistent, or in other words, a persistently open.

Hence, the picture you can see is expected.

As long as you don't run into "Too many connections" error it is all right, though I doubt thee is a measurable benefit in using a persistent connection.

user_2855870

I'm guessing it is connection pooling. In your case, it creates a pool of 4 connections that can be used to serve 4 requests/queries in parallel. If you had only 1 connection and a query was running on it, then any other request which needs the DB would have to wait.

Another explanation could be that your web server is running 4 child processes to serve multiple requests in parallel and therefore each child processes creates/gets it own connection which it can reuse internally

This can be said for many other webserver/frameworks and nothing specific to php/mysqli

user_14636220

Yes, I believe you misunderstood a little bit what persistent connections are.

I've understood, this way mysqli finds an existing connection with same parameters or creates one if it doesn't exist, instead of opening a new connection every time

When you use persistent connections PHP will not request closing of a connection when the PHP script ends its execution. Instead the connection will be kept alive and if in idle state, PHP might reuse that connection again.

Each execution of the same script needs a separate connection anyway. If no connection is available to be reused, then PHP will try to open a new persistent connection.

This is only a recipe for disaster. If you do not clean up the connection manually properly you can see some weird behaviour. If you do not need to use them, it's better to stay away from them. Using persistent connections can only make it so you reach the max_connections limit quicker.

If you use MySQL on localhost, which seems to be the case, you would not see much of a performance gain. There's very little reason to go for such small optimisations. Opening and closing a connection is typically a very quick process, especially if it's on localhost.

user_18394390
Related