MySQL Performance maximization

[Client]

#passwd = your_passwd

Port = 3306

Socket =/data/mysql/mysql.sock

[Mysqld]

#global settings

Port = 3306

Socket =/data/mysql/mysql.sock

Basedir=/usr/local/mysql

Tmpdir=/data/mysql

Datadir=/data/mysql

Pid-file=/data/mysql/rongzhong.pid

#跳过外部锁定 only for MyISAM.

Skip-external-locking

#禁用tcp socket connection, only use Unix/linux socket connection

Skip_networking

#禁用DNS解析, improve connection speed

Skip_name_resolve

#跳过授权表, use when you forget the root password

#skip-grant-tables

#默认存储引擎

#default-storage-engine=myisam/innodb

#默认字符集

Character_set_server=utf8

#最大连接数

max_connections=20480

#最大单用户连接数

max_user_connections=1024

#服务器关闭一个交互连接前等待的秒数, default 28800

interactive_timeout=7200

#线程缓存数 to create a new connection directly using an idle connection,1g-> 8 2g-> 3g-> >3G-> 64

#短连接较多的情况下可以适当增加该值

Thread_cache_size=64

#单个线程 (connection) data sorting (Eg:order/group by) when the buffer, the connection is created one-time allocation, the default 2M, when sort_merge_passes large can increase the value

Sort_buffer_size=2m

#对没有索引的表进行join操作时的buffer, default 256K, one-time allocation when connection is created

join_buffer_size=262144

#指定连接侦听队列的大小, this value exceeds the tcp_max_syn_backlog/somaxconn of the OS and is recommended as an integer less than 512

back_log=512

#table_open_cache中岁多能打开的表数

Table_open_cache = 256

#一次消息传输量的最大值, is the maximum value of the Net_buffer_length

Max_allowed_packet=8m

#bin-log

#打开二进制日志

Log-bin=mysql-bin

Log-bin-index=mysql-bin.index

#日志格式, optional row/statement/mixed, default statement, recommended mixed

Binlog_format=mixed

#一个binlog的最大值

max_binlog_size=1g

#对单个连接的binlog缓存大小, default 1M

Binlog_cache_size=2m

Total size of #binlog cache

max_binlog_cache_size=2g

#sync_binlog May is 0 or 1

#binlog是否进行磁盘同步, for 0 o'clock to the OS, for 1: Things end immediately sync, io loss is big

Sync_binlog=0

#slow-log

#开启slow Log

Slow-query-log=1

#指定慢查询的时长

long-query-time=2

#路径

Slow-query-log-file=/data/mysql/slowlog/slow.log

In #未使用索引的查询也记录到slow Log

Log_queries_not_using_indexes=1

#query Cache

#启用query Cache

Query_cache_type=1

#存放单条query Maximum cache size, default 1M

query_cache_limit=1m

#存放单条query the minimum cache size, the default 4K

query_cache_min_res_unit=4096

#query的最大值

query_cache_size=256m

#MyISAM

#索引缓存大小, formula: Key_size = Key_number * (key_length+4)/0.67

key_buffer_size=128m

#单个线程进行顺序读取时的缓存, default 128K

Read_buffer_size=2m

#单个线程进行随机读取时的缓存, default 256K

Read_rnd_buffer_size = 4M

#重建索引时允许的最大缓存大小, default 8M

Myisam_sort_buffer_size = 64M

#指定索引缓存中block大小, default 1K

key_cache_block_size=1024

#禁止索引的单条刷新

Delay_key_write=1

#innodb

#启用独立的表空间

#innodb_file_per_table =1

#设置innodb内存缓冲区大小, in a pure MySQL environment, the recommended settings for server memory 60~80%

Innodb_buffer_pool_size =8g

#设置OS能进入innodb内核的线程数, recommended 2* (CPU core + disk count)

Innodb_thread_concurrency=16

#设置innodb的io行为, optional value fsync/o_dsync/o_direct recommended o_direct improve random write efficiency

Innodb_flush_method=o_direct

#设置mysql主进程每秒钟向磁盘刷入的脏页数, default value 200

#For systems with individual 5400 RPM or 7200 rpm drives, your might lower the value to the former default of 100

innodb_io_capacity=100

#设置写脏页的线程数

Innodb_write_io_threads = 8

#设置从磁盘读文件块的线程数

Innodb_read_io_threads = 8

#设置事物日志缓冲区大小

#设置事物日志缓冲区大小

Innodb_log_buffer_size=8m

#log the way thread synchronizes logs to disk, the optional value is 0/1/2

#0: Refresh 1 per Second: Refresh 2 every time the thing is submitted: to the OS to sync

Innodb_flush_log_at_trx_commit =0

#数据库字典信息和表结构空间

Innodb_additional_mem_pool_size =16m

#启用Double write buffer area to ensure data integrity, but consumes IO

Innodb_doublewrite=1

[Mysqldump]

# do don't buffer the whole result set in memory before writing it to file. Required for dumping very large tables

Quick

Max_allowed_packet = 16M

[MySQL]

No-auto-rehash

[Myisamchk]

Key_buffer_size = 512M

Sort_buffer_size = 512M

Read_buffer = 8M

Write_buffer = 8M

[Mysqlhotcopy]

Interactive-timeout

[Mysqld_safe]

# Increase the amount of open files allowed per process. Warning:make

# sure you have set the global system limit high enough! The High value

# is required for a large number of opened tables

Open-files-limit = 8192