Skip to content

Instantly share code, notes, and snippets.

@fevangelou
Last active February 14, 2024 09:14
Star You must be signed in to star a gist
Save fevangelou/0da9941e67a9c9bb2596 to your computer and use it in GitHub Desktop.
Optimized my.cnf configuration for MySQL/MariaDB (on cPanel/WHM servers)
# === Optimized my.cnf configuration for MySQL/MariaDB (on cPanel/WHM servers) ===
#
# by Fotis Evangelou, developer of Engintron (engintron.com)
#
# ~ Updated December 2021 ~
#
#
# The settings provided below are a starting point for a 8-16 GB RAM server with 4-8 CPU cores.
# If you have different resources available you should adjust accordingly to save CPU, RAM & disk I/O usage.
#
# The settings marked with a specific comment or the word "UPD" (after the value)
# should be adjusted for your system by using database diagnostics tools like:
#
# https://github.com/major/MySQLTuner-perl
# or
# https://github.com/BMDan/tuning-primer.sh
#
# Run either of these scripts before optimizing your database, at least 1 hr after the optimization & finally
# at least once a day for 3 days (without restarting the database) to see how your server performs and if you need
# to re-adjust anything. The more MySQL/MariaDB runs without restarting, the more usage data it gathers, so these
# diagnostics scripts will report in mode detail how MySQL/MariaDB performs.
#
#
# IMPORTANT NOTE: If there is NO comment after a setting value, then 99,9% of the times you won't need to adjust it.
#
#
# --- THINGS TO DO AFTER YOU UPDATE MY.CNF - TROUBLESHOOTING ---
#
# If any terminal commands are mentioned, make sure you execute them as "root" user.
#
# If MySQL or MariaDB cannot start (or restart), then perform the following actions.
#
# 1. If the server had the stock database configuration and you added or updated any
# "innodb_log_*" settings (as suggested below), then execute these commands ONLY
# the first time you apply this configuration:
#
# $ rm -rvf /var/lib/mysql/ib_logfile*
# $ touch /var/lib/mysql/mysql.sock
# $ touch /var/lib/mysql/mysql.pid
# $ chown -R mysql:mysql /var/lib/mysql
# $ /scripts/restartsrv_mysql
#
# or use the shorthand command:
# $ rm -rvf /var/lib/mysql/ib_logfile*; touch /var/lib/mysql/mysql.sock; touch /var/lib/mysql/mysql.pid; chown -R mysql:mysql /var/lib/mysql; /scripts/restartsrv_mysql
#
# IMPORTANT: If you edit this file from the Engintron WHM app in cPanel/WHM,
# then you DO NOT need to execute the above terminal commands. When you save
# the file through the Engintron WHM app, these terminal commands will be
# executed automatically after the file is saved on disk.
#
# 2. If the setting "bind-address" is not commented out, then make sure the file /etc/hosts is
# properly configured. A good example of a "clean" /etc/hosts file is something like this:
#
# 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
# ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
# 1.2.3.4 hostname.domain.tld hostname # <-- Replace accordingly!
#
# Finally restart the database using the related cPanel script:
#
# $ /scripts/restartsrv_mysql
#
# 3. If the database service cannot restart even after the first 2 steps, make sure the database data folder
# (common for either MySQL or MariaDB) "/var/lib/mysql" is owned by the "mysql" user AND group.
# Additionally, the folder itself can have 0751 or 0755 file permissions. To fix it, simply do this:
# $ chown -R mysql:mysql /var/lib/mysql
# $ chmod 0755 /var/lib/mysql
#
# Finally restart the database using the related cPanel script:
#
# $ /scripts/restartsrv_mysql
#
# 4. Adjust SQL settings under "Tweak Settings" in WHM:
# After applying the optimized my.cnf file, you'll also want to DISABLE the following 3 settings
# in the "SQL" tab of Tweak Settings in WHM:
#
# - Allow cPanel & WHM to determine the best value for your MySQL open_files_limit configuration?
# - Allow cPanel & WHM to determine the best value for your MySQL max_allowed_packet configuration?
# - Allow cPanel & WHM to determine the best value for your MySQL innodb_buffer_pool_size configuration?
#
#
# ~ FIN ~
[mysql]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# === 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
# === 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 = 4 # Use 1 instance per 1GB of InnoDB pool size - max is 64
innodb_buffer_pool_size = 4G # 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 = 16M
innodb_log_file_size = 1G
innodb_sort_buffer_size = 4M # 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 # Depends on the storage tech - use 2000 for SSD, more for NVMe
#innodb_io_capacity_max = 4000 # Usually double the value of innodb_io_capacity
# === 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 = 4M # UPD
#query_cache_size = 64M # UPD
#query_cache_type = 1 # Enabled by default
key_buffer_size = 24M # UPD
low_priority_updates = 1
concurrent_insert = 2
# === Connection Settings ===
max_connections = 100 # UPD - Important: high no. of connections = high RAM consumption
back_log = 512
thread_cache_size = 100
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 # UPD
read_buffer_size = 3M # UPD
read_rnd_buffer_size = 4M # UPD
sort_buffer_size = 4M # UPD
# === 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 = 40000 # UPD
table_open_cache = 40000 # UPD
open_files_limit = 60000 # UPD - This can be 2x to 3x the table_open_cache value or match the system's
# 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 = 128M # Increase to 256M or 512M if you have lots of temporary tables because of missing indices in JOINs
tmp_table_size = 128M # 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 = 0 # Disabled on production
slow_query_log_file = /var/lib/mysql/mysql_slow.log
[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
@EvangelosBalafoutis
Copy link

Hello Fotis.
I tried he steps if mysqldoes not restart and I got the following

[root@sigma etc]# /scripts/restartsrv_mysql
Waiting for “mysql” to restart ……info [restartsrv_mysql] systemd failed to start the service “mysqld” (The “/usr/bin/systemctl restart mysqld.service --no-ask-password” command (process 1084551) reported error number 1 when it ended.): Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

…info [restartsrv_mysql] systemd failed to start the service “mysqld” (The “/usr/bin/systemctl restart mysqld.service --no-ask-password” command (process 1084662) reported error number 1 when it ended.): Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

…info [restartsrv_mysql] systemd failed to start the service “mysqld” (The “/usr/bin/systemctl restart mysqld.service --no-ask-password” command (process 1084780) reported error number 1 when it ended.): Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

…failed.

Cpanel::Exception::Services::StartError
Service Status
mysqld (/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid) is running as mysql with PID 90503 (systemd+/proc check method).

Service Error
(XID p73q5q) The “mysql” service failed to start.

Startup Log
May 19 00:52:49 sigma.omicronacademy.com systemd[1]: Starting MySQL Server...

Log Messages
2020-05-18T21:52:48.384713Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
2020-05-18T21:52:44.259651Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
2020-05-18T21:52:40.371393Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

@fevangelou
Copy link
Author

Run systemctl status mysqld.service to see why MySQL won't start. If there is no practical hint there, see MySQL's error log.

In any case, make sure the contents of this my.cnf are properly copied into your server's /etc/my.cnf file.

@EvangelosBalafoutis
Copy link

EvangelosBalafoutis commented May 19, 2020

Hello Fotis.
The file is properly copied. I tried on another server with no problem to restart.
Because I had mysql down I return to the old my.cnf.

But mmy error log says

2020-05-18T22:39:27.121991Z 0 [Note] Shutting down plugin 'MyISAM'
2020-05-18T22:39:27.122710Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2020-05-18T22:39:28.435750Z 0 [Warning] Could not increase number of max_open_files to more than 50000 (request: 80110)
2020-05-18T22:39:28.435948Z 0 [Warning] Changed limits: table_open_cache: 24945 (requested 40000)
2020-05-18T22:39:28.598329Z 0 [Note] libgovernor.so found
2020-05-18T22:39:28.598350Z 0 [Note] All governors functions found too
2020-05-18T22:39:28.598384Z 0 [Note] Governor connected
2020-05-18T22:39:28.598388Z 0 [Note] All governors lve functions found too
2020-05-18T22:39:28.598695Z 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2020-05-18T22:39:28.598700Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-05-18T22:39:28.598705Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2020-05-18T22:39:28.599674Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-cll-lve) starting as process 1108942 ...
2020-05-18T22:39:28.603292Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-05-18T22:39:28.603317Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-05-18T22:39:28.603321Z 0 [Note] InnoDB: Uses event mutexes
2020-05-18T22:39:28.603324Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-05-18T22:39:28.603327Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-05-18T22:39:28.603329Z 0 [Note] InnoDB: Using Linux native AIO
2020-05-18T22:39:28.603503Z 0 [Note] InnoDB: Number of pools: 1
2020-05-18T22:39:28.603583Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-05-18T22:39:28.629156Z 0 [Warning] InnoDB: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
2020-05-18T22:39:28.629174Z 0 [Warning] InnoDB: io_setup() attempt 1.
2020-05-18T22:39:29.129267Z 0 [Warning] InnoDB: io_setup() attempt 2.
2020-05-18T22:39:29.629363Z 0 [Warning] InnoDB: io_setup() attempt 3.
2020-05-18T22:39:30.129474Z 0 [Warning] InnoDB: io_setup() attempt 4.
2020-05-18T22:39:30.629602Z 0 [Warning] InnoDB: io_setup() attempt 5.
2020-05-18T22:39:31.129723Z 0 [ERROR] InnoDB: io_setup() failed with EAGAIN after 5 attempts.
2020-05-18T22:39:31.129751Z 0 [Note] InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf
2020-05-18T22:39:31.130257Z 0 [ERROR] InnoDB: Cannot initialize AIO sub-system
2020-05-18T22:39:31.130274Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-05-18T22:39:31.130282Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-05-18T22:39:31.130287Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-05-18T22:39:31.130293Z 0 [ERROR] Failed to initialize builtin plugins.
2020-05-18T22:39:31.130297Z 0 [ERROR] Aborting

