Il limite di Default è impostato a 214 connections.
mysql show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 214 | +-----------------+-------+ 1 row in set (0.00 sec)
Il numero massimo di file aperti consentiti è troppo piccolo, per impostazione predefinita 1024, per questo occorre aumentare la max_connections ad un numero maggiore di 214. Ci sono molte guide online che spiegano come gestire questo problema con differenti metodi:
- aumentando l’impostazione fs.file-max del kernel, modificando /etc/sysctl.conf come in questo esempio raddoppiando l’impostazione predefinita di fs.file-max:
fs.file-max = 2459688
Fatto questo, eseguire sysctl -p per rendere immediate le modifiche. (Rimarranno fino al prossimo reboot)
- modificando opportunatamente il file /etc/security/limits.conf:
mysql soft nofile 4096 mysql hard nofile 8192
La modifica di /etc/security/limits.conf non funziona quando il servizio mysqld viene avviato tramite lo script init /etc/init.d/mysql o tramite il service mysql restart.
With standard Red Hat mysql-server (5.1) package that provides /etc/init.d/mysqld (not /etc/init.d/mysql as the Oracle and Percona versions do), you could create a file /etc/sysconfig/mysqld containing ulimit -n 4096 and that setting will take effect for each restart of the MySQL daemon. But the ulimit -n setting hacked into the init script or put into /etc/sysconfig/mysqld isn’t really needed after all, because you can simply set open_files_limit in /etc/my.cnf:
[mysqld] open_files_limit = 8192 max_connections = 1000
… and mysqld_safe will increase the ulimit on its own before invoking the actual mysqld daemon. After service mysql restart you can verify the new open file limit in the running process, like this:
# cat /var/lib/mysql/*.pid 30697 # ps auxww | grep 30697 mysql 30697 97.8 9.8 6031872 1212224 pts/1 Sl 13:09 3:01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/some.hostname.err --open-files-limit=8192 --pid-file=/var/lib/mysql/some.hostname.pid # cat /proc/30697/limits Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 10485760 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes 96086 96086 processes Max open files 8192 8192 files Max locked memory 32768 32768 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 96086 96086 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0
And the running MySQL server will reveal the desired max_connections setting stuck this time:
mysql show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1000 | +-----------------+-------+ 1 row in set (0.00 sec)
and
mysql show variables like '%open_files_limit%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | open_files_limit | 5000 | +----------------------------+----------+
you can force ulimit -n 4096 to /etc/init.d/mysql-server start function. Source: Increasing MySQL 5.5 max_connections on RHEL 5 | End Point Blog
