The operating system provides ways of limiting the amount of resource that can be used. Limits are set for each user but are applied separately to each process that is running for that user. Administering Unix servers can be a challenge, especially when the systems you manage are heavily used and performance problems reduce availability. Linux itself has a Max Processes per user limit. This feature allows us to control the number of processes an existing user on the server may be authorized to have.
Limits can be hard or soft. Hard limits are set by the root user. Only the root user can increase hard limits, though other users can decrease them. Soft limits can be set and changed by other users, but they cannot exceed the hard limits.
The default ulimit (maximum) open files limit is 1024 Which is very low, especially for a web server environment hosting multiple heavy database driven sites.
This ulimit ‘open files’ setting is also used by MySQL. MySQL automatically sets its open_files_limit to whatever the system’s ulimit is set to–at default will be 1024.
NOTE:
MySQL cannot set it’s open_files_limit to anything higher than what is specified under ulimit ‘open files’ you can set it lower, but not above the ‘open files’ limit.
Check current limits
- To check the limits:
ulimit -a
- To view the current hard and soft limits, enter the following command:
ulimit -Ha
ulimit -Sa
Note:
- H for hard limits
- S for soft limits.
Permanently set the new ‘open files’ limit
- Edit the file
/etc/security/limits.confusing your favorite Text Editor. - Add the following for all users to the bottom for of the file and save it.
soft nofile 102400
hard nofile 102400
soft nproc 10240
hard nproc 10240
- Edit the file /etc/security/limits.d/90-nproc.conf
- Add the following for all users to the bottom for of the file and save it.
soft nofile 1024000
hard nofile 1024000
soft nproc 10240
hard nproc 10240
root soft nproc unlimited
Set open_files_limit in my.cnf (MySQL)
- Edit file
/etc/my.cnf - Insert the following under your [mysqld] and save it.
[mysqld]
open_files_limit = 102400
- Find out if any other .conf files are being used with MySQL that overrides the values for open limits:
Run systemctl status mysqldcommand and it will show something like this
Drop-In:
/etc/systemd/system/mariadb.service.d
└─limits.conf
This means there is /etc/systemd/system/mariadb.service.d/limts.conf which is loaded with MySQL Server.
Edit the file and add the following
[Service]
LimitNOFILE=102400
- Run the following command to apply the changes.
systemctl daemon-reload && /scripts/restartsrv_mysql
- Reboot your server.
After the successful reboot of the server, we will again run below SQL Queries.
SHOW VARIABLES LIKE 'open_files_limit';
You should see the following:
+------------------+--------+ | Variable_name | Value | +------------------+--------+ | open_files_limit | 102400 | +------------------+--------+ 1 row in set (0.00 sec)
1 row in set (0.00 sec)
Done !!
