Failure Analysis | TaskMax

Author: Zhou Qichao

The DBA of Aikesheng North Branch is mainly responsible for the preliminary construction of the project and the support of difficult problems in the later stage. Be serious and responsible for things.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

First introduce the background, apply the connection data to execute the task, and report error 1135: Can't create a new thread (errno 11). The error log information is as follows:

2021-08-11T12:25:40.606774+08:00 0 [ERROR] [MY-000000] [connection_h] Error log throttle:         36 'Can't create thread to handle new connection' 
error(s) suppressed
2021-08-11T12:25:40.606886+08:00 0 [ERROR] [MY-010249] [Server] Can't create thread to handle new connection(errno= 11)

The environment version is SLES12SP5, and the MySQL version is 8.0.18.

Seeing the description of "Can't create thread to handle new connection", we first think about whether the ulimit of the operating system corresponding to the user is correct. I confirmed with the user that ulimit is indeed configured normally. If you are lucky, we can find some articles on the Internet about TaskMax settings causing abnormal MySQL connection.

This time, this environmental problem is indeed related to TaskMax. Observe the following screenshot to see that Tasks has reached the limit limit.

Find the problem and fix it. According to information on the Internet, there are two ways to repair TaskMax, one at the system level and one at the process level. "The system level does not need to restart the MySQL service, and the process level modification requires the restart of the MySQL service." Since it is a production environment and a dedicated server, I want to choose the principle that has the least impact on the existing services, so I chose the system level adjustment.

# systemctl show --property=DefaultTasksMax
DefaultTasksMax=512
# vi /etc/systemd/system.conf
#DefaultTasksMax=512
DefaultTasksMax=5120
# systemctl daemon-reexec
# systemctl show --property=DefaultTasksMax
DefaultTasksMax=5120
# systemctl status mysql
[0m mysql.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2021-08-11 12:09:49 CST; 23min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 2585 (mysqld)
   Status: "Server is operational"
    Tasks: 62 (limit: 5120)
   CGroup: /system.slice/mysql.service
           85 /usr/sbin/mysqld
Aug 11 12:09:48 linux systemd[1]: Starting MySQL Server...
Aug 11 12:09:49 linux systemd[1]: Started MySQL Server.

Look at the systemctl status mysql output Tasks. The limit is indeed updated to the latest value. See that the status value is updated, but don't take it lightly.

The test environment sysbench simulates concurrency and found that the problem still exists:

FATAL: unable to connect to MySQL server on host '10.186.63.149', port 3306, aborting...
FATAL: error 1135: Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
FATAL: `thread_init' function failed: /usr/share/sysbench/oltp_common.lua:349: connection creation failed

After restarting the MySQL service, the status is normal:

[ 3s ] thds: 600 tps: 563.43 qps: 9803.74 (r/w/o: 8587.12/0.00/1216.62) lat (ms,95%): 1427.08 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 600 tps: 3031.18 qps: 48139.37 (r/w/o: 42087.54/0.00/6051.82) lat (ms,95%): 831.46 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 600 tps: 1593.91 qps: 25684.59 (r/w/o: 22491.10/0.00/3193.49) lat (ms,95%): 694.45 err/s: 0.00 reconn/s: 0.00

Task status in MySQL status can break through the original limit of 512:

# systemctl status mysql
[0m mysql.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2021-08-11 12:35:12 CST; 3min 30s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 5839 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 5861 (mysqld)
   Status: "Server is operational"
    Tasks: 649 (limit: 5120)
   CGroup: /system.slice/mysql.service
           61 /usr/sbin/mysqld
Aug 11 12:35:09 linux systemd[1]: Starting MySQL Server...
Aug 11 12:35:12 linux systemd[1]: Started MySQL Server.

Process level adjustment method verification:

# cd /etc/systemd/system/
# mkdir mysql.service.d
# cd mysql.service.d/
#  vi override.conf
[Service]
TasksMax=10000
# systemctl daemon-reload
# systemctl status mysql
[0m mysql.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mysql.service.d
           erride.conf
   Active: active (running) since Wed 2021-08-11 12:44:27 CST; 1min 51s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 8229 (mysqld)
   Status: "Server is operational"
    Tasks: 108 (limit: 10000)
   CGroup: /system.slice/mysql.service
           29 /usr/sbin/mysqld
Aug 11 12:44:25 linux systemd[1]: Starting MySQL Server...
Aug 11 12:44:27 linux systemd[1]: Started MySQL Server.

Restart the MySQL service to take effect (the limit in Tasks does not take effect, the phenomenon is the same as the system level adjustment):

# systemctl stop mysql
# systemctl start mysql
# systemctl status mysql
[0m mysql.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mysql.service.d
           erride.conf
   Active: active (running) since Wed 2021-08-11 12:49:56 CST; 13s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 11971 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 11993 (mysqld)
   Status: "Server is operational"
    Tasks: 50 (limit: 10000)
   CGroup: /system.slice/mysql.service
           993 /usr/sbin/mysqld
Aug 11 12:49:54 linux systemd[1]: Starting MySQL Server...
Aug 11 12:49:56 linux systemd[1]: Started MySQL Server.

Confirm that the system level parameters have not changed:

# systemctl show --property=DefaultTasksMax
DefaultTasksMax=512

Run 600 concurrent sysbench, verify the results:

[ 3s ] thds: 600 tps: 185.49 qps: 3283.97 (r/w/o: 2868.95/0.00/415.02) lat (ms,95%): 1589.90 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 600 tps: 9545.12 qps: 152777.72 (r/w/o: 133709.19/0.00/19068.53) lat (ms,95%): 1327.91 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 600 tps: 1495.99 qps: 24004.40 (r/w/o: 21029.73/0.00/2974.66) lat (ms,95%): 694.45 err/s: 0.00 reconn/s: 0.00

Break through the original 512 limit of the original Task:

# systemctl status mysql
[0m mysql.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mysql.service.d
           erride.conf
   Active: active (running) since Wed 2021-08-11 12:49:56 CST; 1min 8s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 11971 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 11993 (mysqld)
   Status: "Server is operational"
    Tasks: 649 (limit: 10000)
   CGroup: /system.slice/mysql.service
           993 /usr/sbin/mysqld
Aug 11 12:49:54 linux systemd[1]: Starting MySQL Server...
Aug 11 12:49:56 linux systemd[1]: Started MySQL Server.

Verification conclusion: You can choose 1 for the system level and process level setting methods, but you need to restart the MySQL service to take effect.