MySQL router in K8 gets connection overflow

Question

I had successfully deployed MySQL router in Kubernetes as described in this answer.

But recently I noticed mysql router was having overflow issues after some time.

Starting mysql-router.
2022-03-08 10:33:33 http_server INFO [7f2ba406f880] listening on 0.0.0.0:8443
2022-03-08 10:33:33 io INFO [7f2ba406f880] starting 4 io-threads, using backend 'linux_epoll'
2022-03-08 10:33:33 metadata_cache INFO [7f2b63fff700] Starting Metadata Cache
2022-03-08 10:33:33 metadata_cache INFO [7f2b63fff700] Connections using ssl_mode 'PREFERRED'
2022-03-08 10:33:33 metadata_cache INFO [7f2b9c2c8700] Starting metadata cache refresh thread
2022-03-08 10:33:33 routing INFO [7f2b617fa700] [routing:myCluster_ro] started: listening on 0.0.0.0:6447, routing strategy = round-robin-with-fallback
2022-03-08 10:33:33 routing INFO [7f2b60ff9700] [routing:myCluster_rw] started: listening on 0.0.0.0:6446, routing strategy = first-available
2022-03-08 10:33:33 routing INFO [7f2b3ffff700] [routing:myCluster_x_ro] started: listening on 0.0.0.0:64470, routing strategy = round-robin-with-fallback
2022-03-08 10:33:33 routing INFO [7f2b3f7fe700] [routing:myCluster_x_rw] started: listening on 0.0.0.0:64460, routing strategy = first-available
2022-03-08 10:33:33 metadata_cache INFO [7f2b9c2c8700] Potential changes detected in cluster 'myCluster' after metadata refresh
2022-03-08 10:33:33 metadata_cache INFO [7f2b9c2c8700] Metadata for cluster 'myCluster' has 1 replicasets:
2022-03-08 10:33:33 metadata_cache INFO [7f2b9c2c8700] 'default' (3 members, single-primary)
2022-03-08 10:33:33 metadata_cache INFO [7f2b9c2c8700]     node3.me.com:3306 / 33060 - mode=RW 
2022-03-08 10:33:33 metadata_cache INFO [7f2b9c2c8700]     node2.me.com:3306 / 33060 - mode=RO 
2022-03-08 10:33:33 metadata_cache INFO [7f2b9c2c8700]     node1.me.com:3306 / 33060 - mode=RO 
2022-03-08 10:33:33 routing INFO [7f2b9c2c8700] Routing routing:myCluster_x_rw listening on 64460 got request to disconnect invalid connections: metadata change
2022-03-08 10:33:33 routing INFO [7f2b9c2c8700] Routing routing:myCluster_x_ro listening on 64470 got request to disconnect invalid connections: metadata change
2022-03-08 10:33:33 routing INFO [7f2b9c2c8700] Routing routing:myCluster_rw listening on 6446 got request to disconnect invalid connections: metadata change
2022-03-08 10:33:33 routing INFO [7f2b9c2c8700] Routing routing:myCluster_ro listening on 6447 got request to disconnect invalid connections: metadata change
2022-03-08 14:59:30 routing WARNING [7f2b9d2ca700] [routing:myCluster_rw] reached max active connections (512 max=512)
2022-03-08 14:59:30 routing WARNING [7f2b9d2ca700] [routing:myCluster_rw] reached max active connections (512 max=512)

We have innodb cluster (MySQL 8) and router is connected to it.

When I check show processlist in master node :

| 6176344 | routeruser                  | 192.168.10.6:61195 | my_db | Sleep   |   23946 |                                                          | NULL   
|
| 6176345 | routeruser                  | 192.168.10.6:62671 | my_db | Sleep   |   23946 |                                                                | NULL   
|
| 6176346 | routeruser                  | 192.168.10.6:65531 | my_db | Sleep   |   23946 |                                                                | NULL 
|
| 6176347 | routeruser                  | 192.168.10.6:39541 | my_db | Sleep   |   23946 |                                                                | NULL                          |
| 6176348 | routeruser                  | 192.168.10.6:34074 | my_db | Sleep   |   23946 |                                                                | NULL

I had stopped all custom applications running in K8,but still I got this issue.

In /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
datadir     = /var/lib/mysql
log-error   = /var/log/mysql/error.log
log_timestamps='SYSTEM'
max_connections = 1000

What am I missing here? why router service overflow after working few hours? Any help to solve/further debug this issue is highly appreciate.

Answer 1

Why router service overflow after working few hours?

From what I know, you would modify the max_connection setting at mysqld section in your MySQL configuration file. You could determine where the location of the configuration file is by running command:

mysqld --help --verbose

After modify don't forget about restart your MySQL server.

You have to set $defaultvalue as the your desired value how it writes in MySQL documentations:

Default Value = 512, Minimum Value = 1, Maximum Value = 9223372036854775807

And also look in max_total_connections parameter at the same file.