mysql8 server shunned for too many connections

[Michael Erwin]

We're in the process of upgrading an existing proxysql config from mysql 5.7 to mysql 8. The cluster is on RDS and in the testing environment has one rw and one replica.

There seems to be a large difference in behavior in terms of how many connections are being created. The only config changes we made besides the server_version are to remove the "match/replace" on a few of our query rules, e.g.

    match_pattern="transaction_isolation"
    replace_pattern="tx_isolation"

All of our multiplexing config is identical. The majority of our services are clojure (jvm) applications using the driver: mysql/mysql-connector-java "8.0.22"

The version of proxysql is 2.3.2 (I also tried upgrading but no difference in behavior).

The mysql_variables.max_connections is set to 500. 

Our RDS instances have a max_connection of 307. I tried setting the mysql_servers.max_connections to 300 and 250. It reduces the number of errors but eventually hits the max_connections and still causes the apps to fail. Before we had this config value unset, which demonstrates how much of a non-issue this was on 5.7 (and earlier). 

On mysql 5.7:
ProxySQL Admin> SELECT * FROM stats_mysql_global WHERE Variable_Name LIKE '%connection%'; +--------------------------------------+----------------+ | Variable_Name | Variable_Value | +--------------------------------------+----------------+ | Client_Connections_aborted | 20566 | | Client_Connections_connected | 141 | | Client_Connections_created | 349590 | | Server_Connections_aborted | 225 | | Server_Connections_connected | 451 | | Server_Connections_created | 91891 | | Server_Connections_delayed | 0 | | Client_Connections_non_idle | 141 | | mysql_killed_backend_connections | 0 | | client_host_error_killed_connections | 0 | | Client_Connections_hostgroup_locked | 0 | | Access_Denied_Max_Connections | 20566 | | Access_Denied_Max_User_Connections | 0 | +--------------------------------------+----------------+

On mysql 8 with server max_connections set to 250

Proxysql Admin> SELECT * FROM stats_mysql_global WHERE Variable_Name LIKE '%connection%'; +--------------------------------------+----------------+ | Variable_Name | Variable_Value | +--------------------------------------+----------------+ | Client_Connections_aborted | 1420 | | Client_Connections_connected | 468 | | Client_Connections_created | 15296 | | Server_Connections_aborted | 5445 | | Server_Connections_connected | 309 | | Server_Connections_created | 10294 | | Server_Connections_delayed | 0 | | Client_Connections_non_idle | 358 | | mysql_killed_backend_connections | 0 | | client_host_error_killed_connections | 0 | | Client_Connections_hostgroup_locked | 0 | | Access_Denied_Max_Connections | 1420 | | Access_Denied_Max_User_Connections | 0 | +--------------------------------------+----------------+

The specific errors I'm seeing: * With mysql_servers.max_connections set to 1000, many 1040: Too Many Connections errors * With mysql_servers.max_connections set to 300, some 1040 errors. More "server has been shunned because it reached max_connections" * With mysql_servers.max_connection set to 250, no 1040 errors. Many "server has been shunned" errors. These are all only happening against the rw server (hostgroup 0), not the replica (hostgroup 1). Is there anything I'm missing here?

[René Cannaò]

Hi Michael,

Not many information here (the full error log will be very useful, there will be useful information there), but I suspect you are having at least 2 distinct and unrelated issues:

1) some of the JDBC driver queries are disabling multiplexing . For example, in "5.7" setup,  Client_Connections_connected and Client_Connections_non_idle are identical: this suggests multiplexing is disabled.

2) in the "mysql 8" setup, Client_Connections_connected is more than 3x the value in the "5.7" setup . This suggests that the client is creating way more connections: maybe some setting has changed in the client too

I hope this helps

--
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/010e5489-5438-4b3f-820b-2e431d69df0bn%40googlegroups.com.