Failure Analysis | MySQL Router: The server backend is so busy, why not allow access?

Author: Yang Jining

A member of the DBA team of Akerson, mainly responsible for the daily maintenance, fault handling and performance optimization of MySQL. Persistence in technology, responsible for customers.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

1. Background Description

The development feedback even the router reports an error: 'ERROR 1040 (HY000): Too many connections to MySQL Router',

However, there are only a few connections on the back-end mysqlserver. After investigation, it is found that when the connection time exceeds the value set by wait_timeout, the TCP connection of the 8.0.29 version router will not be released, and the 8.0.30 version of the router still has this problem, so it is handled by downgrading.

2. Problem recurrence

mysqlserver version

 mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.01 sec)

router version

 shell> ./mysqlrouter -V
MySQL Router  Ver 8.0.30 for Linux on x86_64 (MySQL Community - GPL)

The router port is set to 8030 and the maximum number of connections max_connections = 3 is set to facilitate reproduction

 shell> egrep 'max|port' mysqlrouter.conf 
max_connections = 3
bind_port = 8030

Open 3 sessions

 mysql> show processlist;
+-----+-----------------+---------------------+------+---------+--------+------------------------+------------------+
| Id  | User            | Host                | db   | Command | Time   | State                  | Info             |
+-----+-----------------+---------------------+------+---------+--------+------------------------+------------------+
|   5 | event_scheduler | localhost           | NULL | Daemon  | 427243 | Waiting on empty queue | NULL             |
| 146 | haha            | 10.186.65.132:33324 | NULL | Sleep   |    184 |                        | NULL             |
| 147 | haha            | 10.186.65.132:33326 | NULL | Sleep   |     14 |                        | NULL             |
| 148 | haha            | 10.186.65.132:33328 | NULL | Sleep   |      9 |                        | NULL             |
| 149 | root            | localhost           | NULL | Query   |      0 | init                   | show processlist |
+-----+-----------------+---------------------+------+---------+--------+------------------------+------------------+
5 rows in set (0.00 sec)

All sessions set wait_timeout = 10, timeout to disconnect

 MySQL [(none)]> show databases; set wait_timeout=10;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]>

View the session (the client has disconnected, but doing nothing will hold the router's connection)

 mysql> show processlist;
+-----+-----------------+-----------+------+---------+--------+------------------------+------------------+
| Id  | User            | Host      | db   | Command | Time   | State                  | Info             |
+-----+-----------------+-----------+------+---------+--------+------------------------+------------------+
|   5 | event_scheduler | localhost | NULL | Daemon  | 427509 | Waiting on empty queue | NULL             |
| 157 | root            | localhost | NULL | Query   |      0 | init                   | show processlist |
+-----+-----------------+-----------+------+---------+--------+------------------------+------------------+
2 rows in set (0.00 sec)

new connection error

 shell> mysql -uhaha -p1 -h10.186.65.132 -P8030
ERROR 1040 (HY000): Too many connections to MySQL Router

Check the connection between router and mysqlclient, but it is not released

 [root@router ~]# netstat  -lntpa |grep 8030
tcp        0      0 0.0.0.0:8030            0.0.0.0:*               LISTEN      15045/./mysqlrouter 
tcp        0      0 10.186.65.132:8030      10.186.65.137:56308     ESTABLISHED 15045/./mysqlrouter 
tcp        0      0 10.186.65.132:8030      10.186.65.137:56312     ESTABLISHED 15045/./mysqlrouter 
tcp        0      0 10.186.65.132:8030      10.186.65.137:56310     ESTABLISHED 15045/./mysqlrouter 


[root@mysql ~]# netstat  -lntpa |grep 8030
tcp        0      0 10.186.65.137:56308     10.186.65.132:8030      ESTABLISHED 3992/mysql          
tcp        0      0 10.186.65.137:56310     10.186.65.132:8030      ESTABLISHED 3990/mysql          
tcp        0      0 10.186.65.137:56312     10.186.65.132:8030      ESTABLISHED 3935/mysql

3. Packet capture analysis

Let's see if there is any abnormality in the package on the mysqlserver side

three-way handshake

Timeout to disconnect

I waved my hands four times and found that I didn't receive a signal from the router to say goodbye

Looking at the packet capture on the router side, you can see that there is no goodbye.

Did not receive the signal from the router to say goodbye, the connection of the router should be in the state of close_wait

Then the mysqlserver side has not received the [FIN, ACK] reply from the router, can it only be in the state of FIN_WAIT2 all the time? TCP has no handling of this state, but Linux can adjust the tcp_fin_timeout parameter to set a timeout.

So what about the connection between router and mysql_client? It can be seen that none of the parties send a disconnected request

Therefore, all sessions on the mysqlserver side have been disconnected, but the router and mysqlclient have not been disconnected, resulting in the new connection being unable to connect to the mysqlserver on the backend through the router.

The 8.0.28 version of the router does not have the above problems. You can see that when the mysqlserver sends a disconnection request, the router replies with [FIN, ACK]. Take a look at the packet capture on the router.

As for why only 3 packets are caught after four waves, it is because the 2nd and 3rd packets are combined and sent together. For details, please refer to Section 3.5 of RFC793.

Let's take a look at the packet capture on the mysqlserver side

Why did mysqlserver capture 4 packets? Don't be fooled, the 643's package is actually the 641's return package

At this point, the connection from router to mysqlserver can be closed normally.

Let's take a look at the connection between the router and mysqlclient in version 8.0.28, the packet capture on the mysqlclient side (test port: 6446)

It can be seen that the router actively sends the disconnected request.

4. Summary

The test found that the 8.0.29 and 8.0.30 versions of the router will have this problem.

The 8.0.28 and 8.0.21 versions of the router have been tested and there is no such problem, and other versions can be tested if they are interested.

It may be related to the new feature 'Added connection pooling functionality to reuse server-side connections' of router 8.0.29.

Finally, due to limited personal ability, if there are any mistakes or omissions, please correct me.