Mysql配置文件 innodb引擎（上）

nnodb参数

innodb_buffer_pool_size

这个是Innodb最重要的参数，主要作用是缓存innodb表的索引，数据，插入数据时的缓冲，默认值为128M。

如果是一个专用innodb引擎的服务器，那么它可以占到内存的70%-80%。并不是设置的越大越好。设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。

如果你的数据比较小，那么可分配是你的数据大小+10%左右做为这个参数的值。例如：数据大小为50M,那么给这个值分配innodb_buffer_pool_size＝64M就够了

查询： 在线配置： 配置文件：innodb_buffer_pool_size = 16G

innodb_read_io_threads|innodb_write_io_threads

在MySQL5.1.X版本中，innodb_file_io_threads参数默认是4，该参数在Linux系统上是不可更改的，但Windows系统上可以调整。这个参数的作用是：InnoDB使用后台线程处理数据页上读写I/O（输入输出）请求的数量。

在MySQL5.5.X版本中，或者说是在InnoDB Plugin1.0.4以后，就用两个新的参数，即innodb_read_io_threads和innodb_write_io_threads，取代了innodb_file_io_threads如此调整后，在Linux平台上就可以根据CPU核数来更改相应的参数值了，默认是4。

假如CPU是2颗8核的，共16核，那么可以设置： innodb_read_io_threads = 8 innodb_write_io_threads = 8

如果数据库的读操作比写操作多，那么可以设置： innodb_read_io_threads = 10 innodb_write_io_threads = 6

innodb_open_files

限制Innodb能打开的表的数据，默认为300，数据库里的表特别多的情况，可以适当增大为1000。innodb_open_files的大小对InnoDB效率的影响比较小。

但是在InnoDBcrash的情况下，innodb_open_files设置过小会影响recovery的效率。所以用InnoDB的时候还是把innodb_open_files放大一些比较合适。

查询： 在线配置： 配置文件：innodb_open_files = 1000

innodb_log_file_size

这个参数指定在一个日志组中，每个log的大小。

innodb的logfile就是事务日志，用来在mysql crash后的恢复。所以设置合理的大小对于mysql的性能非常重要，直接影响数据库的写入速度，事务大小，异常重启后的恢复。

在mysql 5.5和5.5以前innodb的logfile最大设置为4GB,在5.6以后的版本中logfile最大的可以设为512GB。一般取256M可以兼顾性能和recovery的速度。

查询：select @@innodb_log_file_size; 在线配置： 配置文件：innodb_log_file_size=256M

innodb_log_buffer_size

事务在内存中的缓冲，也就是日志缓冲区的大小，默认设置即可，具有大量事务的可以考虑设置为64-512MB。

一般来说，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。

查询： 在线配置： 配置文件：innodb_log_buffer_size = 128M

innodb_flush_log_at_trx_commit

控制事务的提交方式,也就是控制log的刷新到磁盘的方式，这个参数只有3个值（0，1，2）.默认为1。

当这个值为0时：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。mysqld进程的崩溃会删除崩溃前最后一秒的事务。

当这个值为1时：innodb 的事务LOG在每次提交后写入日值文件，并对日值做刷新到磁盘。这个可以做到不丢任何一个事务。

当这个值为2时：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。但需要注意的是，由于进程调用方面的问题，并不能保证每秒１００％的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。

查询： 在线配置： 配置文件：innodb_flush_log_at_trx_commit = 1

innodb_flush_method

这个参数控制着innodb数据文件及redo log的打开、刷写模式，有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。

fdatasync：调用fsync()去刷数据文件与redo log的buffer

O_DSYNC时：innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件

O_DIRECT时：innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log。

在类unix操作系统中，文件的打开方式为O_DIRECT会最小化缓冲对io的影响，该文件的io是直接在用户空间的buffer上操作的，并且io操作是同步的，因此不管是read()系统调用还是write()系统调用，数据都保证是从磁盘上读取的

查询： 在线配置： 配置文件：innodb_flush_method=O_DIRECT

innodb_data_home_dir

innodb引擎的共享表空间数据文件根目录。如果未指定，默认会在datadir目录下创建ibdata1 作为innodb tablespace。

查询： 在线配置： 配置文件：innodb_data_home_dir = /Data/data

innodb_data_file_path

它不仅指定了所有InnoDB数据文件的路径，还指定了初始大小分配，最大分配以及超出起始分配界线时是否应当增加文件的大小。

例如，假设希望创建一个数据文件sales，初始大小为100MB，并希望在每次达到当前大小限制时，自动增加8MB（8MB是指定autoextend时的默认扩展大小).但是，不希望此文件超过1GB，可以使用如下配置: innodb_data_home_dir = innodb_data_file_path = /data/sales:100M

8M

1GB

如果此文件增加到预定的1G的限制，可以再增加另外一个数据文件, innodb_data_file_path = /data/sales:100M

8M: max:1GB;innodb_data_file_path = /data2/sales2:100M

8M: max:2GB

要注意的是，在这些示例中，inndb_data_home_dir参数开始设置为空，因为最终数据文件位于单独的位置(/data/和/data2/）.如果希望所有 InnoDB数据文件都位于相同的位置，就可以使用innodb_data_home_dir来指定共同位置，然后在通过 inndo_data_file_path来指定文件名即可。如果没有定义这些值，将在datadir中创建一个sales。

查询： 在线配置： 配置文件：innodb_data_file_path = ibdata1:10M:autoextend

innodb_purge_threads

由于每次DML操作都会生成Undo页，系统需要定期对这些undo页进行清理，也就是所谓purge操作。在5.5之前这些都是在master线程中完成，但5.5及之后的版本可以通过innodb_purge_threads来控制是否使用独立线程进行purge操作。

查询： 在线配置： 配置文件：innodb_purge_threads = 1

innodb_log_group_home_dir

此参数确定日志文件组中的文件的位置，日志组中文件的个数由innodb_log_files_in_group确定，此位置设置默认为MySQL的datadir。

查询： 在线配置： 配置文件：innodb_log_group_home_dir = /Data/data

innodb_autoinc_lock_mode

参考

这个参数控制着在向有auto_increment 列的表插入数据时，相关锁的行为。通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡

insert大致上可以分成三类： 1、simple insert 如insert into t(name) values(‘test’) 2、bulk insert 如load data | insert into … select …. from …. 3、mixed insert 如insert into t(id,name) values(1,’a’),(null,’b’),(5,’c’);

innodb_auto_lockmode有三个取值： 0 这个表示tradition 传统 1 这个表示consecutive 连续 2 这个表示interleaved 交错

tradition(innodb_autoinc_lock_mode=0) 模式: 1、它提供了一个向后兼容的能力 2、在这一模式下，所有的insert语句(“insert like”) 都要在语句开始的时候得到一个表级的auto_inc锁，在语句结束的时候才释放这把锁，注意呀，这里说的是语句级而不是事务级的，一个事务可能包涵有一个或多个语句。 3、它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。 4、由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入。

consecutive(innodb_autoinc_lock_mode=1) 模式: 1、这一模式下去simple insert 做了优化，由于simple insert一次性插入值的个数可以立马得到确定，所以mysql可以一次生成几个连续的值，用于这个insert语句；总的来说这个对复制也是安全的(它保证了基于语句复制的安全) 2、这一模式也是mysql的默认模式，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁

interleaved(innodb_autoinc_lock_mode=2) 模式： 1、由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。

总结： 如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。

由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2 这样可以得到更好的性能。

查询： 在线配置： 配置文件：innodb_autoinc_lock_mode = 2