Known as one of the most popular open source database, MySQL is widely used in various scenarios, Alibaba Cloud provides high available ApsaraDB RDS for MySQL with enhanced MySQL service reduced enterprise’s database expenses, and helped enterprises utilize technology to fight against novel coronavirus.
Today's testers report that a test environment, the mysql server, is very slow to query and sometimes cannot log in. It is found that the CPU is heavily occupied in the top, the system memory is still large, the jave program runs very slowly, and the server is logged in.
show full processlist swiped a lot of processes, and a large number of unauthenticated user status
The connections are as follows:
6465371 | unauthenticated user | connecting host | NULL | Connect | NULL | login | NULL | 0 | 0 |
6465372 | unauthenticated user | connecting host | NULL | Connect | NULL | login | NULL | 0 | 0 |
6465373 | unauthenticated user | connecting host | NULL | Connect | NULL | login | NULL | 0 | 0 |
6465374 | unauthenticated user | connecting host | NULL | Connect | NULL | login | NULL | 0 | 0 |
6465375 | unauthenticated user | connecting host | NULL | Connect | NULL | login | NULL | 0 | 0 |
6465376 | unauthenticated user | connecting host | NULL | Connect | NULL | login | NULL | 0 | 0 |
6465377 | unauthenticated user | connecting host | NULL | Connect | NULL | login | NULL | 0 | 0 |
6465378 | unauthenticated user | connecting host | NULL | Connect | NULL | login | NULL | 0 | 0 |
See the explanation in the manual:
unauthenticated user refers to a thread that has become associated with a client connection but for which authentication of the client user has not yet been done.
That is: there is a thread processing the client's connection, but the client has not yet passed user authentication.
The reasons may be:
1. The server is doing DNS response analysis. There are two solutions:
a. Add client IP in hosts, such as
192.168.100.18 web-jave
b. A skip-name-resolve is added to the MySQL startup parameters, that is, DNS response resolution is not enabled.
Add skip-name-resolve in the configuration file, and then restart the service to take effect;
2. The server's threads are still queued, so you can increase the back_log
back_log is a read-only parameter and requires restarting the mysql service
back_log defines how many requests can be stored on the stack for a short time before MySQL temporarily stops answering requests.
If you expect many connections in end time, you need to increase it
Modify the value of back_log in the configuration file;
In other words, this value defines the size of the TCP / IP connection listening queue
The operating system has its own limit on the size of this queue, and the view setting back_log above the operating system limit will be invalid.
The setting of back_log in mysql depends on the operating system,
The value of this parameter cannot be greater than the value of the system parameter tcp_max_syn_backlog under Linux.
You can view the current value with the following command: cat / proc / sys / net / ipv4 / tcp_max_syn_backlog
Modify it with the following command sysctl -w net.ipv4.tcp_max_syn_backlog = n
3. The program has bugs or abnormal connections are closed or hacked, resulting in a sudden increase in the number of connections. Analysis of the reason: According to the above points, the reasons are excluded. 1. The lock is a connection problem of the jave program. It is seen that there is this 6473469 root 192.168.10.155:47401 xxx_doc Query 0 executing / * mysql-connector-java-5.1.35 (Revision: 5fb9c5849535c13917c2cf9baaece6ef9693ef27) * / SHOW VARIABLES WHERE Variable_name = 'language' OR Variable_na me = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'characterer character_set 'OR Variable_na me =' character_set_server 'OR Variable_name =' tx_isolation 'OR Variable_name =' transaction_isolation 'OR Variable_name =' character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_t ime_zone' OR Variable_name lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable _name = ‘net_buffer_length’ OR Variable_name = ‘sql_mode’ OR Variable_name = ‘query_cache_type’ OR Variable_name = ‘query_cache_size‘ OR Variable_name = ‘license’ OR Variable_name = ‘init_connect’ View the number of process connections:
[email protected] (none) 18:05> show status like 'Thread%'; + ------------------------- + ---- --- + | Variable_name | Value | + ------------------------- + ------- + | Threadpool_idle_threads | 10 | | Threadpool_threads | 15 | | Threads_cached | 0 | | Threads_connected | 998 | ==== has been used up, (the remaining 2 are reserved) | Threads_created | 16944 | | Threads_running | 5 | + ------- ------------------ + ------- + [email protected] (none) 18:05> show variables like '% connect%'; +- --------------------------------------------- + ---- ------------- + | Variable_name | Value | + ------------------------------ ----------------- + ----------------- + | character_set_connection | utf8 | | collation_connection | utf8_general_ci | | connect_timeout | 20 | | disconnect_on_expired_password | ON | | extra_max_connections | 1 | | init_connect | | | max_connect_errors | 10000 | | max_connections | 1000 | -------------------------------------- + ----------- ------ + 10 rows in set (0.00 sec
View the error log:
[Warning] Too many connections
Called to check the program for abnormalities, and found that someone was testing the jdbc connection was not configured properly, resulting
mysql execute show processlist appears a lot of "unauthenticated user" solution
