Debian/mariadb限制？什么都帮不上忙！SQLSTATE[HY000] [1135]无法创建新线程(errno 11“资源暂时不可用”)；

Question

我尝试了两个月来解决这个问题。我已经增加了我所听到的所有限制，但仍然什么也没有。问题是，mariadb (以前在mysql 5.5.x中也是如此)的线程一次限制在4500-4900个线程。当我的软件试图创建更多的连接并使用所有线程时，我会得到以下错误：

SQLSTATEHY000无法创建新线程(errno 11“资源暂时不可用”)；

现在，这里有我目前在服务器上的信任和限制。服务器是Debian 9。

进程号： 见图

Mysql连接： OK可用连接的最高使用率: 59% (4890/8200)

root:~# cat /proc/sys/内核/线程-max

944163

root# cat /proc/$( pgrep -o mysql )/limits

Limit                     Soft Limit           Hard Limit           Units
Max cpu time              unlimited            unlimited            seconds
Max file size             unlimited            unlimited            bytes
Max data size             unlimited            unlimited            bytes
Max stack size            8388608              unlimited            bytes
Max core file size        0                    unlimited            bytes
Max resident set          unlimited            unlimited            bytes
Max processes             472081               472081               processes
Max open files            16364                16364                files
Max locked memory         65536                65536                bytes
Max address space         unlimited            unlimited            bytes
Max file locks            unlimited            unlimited            locks
Max pending signals       472081               472081               signals
Max msgqueue size         819200               819200               bytes
Max nice priority         0                    0
Max realtime priority     0                    0
Max realtime timeout      unlimited            unlimited            us


ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 472081
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65536
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 65536
cpu time               (seconds, -t) unlimited
max user processes              (-u) 472081
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

cat /etc/mysql/maradb.con.d/50-server.cnf\ grep -v "#“