2020-05-18T22:39:31.130318Z 0 [Note] Binlog end
2020-05-18T22:39:31.130399Z 0 [Note] Shutting down plugin 'CSV'
2020-05-18T22:39:31.130410Z 0 [Note] Shutting down plugin 'MyISAM'
2020-05-18T22:39:31.131464Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2020-05-18T22:39:32.431097Z 0 [Warning] Could not increase number of max_open_files to more than 50000 (request: 80110)
2020-05-18T22:39:32.431252Z 0 [Warning] Changed limits: table_open_cache: 24945 (requested 40000)
2020-05-18T22:39:32.588494Z 0 [Note] libgovernor.so found
2020-05-18T22:39:32.588515Z 0 [Note] All governors functions found too
2020-05-18T22:39:32.588550Z 0 [Note] Governor connected
2020-05-18T22:39:32.588554Z 0 [Note] All governors lve functions found too
2020-05-18T22:39:32.588858Z 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2020-05-18T22:39:32.588862Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-05-18T22:39:32.588868Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2020-05-18T22:39:32.589848Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-cll-lve) starting as process 1108988 ...
2020-05-18T22:39:32.593669Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-05-18T22:39:32.593689Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-05-18T22:39:32.593693Z 0 [Note] InnoDB: Uses event mutexes
2020-05-18T22:39:32.593698Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-05-18T22:39:32.593701Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-05-18T22:39:32.593704Z 0 [Note] InnoDB: Using Linux native AIO
2020-05-18T22:39:32.593894Z 0 [Note] InnoDB: Number of pools: 1
2020-05-18T22:39:32.593984Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-05-18T22:39:32.620481Z 0 [Warning] InnoDB: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
2020-05-18T22:39:32.620500Z 0 [Warning] InnoDB: io_setup() attempt 1.
2020-05-18T22:39:33.120626Z 0 [Warning] InnoDB: io_setup() attempt 2.
2020-05-18T22:39:33.620740Z 0 [Warning] InnoDB: io_setup() attempt 3.
2020-05-18T22:39:34.120842Z 0 [Warning] InnoDB: io_setup() attempt 4.
2020-05-18T22:39:34.620919Z 0 [Warning] InnoDB: io_setup() attempt 5.
2020-05-18T22:39:35.121029Z 0 [ERROR] InnoDB: io_setup() failed with EAGAIN after 5 attempts.
2020-05-18T22:39:35.121039Z 0 [Note] InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf
2020-05-18T22:39:35.121400Z 0 [ERROR] InnoDB: Cannot initialize AIO sub-system
2020-05-18T22:39:35.121408Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-05-18T22:39:35.121413Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-05-18T22:39:35.121417Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-05-18T22:39:35.121421Z 0 [ERROR] Failed to initialize builtin plugins.
2020-05-18T22:39:35.121423Z 0 [ERROR] Aborting

