mysql my-innodb-heavy-4g.cnf_如何修改和优化默认的my-innodb-heavy-4g.cnf

这篇文章主要根据我的环境，来给大家分享一下，从默认的配置文件如何简单修改成符合生产环境使用的配置文件，我主要是c语言开发的程序连接的mysql，使用的是长连接，并发有6个左右，增删改查操作都有。

提示：

percona出了一个工具，可以协助你生成一个数据库配置文件。http://tools.percona.com/

mysqld –verbose –help | grep “\-\-max-allowed-packet” -A 2 这个命令主要用于查看配置文件中某个值的含义，如果不明白的可以用此方法查看一下。

我的硬件环境：

CPU：

8核

model name : Intel(R) Xeon(R) CPU E5606 @ 2.13GHz

stepping : 2

cpu MHz : 1200.000

cache size : 8192 KB

内存：4G

机器：DELL R410

系统：RHEL5.4 X86

mysql：percona-5.5.18

# cat support-files/my-innodb-heavy-4G.cnf | egrep -v ‘^#|^$’

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

back_log = 50

#最大连接数，默认100，如果是web应用，并发较高的情况下，可以将此值和thread_cache_size及thread_concurrency调大，具体参考show global status like 'thread%';来调整。我这里是属于c语言的长连接，并发在6个左右，所以我设置为20.

max_connections = 20

#最大错误链接，如果是短链接较多的话，最好设置为一个比较大的值，不然错误次数超过这个值，新的用户就链接不上了。

max_connect_errors = 1000

#根据表的数量来确定，一般2048够了，根据show global status like 'open%tables';来调整

table_open_cache = 2048

#mysql导入数据或者某些操作的数据包大于16M会报错，建议修改成比主要库的文件总和大。

max_allowed_packet = 2048M

binlog_cache_size = 1M

max_heap_table_size = 64M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

sort_buffer_size = 8M

join_buffer_size = 8M

thread_cache_size = 8

thread_concurrency = 8

query_cache_size = 64M

query_cache_limit = 2M

ft_min_word_len = 4

#默认存储引擎，默认是MYISAM，如果用InnoDB的话，一定要改。

default-storage-engine = InnoDB

thread_stack = 192K

#事务级别，建议保留

transaction_isolation = REPEATABLE-READ

tmp_table_size = 64M

log-bin=mysql-bin

#二进制日志格式，默认mixed，如果你要通过二进制日志查询相关操作语句的话，需修改为STATEMENT，语句模式。

binlog_format=STATEMENT

slow_query_log

#查询时间大于此值的语句，会被记录到慢查询日志。默认为2s，在5.1.2后，支持微妙级别，我填写为0.05，具体根据业务需求填写，根据慢查询日志内容来调整此值。

long_query_time = 0.05

server-id = 1

key_buffer_size = 32M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

innodb_additional_mem_pool_size = 16M

#innodb配置重要参数之一，用于缓存索引和数据，建议给到内存的70%-80%

innodb_buffer_pool_size = 3G

#共享表空间设置，如果使用每个表一个表空间的话，此值不用修改。

innodb_data_file_path = ibdata1:10M:autoextend

innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_thread_concurrency = 16

#这个值是innodb日志刷新的配置，是innodb重要配置参数之一。默认值为1，有三个值，分别是0、1、2

#N=0 – 每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上。

#N=1 – 每个事务提交的时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上。

#N=2 – 每个事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度。

#为1的时候最安全，效率最低。为0的时候最不安全，如果数据库崩溃，可能会丢失最后1s的数据，我的应用能够承受丢失最后1s的数据，所以我选择0，效率可以提高10倍以上。

innodb_flush_log_at_trx_commit = 0

innodb_log_buffer_size = 8M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

#自动解除死锁的时间，默认120，建议修改成10s-20s。可以先用120跑一段时间，通过慢查询看，最大的查询时间，如果是不可修复的，那么此值设置比最大慢查询值2倍。

innodb_lock_wait_timeout = 120

#添加两个值

#innodb数据和日志持久化的方式，有个datasync，O_DSYNC和O_DIRECT三种方式，默认是fdatasync。O_DIRECT和fdatasync和很类似，但是它会使用O_DIRECT来打开数据文件。有数据表明，如果是大量随机写入操作，O_DIRECT会提升效率。但是顺序写入和读取效率都会降低，所以使用O_DIRECT需要谨慎。由于我update语句比较多，所以我选择了O_DIRECT模式。

innodb_flush_method=O_DIRECT

#开启每个表独立的表空间，避免表空间文件损坏造成的所以表顺坏问题。支持单表备份。

innodb_file_per_table=1

[mysqldump]

quick

#调整和上面max_allowed_packet值一样

max_allowed_packet = 2048M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 8192