Increasing MySQL open-file-limit

The MySQL open-files-limit setting can be increased by following these steps:

• Add the following line to /etc/mysql/my.cnf:

  open-files-limit               = 32768
  

• Restart MySQL:

  sudo service mysql reboot
  

• If MySQL reboots successully and without strange messages in its log file, we’re done!

• If we get strange log messages:

  [Warning] Could not increase number of max_open_files to more than 1024 (request: 32768)
  [Warning] Changed limits: max_connections: 214 (requested 500)
  [Warning] Changed limits: table_open_cache: 400 (requested 4096)
  

  we need to add the following lines to /etc/security/limits.conf:

  mysql          soft     nofile         32768
  mysql          hard     nofile         32768
  

  and add the following line to /etc/pam.d/common-session and /etc/pam.d/common-session-noninteractive:

  session required pam_limits.so
  

• Finally, we need to make sure everything is working.

  Running the following query in the database:

  SHOW VARIABLES LIKE 'open%';
  

  should produce the following output:

  +------------------+-------+
  | Variable_name    | Value |
  +------------------+-------+
  | open_files_limit | 32768 |
  +------------------+-------+