2020-05-18T22:39:35.121437Z 0 [Note] Binlog end
2020-05-18T22:39:35.121492Z 0 [Note] Shutting down plugin 'CSV'
2020-05-18T22:39:35.121498Z 0 [Note] Shutting down plugin 'MyISAM'
2020-05-18T22:39:35.122266Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2020-05-18T22:39:36.439434Z 0 [Warning] Could not increase number of max_open_files to more than 50000 (request: 80110)
2020-05-18T22:39:36.439590Z 0 [Warning] Changed limits: table_open_cache: 24945 (requested 40000)
2020-05-18T22:39:36.598534Z 0 [Note] libgovernor.so found
2020-05-18T22:39:36.598555Z 0 [Note] All governors functions found too
2020-05-18T22:39:36.598592Z 0 [Note] Governor connected
2020-05-18T22:39:36.598596Z 0 [Note] All governors lve functions found too
2020-05-18T22:39:36.598953Z 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2020-05-18T22:39:36.598957Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-05-18T22:39:36.598963Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2020-05-18T22:39:36.599938Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-cll-lve) starting as process 1109023 ...
2020-05-18T22:39:36.603630Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-05-18T22:39:36.603653Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-05-18T22:39:36.603657Z 0 [Note] InnoDB: Uses event mutexes
2020-05-18T22:39:36.603662Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-05-18T22:39:36.603665Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-05-18T22:39:36.603668Z 0 [Note] InnoDB: Using Linux native AIO
2020-05-18T22:39:36.603841Z 0 [Note] InnoDB: Number of pools: 1
2020-05-18T22:39:36.603944Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-05-18T22:39:36.628651Z 0 [Warning] InnoDB: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
2020-05-18T22:39:36.628672Z 0 [Warning] InnoDB: io_setup() attempt 1.
2020-05-18T22:39:37.128780Z 0 [Warning] InnoDB: io_setup() attempt 2.
2020-05-18T22:39:37.628892Z 0 [Warning] InnoDB: io_setup() attempt 3.
2020-05-18T22:39:38.129025Z 0 [Warning] InnoDB: io_setup() attempt 4.
2020-05-18T22:39:38.629111Z 0 [Warning] InnoDB: io_setup() attempt 5.
2020-05-18T22:39:39.129214Z 0 [ERROR] InnoDB: io_setup() failed with EAGAIN after 5 attempts.
2020-05-18T22:39:39.129227Z 0 [Note] InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf
2020-05-18T22:39:39.129601Z 0 [ERROR] InnoDB: Cannot initialize AIO sub-system
2020-05-18T22:39:39.129609Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-05-18T22:39:39.129615Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-05-18T22:39:39.129619Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-05-18T22:39:39.129623Z 0 [ERROR] Failed to initialize builtin plugins.
2020-05-18T22:39:39.129625Z 0 [ERROR] Aborting

