关于mysql:故障分析-填坑-TaskMax

98次阅读

共计 5232 个字符，预计需要花费 14 分钟才能阅读完成。

作者：周启超

爱可生北分团队 DBA，次要负责项目前期建设及前期疑难问题反对。做事认真，对事负责。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

先介绍下背景，利用连贯数据数执行工作, 报 error 1135: Can’t create a new thread (errno 11) 谬误日志信息如下：

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)

环境版本为 SLES12SP5，MySQL 版本为 8.0.18。

看到“Can’t create thread to handle new connection”的形容咱们首先会想到操作系统对应用户的 ulimit 是否正确。和用户确认了下 ulimit 确实是失常配置的。如果侥幸的话，咱们能够在网上查到一些 TaskMax 设置导致 MySQL 连贯异样的文章。

这次这个环境问题确实和 TaskMax 无关，察看如下截图能够看到 Tasks 达到了 limit 限度。

找到问题所在进行修复。网上有材料说 TaskMax 有两种形式进行修复，一种零碎级别和一种过程级别，“零碎级别不必进行 MySQL 服务重启，过程级别批改须要进行 MySQL 服务重启”。因为是生产环境，而且是专用服务器，想抉择对现有服务影响最小的准则，抉择了零碎级别调整。

# 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.

看 systemctl status mysql 输入 Tasks 一项 limit 确实更新成最新的值了。看到状态值更新了，但也不要漫不经心。

测试环境 sysbench 模仿一下并发，发现问题仍然在：

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

重启 MySQL 服务后，状态失常：

[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

MySQL status 里 Task 状态能够冲破原来 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.

过程级别调整形式验证：

# 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.

重启 MySQL 服务失效（Tasks 中 limit 限度未失效，景象同零碎级别调整）：

# 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.

确认零碎级别参数没有更改：

# systemctl show --property=DefaultTasksMax
DefaultTasksMax=512

跑 600 并发 sysbench，验证后果：

[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

冲破原来了原来 Task 的 512 限度：

# 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.

验证论断：零碎级别和过程级别设置形式 2 选 1 即可，然而都须要重启 MySQL 服务失效。

正文完
mysql
发表至： mysql
2021-08-23
 0
关于mysql:问题定位-Peronca-Xtrabackup-80近日踩坑总结-xtrabackup-24和80区别
关于mysql:mysql数据库-sqlmodeonlyfullgroupby-模式引发的惨案
关于mysql:新特性解读-MySQL-80-新密码策略中
关于mysql:GreatSQL季报20211226