mysql性能

innodb_flush_log_at_trx_commit

一 参数意义

innodb_flush_log_at_trx_commit

如果innodb_flush_log_at_trx_commit设置为0，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。
如果innodb_flush_log_at_trx_commit设置为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去.
如果innodb_flush_log_at_trx_commit设置为2，每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

注意：

  由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。

  

sync_binlog

sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。

当sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

注:

   如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。

   根据上述描述，我做了一张图，可以方便大家查看。
  

二 性能

    两个参数在不同值时对db的纯写入的影响表现如下：

    

 测试场景1 

  innodb_flush_log_at_trx_commit=2 

  sync_binlog=1000

 测试场景2 

  innodb_flush_log_at_trx_commit=1 

  sync_binlog=1000

 测试场景3 

  innodb_flush_log_at_trx_commit=1 

  sync_binlog=1

 测试场景4

  innodb_flush_log_at_trx_commit=1

  sync_binlog=1000

 测试场景5 

  innodb_flush_log_at_trx_commit=2 

  sync_binlog=1000 

 

场景	TPS
场景1	41000
场景2	33000
场景3	26000
场景4	33000

由此可见，当两个参数设置为双1的时候，写入性能 最差，sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 时，(在当前模式下)MySQL的写操作才能达到最高性能。

三 安全

当innodb_flush_log_at_trx_commit和sync_binlog  都为 1 时是最安全的，在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，双11 会导致频繁的io操作，因此该模式也是最慢的一种方式。

当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。

双1适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单,交易,充值,支付消费系统。双1模式下，当磁盘IO无法满足业务需求时 比如11.11 活动的压力。推荐的做法是 innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache，防止系统断电异常。

四 小结

    系统性能和数据安全是业务系统高可用稳定的必要因素。我们对系统的优化需要寻找一个平衡点，合适的才是最好的，根据不同的业务场景需求，可以将两个参数做组合调整，以便是db系统的性能达到最优化。

innodb_flush_method

这个参数控制着innodb数据文件及redo og的打开、刷写模式，有以下几种设置：
   1) 默认是fdatasync，调用fsync()去刷数据文件与redo log的buffer
   2) 为O_DSYNC时，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件，通常比较慢。
   3) 为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log，在Linux上使用Direct IO，可以显著提高速度，特别是在RAID系统上，避免额外的数据复制和double buffering（mysql buffering 和OS buffering）。

任何数据库，只要涉及到持久化，就与上面这三个方面的参数有极大关系，包括NOSQL与内存数据库，有时NOSQL与内存数据库之所以比MYSQL快，与这方面的设置也有很大关系

其实，在大部分场景下，如果某个产品宣称自己的写读tps超过其他存储n倍，一般来说都是从k-v这个角度入手进行优化的，主要入手的点是树的数据结构优化和锁的细化，一般都能在一些特定的场景获得5-10倍的性能提升。

 

也就是说，具体的取值跟硬件配置和工作负载相关，最好做一次压测来决定。不过通常来说，linux环境下具有raid控制器和write-back写策略，o_direct是比较好的选择；如果存储介质是SAN，那么使用默认fsync或者osync或许更好一些。


通常来说，貌似绝大部分人都取值o_direct，底层有raid卡，读写策略设置为write-back。在使用sysbench压测oltp类型时，我发现o_direct确实比fsync性能优秀一些，看来适用于大部分场景，但是最近碰到一个这样的sql，客户反馈很慢，而在相同内存的情况下，它自己搭建的云主机执行相对快很多，后来我发现主要就是innodb_flush_method的设置值不同带来的巨大性能差异。

测试场景1

innodb_flush_method为默认值，即fsync，缓存池512M，表数据量1.2G，排除缓存池影响，稳定后的结果
mysql> show variables like '%innodb_flush_me%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_flush_method |       |
+---------------------+-------+
1 row in set (0.00 sec)


mysql> SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main';
+--------------------------+
| SUM(outcome)-SUM(income) |
+--------------------------+
|               -191010.51 |
+--------------------------+
1 row in set (1.22 sec)


mysql> SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main';
+--------------------------+
| SUM(outcome)-SUM(income) |
+--------------------------+
|               -191010.51 |
+--------------------------+
1 row in set (1.22 sec)
mysql> explain SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main';
+----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows   | Extra                 |
+----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+
|  1 | SIMPLE      | journal | ref  | account_id    | account_id | 62      | const | 161638 | Using index condition |
+----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+
1 row in set (0.03 sec)

测试场景2

