详解MySQL数据库资源不足的异常错误_MySQL

前几天，在管理系统的时候遇到一个奇怪的问题, 今天才有机会安装好MySQL环境来重现此问题,由于不是最原始的环境, 所以未必能够完全重现, 我只能努力重现关键问题了.. 我觉得此问题有点特别, 故在此大概的回想下当时的情景..

工作时, 执行了一个su – mysql 的命令, 遇到了下面这样一个错误..

<ol class="dp-xml">
<li class="alt"><span><span>[root@dbmain ~]# su - mysql   </span></span></li>
<li><span>su: cannot set user id: Resource temporarily unavailable  </span></li>
</ol>

这是一个Shell中由于资源不足引起的问题, 当时下意识的先运行ulimit,看看ulimit的基本限制.

<ol class="dp-xml">
<li class="alt"><span><span>[root@dbmain ~]# ulimit -a   </span></span></li>
<li><span>core file size          (blocks, -c) 0   </span></li>
<li class="alt"><span>data seg size           (kbytes, -d) unlimited   </span></li>
<li><span>scheduling priority             (-e) 0   </span></li>
<li class="alt"><span>file size               (blocks, -f) unlimited   </span></li>
<li><span>pending signals                 (-i) 25600   </span></li>
<li class="alt"><span>max locked memory       (kbytes, -l) 32   </span></li>
<li><span>max memory size         (kbytes, -m) unlimited   </span></li>
<li class="alt"><span>open files                      (-n) 1024   </span></li>
<li><span>pipe size            (512 bytes, -p) 8   </span></li>
<li class="alt"><span>POSIX message queues     (bytes, -q) 819200   </span></li>
<li><span>real-time priority              (-r) 0   </span></li>
<li class="alt"><span>stack size              (kbytes, -s) 10240   </span></li>
<li><span>cpu time               (seconds, -t) unlimited   </span></li>
<li class="alt"><span>max user processes              (-u) 25600   </span></li>
<li><span>virtual memory          (kbytes, -v) unlimited   </span></li>
<li class="alt"><span>file locks                      (-x) unlimited </span></li>
</ol>

又看了看,/etc/security/limits.conf

<ol class="dp-xml">
<li class="alt"><span><span>oracle              soft    nproc   2047   </span></span></li>
<li><span>oracle              hard    nproc   16384   </span></li>
<li class="alt"><span>oracle              soft    nofile  1024   </span></li>
<li><span>oracle              hard    nofile  65536   </span></li>
<li class="alt"><span>oracle              soft    memlock        12582912   </span></li>
<li><span>oracle              hard   memlock        12582912   </span></li>
<li class="alt"><span>grid              soft    nproc   2047   </span></li>
<li><span>grid              hard    nproc   16384   </span></li>
<li class="alt"><span>grid              soft    nofile  1024   </span></li>
<li><span>grid              hard    nofile  65536   </span></li>
<li class="alt"><span>grid              soft    memlock        12582912   </span></li>
<li><span>grid              hard   memlock        12582912   </span></li>
<li class="alt"><span>mysql             soft    nproc  500   </span></li>
<li><span>mysql             hard    nproc  500   </span></li>
<li class="alt"><span>mysql             soft    nofile  1024   </span></li>
<li><span>mysql             hard    nofile  65536   </span></li>
<li class="alt"><span>mysql             soft    memlock  12582912   </span></li>
<li><span>mysql             hard    memlock  12582912 </span></li>
</ol>

经过分析,怀疑也只有process/file这两个出现资源紧张的概率比较大.. 因此就先ps -ef 看系统中该用户的进程数量..

<ol class="dp-xml">
<li class="alt"><span><span>[root@dbmain ~]# ps -ef | grep mysql   </span></span></li>
<li>
<span>root      4733     1  0 10:30 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe </span><span class="attribute"><font color="#ff0000">--datadir</font></span><span>=/var/lib/mysql </span><span class="attribute"><font color="#ff0000">--pid-file</font></span><span>=/var/lib/mysql/dbmain.pid   </span>
</li>
<li class="alt">
<span>mysql     4788  4733  0 10:30 ?        00:00:04 /usr/sbin/mysqld </span><span class="attribute"><font color="#ff0000">--basedir</font></span><span>=/ </span><span class="attribute"><font color="#ff0000">--datadir</font></span><span>=/var/lib/mysql </span><span class="attribute"><font color="#ff0000">--user</font></span><span>=</span><span class="attribute-value"><font color="#0000ff">mysql</font></span><span> </span><span class="attribute"><font color="#ff0000">--log-error</font></span><span>=/var/lib/mysql/dbmain.err </span><span class="attribute"><font color="#ff0000">--pid-file</font></span><span>=/var/lib/mysql/dbmain.pid   </span>
</li>
<li><span>root     15171 17507  0 13:26 pts/2    00:00:00 mysql -uroot -p   </span></li>
<li class="alt"><span>root     20792 17163  0 15:30 pts/1    00:00:00 grep mysql </span></li>
</ol>

从这个输出,,我们暂时排除nproc超标的可能性.

由此, 就根据此进程的pid进入其proc目录查看当前打开的文件数量..

发现有大量socket的文件连接.. 但是其数量远远未达到文件数的限制, 由此怀疑可能是MySQL的线程也会消耗掉Linux系统的nproc基数, 因此尝试调整/etc/security/limits.conf文件的nproc参数的值.

发现调整过后, su – mysql 确实可以成功执行了,,后面又将此参数改回, 重新执行su – mysql,,此问题又再次重现..由此确认,,使用MySQL的系统, 在设置MySQL的参数max_connections之外, 还需要考虑设置/etc/security/limits.conf文件的大小, MySQL是线程模式执行的, 其线程数也会被统计在nproc中, 这可能掩盖或造成对此问题的误判..