Using persistent connections in MySQL but still many open connections

Question

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?

Answer 1

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.