Experience with MySQL (iii) Performance and management optimization

Based on

/application/search/mysql/mysql-5.5.28/support-files/my-innodb-heavy-4g.cnf

Two times optimization

"Delete--if you do not use master-slave sync" annotation, remove unwanted log files

# Log-bin=mysql-bin

# binlog_format=mixed

"Modify Link"

# Maximum link value increased from 100 to 512

Max_connections = 512

# Error link changed from 10 to 100

max_connect_errors = 100

"* Modify the number of threads"

# 2-4 times the number of CPU logical threads, we carry the CPU for 8 core x2

Thread_concurrency = 32

"Set default Engine"

Default-storage-engine=innodb

"* Modify InnoDB Memory"

# to 16G, default is 128M, maximum is set to 80% of server memory

Innodb_buffer_pool_size = 16G

"* Modify things log File"

# refresh every second log and disk set to 0, real-time refresh log files and disk data set to 1, each time to write to the log, refresh disk settings per second 2

Innodb_flush_log_at_trx_commit = 0

# log file changed from 256M to 1G

Innodb_log_file_size = 1G

"Modify log file location, PID location" to facilitate cold copy of physical files

# PID position, increased

Pid-file=/application/search/mysql/mysql-5.5.30/tmp/mysqld.pid

# binlog position,/data0/search/mysql_binlog directory to be manually built in advance

Log-bin=/data0/search/mysql/log/mysql-bin

# define the location and file name of the relay log: copy log

Relay_log=/data0/search/mysql/log/mysql-relay-bin

# Slow query log location, increase

Slow_query_log

Slow-query-log-file=/data0/search/mysql/log/slow_query.log

Long_query_time = 2

# causes replicated events from the server to be logged in its own binary log

Log_slave_updates=1

Log Reference Document

Http://blog.sina.com.cn/s/blog_406127500100pvar.html

"Encoding and data Files"

[MySQL]

Default-character-set=utf8

[Mysqld]

DataDir =/data0/search/mysql/data

Character-set-server=utf8

"*innodb Separate data Files"