MySQL

Mysql optimization

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