Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Optimized my.cnf configuration for MySQL/MariaSQL (on Ubuntu, CentOS etc. servers)
# Optimized my.cnf configuration for MySQL/MariaSQL
#
# 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*
# $ chown -R mysql:mysql /var/lib/mysql
# $ service mysql restart
#
# or use the shorthand command:
# $ rm -rvf /var/lib/mysql/ib_logfile*; chown -R mysql:mysql /var/lib/mysql; service mysql restart
#
# 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 service:
#
# $ service mysql restart
#
# 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 service:
#
# $ service mysql restart
#
#
# ~ FIN ~
[mysql]
port = 3306
socket = /var/run/mysqld/mysqld.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/run/mysqld/mysqld.pid
port = 3306
skip_external_locking
skip_name_resolve
socket = /var/run/mysqld/mysqld.sock
# 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
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
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 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 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
@calvinthefreak

This comment has been minimized.

Copy link

@calvinthefreak calvinthefreak commented Jul 6, 2019

Worked very well on a 2G 2VCore Machine in my ProxMox stack (Its one of my Dev machines)

@progrdk

This comment has been minimized.

Copy link

@progrdk progrdk commented Jul 17, 2019

query_* under MyISAM block caused mysql to be unable to start due to "unknown parameter" (sorry, haven't remembered exact wording) ERROR.
Commenting these 3 lines solved the problem.

# MyISAM Settings
query_cache_limit               = 4M    # UPD
query_cache_size                = 48M   # UPD
query_cache_type                = 1

Deprecated variables in new MySQL version after 8.0.3
2019-07-18_14-37-57_small

MySQL 8.0.16 on Ubuntu Server 18.10

@beppe9000

This comment has been minimized.

Copy link

@beppe9000 beppe9000 commented Jan 21, 2020

is this good for mysql 8.0.19 ?

@fevangelou

This comment has been minimized.

Copy link
Owner Author

@fevangelou fevangelou commented Jan 22, 2020

@beppe9000 Just remove all 3 query_cache_* lines and it should be just fine on MySQL 8.x.

@fevangelou

This comment has been minimized.

Copy link
Owner Author

@fevangelou fevangelou commented Jan 22, 2020

Configuration updated in Jan 2020 with new options and compatibility hints.

@beppe9000

This comment has been minimized.

Copy link

@beppe9000 beppe9000 commented Jan 22, 2020

Thanks! Do you recommend any particular change for docker ? In my case it's a mysql8 container with cpu limit 4 (of 6 total) and 8gb of ram shared with the parent system, which also has cpanel installed with its own dns, email, apache, sql, php stack and default settings?

@fevangelou

This comment has been minimized.

Copy link
Owner Author

@fevangelou fevangelou commented Jan 22, 2020

You should be fine with the default values.

@beppe9000

This comment has been minimized.

Copy link

@beppe9000 beppe9000 commented Jan 22, 2020

Thanks again!

@Theolodewijk

This comment has been minimized.

Copy link

@Theolodewijk Theolodewijk commented Apr 7, 2020

do I add ' local-infile=0 ' ?

@webjive

This comment has been minimized.

Copy link

@webjive webjive commented Apr 7, 2020

Any suggested modifications for a server 32 processors, 32gb of ram, Raid0 attached SATA and less than 300 hosted accounts?

MySQL is constantly using high amounts of CPU and 42% of server memory. Sounds like our config is way out of whack?

@fevangelou

This comment has been minimized.

Copy link
Owner Author

@fevangelou fevangelou commented Apr 7, 2020

@Theolodewijk Not needed.

@webjive Assuming you use various CMSs, try these additional changes...

innodb_buffer_pool_instances  = 16
innodb_buffer_pool_size = 16G
innodb_log_file_size = 1G
innodb_thread_concurrency = 28
@webjive

This comment has been minimized.

Copy link

@webjive webjive commented Apr 7, 2020

@Theolodewijk
Thanks, adjustments made. I'll give it a try when usage dies down a bit. I'll report back ;)

@webjive

This comment has been minimized.

Copy link

@webjive webjive commented Apr 7, 2020

@Theolodewijk Just a heads up, if you enable skip-name-resolve, cPanel restores will throw a lot of errors. Just finished troubleshooting that with cPanel last month.

@fevangelou

This comment has been minimized.

Copy link
Owner Author

@fevangelou fevangelou commented Apr 7, 2020

@webjive "skip_name_resolve " was never an issue on my end.

@webjive

This comment has been minimized.

Copy link

@webjive webjive commented Apr 7, 2020

@fevangelou Noted! I'm going to run with that enabled again, find a site and restore and see what that does. The only effect it seemed to have was on cPanel accounts with more than one DB and one user (this account had 10). During the restore, none of the DB users were restored but, the DB's and sites were.

@webjive

This comment has been minimized.

Copy link

@webjive webjive commented Apr 7, 2020

@fevangeou - This is cPanel supports response for that account.

Hello,

The error you have reported frequently occurs when the following MySQL variable is enabled:

skip-name-resolve

