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/
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/
https://ma.ttias.be/increase-
Solution to the problem – THE fix
I have found a solution to edit /usr/lib/systemd/system/
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!