InnoDB内存优化

InnoDB内存优化

1、innodb_buffer_pool_size

​ innodb_buffer_pool_size 的值越大，缓存命中率越高，访问 InnoDB 表需要的磁盘 I/O 就越少，性能也就越高。在一个专用的数据库服务器上可以将 80% 的物理内存分配给 InnoDB buffer pool ，需要注意避免设置过大而导致页的交换。

#查看 buffer pool 的使用情况 
show global status like '%Innodb_buffer_pool%';

#计算 InnoDB 缓存池的命中率,如果命中率太低，则应该考虑扩充内存、增加 innodb_buffer_pool_size 的值
(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100

​ 参考设置

1)物理内存小于 1 GB
innodb_buffer_pool_size=128M
innodb_log_file_size=48M

2)物理内存为 1 GB ~ 4GB
innodb_buffer_pool_size=物理内存*0.5
innodb_log_file_size=128M


3)物理内存大于 4 GB
innodb_buffer_pool_size=物理内存*0.75
innodb_log_file_size=1024M

2、innodb_old_blocks_pct

​ innodb_old_blocks_pct （old sublist 的比例），可以根据 InnoDB Monitor的输出信息来调整 innodb_old_blocks_pct 的值。如果 youngs/s 的值很低，可能需要适当增大 innodb_old_blocks_pct 的值或减少 innodb_old_blocks_time 的值。

mysql> show engine innodb status\G;
...
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
...

3、innodb_old_blocks_time

​ 一个缓存数据块被插入到 midpoint(old sublist)后，至少要在 old sublist 停留超过 innodb_old_blocks_time(ms)后，才有可能被转移到 young sublist .

​ 可以根据 InnoDB Monitor的输出信息来调整 innodb_old_blocks_time 的值。在进行表扫描时，如果 non-youngs/s 很低，youngs/s 很高，就应该考虑将 innodb_old_blocks_time 适当调大，以防止表扫描将真正的热数据淘汰。

mysql> show engine innodb status\G;
...
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
...

4、innodb_buffer_pool_instances

​ 适当增加此参数的值，可以降低并发导致的内部缓存访问冲突，改善性能。 InnoDB 缓存系统会将 innodb_buffer_pool_size 的大小平分为 innodb_buffer_pool_instances 个 buffer pool。

5、innodb_max_dirty_pages_pct

​ 1) 控制缓冲池中脏页的最大比例，如果脏页达到或超过该值， InnoDB 后台线程将开始缓存刷新。

​ 2) 若 Innodb_buffer_pool_wait_free 的值增长较快，则说明 InnoDB 经常在等待空闲缓存页，如果无法增大缓存池，那么应将 innodb_max_dirty_pages_pct的值调小或将 innodb_io_capacity 的值提高，以加快脏页的刷新。

6、innodb_io_capacity

​ 1) 代表磁盘系统的 I/O 能力，对于转速较低的磁盘; 如 7200RPM 的磁盘，可将 innodb_io_capacity 的值降低到 100 ;而对于固态硬盘和由多个磁盘组成的盘阵, innodb_io_capacity 的值可以适当增大。对于固态硬盘来说，建议设置为 2000 或者更高。

​ 2) innodb_io_capacity 决定一批刷新脏页的数量，当缓存池脏页的比例达到 innodb_max_dirty_pages_pct 时， InnoDB 大约将 innodb_io_capacity 个已改变的缓存页刷新到磁盘。

​ 3) 当脏页小于 innodb_max_dirty_pages_pct 时，如果 innodb_adaptive_flushing=ON , InnoDB 将根据函数 buf_flush_get_desired_flush_rate 返回的重做日志产生的速度来确定要刷新的脏页数。

​ 4) 在合并插入缓存时，InnoDB 每次合并的页数是 0.05*innodb_io_capacity

​ 5) 若 Innodb_buffer_pool_wait_free 的值增长较快，则说明 InnoDB 经常在等待空闲缓存页，如果无法增大缓存池，那么应将 innodb_max_dirty_pages_pct的值调小或将 innodb_io_capacity 的值提高，以加快脏页的刷新。

7、innodb_doublewrite

​ 对于要求超高性能，有能容忍极端情况下少量数据丢失的应用，可以通过在配置文件中增加 innodb_doublewrite=0 参数设置来关闭 innodb_doublewrite，以尽量满足性能方面的要求

8、join_buffer_size & sort_buffer_size

​ 如果 Sort_merge_passes 的值很大，可以考虑调整参数 sort_buffer_size 的值来增大排序缓存区，以改善带有 order by 子句或 group 子句 SQL 的性能。

mysql> show global status like 'Sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
+-------------------+-------+
1 row in set (0.00 sec)

​ 注意：join_buffer_size 和 sort_buffer_size 都是面向客户服务线程分配的，如果设置过大造成内存浪费，甚至导致内存交换。尤其是 join_buffer_size ,如果是多表关联的复杂查询，还可能会分配多个 join buffer ,因此最好是设置较小的全局 join_buffer_size ,而对需要做复杂连接操作的 session 单独设置较大的 join_buffer_size。