mysql server innodb_每天进步一点点————优化MySQL SERVER（1）InnoDB篇

InnoDB内存优化

InnoDB缓存机制

InnoDB用一块内存区做IO缓存池，该缓存池不仅用来缓存InnoDB的索引块，而且也能用来缓存InnoDB的数据块，这一点与MyISAM不同。

在内部，InnoDB缓存池逻辑上由freelist ,flush list 和LRU list组成。顾名思义，free list是空闲缓存块列表，flush list是需要刷新到磁盘的缓存块列表，而LRU list是InnoDB正在使用的缓存块，它是InnoDB buffer pool的核心。

臧也的刷新存在于flushlist 和LRU list这两个链表，LRU上也存在可以刷新的脏页，这里是直接可以刷新的，默认BP(innodb_buffer_pool)中不存在可用的数据页的时候，会扫描LRU list 尾部的INNODB_LRU_SCAN_DEPTH个数据页(默认是1024个数据页),进行相关刷新操作。从LRU list 淘汰的数据页会立即放入到FREE LIST中去。

InnoDB_buffer_pool_size

innodb_buffer_pool_size决定InnoDB存储引擎表数据和索引数据的最大缓存大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O越少，性能也就越高，一般在一个专用的数据库服务器上，可以将80%的物理内存分配给InnoDB buffer pool但是一定要注意避免设置过大而导致页交换。

查看buffer pool的使用情况

[root@localhost~]# mysqladmin -uroot -p -S /tmp/mysql.sock ext|grep -i innodb_buffer_pool

| Innodb_buffer_pool_dump_status                | not started |

| Innodb_buffer_pool_load_status                | not started |

| Innodb_buffer_pool_pages_data                 | 7159        |

| Innodb_buffer_pool_bytes_data                 | 117293056   |

| Innodb_buffer_pool_pages_dirty                | 0           |

| Innodb_buffer_pool_bytes_dirty                | 0           |

| Innodb_buffer_pool_pages_flushed              | 35216       |

| Innodb_buffer_pool_pages_free                 | 1031        |

| Innodb_buffer_pool_pages_misc                 | 1           |

| Innodb_buffer_pool_pages_total                | 8191        |

| Innodb_buffer_pool_read_ahead_rnd             | 0           |

| Innodb_buffer_pool_read_ahead                 | 9272        |

|Innodb_buffer_pool_read_ahead_evicted        | 1612        |

| Innodb_buffer_pool_read_requests              | 48514207    |

| Innodb_buffer_pool_reads                      | 19285       |

| Innodb_buffer_pool_wait_free                  | 0           |

| Innodb_buffer_pool_write_requests             | 17836900    |

可以通过以下公式计算InnoDB缓存池的命中率

(1-innodb_buffer_pool_reads/inodb_buffer_pool_read_request)*100

如果命中率太低，则应考虑扩充内存，增加innodb_buffer_pool_size的值。

调整old subilst大小

在LRU list中，old sublist的比例由系统参数innodb_old_blocks_pct决定，其取值范围是5~95，默认是37(3/8)

mysql>show global variables like '%innodb_old_blocks_pct%';

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

| Variable_name         | Value |

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

| innodb_old_blocks_pct | 37    |

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

1 row in set (0.00 sec)

可以根据innoDB Monitor的输出信息来调整Innodb_old_blocks_pct的值。例如，在没有较大表扫描或者索引的情况下，如果young/s的值很低，可以适当增加innodb_old_block_pct的值或者减少innodb_old_blocks_time的值。

调整innodb_old_blocks_time设置

innodb_old_blocks_time参数决定了缓存数据块由oldsublist转到young sublist的快慢，当一个缓存数据块被插入到midpoint后，至少要在old sublist 停留超过innodb_old_blocks_time(ms)后，才有可能被转移到new sublist。

如果non-young/s很低，young/s很高，就应该考虑将innodb_old_blocks_time适当调大，以防止表扫描将真正的热数据淘汰。这个值可以动态设置，如果要进行大的表扫描操作，可以很方便地临时做调整。

调整缓存池数量，减少对内部缓存池数据结构的征用

MySQL内部不同线程对InnoDB缓存池的访问在某些阶段是互斥的，这种内部竞争也会产生性能问题，尤其在高并发和buffer pool较大的情况下。为了解决这个问题，innoDB的缓存系统引入了innodb_buffer_pool_instances配置参数，对于较大的缓存池，适当增加此参数的值，可以降低并发导致的内部缓存访问冲突。innodb_buffer_pool_size指定大小的缓存平分为innodb_buffer_pool_instances个buffer pool

控制Innodb buffer刷新，延长数据缓存时间，减少磁盘I/O

在InnoDB找不到干净的可用缓存页或者检查点被触发等情况下，InnoDB的后台线程就会开始把“脏的缓存页”回写到磁盘文件中，这个过程叫做缓存刷新。

InnoDB buffer pool的刷新快慢主要取决于两个参数。

l  innoDB_max_dirty_pages_pct，它控制缓存池中脏页的最大比例，默认值是75%，如果脏页的数量达到或者超过该值，InnoDB的后台线程开始缓存刷新。

l  另个是innodb_io_capactiy，它代表磁盘系统的IO能力，其值在一定程度上代表磁盘每秒可完成I/O的次数。默认是200如果是磁盘转速较低可以设置小一点，如果磁盘IO能力较强可以设置大一些。

innodb_io_capacity决定一批脏数页的数量，当缓存池脏页的比例达到innodb_max_dirty_pages_pct时，InnoDB大约将Innodb_io_capacity个改变的缓存页面刷新到磁盘，当脏页比例小于innodb_max_dirty_pages_pct时，如果innodb_adaptive_flushing的设置为true，innoDB将根据函数buf_flush_get_desired_flush_rate返回的重做日志产生的速度来确定要刷新的脏页数。在合并插入缓存时，InnoDB每次合并的页数是0.05*innodb_io_capactiy.

可以根据InnoDB Monitor的值来调整innodb_max_dirty_pages_pct和innodb_io_capacity。弱innodb_buffer_pool_wait_free的值增长较快，则说明InnoDB经常在等待空闲缓存页，如果无法增大缓存池，那么应将Innodb_max_dirty_pages_pct的值调小或将InnoDB_io_capactiy的值提高，以加快脏页的刷新。

InnoDB doublewrite(双写)

由于MySQL的数据页大小(一般16KB)与操作系统的IO数据页大小(4KB)不一致，无法保证InnoDB缓存页被完整、一致地刷新到磁盘，而InnoDB的redo日志只记录了数据页改变的部分，并未记录数据页的完整前像，当发生部分写或断裂写(比如将缓存页的第一个4KB写入后服务器断电)会出现页面无法恢复的问题，为了解决这个问题，InnoDB引入了doublewrite技术

默认情况下：InnoDB doublewrite是开启的

mysql>show global variables like '%doublewrite%';

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

| Variable_name      | Value |

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

| innodb_doublewrite | ON    |

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

1 row in set (0.00 sec)

调整用户服务线程排序缓存区

如果sort_merge_passes的值很大

mysql>show global status like '%sort_merge_passes%';

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

| Variable_name     | Value |

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

| Sort_merge_passes | 0     |

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

1 row in set (0.00 sec)

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

对于无法通过索引进行连接的操作查询，可以尝试通过增大join_buffer_size的值来改善性能。不过需要注意的是sort buffer和join buffer都是面向session的，所以不可设置过大，尤其是join buffer，如果是多表关联的复杂查询，还可能会分配多个join buffer，因此最好的策略是设置较小的全局join_buffer_size，而需要复杂连接的session单独设置较大的join_buffer_size。