2020-05-18T22:39:39.129644Z 0 [Note] Binlog end
2020-05-18T22:39:39.129689Z 0 [Note] Shutting down plugin 'CSV'
2020-05-18T22:39:39.129695Z 0 [Note] Shutting down plugin 'MyISAM'
2020-05-18T22:39:39.130426Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2020-05-18T22:39:40.428814Z 0 [Warning] Could not increase number of max_open_files to more than 50000 (request: 80110)
2020-05-18T22:39:40.429004Z 0 [Warning] Changed limits: table_open_cache: 24945 (requested 40000)
2020-05-18T22:39:40.588371Z 0 [Note] libgovernor.so found
2020-05-18T22:39:40.588393Z 0 [Note] All governors functions found too
2020-05-18T22:39:40.588427Z 0 [Note] Governor connected
2020-05-18T22:39:40.588431Z 0 [Note] All governors lve functions found too
2020-05-18T22:39:40.588735Z 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2020-05-18T22:39:40.588739Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-05-18T22:39:40.588745Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2020-05-18T22:39:40.589742Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-cll-lve) starting as process 1109081 ...
2020-05-18T22:39:40.593523Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-05-18T22:39:40.593546Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-05-18T22:39:40.593550Z 0 [Note] InnoDB: Uses event mutexes
2020-05-18T22:39:40.593553Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-05-18T22:39:40.593557Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-05-18T22:39:40.593560Z 0 [Note] InnoDB: Using Linux native AIO
2020-05-18T22:39:40.593727Z 0 [Note] InnoDB: Number of pools: 1
2020-05-18T22:39:40.593805Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-05-18T22:39:40.620202Z 0 [Warning] InnoDB: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
2020-05-18T22:39:40.620220Z 0 [Warning] InnoDB: io_setup() attempt 1.
2020-05-18T22:39:41.120328Z 0 [Warning] InnoDB: io_setup() attempt 2.
2020-05-18T22:39:41.620456Z 0 [Warning] InnoDB: io_setup() attempt 3.
2020-05-18T22:39:42.120598Z 0 [Warning] InnoDB: io_setup() attempt 4.
2020-05-18T22:39:42.620718Z 0 [Warning] InnoDB: io_setup() attempt 5.
2020-05-18T22:39:43.120849Z 0 [ERROR] InnoDB: io_setup() failed with EAGAIN after 5 attempts.
2020-05-18T22:39:43.120866Z 0 [Note] InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf
2020-05-18T22:39:43.121237Z 0 [ERROR] InnoDB: Cannot initialize AIO sub-system
2020-05-18T22:39:43.121248Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-05-18T22:39:43.121254Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-05-18T22:39:43.121257Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-05-18T22:39:43.121261Z 0 [ERROR] Failed to initialize builtin plugins.
2020-05-18T22:39:43.121264Z 0 [ERROR] Aborting

