ProxySQL sending more connections to the MariaDB servers on the backend

[Fatbardh Hysa]

Hi, 

I have a MariaDB Galera Cluster version 10.3 with 3 nodes and ProxySQL version 2.4.8-2-g2cc900a in front of the nodes. Application connecting to the database sends around 250k requests per minute during peak hours, but creates a connection pool with only 300 connections. Basically it uses that connection pool of 300 connections to send any requests to the database. What happens during peak hours is that too many connections reach to the MariaDB nodes on the backend from ProxySQL that the whole cluster goes out of sync and fails. From the ProxySQL log file I see the following logs:

MySQL_Session.cpp:5335:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [WARNING] mysql-max_connections reached. Returning 'Too many connections'
MySQL_Session.cpp:5335:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [WARNING] mysql-max_connections reached. Returning 'Too many connections'
MySQL_Session.cpp:5335:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [WARNING] mysql-max_connections reached. Returning 'Too many connections'
MySQL_Session.cpp:5335:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [WARNING] mysql-max_connections reached. Returning 'Too many connections'

This issue does not happen often, maybe once every few months and it does not have a specific pattern that we can reproduce. We tried to reproduce the issue by generating high amount of load to the database, but nothing happened. In attempts to solve the issue at some point we disabled multiplexing completely after reading in some forums that it might cause issues and create more connections on the backend about some types of queries and since the application only creates a connection pool of 300 connections we disabled it.  I am aware that the ProxySQL and MariaDB are over a year old and a lot might have improved since then, but before considering the upgrade (which we will have to at some point) I wanted to ask if someone else had such issues and if yes how did they solve it. Another important information could be that the application is from a 3rd party vendor and I don't have much control over it. Besides changing some parameters which are relevant to the workflow we have I cannot control anything else. Especially in terms of how it interacts with the database. MariaDB allows 1000 connections. Please let me know what else I should provide to help troubleshoot the issue. 

Kind Regards,

Fatbardh

[René Cannaò]

Hi Fatbardh,

Thank you for using ProxySQL.

Please do not disable multiplexing: it may create even more connections.

Multiplexing allows a single backend connection to be used by multiple frontend connections, thus if you disable that feature, potentially more connections are created. 

The root cause of the problem is, almost certainly, that you have configured a high value in mysql_servers.max_connections , therefore proxysql was configured ("was given permission") to create (and potentially retain) a large amount of connections.

I hope this helps.

Sincerely, 

René 

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/d2422b33-7630-49c1-9169-8e51dc1c8802n%40googlegroups.com.

[Fatbardh Hysa]

Hi René,

Thank you for your quick reply.

Could it be the "mysql-max_connections | 2048"? I pulled that value from global_variables.

Your suggestion would be to set it to a lower value or to increase the connections on the MariaDB side? 

Kind Regards,

Fatbardh 

[René Cannaò]

Hi,

On Wed, 17 Apr 2024, 18:35 Fatbardh Hysa, <bard...@gmail.com> wrote:

Hi René,

Thank you for your quick reply.

Could it be the "mysql-max_connections | 2048"? I pulled that value from global_variables.

mysql-max_connections is not relevant.

Your suggestion would be to set it to a lower value or to increase the connections on the MariaDB

side? 

I wrote I believe you configured mysql_servers.max_connections to a high value, thus the suggestion is to lower it.

To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/2f6d2076-e7e3-4b63-9845-6479b398ac1bn%40googlegroups.com.

[Fatbardh Hysa]

Hi René,

Apologies for the dumb question, but could you maybe please point me to where I can find this value? 

I configured ProxySQL from the CLI and not from the config file. I did not configure that value at all so probably it has some default value set to it. I am also searching online, but cannot figure out where to find the value of that.

Kind Regards,

Fatbardh

[Fatbardh Hysa]

Is it the value from the runtime_mysql_servers?

Admin> SELECT * FROM runtime_mysql_servers;
+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.10.190 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.10.26  | 3306 | 0         | SHUNNED | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.10.52  | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.10.190 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.10.26  | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.10.52  | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 6            | 192.168.10.190 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 6            | 192.168.10.26  | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
8 rows in set (0.00 sec)

[René Cannaò]

Yes,

It is the current value in runtime_mysql_servers , that likely you configured in mysql_servers and then LOADed to runtime

To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/95ce41f9-1ee4-4b16-949e-2b3b27edd654n%40googlegroups.com.

[Fatbardh Hysa]

Thank you for the help. I will test that and see how it goes.