From MySQL Large Configuration.
MySQL Large Original
# The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8
After Optimization
# The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer_size = 384M max_allowed_packet = 1M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8
Other ideas…
mysql optimize
innodb_buffer_pool_instances = 2
reduced the number of max connections to 500 (which is still high)
syn error
adjusting /proc/sys/net/ipv4/tcp_max_syn_backlog
nf_conntrack: table full, dropping packet.
network and ulimit settings
number of connections in time_wait – how to contrl:
set both tcp_tw_recycle and lowered tcp_fin_timeout to 30