2020-05-18T22:39:43.121278Z 0 [Note] Binlog end
2020-05-18T22:39:43.121321Z 0 [Note] Shutting down plugin 'CSV'
2020-05-18T22:39:43.121338Z 0 [Note] Shutting down plugin 'MyISAM'
2020-05-18T22:39:43.122104Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2020-05-18T22:39:44.430237Z 0 [Warning] Could not increase number of max_open_files to more than 50000 (request: 80110)
2020-05-18T22:39:44.430391Z 0 [Warning] Changed limits: table_open_cache: 24945 (requested 40000)
2020-05-18T22:39:44.588306Z 0 [Note] libgovernor.so found
2020-05-18T22:39:44.588328Z 0 [Note] All governors functions found too
2020-05-18T22:39:44.588363Z 0 [Note] Governor connected
2020-05-18T22:39:44.588367Z 0 [Note] All governors lve functions found too
2020-05-18T22:39:44.588673Z 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2020-05-18T22:39:44.588677Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-05-18T22:39:44.588683Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2020-05-18T22:39:44.589669Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-cll-lve) starting as process 1109129 ...
2020-05-18T22:39:44.593358Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-05-18T22:39:44.593382Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-05-18T22:39:44.593385Z 0 [Note] InnoDB: Uses event mutexes
2020-05-18T22:39:44.593388Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-05-18T22:39:44.593391Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-05-18T22:39:44.593394Z 0 [Note] InnoDB: Using Linux native AIO
2020-05-18T22:39:44.593562Z 0 [Note] InnoDB: Number of pools: 1
2020-05-18T22:39:44.593641Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-05-18T22:39:44.620025Z 0 [Warning] InnoDB: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
2020-05-18T22:39:44.620046Z 0 [Warning] InnoDB: io_setup() attempt 1.
2020-05-18T22:39:45.120147Z 0 [Warning] InnoDB: io_setup() attempt 2.
2020-05-18T22:39:45.620277Z 0 [Warning] InnoDB: io_setup() attempt 3.
2020-05-18T22:39:46.120412Z 0 [Warning] InnoDB: io_setup() attempt 4.
2020-05-18T22:39:46.620542Z 0 [Warning] InnoDB: io_setup() attempt 5.
2020-05-18T22:39:47.120657Z 0 [ERROR] InnoDB: io_setup() failed with EAGAIN after 5 attempts.
2020-05-18T22:39:47.120668Z 0 [Note] InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf
2020-05-18T22:39:47.121029Z 0 [ERROR] InnoDB: Cannot initialize AIO sub-system
2020-05-18T22:39:47.121038Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-05-18T22:39:47.121044Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-05-18T22:39:47.121047Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-05-18T22:39:47.121051Z 0 [ERROR] Failed to initialize builtin plugins.
2020-05-18T22:39:47.121054Z 0 [ERROR] Aborting

