-
-
Save AjmalPraveeN/1193ec661c945c72cd36b8ef7de36224 to your computer and use it in GitHub Desktop.
Optimized my.cnf configuration for MySQL/MariaSQL (on Ubuntu, CentOS etc. servers)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# === Optimized my.cnf configuration for MySQL/MariaSQL (on Ubuntu, CentOS etc. servers) === | |
# | |
# by Fotis Evangelou, developer of Engintron (engintron.com) | |
# | |
# ~ Updated February 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/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 | |
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 | |
# === 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 = 1 | |
innodb_flush_log_at_trx_commit = 0 | |
innodb_flush_method = O_DIRECT | |
innodb_log_buffer_size = 16M | |
innodb_log_file_size = 1G | |
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 | |
#innodb_io_capacity = 1000 # Max is 2000 | |
# === 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 = 32M # 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 = 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 === | |
innodb_sort_buffer_size = 2M # UPD | |
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 = 512M |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment