Detailed MySQL database resources inadequate solution to the problem

A few days, in the management of the system encountered a strange problem, today have the opportunity to install the MySQL environment to reproduce the problem, because it is not the most primitive environment, so may not be able to fully reproduce, I can only try to reproduce the key The problem is .. I think this issue is a bit special, so I will recall the scenario at that time.
At work, I ran a su & ndash; mysql command and encountered such an error as below.

view sourceprint? 1 [root @ dbmain ~] # su - mysql

2 su: can not set user id: Resource temporarily unavailable

This is a problem caused by the lack of resources in the Shell, then unconsciously run ulimit, take a look at the basic restrictions ulimit.

view sourceprint? 01 [root @ dbmain ~] # ulimit -a

02 core file size (blocks, -c) 0

03 data seg size (kbytes, -d) unlimited

04 scheduling priority (-e) 0

05 file size (blocks, -f) unlimited

06 pending signals (-i) 25600

07 max locked memory (kbytes, -l) 32

08 max memory size (kbytes, -m) unlimited

09 open files (-n) 1024

10 pipe size (512 bytes, -p) 8

11 POSIX message queues (bytes, -q) 819200

12 real-time priority (-r) 0

13 stack size (kbytes, -s) 10240

14 cpu time (seconds, -t) unlimited

15 max user processes (-u) 25600

16 virtual memory (kbytes, -v) unlimited

17 file locks (-x) unlimited

Looked again, / etc / security / limits.conf

view sourceprint? 01 Oracle soft nproc 2047

02 oracle hard nproc 16384

03 oracle soft nofile 1024

04 oracle hard nofile 65536

05 oracle soft memlock 12582912

06 oracle hard memlock 12582912

07

08 grid soft nproc 2047

09 grid hard nproc 16384

10 grid soft nofile 1024

11 grid hard nofile 65536

12 grid soft memlock 12582912

13 grid hard memlock 12582912

14

15 mysql soft nproc 500

16 mysql hard nproc 500

17 mysql soft nofile 1024

18 mysql hard nofile 65536

19 mysql soft memlock 12582912

20 mysql hard memlock 12582912

After analysis, there is only suspicion that the probability of two resource / resource intensive processes is high. Therefore, ps -ef first looks at the number of processes the user has in the system.

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 rule out the possibility of exceeding nproc.

Thus, according to this process pid into its proc directory to view the current number of open files.

Found a large number of socket file connections .. But the number is far below the limit of the number of files, which may be suspected MySQL threads will consume Linux system nproc base, so try to adjust /etc/security/limits.conf The nproc parameter value of the file.

Found that after the adjustment, su - mysql can indeed be successfully executed, followed by this parameter again back to the implementation of su - mysql, the problem again to reproduce .. Thus confirm that the use of MySQL system, set the MySQL The max_connections parameter, you also need to consider setting the size of the /etc/security/limits.conf file. MySQL is executing in threaded mode and its number of threads is also counted in nproc, which may mask or cause errors Judgment