2020-05-18T22:39:47.121067Z 0 [Note] Binlog end
2020-05-18T22:39:47.121111Z 0 [Note] Shutting down plugin 'CSV'
2020-05-18T22:39:47.121118Z 0 [Note] Shutting down plugin 'MyISAM'
2020-05-18T22:39:47.121808Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2020-05-18T22:39:48.431640Z 0 [Warning] Could not increase number of max_open_files to more than 50000 (request: 80110)
2020-05-18T22:39:48.431795Z 0 [Warning] Changed limits: table_open_cache: 24945 (requested 40000)
2020-05-18T22:39:48.588549Z 0 [Note] libgovernor.so found
2020-05-18T22:39:48.588570Z 0 [Note] All governors functions found too
2020-05-18T22:39:48.588605Z 0 [Note] Governor connected
2020-05-18T22:39:48.588609Z 0 [Note] All governors lve functions found too
2020-05-18T22:39:48.588923Z 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2020-05-18T22:39:48.588928Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-05-18T22:39:48.588933Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2020-05-18T22:39:48.589908Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-cll-lve) starting as process 1109169 ...
2020-05-18T22:39:48.593632Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-05-18T22:39:48.593658Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-05-18T22:39:48.593661Z 0 [Note] InnoDB: Uses event mutexes
2020-05-18T22:39:48.593664Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-05-18T22:39:48.593667Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-05-18T22:39:48.593670Z 0 [Note] InnoDB: Using Linux native AIO
2020-05-18T22:39:48.593843Z 0 [Note] InnoDB: Number of pools: 1
2020-05-18T22:39:48.593948Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-05-18T22:39:48.619840Z 0 [Warning] InnoDB: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
2020-05-18T22:39:48.619863Z 0 [Warning] InnoDB: io_setup() attempt 1.
2020-05-18T22:39:49.119916Z 0 [Warning] InnoDB: io_setup() attempt 2.
2020-05-18T22:39:49.620033Z 0 [Warning] InnoDB: io_setup() attempt 3.
2020-05-18T22:39:50.120145Z 0 [Warning] InnoDB: io_setup() attempt 4.
2020-05-18T22:39:50.620255Z 0 [Warning] InnoDB: io_setup() attempt 5.
2020-05-18T22:39:51.120368Z 0 [ERROR] InnoDB: io_setup() failed with EAGAIN after 5 attempts.
2020-05-18T22:39:51.120379Z 0 [Note] InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf
2020-05-18T22:39:51.120748Z 0 [ERROR] InnoDB: Cannot initialize AIO sub-system
2020-05-18T22:39:51.120757Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-05-18T22:39:51.120763Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-05-18T22:39:51.120767Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-05-18T22:39:51.120771Z 0 [ERROR] Failed to initialize builtin plugins.
2020-05-18T22:39:51.120773Z 0 [ERROR] Aborting

2020-05-18T22:39:51.120788Z 0 [Note] Binlog end
2020-05-18T22:39:51.120831Z 0 [Note] Shutting down plugin 'CSV'
2020-05-18T22:39:51.120847Z 0 [Note] Shutting down plugin 'MyISAM'
2020-05-18T22:39:51.121623Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

@EvangelosBalafoutis
Copy link

EvangelosBalafoutis commented May 21, 2020

Hello Fotis. I removed every line had to do with innodb and it did restarted if this make sence.
I removed

# InnoDB Settings
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 2     # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size         = 2G    # 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          = 16M
innodb_log_file_size            = 512M
innodb_stats_on_metadata        = 0

#innodb_temp_data_file_path     = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file
#innodb_thread_concurrency      = 7     # 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

@fevangelou
Copy link
Author

@EvangelosBalafoutis This is not a solution though... Seeing the logs that you sent, it's most likely you have limits enforced by MySQL Governor (by CloudLinux). For the record, unless your server hosts 300+ accounts, you probably don't need CloudLinux. And hey, if MySQL Governor worked, you wouldn't be looking to optimize MySQL, right? Food for thought...

