[mysqld]
binlog_cache_size = 64K
thread_stack = 256K
join_buffer_size = 2048K
query_cache_type = 1 // query cache can actually slow your database down, the time required to get query cache and check permissions can take longer than just running a new query, it is also not supported in MySQL 8.0.3+
max_heap_table_size = 384M
port = 3306
socket = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 256M // this only applies to MyISAM tables, remove if you have no MyISAM tables
max_allowed_packet = 100G // this is crazy high, since you are already distinguishing a separate value for mysqldump below this value should be something less than what is needed to dump your entire database
table_open_cache = 192
sort_buffer_size = 768K // this may be a little low, check your sort merge passes for your database; a setting of "1M" may help
net_buffer_length = 4K // you are likely better off removing this, this database will typically be better at regulating the connection buffer itself
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 32M // this is only used when rebuilding indexes for MyISAM tables, can be removed if you have no MyISAM tables
thread_cache_size = 96
query_cache_size = 128M // see query_cache_type note above
tmp_table_size = 384M //
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#skip-name-resolve
max_connections = 200
max_connect_errors = 100
open_files_limit = 65535
#log-bin=mysql-bin
#binlog_format=mixed
server-id = 1
expire_logs_days = 2
#slow_query_log=1 // leave the slow query log off (=0) unless you are actively diagnosing something, otherwise it will just waste resources
#slow-query-log-file=/www/server/data/mysql-slow.log
#long_query_time=3
#log_queries_not_using_indexes=on
innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 384M // this is low, look at your databases's InnoDB stats, increasing to "768M" will likely perform much better
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1 // setting to "2" reduces log flushing to disk to only once per second, this is more than safe for a forum, if you were a bank "2" would not be the recommended setting
innodb_lock_wait_timeout = 50 // this is the default setting and can be removed
innodb_max_dirty_pages_pct = 90 // this is the default setting and can be removed
innodb_read_io_threads = 4 // this is the default setting and can be removed
innodb_write_io_threads = 4 // this is the default setting and can be removed
[mysqldump]
quick
max_allowed_packet = 500M
[mysql]
no-auto-rehash
[myisamchk] // if your're not running MyISAM tables this section can be removed
key_buffer_size = 128M
sort_buffer_size = 2M
read_buffer = 2M
write_buffer = 2M
