How to increment the Mysql 5.5’s max_connections value on RHEL 5


Default limit of 214 connections.

mysql show variables like 'max_connections';
| Variable_name   | Value |
| max_connections |  214  |
1 row in set (0.00 sec)
The problem is that the maximum number of open files allowed is too small, by default 1024, to increase max_connections beyond 214.

There are plenty of online guides that explain how to handle this, including increasing the kernel fs.file-max setting, which may be necessary by editing /etc/sysctl.conf, in this example to double the default:

fs.file-max = 2459688

Then run sysctl -p to make the change take immediate effect. (It’ll remain after reboot too.)

There are also many guides that say you need to change /etc/security/limits.conf along these lines:

mysql   soft   nofile    4096
mysql   hard   nofile    8192

However, the /etc/security/limits.conf change does not actually work when mysqld is started via the init script in /etc/init.d/mysql or via 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:

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
# 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/
# 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)


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