@EvangelosBalafoutis
Copy link

Thank you for the info Fotis, Ill think about asking the client to remove it.

@EvangelosBalafoutis
Copy link

Or I'll try with completely remove mysql govenor and let you know. Thank you my friend.

Copy link

ghost commented Nov 11, 2020

Cant do anything on wp site without mysql and php-fpm using 60% or higher cpu. What is causing this? Here is my.cnf

[mysql]
port                            = 3306
socket                          = /var/lib/mysql/mysql.sock

[mysqld]
# Required Settings
basedir                         = /usr
bind_address                    = 0.0.0.0 # Change to 0.0.0.0 to allow remote connections
datadir                         = /var/lib/mysql
max_allowed_packet              = 16M
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
performance_schema              = ON
skip-name-resolve
# to pinpoint aborted connection we need this:
log-warnings=2

# InnoDB Settings
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 4     # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size         = 4G    # Use up to 70-80% of RAM
innodb_file_per_table           = On
innodb_flush_log_at_trx_commit  = 2
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 8M
innodb_log_file_size            = 512M
innodb_stats_on_metadata        = 0

#innodb_temp_data_file_path     = ibtmp1:3G:autoextend # Control the maximum size for the ibtmp1 file
innodb_thread_concurrency      = 0    # 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          = 128
innodb_write_io_threads         = 128
innodb_use_native_aio           = 0

# MyISAM Settings
query_cache_limit               = 4M    # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
query_cache_size                = 0   # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
query_cache_type                = 0     # Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x

#key_buffer_size                 = 32M   # UPD

low_priority_updates            = 1
concurrent_insert               = 2

# Connection Settings
max_connections                 = 200   # UPD - Important: high no. of connections = more RAM consumption

back_log                        = 512
thread_cache_size               = 100
thread_stack                    = 192K

interactive_timeout             = 300
wait_timeout                    = 300

# For MySQL 5.7+ only (disabled by default)
#max_execution_time             = 30000 # 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             = 30    # 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
#join_buffer_size                = 4M    # UPD
#read_buffer_size                = 3M    # UPD
#read_rnd_buffer_size            = 4M    # UPD
#sort_buffer_size                = 4M    # UPD

# Table Settings
# In systemd managed systems like 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 (when it's supported in cPanel), 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          = 40000 # UPD
table_open_cache                = 40000 # UPD
open_files_limit                = 60000 # UPD - This can be 2x to 3x the table_open_cache value or match the system's
                                        # open files limit usually set in /etc/sysctl.conf or /etc/security/limits.conf
                                        # In systemd managed systems this limit must also be set in:
                                        # /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+) and
                                        # /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB)

max_heap_table_size             = 126M
tmp_table_size                  = 128M

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

# Logging
log_error                       = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes   = 1
long_query_time                 = 5
slow_query_log                  = 0     # Disabled for production
slow_query_log_file             = /var/lib/mysql/mysql_slow.log

