MySQL optimizations

optimizations.conf

[server]

# on SSD do we need it?
tmpdir=/ram/mysql
tmp_table_size=2K

# runtime edit:
#set global net_buffer_length=1000000;
#set global max_allowed_packet=1000000000;
max_allowed_packet=100M

table_cache=4096

# When making adjustments, make tmp_table_size/max_heap_table_size equal
tmp_table_size=512MB
max_heap_table_size=512MB

#join_buffer_size - 1M for 1GB, 2M for 2GB, 4M for 4GB
join_buffer_size=5M

# if dedicated mysql, make it 50-80% of ram
innodb_buffer_pool_size=16384MB

innodb_log_file_size=1GB

# helps changing the innodb log file size before changing the size, run
# SET GLOBAL innodb_fast_shutdown = 0
# mysql stop
# change innodb_log_file_size
# mysql start
innodb_fast_shutdown=0

innodb_flush_method=O_DIRECT
#innodb_flush_neighbors=0

#innodb_io_capacity=1000
#innodb_io_capacity_max=3000

# see utf8.conf
#character-set-server=utf8
#collation-server=utf8_general_ci

skip-name_resolve
max-connect-errors=100000
max-connections=500

utf8.conf

[client]
default-character-set=UTF-8
    
[mysql]
default-character-set=UTF-8
    
[mysqld]
character-set-client-handshake = false #force encoding to uft8
character-set-server=UTF-8
collation-server=UTF-8_general_ci
    
[mysqld_safe]
default-character-set=UTF-8