MySQL 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_arch

下面结合这个架构图来介绍下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

开启/关闭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的性能。

^^
参考:
MySQL核心技术手册
MySQL官方文档

Posted in Database, MySQL.