I see that this variable has been abled via your MySQL configuration (/etc/my.cnf).

@Theolodewijk

This comment has been minimized.

Copy link

@Theolodewijk Theolodewijk commented Apr 7, 2020

this has also been tested with directadmin ?

@webjive

This comment has been minimized.

Copy link

@webjive webjive commented Apr 7, 2020

@Theolodewijk - cPanel, DA shouldn't matter. MySQL is the same across platforms.

@webjive

This comment has been minimized.

Copy link

@webjive webjive commented Apr 8, 2020

Reporting back that using this config with some tweaks is working great. Please keep this project rolling! Always looking for better MySQL performance. Especially for those of us still on 5.6 for shared hosting. Too scared to upgrade to 5.7 with Joomla 1.x sites still running. YIKES

@fevangelou

This comment has been minimized.

Copy link
Owner Author

@fevangelou fevangelou commented Apr 8, 2020

Actually, as long as you can adapt your sql_mode (included, but commented) to be less strict (e.g. empty date fields), Joomla 1.5.x will work just fine with MySQL 5.7. I would also recommend fine tuning your query_cache_size to a higher value, e.g. 96M (but don't go above 128M), measure your connections (lots of folks do the mistake of using crazy numbers there and they don't realize each connection is multiplied by all the buffer & cache values), adapt your read_rnd_buffer_size (e.g. to 6M) & sort_buffer_size (e.g. to 8M) but don't overdo it.

Use MySQL Tuner or https://github.com/RootService/tuning-primer to see max RAM usage by your current settings first.

@webjive

This comment has been minimized.

Copy link

@webjive webjive commented Apr 8, 2020

Thanks for the heads up. My concern is more with Joomla 1.0 and not 1.5. Never tested that combo. Still have about 2-3 of those sites running. As for query cache size, thanks again for the input on that!! We have some moodle sites which can be absolute resource hogs. We also run CloudLinux with their LVE manager and have to open up PMEM on some of those accounts.

@fevangelou

This comment has been minimized.

Copy link
Owner Author

@fevangelou fevangelou commented Apr 8, 2020

Joomla 1.0? Brave man :)

But they could work if they've worked with MySQL 5.6 all this time.

@webjive

This comment has been minimized.

Copy link

@webjive webjive commented Apr 8, 2020

LOL

I guess its time to maybe bite the bullet and upgrade MySQL via WHM. We even have 1 MODX (super legacy on PHP 5.2) running. Now that I think of it, that scares me maybe a bit more!

Any thoughts before pulling the trigger on that one? BTW we run CSF, COMODO WAF, CXS and some other utilities to keep things safe and watch Imunify360 as a possible path. Not sold on that one yet. CSF has served us well.

Any my.cnf changes you might suggest to provide backward compatibility for those old creeky sites?

@beppe9000

This comment has been minimized.

Copy link

@beppe9000 beppe9000 commented Apr 8, 2020

I think cpanel expects skip-name-resolve to be disabled for some features to work properly

@fevangelou

This comment has been minimized.

Copy link
Owner Author

@fevangelou fevangelou commented Apr 8, 2020

@webjive Comment out the "sql_mode" line and test the old sites after upgrading.

@webjive

This comment has been minimized.

Copy link

@webjive webjive commented May 11, 2020

Quick question, looking to upgrade from MySQL 5.6 to 5.7. Without any further tuning that I have done base on this GH list, will we notice a performance boost? Right now MySQL seems to account for most of our disk IO on our shared hosting server.

Thoughts?

Also, MySQL 8 is about to be rolled out from cPanel. Not sure older Joomla and WP sites are ready for that?

@fevangelou

This comment has been minimized.

Copy link
Owner Author

@fevangelou fevangelou commented May 12, 2020

@webjive Yes on 5.7. As for 8, just make sure you remove the query_cache_* lines.

@tallesairan

This comment has been minimized.

Copy link

@tallesairan tallesairan commented May 26, 2020

Hi good day, please help @fevangelou
I have a server with 96 ram and 24 processor cores, I'm migrating for now, and I'm looking for a mysql 8 configuration for constant reading and writing, I accept to reserve up to 50% of the ram for mysql, but I can't find anything that can help me. help with the calculation :(

@luismisanchez

This comment has been minimized.

Copy link

@luismisanchez luismisanchez commented Jun 25, 2020

Just to say thanks @fevangelou. Saved my day.

10.1.44-MariaDB-0ubuntu0.18.04.1

@fevangelou

This comment has been minimized.

Copy link
Owner Author

@fevangelou fevangelou commented Jun 26, 2020

You're welcome @luismisanchez :)

@majimboo

This comment has been minimized.

Copy link

@majimboo majimboo commented Aug 2, 2020

What should I change on this configuration if my ram is 64gb and the server is exclusively for mysql use? @fevangelou

Tried using MySQLTuner on this config file and it gave me the following recommendation:

default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 23     # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size         = 23G    # 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            = 2G
innodb_stats_on_metadata        = 0
General recommendations:
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    innodb_log_file_size should be (=2G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
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.