MySQL Note – innodb参数

原文：http://debugo.com/innodb-parameter/

 

innoDB是事务型数据库的首选引擎，支持ACID事务，支持行级锁定，也是现在Percona等发型版本的默认引擎。innoDB支持4个事务隔离级，默认为repeatable read，比Oracle支持的两个事务隔离级更加强大。（参考：Innodb事务隔离级别 ）
MySQL innodb事务引擎的逻辑框架图和Oracle非常类似，包含一个大的buffer pool，重做日志（redo log），表空间文件ibddata1（等同于Oracle数据文件头+system表空间）。另外，回滚(undo）也不是使用独立的undo表空间来管理undo segment，而是使用主表空间文件。undo相关参考：
概念原理——MySQL数据库InnoDB存储引擎Log漫游
Mysql Innodb的undo redo操作过程
深入分析——InnoDB undo log解析（一）
InnoDB undo log解析（二）

InnoDB整体架构图（摘自Frank.Wang PPT)

下面结合这个架构图来介绍下innodb的相关参数：
--innodb or --skip-innodb
启动/禁用innodb引擎的支持，默认情况下引擎是启动的。而innodb一般作为默认存储引擎支持，这两个选项都是不必要的。

起停和内存池

--innodb_buffer_pool_size=26G
如图所示，这个值是存储表数据和索引的内存缓冲区buffer_pool的大小，在一个OLTP系统中，这个值往往高达60%-80%的内存或者1.1 * 热数据的大小。一味加大buffer_pool_size不一定起到优化的效果，可以参考:http://blog.csdn.net/yang1982_0907/article/details/20123055
--innodb_additional_mem_pool_size=60M
设置innodb中用户存储数据字典或者其他内存结构的内存pool容量，默认为1MB。如果这个pool内存不足的时候，innodb会向MySQL error log中写入警告信息。
--innodb_max_dirty_pages_pct=90
进行flush前最大允许buffer pool中脏页的百分比，默认为90。这个值太小会导致频繁的checkpoint操作，降低性能。
--Innodb_fast_shutdown={0|1|2}
设置innodb在它关闭的时候该做什么工作。默认为1有三个值可以选择：
1. 0表示在innodb关闭的时候，需要purge all, merge insert buffer,flush dirty pages。这是最慢的一种关闭方式，但是restart的时候也是最快的。后面将介绍purge all,merge insert buffer,flush dirty pages这三者的含义。
2. 1表示在innodb关闭的时候，它不需要purge all，merge insert buffer，只需要flush dirty page。
3. 2表示在innodb关闭的时候，它不需要purge all，merge insert buffer，也不进行flush dirty page，只将log buffer里面的日志flush到log files。因此等下进行恢复的时候它是最耗时的。
--innodb_force_recovery={1|2|3|4|5|6}
这个选项让innodb进入崩溃还原模式。
关于参数的详细解释请参见：innodb_force_recovery对mysql 宕机恢复影响 
--innodb_io_capacity=800
innodb每秒后台进程处理IO操作的数据页上限,即innodb_buffer_pool_size总的io处理能力上限，需要测试后给一个合适的大小。如果是SSD磁盘，这个值可以设置几千甚至上万。
innodb_buffer_pool_instances分割成多个内存块时，每个内存块的IO处理能力为：innodb_io_capacity/innodb_buffer_pool_instances
-innodb_buffer_pool_instances=4
将innodb_buffer_pool hash为不同的instance，每个instance独立的LRU、FLUSH、FREE和独立的mutex控制,提高并行读写的能力。 mysql优化—第7篇：参数 innodb_buffer_pool_instances设置

日志文件

--innodb_flush_method={ fdatasync | O_DIRECT | O_DSYNC }
这个选项设置InnoDB同步数据和刷新日志的方法。默认为fdatasync，innoDB会使用操作系统的fsync（）调用来同步数据和日志文件。O_DIRECT会使用O_DIRECT参数的fsync来同步数据和日志文件，一般要使用这个参数，来避免双缓存。
--innodb_log_buffer_size=10M
设置innodb日志缓冲区大小，innodb会向缓冲区写入日志。默认为1M
--innodb_log_file_size=512M
和Oracle中的redo定义类似，位于日志中每一个日志文件的大小，默认为5M。该选项的值越小，日志切换的越频繁，从而导致日志同步等I/O性能问题就越严重，但文件恢复时间越短。一般综合考虑可以设置为512M。
--innodb_log_in_group=3
这个选项确定了日志日志文件的数量，默认值为2。
--innodb_log_group_home_dir= 
InnoDB日志文件的路径。 如果没有明确指定将默认在 MySQL 的 datadir 目录下建立 ib_logfile… 文件。
InnoDB 日志文件的路径。必须与 innodb_log_arch_dir 设置相同值。 如果没有明确指定将默认在 MySQL 的 datadir 目录下建立两个 5 MB 大小的 ib_logfile… 文件。
--innodb_flush_log_at_trx_commit={0|1|2}
设置当事务提交操作时日志刷新行为。三个选项分别为：
0：每秒将log buffer的内容写事务日志并且刷新到磁盘；
1：每个事务提交后，将log_buffer的内容写事务日志并数据磁盘；
2：每个事务提交，将log_buffer内容写事务日志，但不进行数据刷盘
为了保证高安全性，可以设置刷新binlog的双1模式，即：innodb_flush_log_at_trx_commit= 1，sync_binlog = 1，这样主备的数据是一致的，不会丢失数据。
--innodb_mirrored_log_groups=value
设置innodb维护的镜像日志组的数量，默认为1。一般情况下不必修改这个值。
--innodb_max_purge_lag=value
对undo的pages进行purge类操作的延时，默认值为0，表示没有延时。参考：
innodb purge 
MySQL 研究innodb_max_purge_lag分享
--innodb-safe-binlog
这个选项保证了InnoDB表内容和二进制日志的一致性。

表空间数据文件

--innodb_data_home_dir=/var/data/mysql
指定innodb的数据文件的路径，默认使用datadir
--innodb_data_file_path=ibdata1:100M:autoextend
指定innodb表空间数据文件的大小，可以指定多个数据文件（路径或文件名），并指定大小和autoextend选项（默认为10MB+autoextend）。
--innodb_autoextend_increment=value
设置innodb表空间文件自动扩展的幅度，默认为8（8MB）
--innodb_open_files=800
设置innodb一次可以打开.idb文件的最大数量，最小值为10，默认为300。在存在多个表空间文件时应该加大这个值。
--innodb_status_file
这个选项令innodb为SHOW ENGINE INNODB STATUS语句的结果维持一个状态文件。它偶尔会把结果写入文件中，该文件位于数据文件目录下，命名为innodb_status.pid。
--innodb_file_per_table
如果启动了这个参数，则innodb对每个表都会使用一个.idb数据文件来取代使用默认的表空间文件存储数据。默认情况下这个选项是禁用的。
--innodb_checksums or --skip-innodb-checksums
默认情况下，MySQL启用了这个选项，并在从文件系统中读取page时使用校验和来进行验证。这提供了可靠的安全性，一般情况没有必要关闭
--innodb_doublewrite or --skip-innodb-doublewrite

开启/关闭doublewrite功能，默认为开启。这使得InnoDB将它接收到的数据写入两次，第一次写入数据缓冲区(在共享表空间数据文件中），第二次才会把数据写入数据文件上并校验数据的完整性。目的是为了保证出现部分写失效（partial page write）–即数据页写到一半时就出现故障–时的数据安全性。相关参考：
MySQL性能日志(1): InnoDB Double Write

线程和并行

--innodb_thread_concurrency=0
设置并发使用InnoDB的最大线程最大数量，超过这个线程数访问时，线程将处于等待状态（不是拒绝用户的连接）。该值取值范围为0-1000，0表示没有任何限制。
--innodb_commit_concurrency=0
设置并发提交事务的最大线程数量，其值为0将以移除对并发提交的限制。
--innodb_file_io_threads=12
--innodb_read_io_threads=8
--innodb_write_io_threads=4
设置I/O线程允许的文件数量，在现在的MySQL发行版里，不再使用innodb_file_io_threads来设置而是直接设置读和写的I/O线程数量。可以视系统开销加大这个值。
--innodb_sync_spin_loops=value
设置当InnoDB中线程被挂起之前，等待线程资源的次数。一旦超过了这个次数，线程将被挂起。
--innodb_thread_sleep_depaly=10000
在进入InnoDB队列之前，使用这个选项可以设置线程休眠时间。单位为微秒，默认为10000。设置为0表示禁用休眠。
表和锁定
--innodb_lock_wait_timeout=value
设置InnoDB事务等待一个表锁定的时候最大等待时间，单位是秒，默认为50.
--innodb_table_locks[={0|1}]
当启用这个选项，当运行lock table语句并把autocommit设置为0时，将导致innodb给内部加一个表级锁。
--innodb_autoinc_lock_mode={0|1|2}
当存储引擎自动产生扩展值时，这个选项用于设置用到的锁定模式。可能的值为0（传统模式）、1（连续模式）和2（交叉存取模式）。越高的值表示速度越快，但是安全性越低。
--innodb_locks_unsafe_for_binlog
为了完成类似于行级锁定，innodb锁定了行上的索引。一般来说，这会阻止其他用户写入相邻行间隔(gap)中。正因为这样的锁算法，innodb在可重复读这样的默认隔离级别上，可以避免幻象的产生。innodb_locks_unsafe_for_binlog最主要的作用就是控制innodb是否对gap加锁。注意该参数如果是enable的，则是unsafe的，此时gap不会加锁；反之，如果disable掉该参数，则gap会加锁。当然对于一些和数据完整性相关的定义，如外键和唯一索引（含主键）需要对gap进行加锁，那么innodb_locks_unsafe_for_binlog的设置并不会影响gap是否加锁。
--innodb_support_xa
这个选项令innodb在XA事务中支持二阶段事务（可以对比MongoDB的二阶段提交：MongoDB – Two-phase Commit），这个参数确保事务日志写入bin-log 的顺序与是事务的time-line 是一致的。它被设置为默认值1启用这个选项。可以设置为0来提高innodb的性能。