Skip to content

Instantly share code, notes, and snippets.

@fevangelou fevangelou/my.cnf
Last active Jan 22, 2020

Embed
What would you like to do?
Optimized my.cnf configuration for MySQL/MariaSQL (on cPanel/WHM servers)
# === Optimized my.cnf configuration for MySQL/MariaSQL on cPanel/WHM servers ===
#
# by Fotis Evangelou, developer of Engintron (engintron.com)
#
# ~ Updated January 2020 ~
#
#
# The settings provided below are a starting point for a 2GB - 4GB RAM server with 2-4 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/RootService/tuning-primer (supports MySQL up to v5.7)
#
# 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
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
# 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 = 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
# 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 = 64M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
query_cache_type = 1 # 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 = 100 # UPD - Important: high no. of connections = more 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 = 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 = 128M
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
@Friends4U

This comment has been minimized.

Copy link

Friends4U commented Aug 23, 2016

What should I use with 16GB ram and 8 cores?

@brinsleyparagao

This comment has been minimized.

Copy link

brinsleyparagao commented Nov 9, 2016

whats better for: 8 cores / 32g ram / ssd / 1gbps

@ntorga

This comment has been minimized.

Copy link

ntorga commented Nov 11, 2016

Nice, but... I think you should consider study more the follow options: "innodb-flush-log-at-trx-commit", "query_cache_limit", "max-heap-table-size", "tmp-table-size", "innodb-log-file-size" & [mysqldump] -> "max-allowed-packet".

@fevangelou

This comment has been minimized.

Copy link
Owner Author

fevangelou commented Dec 2, 2016

@ntorga I have, these options are not there randomly...

@Nekkam

This comment has been minimized.

Copy link

Nekkam commented Jan 27, 2017

Great config Fotis i have only one think to comment
query_cache_type = 1 is good only if you use MyISAM . if you use InnoDB query cache is not required ( query_cache_type = 0 ). Moreover query cache harms the speed of overall performance. So it depents on your DB type.

@fevangelou

This comment has been minimized.

Copy link
Owner Author

fevangelou commented Jul 15, 2017

@Nekkam Actually, most of the times you'll have lots of MyISAM tables on your DBs. And for that reason, using query cache works miracles. Confirm with Munin and you'll see what I mean. Those preaching that query cache should be off usually refer to servers with 1-2 websites fully controlled and able to work on InnoDB. That's not the case with a typical cPanel hosting scenario.

@chrismfz

This comment has been minimized.

Copy link

chrismfz commented Jul 16, 2017

No point of having "skip-name-resolve" when you already have bind address only localhost. Especially with cPanel, or having a dedicated mysql box.

https://documentation.cpanel.net/pages/viewpage.action?pageId=1507383
"Do not use the skip-name-resolve option in your server's MySQL configuration. This option can cause problems on any server. It will create more problems on remote MySQL servers, during account transfers and restorations, and with phpMyAdmin."

@bscreative

This comment has been minimized.

Copy link

bscreative commented Jul 22, 2017

Do these changes also apply to MariaDB?

@screege

This comment has been minimized.

Copy link

screege commented Nov 11, 2017

Do these changes also apply to MariaDB?

Also does anyone recommend changing the swappines to 10 (default is 60)?

@ciscospirit

This comment has been minimized.

Copy link

ciscospirit commented Feb 24, 2018

Hello,

i have the same question like @screege
Are this settings also valid for mariaDB 10.2 ?

@timeassistant

This comment has been minimized.

Copy link

timeassistant commented Mar 16, 2018

@screege and @ciscospirit yes this should work for Maria DB, if there are any issues it will be logged or shown to you upon restarting the service. - Take a backup

Also yes setting swappines to 10 would be an advantage. It means your server would prefer to use physical RAM over SWAP (until you run out of RAM) just make sure you have plenty swap configured as a backup so you dont crash your server!

@baowesome

This comment has been minimized.

Copy link

baowesome commented Apr 3, 2018

@fevangelou can you help me optimizing my vps linux server please sorry i'm new!

@fevangelou

This comment has been minimized.

Copy link
Owner Author

fevangelou commented Apr 28, 2018

Updated in April 2018 to include various fixes & improvements.

@codep0et

This comment has been minimized.

Copy link

codep0et commented Apr 30, 2018

Hello. If I have a 8 core 32 GB RAM server. Should I simply multiply the values? Since the values are for a 4 core 4gb RAM starting point

Thanks

@frilogg

