MySQL hits Too many connections frequently

MySQL & MariaDB Percona Server for MySQL 8.0
1

We are using the below version and enabled MySQLX plugin. We frequently seeing issues with this instance with “Too Many Connections” & Show status is getting hung and we were unable to access the instance and restart is the only solution during that time.

8.0.16-7 Percona Server (GPL), Release 7, Revision 613e312

We have modified Thread related parameters and open files limit but no luck

Any inputs related to this issue will be grateful

mysql> show global variables like ‘%open%’;
±---------------------------±------+
| Variable_name | Value |
±---------------------------±------+
| have_openssl | YES |
| innodb_open_files | 12112 |
| mysqlx_port_open_timeout | 0 |
| open_files_limit | 90424 |
| table_open_cache | 12112 |
| table_open_cache_instances | 16 |
±---------------------------±------+

2

What is your max_connections variable set to? You may need to increase this parameter as this one directly affects how many simultaneous connections there can be.

3

Max connections is set to 1500, but as soon as it reaches 340+ connections I see too many connections error.

When I check like below the process ID of MySQL is occupied whole 1500 threads from OS side but they are not visible inside MySQL

ps -T 11105

11105 is a MySQL Process ID which I took from ps-ef | grep mysqld
Its just showing below output for 1500 times

11105 12739 ? Sl 0:06 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

4

You will always see many more threads in MySQL as its model is different from other databases. There is not a 1:1 relationship between number threads and connections.

I would log into your mysql and run ‘show processlist’ every 1s and absolutely confirm that you never see more than 340 connections.

5

I have the script in please for every min and it doesn’t cross more than 350+ connections for sure

Regards
Kalyan

6

I would remove the mysqlx plugin, unless you specifically need it (very rare) and I would also recommend you upgrade to the latest 8.0.* version to see if a bug was fixed that is causing this issue.

7

MySQL X plugin is actively used by app teams. We will try to upgrade to latest version and update you

Thanks
Kalyan