CloudLinux 7 MySql open_files_limit and max_open_files setup

0
5675

CloudLinux 7 MySql open_files_limit and max_open_files setup

 

CentOS 7 was released on 7 July 2014 with new systemd format. As the Systemd now replaces SysVinit, it is time to get familiar with it.

With new Systemd in CloudLinux 7 – comes new problems that need to be fixed and solved 🙂

The problem

When you try to change open_files_limit and max_open_files in /etc/my.cnf , the setting in my.cnf ignored completely.

My system is : CloudLinux 7.2 , cPanel v.56 with CageFS, PHPselector, DB_governor, LSPHP.

You can try to see thous settings in mysql with this command , you will see the same numbers as before.

mysql -e "show variables like '%open_%'"

 

My my.cnf

[mysqld]
innodb_file_per_table=1
default-storage-engine=MyISAM
performance-schema=0
max_allowed_packet=268435456
bind-address=127.0.0.1
old_password=0
#secure_auth=0

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#log=/var/log/mysql_query

#open_files_limit=50000
open_files_limit=1024000
#open_files_limit=16000

#slow-query-log=1
#long-query-time=1
#slow-query-log-file="/var/log/log-slow-queries.log"
#datadir=/var/lib/mysql
#tmpdir=/var/lib/mysql/tmp
#socket=/var/lib/mysql/mysql.sock
#user=mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
open_files_limit=1024000

My mysql server logs:

2016-07-17 18:45:42 9775 [Warning] Buffered warning: Could not increase number of max_open_files to more than 1024 (request: 10000)
2016-07-17 18:46:31 10148 [Warning] Buffered warning: Could not increase number of max_open_files to more than 1024 (request: 10000)
2016-07-17 18:47:48 10504 [Warning] Buffered warning: Could not increase number of max_open_files to more than 1024 (request: 10000)
2016-07-17 18:56:04 12480 [Warning] Buffered warning: Could not increase number of max_open_files to more than 1024 (request: 1024000) And restart the server when convenient

A little about CloudLinux 7

CloudLinux OS is an operating system designed specifically for shared hosting providers, developed by computer software company CloudLinux Inc. CloudLinux OS is based on OpenVZ kernel and is fully compatible with CentOS/RHEL packages. CloudLinux OS is designed to improve stability and security on server and to optimize its work on account of isolating tenants on server, setting resource usage limits for them, optimizing work with databases etc.

 

Possible solution – not helped me 🙁

You can try to change LimitNOFILE option in /usr/lib/systemd/system/mysqld.service  .

Then run:

systemctl daemon-reload
systemctl restart mysql.service

 

Copy of the mysqld.service file :

[Unit]
Description=MySQL database server
After=syslog.target
After=network.target
BindsTo=mysql.service

[Service]
Type=simple
User=mysql
Group=mysql

ExecStartPre=/usr/sbin/mysqld-prepare-db-dir %n
# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
# per bug #547485
ExecStart=/usr/bin/mysqld_safe --basedir=/usr
ExecStartPost=/usr/sbin/mysqld-wait-ready $MAINPID

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

# Place temp files in a secure directory, not /tmp
PrivateTmp=true

[Install]
WantedBy=multi-user.target
Alias=mysql.service

LimitNOFILE=infinity
LimitMEMLOCK=infinity

Following links might be helpful:
http://dba.stackexchange.com/questions/86987/mysql-open-files-limit-cannot-change-this-variable
https://ma.ttias.be/increase-open-files-limit-in-mariadb-on-centos-7-with-systemd/

 

 

Solution to the problem – THE fix

I have found a solution to edit /usr/lib/systemd/system/mysqld.service and add there:

LimitNOFILE=infinity
LimitMEMLOCK=infinity

Those options has been set in it however in [Install] section. I moved them to [Service] section and after “systemctl daemon-reload” and “systemctl restart mysql.service” the value has been applied properly:

mysql> show variables like 'open_file%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 65536 |
+------------------+-------+
1 row in set (0.00 sec)

After that change , my.cnf will have full control to open_files_limit and max_open_files and you can successfully change it there and of course restart mysql service after the change.

Things become more complicated with systemd now, looks like that is only one right way to set open_files_limit for mysql now.

However that is a way CentOS7/RHEL7/CloudLinux7 works with new systemd 🙁

 

Letter from cPanel team

Hello Sergey,

Thank you very much for the additional information, I'm glad to hear that were able to track down the source of the problem, and if they really are incorporating "LimitNOFILE" in systemd's files, then it should be fixed quickly to appear in the "service" section rather than just "install".

In any case, our team will definitely use the information here to update our own internal tools, and hopefully, catch this issue much quicker when we encounter it again.

While I hope that everything's working here now as expected after their suggested changes, please don't hesitate to let me know if you have any other questions or would like for our team to look into anything else and I'll be happy to assist you with anything else you might need.

Best Regards,

 

Thanks to CloudLinux and cPanel teams (and to Sergey Babkevych meeee:)) ) that take participation in that cooperative investigation, research and testing.

Good luck!