-
-
Save gautiermichelin/2c2b3cd0651d6da0630596722a93af22 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 | |
# | |
# by Fotis Evangelou, developer of Engintron (engintron.com) | |
# Edited by zabustak | |
# Edited by GautierMichelin, idéesculture | |
# | |
# 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) | |
# | |
# 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 | |
# | |
# $ rm -rvf /var/lib/mysql/ib_logfile*; chown -R mysql:mysql /var/lib/mysql; service mysql restart | |
# ~ FIN ~ | |
[mysql] | |
port = 3306 | |
socket = /var/run/mysqld/mysqld.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 = 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 = 4 # Use 1 instance per 1GB of InnoDB pool size | |
innodb_buffer_pool_size = 8G # 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 = 6 # 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment