As a part of the MySQL Support, we had a support request from a client.The issue is DB server runs out of open files limit, though it is configured. It causes the DB hang and crash at times. Sometimes they can’t able to fix. So we plan to write our experience with configuring. We believe this article can help in configuring appropriate Ulimit value without any obstacles.
Let us jump to the subject.
What are the errors we might face while ulimit is not properly configured?
Too many open files
System unable to allocate necessary resources for the monitor thread
can’t create new thread, closing connection
The above-shared list is just sample’s, maybe people who currently reading this blog may also face issue related to ulimit, for that they may have different debug message too.
What is ulimit in a simple term?
User limits command, limit the use of system-wide resources.
Where are the Ulimit Configuration file located and how it is loaded?
The ulimit resource configuration is located from the below file
/etc/security/limits.conf
/etc/security/limits.d/*.conf
By default /etc/security/limits.conf is loaded but it can be override by the configuration reside on /etc/security/limit.d/*conf.
ulimit resource limitation based on the Linux user. Every individual /etc/security/limit.d/*.conf are read and the file is parsed one after another in the order of “C” locale and concatenate together in the order of parsing.
As I have specified above, the file loaded from /etc/security/limit.d/*.conf in the order of “C” locale and apply, but there will be one exception. For example, we have two configurations in /etc/security/limit.d/ folder.
alpha.conf
mysql.conf
both files configured for resource limitation for mysql user(domain). Only mysql.conf applied because the file is in case the domain is the same or more specific.
How the default soft or hard limit for the number of user’s processes is applied?
The system wide configuration file /etc/security/limits.d/90-nproc.conf (RHEL5, RHEL6), /etc/security/limits.d/20-nproc.conf (RHEL7) specifies the default nproc limits as:
* soft nproc 4096 root soft nproc unlimited
How PAM Modules related to /etc/security/limit.d/*.conf and /etc/security/limits.conf ?
The pam_limits PAM module sets limits on the system resources that can be obtained in a user session.
The /etc/pam.d/system-auth file is used by Red-Hat and like systems to group together common security policies. It is often included in other /etc/pam.d policy files where those common policies are required.
When accessing a system via ssh through sshd the /etc.pam.d/sshd policy file is consulted. This file includes /etc/pam.d/system-auth so your changes to /etc/pam.d/system-auth are valid.
The file /etc/pam.d/login is consulted when you log in via the /bin/login program, therefore, any changes to it only affect /bin/login.
login – rules for local (console login)
system-auth – common rules many services
password-auth – common rules for many remote services
sshd – rules for SSHD daemon only
How PAM create Obstacle while applying resource limit and How we mitigate?
After we allocate resource limit to the user, we try to login into the user account and check for the update resource information. But unfortunately, the new updated information not updated on the user login.
session requires pam_limits.so
This happen because of pam module not properly configured to load the /etc/security/limit.d/*.conf and /etc/security/limits.conf . We need to apply the above line into the /etc/pam.d/login or system-auth or password-auth or sshd file.
In most cases pam_limits.so will be enabled by default. In some cases, it is not so we need to append session required pam_limits line on appropriate pam config file related to the login program. This modification required logout.
Below I have shown, how to configure pam_limits.so module in system-auth configuration and applying ulimit value to the user:
Environment:
Os: Centos 7.4
Kernel: 3.10.0-693.11.1.el7.x86_64
Current Status:
[root@centos7 ~]# id uid=0(root) gid=0(root) groups=0(root) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
Logging in as mysql (nologin)
root@centos7 ~]# su -s /bin/bash mysql
[mysql@centos7 root]$ id uid=1001(mysql) gid=1001(mysql) groups=1001(mysql) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
Validating the ulimit for mysql user after logging in
[mysql@centos7 root]$ 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) 1870
max locked memory (kbytes, -l) 64
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) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 1870
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
Trying to increase ulimit value for nofile and nproc by creating mysql.conf under /etc/security/limit.d/ dir:
[root@centos7 ~]# vim /etc/security/limits.d/mysql.conf
mysql soft nofile 55000
mysql hard nofile 655000
mysql soft nproc 55000
mysql hard nproc 655000
Now login and check the current ulimit value for mysql user:
[root@centos7 ~]# su -s /bin/bash mysql
[mysql@centos7 root]$ 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) 1870
max locked memory (kbytes, -l) 64
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) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 1870
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
Ulimit value was not updated still, Now let us check the pam module:
[root@centos7 ~]# vim /etc/pam.d/system-auth
#%PAM-1.0 # This file is auto-generated.
# User changes will be destroyed the next time authconfig is run.
auth required pam_env.so
auth required pam_faildelay.so delay=2000000
auth sufficient pam_unix.so nullok try_first_pass
auth requisite pam_succeed_if.so uid >= 1000 quiet_successauth required pam_deny.so
account required pam_unix.so
account sufficient pam_localuser.so
account sufficient pam_succeed_if.so uid < 1000 quiet
account required pam_permit.so
password requisite pam_pwquality.so try_first_pass local_users_only retry=3 authtok_type=
password sufficient pam_unix.so sha512 shadow nullok try_first_pass use_authtok
password required pam_deny.so
session optional pam_keyinit.so revoke
#session required pam_limits.so
session optional pam_systemd.so
session [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
session required pam_unix.so
We see observe that pam_limits.so is not enabled (commented).Now let us enable pam_limits.so and validate it.
[root@centos7 ~]# su -s /bin/bash mysql
[mysql@centos7 root]$ ulimit -n 55000
[mysql@centos7 root]$ 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) 1870
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 55000
pipe size (512 bytes, -p) 8 POSIX
message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 55000
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
Successfully we have updated the resource limit for the user.
After changing the ulimit settings, you must restart the process (mysqld) to take advantage of the modified settings. You can use the /proc file system to see the current limitations on a running process.
Conflict between ulimit and Systemd
Now mostly every Linux distribution moving from init or upstart to Systemd. Systemd is very mature layer between kernel and application. Unfortunately not every User and OS is fully integrated with it.
When service started using systemd, it won’t consider the ulimit value defined for the process owner. Systemd is providing an option to set a limit over process using systemd variable likes LimitNOFILE and LimitNPROC.
How to configure systemd to limit open files and process?
Login into MySQL user and checking the openfile limit and process limit for the MySQL user.
[root@centos7 ~]# su -s /bin/bash mysql
[mysql@centos7 root]$ 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) 1870
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 55000
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 55000
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
Validating current status of Mysql process using systemctl.
[root@centos7 ~]#systemctl status mysqld mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2017-09-13 09:32:39 IST; 2 months 27 days ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 9131 (mysqld)
CGroup: /system.slice/mysqld.service
└─9131 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Finding openfile limit and process limit for MySQL process.
[root@centos7 ~]# cat /proc/`pidofmysqld`/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 8388608 unlimited bytes
Max core file size 0 unlimited bytes
Max resident set unlimited unlimited bytes
Max processes 1870 1870 processes
Max open files 5000 5000 files
Max locked memory 65536 65536 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 1870 1870 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us
Even Though MySQL User(Domain) process and openfile soft limit were set to 55000, it is not affecting MySQL process it is still the older value of 1870 and 5000. This because of MySQL service is started using systemctl.
Fixing systemd MySQL service file
Open the systemd config for MySQL service and append the limits LimitNOFILE=55000 and LimitPROC=55000 under service category.
[root@centos7 ~]# vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
Alias=mysql.service
[Service]
User=mysql
Group=mysql
LimitNOFILE=55000
LimitNPROC=55000
Reload the daemon and Restart the MySQL service.
[root@centos7 ~]# systemctl daemon-reload
[root@centos7 ~]# systemctl restart mysqld
[root@centos7 system]# cat /proc/`pidof mysqld`/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 8388608 unlimited bytes
Max core file size 0 unlimited bytes
Max resident set unlimited unlimited bytes
Max processes 55000 55000 processes
Max open files 55000 55000 files
Max locked memory 65536 65536 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 1870 1870 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us
Sometimes we get a conflict between unit file located in /etc/systemd/system/multi-user.target.wants/mysqld.service if you still face issues with resource limits have a look at this file too.
Hope this helps in modifying the ulimit values for a user and process.
%20vs%20systemctl%20stop%20(2).png)
.svg)
