Skip to main content
cPanel Technical Support has been heavily impacted by hurricane Beryl and our ability to respond to tickets has been hindered as a result. We appreciate your understanding and patience as we address these delays.

Limits open files limit

Comments

14 comments

  • Andrew

    Have a look at this:

    https://support.cpanel.net/hc/en-us/articles/1500011233641-How-To-Increase-max-open-files-for-MySQL

    Andrew N. - cPanel Plesk VMWare Certified Professional
    Do you need immediate assistance? 20 minutes response time!* Open a ticket
    EmergencySupport - Professional Server Management and One-time Services

    1
  • Nermin

    Hello, I did this already, but same thing happens. phpMyAdmin says I need to:

    • Consider increasing open_files_limit, and check the error log when restarting after changing open_files_limit.
    • Opening tables requires disk I/O which is costly. Increasing table_open_cache might avoid this.

    Also I did this too: https://mariadb.com/kb/en/systemd/#configuring-the-open-files-limit

    But I can't increase it more than table open cache: 524.123 and open files limit: 1.048.​576. Maybe there is some limit elsewhere.

    I have 130 Wordpress websites hosted, and  there are some of them with a lot of visitors, maybe 2000 email accounts. I would like to try resolve this open files open cache limit and to see if there will be any performance increase.

    I've been using perl mysqltuner.pl for mysql tuning, and Engintron instructions for my.cnf to optimize sql. MySQL has been working good, fast, stable and all. But for months now I can't figure out how to increase these two open files and table open cache. Maybe I am missing on some performance increase.

    Server has 40 CPU, 256GB of RAM, RAID 1 on all SSD drives. OS & cPanel is on one drive, websites on another, 12TB HDD is for backups. Average CPU usage is 8. Half of RAM is not used. But for some reason Wordpress administration kind of goes slow on save and upload. It by all means is not slow, but I just feel like it could go faster.

    I have Litespeed Web server, and almost all websites are on Quic.Cloud

    Here is the output or MySQL restart:
    Waiting for “mysql” to restart ……waiting for “mysql” to initialize ………finished.

    Service Status
    mariadb (/usr/sbin/mariadbd) is running as mysql with PID 565290 (systemd+/proc check method).

    Startup Log
    May 12 19:02:28 server2.fmm.ba systemd[1]: Starting MariaDB 10.6.17 database server...
    May 12 19:02:28 server2.fmm.ba mariadbd[565290]: 2024-05-12 19:02:28 0 [Warning] option 'table_open_cache': unsigned value 5048576 adjusted to 1048576
    May 12 19:02:28 server2.fmm.ba mariadbd[565290]: 2024-05-12 19:02:28 0 [Warning] Could not increase number of max_open_files to more than 1048576 (request: 16777587)
    May 12 19:02:28 server2.fmm.ba mariadbd[565290]: 2024-05-12 19:02:28 0 [Warning] Changed limits: max_open_files: 1048576 max_connections: 300 (was 300) table_cache: 524123 (was 1048576)
    May 12 19:02:30 server2.fmm.ba systemd[1]: Started MariaDB 10.6.17 database server.

    mysql restarted successfully.

    Here is /etc/systemd/system/mariadb.service.d/override.conf:

    [Service]
    LimitNOFILE=infinity
    LimitMEMLOCK=infinity
    LimitNPROC=infinity

    Here is /etc/security/limits.conf:

    # /etc/security/limits.conf
    #
    #This file sets the resource limits for the users logged in via PAM.
    #It does not affect resource limits of the system services.
    #
    #Also note that configuration files in /etc/security/limits.d directory,
    #which are read in alphabetical order, override the settings in this
    #file in case the domain is the same or more specific.
    #That means for example that setting a limit for wildcard domain here
    #can be overriden with a wildcard setting in a config file in the
    #subdirectory, but a user specific setting here can be overriden only
    #with a user specific setting in the subdirectory.
    #
    #Each line describes a limit for a user in the form:
    #
    #<domain>        <type>  <item>  <value>
    #
    #Where:
    #<domain> can be:
    #        - a user name
    #        - a group name, with @group syntax
    #        - the wildcard *, for default entry
    #        - the wildcard %, can be also used with %group syntax,
    #                 for maxlogin limit
    #
    #<type> can have the two values:
    #        - "soft" for enforcing the soft limits
    #        - "hard" for enforcing hard limits
    #
    #<item> can be one of the following:
    #        - core - limits the core file size (KB)
    #        - data - max data size (KB)
    #        - fsize - maximum filesize (KB)
    #        - memlock - max locked-in-memory address space (KB)
    #        - nofile - max number of open file descriptors
    #        - rss - max resident set size (KB)
    #        - stack - max stack size (KB)
    #        - cpu - max CPU time (MIN)
    #        - nproc - max number of processes
    #        - as - address space limit (KB)
    #        - maxlogins - max number of logins for this user
    #        - maxsyslogins - max number of logins on the system
    #        - priority - the priority to run user process with
    #        - locks - max number of file locks the user can hold
    #        - sigpending - max number of pending signals
    #        - msgqueue - max memory used by POSIX message queues (bytes)
    #        - nice - max nice priority allowed to raise to values: [-20, 19]
    #        - rtprio - max realtime priority
    #
    #<domain>      <type>  <item>         <value>
    #

    #*               soft    core            0
    #*               hard    rss             10000
    #@student        hard    nproc           20
    #@faculty        soft    nproc           20
    #@faculty        hard    nproc           50
    #ftp             hard    nproc           0
    #@student        -       maxlogins       4

    # End of file
    * soft nofile 10048576
    * hard nofile 10048576
    * soft nproc 10048576
    * hard nproc 10048576

    Here is sysctl.conf:

    # sysctl settings are defined through files in
    # /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
    #
    # Vendors settings live in /usr/lib/sysctl.d/.
    # To override a whole file, create a new file with the same in
    # /etc/sysctl.d/ and put new settings there. To override
    # only specific settings, add a file with a lexically later
    # name in /etc/sysctl.d/ and put new settings there.
    #
    # For more information, see sysctl.conf(5) and sysctl.d(5).
    kernel.panic = 10
    kernel.watchdog_thresh = 20
    net.ipv6.conf.all.disable_ipv6 = 1

    # Nermin added based on Engintron instructions
    # Performance Tuning
    fs.file-max = 2097152
    fs.nr_open = 5048576‬
    net.core.netdev_max_backlog = 131070
    net.core.somaxconn = 65535
    net.ipv4.ip_local_port_range = 10000 65535
    net.ipv4.ip_nonlocal_bind = 1
    net.ipv4.tcp_max_syn_backlog = 3240000
    net.ipv4.tcp_max_tw_buckets = 1440000
    net.ipv4.tcp_tw_reuse = 1
    net.ipv4.tcp_window_scaling = 1
    net.netfilter.nf_conntrack_tcp_timeout_established = 600
    vm.swappiness = 0
    # End of Engintron instructions

    Here is my.cnf:

    [mysqld]
    performance-schema              = 1
    # === Required Settings ===
    basedir                         = /usr
    bind_address                    = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections
    datadir                         = /var/lib/mysql
    #default_authentication_plugin  = mysql_native_password # Enable in MySQL 8+ or MariaDB 10.6+ for backwards compatibility with common CMSs
    max_allowed_packet              = 256M
    max_connect_errors              = 1000000
    pid_file                        = /var/lib/mysql/mysql.pid
    port                            = 3306
    skip_external_locking
    socket                          = /var/lib/mysql/mysql.sock
    tmpdir                          = /tmp
    user                            = mysql
    #skip-name-resolve

    # === SQL Compatibility Mode ===
    # Enable for b/c with databases created in older MySQL/MariaDB versions
    # (e.g. when using null dates)
    #sql_mode                       = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES
    # Crappy SQL queries/schema? Go bold!
    #sql_mode                       = ""

    # === InnoDB Settings ===
    default_storage_engine          = InnoDB
    innodb_buffer_pool_instances    = 16     # Use 1 instance per 1GB of InnoDB pool size - max is 64
    innodb_buffer_pool_size         = 175G    # Use up to 70-80% of RAM
    innodb_file_per_table           = 1
    innodb_flush_log_at_trx_commit  = 0
    innodb_flush_method             = O_DIRECT
    innodb_log_buffer_size          = 64M
    innodb_log_file_size            = 44800M
    innodb_sort_buffer_size         = 32M    # UPD - Defines how much data is read into memory for sorting operations before writing to disk (default is 1M / max is 64M)
    innodb_stats_on_metadata        = 0
    #innodb_use_fdatasync           = 1     # Only (!) for MySQL v8.0.26+

    #innodb_temp_data_file_path     = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file
    #innodb_thread_concurrency      = 4     # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better
                                            # contain CPU usage. E.g. if your system has 8 CPUs, try 6 or 7 and check
                                            # the overall load produced by MySQL/MariaDB.
    innodb_read_io_threads          = 64
    innodb_write_io_threads         = 64
    innodb_io_capacity = 2000
    innodb_io_capacity_max = 4000

    # === MyISAM Settings ===
    # The following 3 options are ONLY supported by MariaDB & up to MySQL 5.7
    # Do NOT un-comment on MySQL 8.x+
    #query_cache_limit              = 8M    # UPD
    #query_cache_size               = 2048M   # UPD
    #query_cache_type               = 0     # Enabled by default

    key_buffer_size                 = 128M

    low_priority_updates            = 1
    concurrent_insert               = 2

    # === Connection Settings ===
    max_connections                 = 300

    back_log                        = 512
    thread_cache_size               = 256
    thread_stack                    = 192K

    interactive_timeout             = 180
    wait_timeout                    = 180

    # For MySQL 5.7+ only (disabled by default)
    #max_execution_time             = 90000 # Set a timeout limit for SELECT statements (value in milliseconds).
                                            # This option may be useful to address aggressive crawling on large sites,
                                            # but it can also cause issues (e.g. with backups). So use with extreme caution and test!
                                            # More info at: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time

    # For MariaDB 10.1.1+ only (disabled by default)
    #max_statement_time             = 90    # The equivalent of "max_execution_time" in MySQL 5.7+ (set above)
                                            # The variable is of type double, thus you can use subsecond timeout.
                                            # For example you can use value 0.01 for 10 milliseconds timeout.
                                            # More info at: https://mariadb.com/kb/en/aborting-statements/

    # === Buffer Settings ===
    # Handy tip for managing your database's RAM usage:
    # The following values should be treated carefully as they are added together and then multiplied by your "max_connections" value.
    # Other options will also add up to RAM consumption (e.g. tmp_table_size). So don't go switching your "join_buffer_size" to 1G, it's harmful & inefficient.
    # Use one of the database diagnostics tools mentioned at the top of this file to count your database's potential total RAM usage, so you know if you are within
    # reasonable limits. Remember that other services will require enough RAM to operate properly (like Apache or PHP-FPM), so set your limits wisely.
    join_buffer_size = 4M
    read_buffer_size = 3M
    read_rnd_buffer_size = 4M
    sort_buffer_size = 4M

    # === Table Settings ===
    # In systemd managed systems like Ubuntu 16.04+ or CentOS 7+, you need to perform an extra action for table_open_cache & open_files_limit
    # to be overriden (also see comment next to open_files_limit).
    # E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
    # and for MariaDB check: https://mariadb.com/kb/en/library/systemd/
    table_definition_cache = 2097152 # UPD
    table_open_cache = 5048576 # UPD
    open_files_limit = 5048576 # UPD

                                            # open files limit usually set in /etc/sysctl.conf and /etc/security/limits.conf
                                            # In systemd managed systems this limit must also be set in:
                                            # - /etc/systemd/system/mysql.service.d/override.conf (for MySQL 5.7+ in Ubuntu) or
                                            # - /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+ in CentOS) or
                                            # - /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB)
                                            # otherwise changing open_files_limit will have no effect.
                                            #
                                            # To edit the right file execute:
                                            # $ systemctl edit mysql (or mysqld or mariadb)
                                            # and set "LimitNOFILE=" to something like 100000 or more (depending on your system limits for MySQL)
                                            # or use "LimitNOFILE=infinity" for MariaDB only.
                                            # Finally merge the changes with:
                                            # $ systemctl daemon-reload; systemctl restart mysql (or mysqld or mariadb)

    max_heap_table_size             = 4095M  # Increase to 256M or 512M if you have lots of temporary tables because of missing indices in JOINs
    tmp_table_size                  = 4095M # Use same value as max_heap_table_size

    # === Search Settings ===
    ft_min_word_len                 = 3     # Minimum length of words to be indexed for search results

    # === Binary Logging ===
    disable_log_bin                 = 1     # Binary logging disabled by default
    #log_bin                                # To enable binary logging, uncomment this line & only one of the following 2 lines
                                            # that corresponds to your actual MySQL/MariaDB version.
                                            # Remember to comment out the line with "disable_log_bin".
    #expire_logs_days               = 1     # Keep logs for 1 day - For MySQL 5.x & MariaDB before 10.6 only
    #binlog_expire_logs_seconds     = 86400 # Keep logs for 1 day (in seconds) - For MySQL 8+ & MariaDB 10.6+ only

    # === Error & Slow Query Logging ===
    log_error                       = /var/lib/mysql/mysql_error.log
    log_queries_not_using_indexes   = 0     # Disabled on production
    long_query_time                 = 5
    slow_query_log                  = 1
    slow_query_log_file             = /var/lib/mysql/mysql_slow.log

    unix_socket                     =   OFF
    [mysqldump]
    # Variable reference
    # For MySQL 5.7+:  https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
    # For MariaDB:     https://mariadb.com/kb/en/library/mysqldump/
    quick
    quote_names
    max_allowed_packet=1024M

    0
  • Andrew
    Translate

    What happens if you set a limit rather than "infinity"? I believe thats not a valid value.

    Andrew N. - cPanel Plesk VMWare Certified Professional
    Do you need immediate assistance? 20 minutes response time!* Open a ticket
    EmergencySupport - Professional Server Management and One-time Services

    0
  • Nermin

    Hello, I just did that in /etc/systemd/system/mariadb.service.d/override.conf
    [Service]
    LimitNOFILE=3000000
    LimitMEMLOCK=3000000
    LimitNPROC=3000000

    Then I did systemctl daemon-reload, and restarted SQL thru WHM, but same thing:
    Waiting for “mysql” to restart ……waiting for “mysql” to initialize ………finished.

    Service Status
    mariadb (/usr/sbin/mariadbd) is running as mysql with PID 600746 (systemd+/proc check method).

    Startup Log
    May 12 20:11:17 server2.fmm.ba systemd[1]: Starting MariaDB 10.6.17 database server...
    May 12 20:11:17 server2.fmm.ba mariadbd[600746]: 2024-05-12 20:11:17 0 [Warning] option 'table_open_cache': unsigned value 5048576 adjusted to 1048576
    May 12 20:11:17 server2.fmm.ba mariadbd[600746]: 2024-05-12 20:11:17 0 [Warning] Could not increase number of max_open_files to more than 1048576 (request: 16777587)
    May 12 20:11:17 server2.fmm.ba mariadbd[600746]: 2024-05-12 20:11:17 0 [Warning] Changed limits: max_open_files: 1048576 max_connections: 300 (was 300) table_cache: 524123 (was 1048576)
    May 12 20:11:19 server2.fmm.ba systemd[1]: Started MariaDB 10.6.17 database server.

    mysql restarted successfully.

    0
  • Unnamed User

    I have the same problem as you. Did you solve it?

    I also tried changing the parameters in all the necessary files but it still loads the default limit of cpanel after reboot service.

    0
  • Unnamed User

    Yes, I tried this but the new values ​​are not loaded after the reboot.

    Could there be some setting that I haven't seen inside whm?

    0
  • cPRex Jurassic Moderator

    You mention it "still loads the default limit of cpanel" after the reboot - can you let me know where you are checking this value?

    0
  • Unnamed User

    I read the value from the "variables" tab in phpmyadmin

    0
  • cPRex Jurassic Moderator

    Thanks for the additional details - I've confirmed this behavior so I'm looking into this now and I'll let you know as soon as I have more details.

    0
  • cPRex Jurassic Moderator

    I've created case CPANEL-45618 with our developers so they can look into this behavior as I don't have a good explanation for why this is happening.  I've linked them to this Forums post as well and I'll be sure to post here once I hear an update.

    1
  • Unnamed User

    I'm waiting for the solution

    Thanks!

    2
  • Unnamed User

    Good morning, do you have any news?

    0
  • cPRex Jurassic Moderator

    I don't - I'm guessing I'm not going to hear much on this within a few days as it could be a while before a case like this gets resolved.

    0

Please sign in to leave a comment.