Exception error due to insufficient MySQL database resources

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 ..

 
 

  
  
[root@dbmain ~]# su - mysql   
  
  
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.

 
 

  
  
[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            (512 bytes, -p) 8   
  
  
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 
 
 

Again,/etc/security/limits. conf

 
 

  
  
oracle              soft    nproc   2047   
  
  
oracle              hard    nproc   16384   
  
  
oracle              soft    nofile  1024   
  
  
oracle              hard    nofile  65536   
  
  
oracle              soft    memlock        12582912   
  
  
oracle              hard   memlock        12582912   
  
  
grid              soft    nproc   2047   
  
  
grid              hard    nproc   16384   
  
  
grid              soft    nofile  1024   
  
  
grid              hard    nofile  65536   
  
  
grid              soft    memlock        12582912   
  
  
grid              hard   memlock        12582912   
  
  
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, 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 ..

 
 

  
  
[root@dbmain ~]# ps -ef | grep mysql   
  
  
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   
  
  
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   
  
  
root     15171 17507  0 13:26 pts/2    00:00:00 mysql -uroot -p   
  
  
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.

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 ..

Edit recommendations]