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
  • Finally, we need to make sure everything is working.

    Running the following query in the database:


    should produce the following output:

    | Variable_name    | Value |
    | open_files_limit | 32768 |
Alexey Strokach
Alexey Strokach
Graduate Student

If you found something wrong, let me know!