Limits open files limit
Hello,
I would like to change open files limit to a number like 5 milionions.
Waiting for “mysql” to restart ……waiting for “mysql” to initialize ………finished.
Service Status
mariadb (/usr/sbin/mariadbd) is running as mysql with PID 12031 (systemd+/proc check method).
Warning: The unit file, source configuration file or drop-ins of mariadb.service changed on disk. Run 'systemctl daemon-reload' to reload units. Startup Log
May 12 00:22:09 server2.fmm.ba systemd[1]: Starting MariaDB 10.6.17 database server...
May 12 00:22:09 server2.fmm.ba mariadbd[12031]: 2024-05-12 0:22:09 0 [Warning] option 'table_open_cache': unsigned value 5048576 adjusted to 1048576
May 12 00:22:09 server2.fmm.ba mariadbd[12031]: 2024-05-12 0:22:09 0 [Warning] Could not increase number of max_open_files to more than 1048576 (request: 16777587)
May 12 00:22:09 server2.fmm.ba mariadbd[12031]: 2024-05-12 0:22:09 0 [Warning] Changed limits: max_open_files: 1048576 max_connections: 300 (was 300) table_cache: 524123 (was 1048576)
May 12 00:22:11 server2.fmm.ba systemd[1]: Started MariaDB 10.6.17 database server.
mysql restarted successfully.
-
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 Services1 -
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=infinityHere 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 10048576Here 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 instructionsHere 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 defaultkey_buffer_size = 128M
low_priority_updates = 1
concurrent_insert = 2# === Connection Settings ===
max_connections = 300back_log = 512
thread_cache_size = 256
thread_stack = 192Kinteractive_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.logunix_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=1024M0 -
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 Services0 -
Hello, I just did that in /etc/systemd/system/mariadb.service.d/override.conf
[Service]
LimitNOFILE=3000000
LimitMEMLOCK=3000000
LimitNPROC=3000000Then 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 -
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 -
Have you tried using the details here?
0 -
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 -
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 -
I read the value from the "variables" tab in phpmyadmin
0 -
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 -
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 -
I'm waiting for the solution
Thanks!
2 -
Good morning, do you have any news?
0 -
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.
Comments
14 comments