innodb_flush_method改为o_direct，排除缓存池影响，稳定后的结果
mysql> show variables like '%innodb_flush_me%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| innodb_flush_method | O_DIRECT |
+---------------------+----------+
1 row in set (0.00 sec)


mysql> SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main';
+--------------------------+
| SUM(outcome)-SUM(income) |
+--------------------------+
|               -191010.51 |
+--------------------------+
1 row in set (3.22 sec)


mysql> SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main';
+--------------------------+
| SUM(outcome)-SUM(income) |
+--------------------------+
|               -191010.51 |
+--------------------------+
1 row in set (3.02 sec)


mysql> explain SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main';
+----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows   | Extra                 |
+----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+
|  1 | SIMPLE      | journal | ref  | account_id    | account_id | 62      | const | 161638 | Using index condition |
+----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+
1 row in set (0.00 sec)

结果比较：

两者执行计划一摸一样，性能却差距很大。在数据库第一次启动时的查询结果也差距很大，o_direct也差很多（测试结果略）。不是很懂为啥这种情况下多了一层操作系统缓存，读取效率就高了很多，生产环境设置一定要以压测结果为准，实际效果为准，不能盲目信任经验值。

改进措施：

不改变innodb_flush_method的情况下，其实这条sql还可以进一步优化，通过添加组合索引(account_id,outcome,income)，使得走覆盖索引扫描，可大大地减少响应时间

innodb_buffer_pool_size 

这是Innodb最重要的一个配置参数，这个参数控制Innodb本身的缓大小，也影响到，多少数据能在缓存中。建议该参数的配置在物理内存的70％－80％之间。

无论是对于哪一种数据库来说，缓存技术都是提高数据库性能的关键技术，物理磁盘的访问速度永 远都会与内存的访问速度永远都不是一个数量级的。通过缓存技术无论是在读还是写方面都可以大大提 高数据库整体性能。

Innodb_buffer_pool_size 的合理设置

Innodb 存储引擎的缓存机制和 MyISAM 的最大区别就在于 Innodb 不仅仅缓存索引，同时还会缓存实 际的数据。所以，完全相同的数据库，使用 Innodb 存储引擎可以使用更多的内存来缓存数据库相关的信 息，当然前提是要有足够的物理内存。这对于在现在这个内存价格不断降低的时代，无疑是个很吸引人 的特性。

innodb_buffer_pool_size 参数用来设置 Innodb 最主要的 Buffer(Innodb_Buffer_Pool)的大小，也 就是缓存用户表及索引数据的最主要缓存空间，对 Innodb 整体性能影响也最大。无论是 MySQL 官方手册 还是网络上很多人所分享的 Innodb 优化建议，都简单的建议将 Innodb 的 Buffer  Pool 设置为整个系统 物理内存的 50%  ～ 80%  之间。如此轻率的给出此类建议，我个人觉得实在是有些不妥。

不管是多么简单的参数，都可能与实际运行场景有很大的关系。完全相同的设置，不同的场景下的 表现可能相差很大。就从 Innodb 的 Buffer  Pool 到底该设置多大这个问题来看，我们首先需要确定的是 这台主机是不是就只提供 MySQL 服务？MySQL 需要提供的的最大连接数是多少？MySQL 中是否还有 MyISAM 等其他存储引擎提供服务？如果有，其他存储引擎所需要使用的 Cache 需要多大？

假设是一台单独给 MySQL 使用的主机，物理内存总大小为 8G，MySQL 最大连接数为 500，同时还使用 了 MyISAM 存储引擎，这时候我们的整体内存该如何分配呢？

内存分配为如下几大部分：

a)     系统使用，假设预留 800M；

b)     线程独享，约 2GB  = 500  * (1MB  + 1MB  + 1MB  + 512KB  + 512KB)，组成大概如下:sort_buffer_size：1MB join_buffer_size：1MB read_buffer_size：1MB read_rnd_buffer_size：512KB thread_statck：512KB

c)    MyISAM  Key Cache，假设大概为 1.5GB；

d)     Innodb Buffer  Pool 最大可用量：8GB  - 800MB  - 2GB  - 1.5GB = 3.7GB；

假设这个时候我们还按照 50%～80%的建议来设置，最小也是 4GB，而通过上面的估算，最大可用值 在 3.7GB 左右，那么很可能在系统负载很高当线程独享内存差不多出现极限情况的时候，系统很可能就 会出现内存不足的问题了。而且上面还仅仅只是列出了一些使用内存较大的地方，如果进一步细化，很 可能可用内存会更少。上面只是一个简单的示例分析，实际情况并不一定是这样的，这里只是希望大家了解，在设置一些 参数的时候，千万不要想当然，一定要详细的分析可能出现的情况，然后再通过不断测试调整来达到自 己所处环境的最优配置。就我个人而言，正式环境上线之初，我一般都会采取相对保守的参数配置策 略。上线之后，再根据实际情况和收集到的各种性能数据进行针对性的调整。