[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              = 64M

@fevangelou
Copy link
Author

@darnellkeithj It's really way off. You are increasing some variables to irrational values (e.g. max_connections, innodb_*_io_threads -which should not exceed 64- and others) and commenting out variables (e,g, *_buffer_size) which are important. You should seek professional performance auditing (which extends beyond MySQL/MariaDB). If you need my services you can always email me at engintron [at] gmail [dot] com.

@talk2rajasimman
Copy link

I have Intel Xeon E3-1230 v2 - 3.3 GHz - 4 core(s) 8 threads.
RAM: 16GB - DDR3

Hello @fevangelou can you suggest me the my.cnf file. Really i am confused lot about this configuration.

@karimrattani
Copy link

Thanks for the config, I had to change below config otherwise MySQL failed to restart
innodb_read_io_threads = 40
innodb_write_io_threads = 40

@locksmithunit
Copy link

locksmithunit commented Mar 5, 2021

is because you have 40 cores 4M 8C like mine.
i did that 16. just in case, is not good to read everything from the DISK anyway.

the only thing I little bit confused about.
is this:
innodb_io_capacity = 1000

https://dev.mysql.com/doc/refman/8.0/en/innodb-configuring-io-capacity.html

this very good but it depends on your drive.
in this case, I dont know what they talking about if you have Linux.
they recommended 1000 as well.

innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 1000

This can be very good to cPanel on a VPS cloud 4M 8C
BUT YOU MUST SPEAK WITH YOUR HOSTING ASK THEM IF IS SSD OR SATA 2 WITH 7200RPM

If they hosting with SSD (Must of the VPS hosting with SSD)
so need to uncomment the io_capacitiy = 1000

This 16M 8C is not what must of the hosting selling today...
I think is be better to improve the old version of the 4M 8C

@fevangelou
Copy link
Author

Updated with better defaults and new performance related additions for MySQL 8.

@locksmithunit
Copy link

locksmithunit commented Nov 13, 2021

MariaDB 10.5?

I try and adjust the file already but i was needed to change it and comment out a lot from the configuration.
btw MariaDB 10.5 by default force you to put socket_unix=off
user name changed to MariaDB and not MySQL...
:(

can you do one smaller and matching MariaDB 10.5? (BTW CPANEL FORCE CLIENTS INSTALL IT AND UPGRADE TO 10.5)
CPANEL ALREADY ELECTED MARIADB 10.6 EXPERIMENTAL,

It is a matter of time everybody will move these versions... :/

@fevangelou
Copy link
Author

Config updated with new tool references for DB diagnostics, minor changes in defaults and additional details in open_files_limit.

@raramuridesign
Copy link

@fevangelou Thank you. This has been a great improvement on our servers. Appreciated

@fevangelou
Copy link
Author

@raramuridesign You're most welcome Matthew!

@fevangelou
Copy link
Author

New version of the config released.

Binary logging is now disabled by default, "innodb_sort_buffer_size" has been bumped to 4M as a better default value, "default_authentication_plugin" is referenced (but commented by default - read the comments there), new performance related comments added in the buffers section.

@dandidan2
Copy link

Can i hire you for my.cnf optimize for a server with 128gb ram?

@fevangelou
Copy link
Author

@soulkeeperxx
Copy link

@dandidan2 Sure - contact details are here: https://github.com/engintron/engintron#commercial-support--server-optimization-services

Can I use your help to optimize my WHM ? I have a VDS with high specs and I need some optimization :D couldnt find your contact detail as your site is down.

@fevangelou
Copy link
Author

Nothing was ever down... The URL above ALSO links to: "...simply email us at: engintron [at] gmail [dot] com"

@raramuridesign
Copy link

@fevangelou any chance you could look at a version of this for a native mariadb install thats not on whm/cpanel?

@asciixster
Copy link

@fevangelou any change you can update this configs to 8.0.32?
Since we have "forced" to move to almalinux and 8.0.32 is enforced? thk you

@fevangelou
Copy link
Author

@raramuridesign
Here you go Matthew: https://gist.github.com/fevangelou/fb72f36bbe333e059b66

@asciixster
The config works just fine with MySQL 8 on Almalinux.

To both, there are distinct comments for any differences between MySQL and MariaDB. Read them please. :)

@siamnews
Copy link

siamnews commented Mar 22, 2023 via email

@raramuridesign
Copy link

@fevangelou Thanks ;-)

@fevangelou
Copy link
Author

@siamnews Not exactly. You can define variables with both dashes and underscores.

@theozsnowman
Copy link

ive tried this config on a Centos 7 server running Maria 10.6.12

are the following warnings normal on restart?

Apr 12 15:41:25 xxx.server.com systemd[1]: Starting MariaDB 10.6.12 database server...
Apr 12 15:41:25 xxx.server.com mariadbd[8812]: 2023-04-12 15:41:25 0 [Warning] Could not increase number of max_open_files to more than 40000 (request: 640139)
Apr 12 15:41:25 xxx.server.com mariadbd[8812]: 2023-04-12 15:41:25 0 [Warning] Changed limits: max_open_files: 40000 max_connections: 100 (was 100) table_cache: 19935 (was 40000)

@fevangelou
Copy link
Author

@theozsnowman Read the comment next to "open_files_limit" in the config.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment