Can not increase max_open_files for Mysql max-connections in Ubuntu 15
mysql, performance, ubuntu
Solution
Ubuntu has moved from Upstart to Systemd in version 15.04 and no longer respects the limits in /etc/security/limits.conf for system services. These limits now apply only to user sessions.
The limits for the MySQL service are defined in the Systemd configuration file, which you should copy from its default location into /etc/systemd and then edit the copy.
sudo cp /lib/systemd/system/mysql.service /etc/systemd/system/
sudo vim /etc/systemd/system/mysql.service # or your editor of choice
Add the following lines to the bottom of the file:
LimitNOFILE=infinity
LimitMEMLOCK=infinity
You could also set a numeric limit, eg `LimitNOFILE=4510`.
Now reload the Systemd configuration with:
sudo systemctl daemon-reload
Restart MySQL and it should now obey the max_connections directive.
I also had problems stopping MySQL cleanly after upgrading to 15.04. If this affects you (you'll know because it will take 300 seconds to time out when you do `service mysql stop` or `service mysql restart`) then adding the following line to the same /etc/systemd/system/mysql.service file fixed it for me:
ExecStop=/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf shutdown
This latter problem seems to have been fixed by 16.04 and this line is no longer required, so before you do a distribution upgrade you'll want to stop MySQL and remove the `ExecStop` line from the config file.
Problem
I am running this version of Mysql ``` Ver 14.14 Distrib 5.6.24, for debian-linux-gnu (x86_64) ``` On this version of Ubuntu ``` Distributor ID: Ubuntu Description: Ubuntu 15.04 Release: 15.04 Codename: vivid ``` This is the config I set for Mysql: ``` key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 innodb_buffer_pool_size=20G innodb_log_buffer_size=16M tmp_table_size = 32M max_heap_table_size = 32M open-files-limit=4510 max_connections=500 myisam-recover = BACKUP query_cache_type=1 query_cache_limit = 32M query_cache_size = 32M ``` These are the warnings I keep getting when starting MYSQL: ``` 2015-06-17 17:28:53 26720 [Warning] Buffered warning: Could not increase number of max_open_files to more than 1024 (request: 4510) 2015-06-17 17:28:53 26720 [Warning] Buffered warning: Changed limits: max_connections: 214 (requested 500) 2015-06-17 17:28:53 26720 [Warning] Buffered warning: Changed limits: table_open_cache: 400 (requested 2000) ``` I already tried these steps: 1) Adding this to `/etc/security/limits.conf` ``` mysql soft nofile 65535 mysql hard no file 65535 ``` 2) Adding this to `/etc/pam.d/common-auth` and `/etc/pam.d/commom-session` ``` session required pam_limits.so ``` 3) Add this to `/etc/mysql/mysql.conf.d/mysqld.cnf` ``` open-files-limit=4510 or open_files_limit=4510 ``` None of these have worked and I am still not able to raise the mysql max connections to 500. I'd really appreciate some help at this point. Thanks a lot in advance.