Detailed MySQL database resource insufficient error solution

A few days ago, in the management system encountered a strange problem, today has the opportunity to install a good MySQL environment to reproduce this problem, because it is not the most original environment, so may not be able to reproduce completely, I can only try to reproduce the key problem. I think this question is a little special, so I would like to think about the situation at that time.

At work, I executed a su–mysql command, and encountered one of the following errors.

View sourceprint?1 [Root@dbmain ~]# Su-mysql

2 Su:cannot Set user Id:resource temporarily unavailable

This is a shell in the lack of resources caused by the problem, then subconsciously run Ulimit first, look at the basic limitations of ulimit.

View sourceprint?01 [Root@dbmain ~]# 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) 25600

Max locked Memory (Kbytes-L) 32

Max memory Size (Kbytes, M) Unlimited

-Open files (-N) 1024

Pipe Size (bytes, p) 8

One POSIX message queues (bytes, q) 819200

Real-time priority (-R) 0

Stack size (Kbytes,-s) 10240

CPU time (seconds,-t) unlimited

MAX User Processes (-u) 25600

Virtual memory (Kbytes, V) Unlimited

File locks (-X) Unlimited

Look again,/etc/security/limits.conf.

View sourceprint?01 Oracle Soft Nproc 2047

Oracle Hard Nproc 16384

Oracle Soft Nofile 1024

Oracle Hard Nofile 65536

Oracle Soft Memlock 12582912

Oracle Hard Memlock 12582912

07

Grid Soft Nproc 2047

Grid hard Nproc 16384

Ten grid soft Nofile 1024

One grid hard Nofile 65536

Grid Soft Memlock 12582912

Grid hard Memlock 12582912

14

MySQL Soft Nproc 500

MySQL Hard Nproc 500

MySQL Soft nofile 1024

MySQL Hard nofile 65536

MySQL Soft memlock 12582912

MySQL Hard Memlock 12582912

After analysis, suspicion is only process/file the probability of resource tension is relatively large. So first ps-ef look at the number of processes in the system for that user.

View sourceprint?1 [Root@dbmain ~]# ps-ef grep mysql

2 root 4733 1 0 10:30? 00:00:00/bin/sh/usr/bin/mysqld_safe--datadir=/var/lib/mysql--pid-file=/var/lib/mysql/dbmain.pid

3 MySQL 4788 4733 0 10:30? 00:00:04/usr/sbin/mysqld--basedir=/--datadir=/var/lib/mysql--user=mysql--log-error=/var/lib/mysql/dbmain.err-- Pid-file=/var/lib/mysql/dbmain.pid

4 root 15171 17507 0 13:26 pts/2 00:00:00 mysql-uroot-p

5 root 20792 17163 0 15:30 pts/1 00:00:00 grep mysql

From this output, we temporarily exclude the possibility of nproc exceeding the standard.

Thus, the PID of this process enters its proc directory to see the number of files currently open.

A file connection with a large number of sockets was found. However, the number is far from the limit of the number of files, which is suspected that the MySQL thread will also consume the Nproc cardinality of the Linux system, so try to adjust the value of the/etc/security/limits.conf file Nproc parameters.

After the adjustment was found, Su–mysql did succeed, and then changed the parameter back to Su–mysql again, and the problem recurs again. From this confirmation, the use of MySQL system, in the setting of the MySQL parameter max_connections, you also need to consider setting the size of the/etc/security/limits.conf file, MySQL is thread-mode execution, The number of threads will also be counted in the Nproc, which may obscure or cause a miscarriage of the problem.