) An Exception error related to MySQL

A few days ago, I encountered a strange problem in the management system. Today I have the opportunity to install the MySQL environment to reproduce this problem. Because it is not the most primitive environment, it may not be completely reproduced, I can only try to reproduce the key issues .. I think this problem is a bit special, so I would like to look back at the situation here ..

At work, I executed a su-mysql command and encountered the following error ..

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

2 su: cannot set user id: Resource temporarily unavailable

This is a problem caused by insufficient resources in Shell. At that time, we subconsciously ran ulimit first to see the basic limits of ulimit.

View sourceprint? 01 [root @ dbmain ~] # Ulimit-

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 file (-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

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 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, it is suspected that only the process and file have a high probability of resource shortage. Therefore, ps-ef is used to check the number of processes of the user in the system ..

View sourceprint? 1 [root @ dbmain ~] # Ps-ef | grep mysql

2 root 4733 1 0 :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? 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 00:00:00 pts/2 mysql-uroot-p

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

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

Then, enter the proc directory based on the pid of the process to view the number of currently opened files ..

A large number of socket file connections are found .. however, the number of files is far from the limit, and it is suspected that the MySQL thread may also consume the nproc base of the Linux system, so try to adjust/etc/security/limits. the value of the nproc parameter in the conf file.

After the adjustment is found, su-mysql can indeed be successfully executed, and this parameter will be changed back later, and then re-executed su-mysql. This problem is reproduced again .. it is confirmed that, in addition to setting the MySQL parameter max_connections, you must also consider setting/etc/security/limits. the size of the conf file. MySQL is executed in thread mode, and the number of threads is also counted in nproc. This may mask or cause a false judgment on this issue ..