mysql 使用hp vls_MySQL Note – innodb参数

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的相关参数：

--innodbor--skip-innodb

启动/禁用innodb引擎的支持，默认情况下引擎是启动的。而innodb一般作为默认存储引擎支持，这两个选项都是不必要的。

起停和内存池

--innodb_buffer_pool_size=26G

如图所示，这个值是存储表数据和索引的内存缓冲区buffer_pool的大小，在一个OLTP系统中，这个值往往高达60%-80%的内存或者1.1 * 热数据的大小。一味加大buffer_pool_size不一定起到优化的效果，可以参考:http://www.voidcn.com/article/p-vlshppqr-bkr.html

--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_checksumsor--skip-innodb-checksums

默认情况下，MySQL启用了这个选项，并在从文件系统中读取page时使用校验和来进行验证。这提供了可靠的安全性，一般情况没有必要关闭

--innodb_doublewriteor--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的性能。