[server]
[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
key_buffer_size     = 1G
max_allowed_packet  = 64M
thread_stack        = 16M
thread_cache_size       = 4096
myisam_recover_options  = BACKUP
max_connections        = 8200
max_connect_errors     = 10000000
query_cache_limit   = 1G
query_cache_size        = 30G
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow-query.log
log_queries_not_using_indexes
table_open_cache = 65536
open_files_limit = 65536
key_buffer_size = 512M
table_cache = 4096
max_heap_table_size = 512M
innodb_buffer_pool_size = 20G
innodb_log_file_size = 2G
innodb_log_buffer_size = 64M
read_rnd_buffer_size=1M
sort_buffer_size=1G
skip_name_resolve
log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size   = 100M
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

[embedded]
[mariadb]
[mariadb-10.1]

cat /etc/安全/限制

root soft  nofile 65536
root hard  nofile 65536
root soft stack 65536
root hard stack 65536

www-data soft nofile 65536
www-data hard nofile 65536

mysql soft nofile 65536
mysql hard nofile 65536
mysql soft nproc 65536
mysql hard nproc 65536
mysql soft stack 65536
mysql hard stack 65536

nobody soft nofile 65536
nobody hard nofile 65536

*          soft     nproc          40960
*          hard     nproc          40960
*          soft     nofile         40960
*          hard     nofile         40960
* soft stack 65536
* hard stack 65536

# End of file

cat /etc/sysctl.conf

# Disable IPv6 autoconf
net.ipv6.conf.all.autoconf = 0
net.ipv6.conf.default.autoconf = 0
net.ipv6.conf.eth2.autoconf = 0
net.ipv6.conf.all.accept_ra_defrtr = 0
net.ipv6.conf.default.accept_ra_defrtr = 0
net.ipv6.conf.eth2.accept_ra_defrtr = 0
net.ipv6.conf.all.accept_ra_pinfo = 0
net.ipv6.conf.default.accept_ra_pinfo = 0
net.ipv6.conf.eth2.accept_ra_pinfo = 0
net.ipv6.conf.all.accept_ra = 0
net.ipv6.conf.default.accept_ra = 0
net.ipv6.conf.eth2.accept_ra = 0


net.core.somaxconn=1024
#kernel.sched_migration_cost_ns = 5000000



# Number of times SYNACKs for passive TCP connection.
net.ipv4.tcp_synack_retries = 2

# Allowed local port range
net.ipv4.ip_local_port_range = 1024 65535

# Protect Against TCP Time-Wait
net.ipv4.tcp_rfc1337 = 1

# Decrease the time default value for tcp_fin_timeout connection
net.ipv4.tcp_fin_timeout = 15

# Decrease the time default value for connections to keep alive
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_probes = 5
net.ipv4.tcp_keepalive_intvl = 15

### TUNING NETWORK PERFORMANCE ###

# Default Socket Receive Buffer
net.core.rmem_default = 31457280

# Maximum Socket Receive Buffer
net.core.rmem_max = 16777216

# Default Socket Send Buffer
net.core.wmem_default = 31457280

# Maximum Socket Send Buffer
net.core.wmem_max = 16777216

# Increase number of incoming connections
net.core.somaxconn = 4096

# Increase number of incoming connections backlog
net.core.netdev_max_backlog = 65536

# Increase the maximum amount of option memory buffers
net.core.optmem_max = 25165824

# Increase the maximum total buffer-space allocatable
# This is measured in units of pages (4096 bytes)
net.ipv4.tcp_mem = 65536 131072 262144
net.ipv4.udp_mem = 65536 131072 262144

# Increase the read-buffer space allocatable
net.ipv4.tcp_rmem = 8192 87380 16777216
net.ipv4.udp_rmem_min = 16384

# Increase the write-buffer-space allocatable
net.ipv4.tcp_wmem = 8192 65536 16777216
net.ipv4.udp_wmem_min = 16384

# Increase the tcp-time-wait buckets pool size to prevent simple DOS attacks
net.ipv4.tcp_max_tw_buckets = 1440000
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1

fs.file-max = 500000
net.ipv4.tcp_max_syn_backlog = 20480

我在这里发现了一些东西：

root@baza:~# systemctl status mysql
● mariadb.service - MariaDB database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2017-10-26 17:02:27 CEST; 1 months 11 days ago
  Process: 12508 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 12284 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
  Process: 12106 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment
_WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 12102 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 12099 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
 Main PID: 12244 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 4915 (limit: 4915)
   CGroup: /system.slice/mariadb.service
           └─12244 /usr/sbin/mysqld

paź 26 17:02:23 baza systemd[1]: Starting MariaDB database server...
paź 26 17:02:24 baza mysqld[12244]: 2017-10-26 17:02:24 140040257753664 [Note] /usr/sbin/mysqld (mysqld 10.1.26-MariaDB-0+deb9u1) starting as process 12244 ...
paź 26 17:02:24 baza mysqld[12244]: 2017-10-26 17:02:24 140040257753664 [Warning] Could not increase number of max_open_files to more than 16364 (request: 16403)
paź 26 17:02:27 baza systemd[1]: Started MariaDB database server.

然后： systemctl显示-all \ grep任务DefaultTasksAccounting=yes DefaultTasksMax=4915

解出

通过在/etc/systemd/system/mysql.service中添加 DefaultTasksMax=infinity到/etc/systemd/system.conf TasksMax=infinity ( 4915个线程的愚蠢默认限制)解决了问题

Answer 1

再次:通过添加以下内容解决问题：( 4915个线程的愚蠢默认限制) DefaultTasksMax=infinity到/etc/systemd/system.conf TasksMax=infinity到/etc/systemd/system/mysql.service

Answer 2

线程不是问题！

什么版本的MySQL？对于几乎所有的版本来说，query_cache_size = 30G的性能都很差。不要对50M过问。

非常糟糕: Load = 11；Connections = 4890/8200。数十个活动连接通常太多-- MySQL本身就会绊倒，当延迟过高时，吞吐量不会下降。

我通常建议在客户端设置节流器，以避免MySQL中的赫德发出雷鸣般的声音。

Threads_running = 64 --这证实了我上面的恐惧。

key_buffer_size = 1G --如果您不使用MyISAM (而且不应该使用)，这是在浪费大量内存。合理的尺寸是40M。

你打开了慢速原木。pk-query-digest (或mysqldumpslow -s t)说最糟糕的几个查询是什么？

内存多少？我看到超过50 do的承诺--你有115 do吗？如果不是，那么IOP正在交换中。这对表演来说是致命的。

请提供所讨论的http://mysql.rjweb.org/doc.php/mysql_analysis#tuning设置和状态。我担心会有更多丑陋的惊喜。

可能还有更多。请按我刚才所说的部分行事，然后我会继续跟进。

Answer 3

它是在2015年引入systemd的，现在的默认上限是512。请找到下面的参考链接，这将有助于确定问题。

https://github.com/systemd/systemd/commit/9ded9cd14

https://www.percona.com/blog/2019/01/02/tasksmax-another-setting-that-can-cause-mysql-error-messages/