当系统上线之后，我们可以通过 Innodb 存储引擎提供给我们的关于 Buffer  Pool 的实时状态信息作 出进一步分析，来确定系统中 Innodb 的 Buffer  Pool 使用情况是否正常高效：

sky@localhost  : example 08:47:54> show status like  'Innodb_buffer_pool_%';

+-----------------------------------+-------+

| Variable_name    | Value |

+-----------------------------------+-------+

| Innodb_buffer_pool_pages_data     | 70    |

| Innodb_buffer_pool_pages_dirty    | 0    |

| Innodb_buffer_pool_pages_flushed | 0    |

| Innodb_buffer_pool_pages_free    | 1978    |

| Innodb_buffer_pool_pages_latched | 0    |

| Innodb_buffer_pool_pages_misc     | 0    |

| Innodb_buffer_pool_pages_total    | 2048    |

| Innodb_buffer_pool_read_ahead_rnd  | 1    |

| Innodb_buffer_pool_read_ahead_seq  | 0    |

| Innodb_buffer_pool_read_requests | 329    |

| Innodb_buffer_pool_reads    | 19    |

| Innodb_buffer_pool_wait_free    | 0    |

| Innodb_buffer_pool_write_requests | 0    |

+-----------------------------------+-------+

从上面的值我们可以看出总共 2048  pages，还有 1978 是 Free 状态的仅仅只有 70 个 page 有数据， read 请求 329 次，其中有 19 次所请求的数据在 buffer  pool 中没有，也就是说有 19 次是通过读取物理 磁盘来读取数据的，所以很容易也就得出了 Innodb Buffer  Pool 的 Read 命中率大概在为：(329 - 19)/ 329  * 100%  = 94.22%。

当然，通过上面的数据，我们还可以分析出 write 命中率，可以得到发生了多少次 read_ahead_rnd，多少次 read_ahead_seq，发生过多少次 latch，多少次因为 Buffer 空间大小不足而产 生 wait_free 等等。

单从这里的数据来看，我们设置的 Buffer  Pool 过大，仅仅使用 70  / 2048  * 100%  = 3.4%。

在 Innodb Buffer  Pool 中，还有一个非常重要的概念，叫做“预读”。一般来说，预读概念主要是 在一些高端存储上面才会有，简单来说就是通过分析数据请求的特点来自动判断出客户在请求当前数据 块之后可能会继续请求的数据快。通过该自动判断之后，存储引擎可能就会一次将当前请求的数据库和 后面可能请求的下一个（或者几个）数据库一次全部读出，以期望通过这种方式减少磁盘 IO 次数提高 IO 性能。在上面列出的状态参数中就有两个专门针对预读：

Innodb_buffer_pool_read_ahead_rnd，记录进行随机读的时候产生的预读次数； Innodb_buffer_pool_read_ahead_seq，记录连续读的时候产生的预读次数；

innodb_log_buffer_size 参数的使用

顾名思义，这个参数就是用来设置 Innodb 的 Log Buffer 大小的，系统默认值为 1MB。Log  Buffer 的主要作用就是缓冲 Log 数据，提高写 Log 的 IO 性能。一般来说，如果你的系统不是写负载非常高且以 大事务居多的话，8MB 以内的大小就完全足够了。

我们也可以通过系统状态参数提供的性能统计数据来分析 Log 的使用情况：

sky@localhost  : example 10:11:05> show status like 'innodb_log%';

+---------------------------+-------+

| Variable_name    | Value |

+---------------------------+-------+

| Innodb_log_waits     | 0    |

| Innodb_log_write_requests | 6    |

| Innodb_log_writes     | 2    |

+---------------------------+-------+

通过这三个状态参数我们可以很清楚的看到 Log Buffer 的等待次数等性能状态。

当然，如果完全从 Log Buffer 本身来说，自然是大一些会减少更多的磁盘 IO。但是由于 Log 本身是 为了保护数据安全而产生的，而 Log 从 Buffer 到磁盘的刷新频率和控制数据安全一致的事务直接相关， 并且也有相关参数来控制（innodb_flush_log_at_trx_commit），所以关于 Log 相关的更详细的实现机 制和优化在后面的“事务优化”中再做更详细的分析，这里就不展开了。

innodb_additional_mem_pool_size 参数理解

