MySQL服务器因进程资源设定无法操作的问题
2018-05-16 12:08:36 
阿炯
具体表现：多个实例不能启动，通过mysqladmin或mysql指令不能连接进行操作，不能由root用户切换到mysql用户下。su: cannot set user id: Resource temporarily unavailable
fork: retry: Resource temporarily unavailable
# w
-bash: fork: Cannot allocate memory
# free -m
-bash: fork: Cannot allocate memory
mysqladmin: connect to server at 'localhost' failed
error: 'Can't create a new thread (errno 11 "Resource temporarily unavailable"); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug'
ERROR 1135 (HY000): Can't create a new thread (errno 11 "Resource temporarily unavailable"); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
Host '192.168.40.31' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
报出这个错误和mysql本身没关系，是操作系统限定的连接数太小。(比如centos 6 默认的 max user process只有 1024个。当mysql process大于这个值时 就会出现Can't create a new thread的问题)。先查询连接数超限的办法：执行'ulimit -a'，查看max user processes 这一项：
要是这个值比较的小的话，而mysql中process的数目超过这个数的时候就会报出上页相应的错误。能够控制用户资源的文件一般有两个，一是/etc/profile,二是/etc/security/limits.conf。
先来了解一下这两个文件：
1./etc/profile是环境变量配置文件，可能也有ulimit配置，在这里并没有做特别的limit限制。
2./etc/security/limits.conf
Check /etc/security/limits.conf and all files in /etc/security/limits.d/ for the current setting of the nproc value.Extend the nproc value. This can be done in /etc/security/limits.conf or in any file in a file in /etc/security/limits.d/.
该文件内容格式如下：
domain:
是指限制的对象，可以是个人，也可以是组，组前面要加@符号，也可以设置为除root用户外的 任何人，用*号表示；
type:
是指类型，soft是当前系统生效的值，hard是系统可以设置的最大值；
item:
项目，是可以对什么项目做限制，如最大进程数，文件最大值；
value:
值，所设置的值的大小。
nproc - max number of processes：可打开的最大进程数
nofile - max number of open file descriptors：最大文件打开数(描述符)
/etc/security/limits.conf
#Each line describes a limit for a user in the form:
#
#<domain> <type> <item> <value>
#
#Where:
#<domain> can be:
# - a user name
# - a group name, with @group syntax
# - the wildcard *, for default entry
# - the wildcard %, can be also used with %group syntax,
# for maxlogin limit
#
#<type> can have the two values:
# - "soft" for enforcing the soft limits
# - "hard" for enforcing hard limits
#
#<item> can be one of the following:
# - core - limits the core file size (KB)
# - data - max data size (KB)
# - fsize - maximum filesize (KB)
# - memlock - max locked-in-memory address space (KB)
# - nofile - max number of open file descriptors
# - rss - max resident set size (KB)
# - stack - max stack size (KB)
# - cpu - max CPU time (MIN)
# - nproc - max number of processes
# - as - address space limit (KB)
# - maxlogins - max number of logins for this user
# - maxsyslogins - max number of logins on the system
# - priority - the priority to run user process with
# - locks - max number of file locks the user can hold
# - sigpending - max number of pending signals
# - msgqueue - max memory used by POSIX message queues (bytes)
# - nice - max nice priority allowed to raise to values: [-20, 19]
# - rtprio - max realtime priority
疑惑的是，在/etc/security/limits.conf配置文件中可以看到，进程数和打开文件数的最大值，已经开得够大了，可是为什么还会出现这种现象呢？
经过查找，发现Centos6.x版本后，还有一个配置文件对ulimit设置生效，就是/etc/security/limits.d/90-nproc.conf，打开文件后发现，默认最大进程数只有1024，明显是不能满足我搭建应用环境的需求的。于是把1024改成10240后，再执行'su - freeoa'就成功了。同时可以看到对root是unlimited的。
In Red Hat Enterprise Linux 6, there's a default setting in /etc/security/limits.d/90-nproc.conf. Change the default limit for all users or add a new limit for the affected user.
freeoa soft nproc 10240 <== add
如果是多用途机器，只针对mysql进行设置
mysql soft nproc 40960
mysql hard nproc 65535
解决这个问题的方法有两个：
1.把无用的连接线程杀掉。
2.把最大进程限制数调大。
另外可以使用ulimit命令来临时设置各种资源限制，并且通过 ulimit -a 命令可以查看当前用户的各种限制值的大小。即时生效：
ulimit -u 65535
修改后最好在同一终端中将应用重启一下。当然进(线)程数量也不能无限的加，可修改MySQL系统内的超时参数来控制连接数量 。
修改后的验证及发现该问题：
Check nproc and nofile in /etc/security/limits.conf for the user.
Log in as this user and run ulimit -u:
$ ulimit -u
120831
# cat /proc/sys/kernel/threads-max
241663
The number of user processes(threads)
# ps -u freeoa -L | wc -l
对于像mysql这种基于线程的用ps指令来查看：
THREAD DISPLAY
H Show threads as if they were processes
-L Show threads, possibly with LWP and NLWP columns
-T Show threads, possibly with SPID column
m Show threads after processes
-m Show threads after processes
Look at /var/log/secure:
su: pam_keyinit(su-l:session): Unable to change UID to 24070 temporarily
Look at strace. Run strace -ttTvfo /tmp/su.strace su - freeoa:
3318 10:35:59.758440 setgid(24075) = 0 <0.000005>
3318 10:35:59.758471 setuid(24074) = -1 EAGAIN (Resource temporarily unavailable) <0.000018>