This comment has been minimized.

Copy link

frilogg commented May 1, 2018

Please make a version for a server with 32 gb ram also

@Nirjonadda

This comment has been minimized.

Copy link

Nirjonadda commented May 2, 2018

@fevangelou Please what you are recommended for 8 CPUs with 16 GB Ram?

@shigri20

This comment has been minimized.

Copy link

shigri20 commented Jul 9, 2018

I have 10 (hybrid) cpu , 32g ram ... i just used above setting with minor changing in buffer , after that i face bellow mentioned issues :
1- Error failed to start application : could not connect to MySQL server
2- i'm using joomla CMS and cannot login into admin panel
Any suggestion or guide please

@fevangelou

This comment has been minimized.

Copy link
Owner Author

fevangelou commented Jul 9, 2018

@shigri20 This means that the connection to the database failed. Make sure the service is up and read the comments at the top of the file very carefully. You probable need to delete your log files so that MySQL can restart with the new configuration.

@malikzubi

This comment has been minimized.

Copy link

malikzubi commented Sep 27, 2018

Hi,
I have 32 Core Cpu and 64gb Ram, Please suggest me the settings accordingly.

@fevangelou

This comment has been minimized.

Copy link
Owner Author

fevangelou commented Sep 27, 2018

@malikzubi it depends on many factors e.g. how much RAM you need for other apps, what kind of/how fast storage you have, what type of web app, can you utilize Memcached etc. etc. If you need professional consultation you can always contact me here: https://github.com/engintron/engintron#i-need-commercial-support---do-you-offer-such-services

@siamnews

This comment has been minimized.

Copy link

siamnews commented Feb 14, 2019

there is typo it's bind-adress and not bind_adress on line 70. If you don't correct it your mysql server is open to the wicked outside word :)

@fevangelou

This comment has been minimized.

Copy link
Owner Author

fevangelou commented Feb 14, 2019

@siamnews It's actually valid https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_bind-address and ironically to your comment it's "address", not "adress".

@mustafaerdinc

This comment has been minimized.

Copy link

mustafaerdinc commented Mar 26, 2019

@fevangelou I have Maria DB 10.3.3 installed. I got this error:

Startup Log:
Mar 26 21:58:54 server.derisnameserver.com mysqld[29223]: 2019-03-26 21:58:54 0 [Warning] Could not increase number of max_open_files to more than 10000 (request: 40135) Mar 26 21:58:54 server.derisnameserver.com mysqld[29223]: 2019-03-26 21:58:54 0 [Warning] Changed limits: max_open_files: 10000 max_connections: 100 (was 100) table_cache: 4935 (was 20000)

and
Log Messages
Mar 26 21:58:54 server mysqld: 2019-03-26 21:58:54 0 [Warning] Changed limits: max_open_files: 10000 max_connections: 100 (was 100) table_cache: 4935 (was 20000) Mar 26 21:58:54 server mysqld: 2019-03-26 21:58:54 0 [Warning] Could not increase number of max_open_files to more than 10000 (request: 40135)

I have created the file blowing:

mkdir /etc/systemd/system/mysqld.service.d

and added:

[Service] LimitNOFILE = 65535

Warning stills there. What may cause this?

@fevangelou

This comment has been minimized.

Copy link
Owner Author

fevangelou commented Mar 26, 2019

@mustafaerdinc If you have MariaDB, you should do:

mkdir -p /etc/systemd/system/mariadb.service.d
touch /etc/systemd/system/mariadb.service.d/override.conf
cat > "/etc/systemd/system/mariadb.service.d/override.conf" <<EOF
[Service]
LimitNOFILE=65535
EOF
systemctl daemon-reload
@fevangelou

This comment has been minimized.

Copy link
Owner Author

fevangelou commented Jan 16, 2020

The config has been updated.

Additionally, someone asked what would the right tweaks be for a 64GB RAM / 20 CPU server.

I would adjust the following (from the current config):

innodb_buffer_pool_instances    = 24
innodb_buffer_pool_size         = 24G
innodb_log_file_size            = 1G
innodb_thread_concurrency       = 16
query_cache_limit               = 4M
query_cache_size                = 96M
key_buffer_size                 = 64M

But before doing anything, run a script like https://github.com/major/MySQLTuner-perl or https://github.com/RootService/tuning-primer to see your current usage. Also run the scripts 1hr after the optimization & 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 get you a better view of how MySQL/MariaDB performs.

You'll also want to DISABLE these 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?
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.