innodb_additional_mem_pool_size 所设置的是用于存放 Innodb 的字典信息和其他一些内部结构所 需要的内存空间。所以我们的 Innodb 表越多，所需要的空间自然也就越大，系统默认值仅有 1MB。当 然，如果 Innodb 实际运行过程中出现了实际需要的内存比设置值更大的时候，Innodb 也会继续通过 OS 来申请内存空间，并且会在 MySQL 的错误日志中记录一条相应的警告信息让我们知晓。

从我个人的经验来看，一个常规的几百个 Innodb 表的 MySQL，如果不是每个表都是上百个字段的 话，20MB 内存已经足够了。当然，如果你有足够多的内存，完全可以继续增大这个值的设置。实际上， innodb_additional_mem_pool_size 参数对系统整体性能并无太大的影响，所以只要能存放需要的数据即 可，设置超过实际所需的内存并没有太大意义，只是浪费内存而已。

Double Write Buffer

Double Write Buffer 是 Innodb 所使用的一种较为独特的文件 Flush 实现技术，主要做用是为了通 过减少文件同步次数提高 IO 性能的情况下，提高系统 Crash 或者断电情况下数据的安全性，避免写入的 数据不完整。

一般来说，Innodb 在将数据同步到数据文件进行持久化之前，首先会将需要同步的内容写入存在于表空间中的系统保留的存储空间，也就是被我们称之为 Double Write Buffer 的地方，然后再将数据进 行文件同步。所以实质上，Double Write Buffer 中就是存放了一份需要同步到文件中数据的一个备份， 以便在遇到系统 Crash 或者主机断电的时候，能够校验最后一次文件同步是否准确的完成了，如果未完 成，则可以通过这个备份来继续完成工作，保证数据的正确性。

那这样 Innodb 不是又一次增加了整体 IO 量了吗？这样不是可能会影响系统的性能么？这个完全不用 太担心，因为 Double Write Buffer 是一块连续的磁盘空间，所有写入 Double Write Buffer 的操作都是 连续的顺序写入操作，与整个同步过程相比，这点 IO 消耗所占的比例是非常小的。为了保证数据的准确 性，这样一点点性能损失是完全可以接受的。

实际上，并不是所有的场景都需要使用 Double Write 这样的机制来保证数据的安全准确性，比如当 我们使用某些特别文件系统的时候，如在 Solaris 平台上非常著名的 ZFS 文件系统，他就可以自己保证文 件写入的完整性。而且在我们的 Slave 端，也可以禁用 Double Write 机制。

Adaptive  Hash Index

在 Innodb 中，实现了一个自动监测各表索引的变化情况的机制，然后通过一系列的算法来判定如果 存在一个 Hash Index 是否会对索引搜索带来性能改善。如果 Innodb 认为可以通过 Hash Index 来提高检 索效率，他就会在内部自己建立一个基于某个 B-Tree 索引的 Hash Index，而且会根据该 B-Tree 索引的 变化自行调整，这就是我们常说的 Adaptive  Hash Index。当然，Innodb 并不一定会将整个 B-Tree 索引 完全的转换为 Hash Index，可能仅仅只是取用该 B-Tree 索引键一定长度的前缀来构造一个 Hash Index。

Adaptive  Hash Index 并不会进行持久化存放在磁盘上面，仅仅存在于 Buffer  Pool 中。所以，在每 次 MySQL 刚启动之后是并不存在 Adaptive  Hash Index 的，只有在停工服务之后，Innodb 才会根据相应 的请求来构建。

Adaptive  Hash Index 的目的并不是为了改善磁盘 IO 的性能，而是为了提高 Buffer  Pool 中的数据 的访问效率，说的更浅显一点就是给 Buffer  Pool 中的数据做的索引。所以，Innodb 在具有大容量内存

（可以设置大的 Buffer  Pool）的主机上，对于其他存储引擎来说，会存在一定的性能优势。

5、innodb_io_capacity
这个参数控制Innodb checkpoint时的IO能力，一般可以按一块SAS 15000转的磁盘200个计算，6块盘的SAS做的Raid10这个值可以配到600即可。如果是普通的SATA一块盘只能按100算。(innodb-plugin, Percona有这个参数) 
 

MYSQL对这方面的设置比较保守，因为它要充分保证数据在任何异常情况下都不能有丢失，这就要求事务提交时，日志必须被最终完全的刻到磁盘上，不可以到任何的缓存上。如果，innodb_buffer_pool_size参数设置的够大，能够容纳整个表，并且把上面三个参数设置成最优设置（如：sync_binlog=0，innodb_flush_logs_at_trx_commit=0，sync_binlog=O_DIRECT），那么性能会有10-50倍的提高