[mysql] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] sql_mode = # Required Settings basedir = /usr bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote servers to connect to this server's # database instance event_scheduler = 1 datadir = /var/lib/mysql max_allowed_packet = 512M max_connect_errors = 100 pid_file = /var/lib/mysql/mysql.pid port = 3306 skip_external_locking socket = /var/lib/mysql/mysql.sock tmpdir = /home/mysqltmp user = mysql # InnoDB Settings default_storage_engine = InnoDB innodb_buffer_pool_instances = 22 # Use 1 instance per 1GB of InnoDB pool size innodb_buffer_pool_size = 22G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0 innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 5120M innodb_log_file_size = 2G innodb_thread_concurrency = 8 # 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 = 8 innodb_write_io_threads = 8 # MyISAM Settings query_cache_limit = 10M # UPD query_cache_size = 50M # UPD query_cache_type = DEMAND key_buffer_size = 4G # UPD low_priority_updates = 1 concurrent_insert = 1 # Connection Settings max_connections = 700 # UPD max_user_connections = 40 back_log = 512 thread_cache_size = 128 thread_stack = 292K interactive_timeout = 28800 wait_timeout = 30 #net_read_timeout = 30 #net_write_timeout = 60 # Buffer Settings join_buffer_size = 1G # UPD read_buffer_size = 4M # UPD read_rnd_buffer_size = 4M # UPD sort_buffer_size = 32M # 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 = 32252 # UPD stored_program_cache = 8000 table_open_cache = 370000 # UPD open_files_limit = 65535 # 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 = 5120M tmp_table_size = 5120M # Search Settings ft_min_word_len = 3 # Minimum length of words to be indexed for search results # Logging log_error = /var/log/mysqld.log log_queries_not_using_indexes = 1 long_query_time = 5 slow_query_log = 0 # Disabled for production slow_query_log_file = /var/log/mysql_slow.log #skip-name-resolve = 0 performance_schema = OFF [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