Optimize my.cnf MySQL

Optimize my.cnf MySQL



Just add the following lines in "/etc/my.cnf" or comet it out

# to bound from global access
bind-address=127.0.0.1
# host_cache_size=200

net_write_timeout = 28800
net_read_timeout = 28800
max_connections = 500
wait_timeout = 300
interactive_timeout = 120
connect_timeout = 120
query_cache_type = 1
table_open_cache = 4096
key_buffer_size = 256M


query_cache_type = ON
query_cache_size = 2G
query_cache_min_res_unit = 64M
query_cache_limit = 128M


innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 16
innodb_log_file_size = 4G
innodb_log_buffer_size = 1024M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

tmp_table_size = 64M

max_heap_table_size = 64M

join_buffer_size = 64M

sort_buffer_size = 4M

read_rnd_buffer_size = 1M

read_buffer_size = 1M

max_allowed_packet=128M

max_connect_errors=1000000

tmp_table_size=32M

max_heap_table_size=128M

After adding these lines, just save your file and exit using :wq

and restart the mysql service

# service mysqld restart