INNODB判断缓存是否充足

INNODB存储类型的重要参数设置

一：二进制日志缓冲参数：binlog_cache_size

当使用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中，等该事务提交时（committed）时直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32KB。此外，binlog_cache_size是基于会话（session）的，也就是说，当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存，因此该值的设置需要相当小心，不能设置过大。当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。通过SHOW GLOBAL STATUS命令查看binlog_cache_use、binlog_cache_disk_use的状态,可以判断当前binlog_cache_size的设置是否合适。Binlog_cache_use记录了使用缓冲写二进制日志的次数，binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。现在来看一个数据库的状态：

mysql> show variables like 'binlog_cache_size';

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

| Variable_name   | Value |

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

| binlog_cache_size | 32768 |

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

1 row in set (0.00 sec)

 

mysql> show global status like 'binlog_cache%';

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

| Variable_name  | Value |

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

| binlog_cache_disk_use | 0     |

| binlog_cache_use     | 33553 |

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

2 rows in set (0.00 sec)

使用缓冲次数33 553次，临时文件使用次数为0。看来，32KB的缓冲大小对于当前这个MySQL数据库完全够用，所以暂时没有必要增加binlog_cache_size的值。

二：nnodb Buffer Pool参数设置

假设是一台单独给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_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 参数对系统整体性能并无太大的影响，所以只要能存放需要的数据即

可，设置超过实际所需的内存并没有太大意义，只是浪费内存而已。

五：重做日志设置的大小

总的来说，Innodb 的事务日志文件设置的越大，系统的IO 性能也就越高，但是当遇到MySQL ，OS

或者主机Crash 的时候系统所需要的恢复时间也就越长；反之，日志越小，IO 性能自然也就相对会差一

些，但是当MySQL，OS 或者主机Crash 之后所需要的恢复时间也越小。所以，到底该将事务日志设置多

大其实是一个整体权衡的问题，既要考虑到系统整体的性能，又要兼顾到Crash 之后的恢复时间。一般

来说，在我个人维护的环境中，比较偏向于将事务日志设置为3 组，每个日志设置为256MB 大小，整体效

果还算不错

六：

对于Innodb 所使用的行级锁定，系统中是通过另外一组更为详细的状态变量来记录的，如下：

mysql> show status like 'innodb_row_lock%';

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

| Variable_name | Value |

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

| Innodb_row_lock_current_waits | 0 |

| Innodb_row_lock_time | 490578 |

| Innodb_row_lock_time_avg | 37736 |

| Innodb_row_lock_time_max | 121411 |

| Innodb_row_lock_waits | 13 |

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

Innodb 的行级锁定状态变量不仅记录了锁定等待次数，还记录了锁定总时长，每次平均时长，以及

最大时长，此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。对各个状态量的说明如

下：

● Innodb_row_lock_current_waits：当前正在等待锁定的数量；

● Innodb_row_lock_time：从系统启动到现在锁定总时间长度；

● Innodb_row_lock_time_avg：每次等待所花平均时间；

● Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；

● Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

对于这5 个状态变量， 比较重要的主要是Innodb_row_lock_time_avg（ 等待平均时长） ，

Innodb_row_lock_waits（等待总次数）以及Innodb_row_lock_time（等待总时长）这三项。尤其是当等

待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后

根据分析结果着手指定优化计划。

 

源文档 <http://blog.sina.com.cn/s/blog_53b13d950100vi0l.html>