MySQL InnoDB内存结构

leokim / / MYSQL

  • innodb_buffer_pool

  • innodb_additional_mem_pool_size

  • innodb_log_buffer_size

这3个内存参数是可见的，可控的

还有一些是看不见，不可控的靠mysql内部管理的：

  • adaptive index hash

  • system dictionary hash

  • locking system

  • sync_array

  • os_events

Innodb内存结构

image.png

innodb在5.6以前undo 是放在共享表空间里面的

在高并发的时候，可能会导致ibdata1文件持续增长

ibdata1这个共享表空间又是不能自动释放的 

如果在高并发一直没有提交有大量事务等待回滚的时候会把大量脏数据放在undo里面

所以会导致ibdata1特别庞大 特别是32位的系统下

需要我们及时提交事务 不要把大量事务堆积在innodb redo

或者在初始化的时候把ibdata1初始化大一些 最好在1G以上

5.6可以有独立undo 但是很麻烦 不建议使用

5.7可以设置undo大小和路径、

1.innodb_buffer_pool

    • Innodb高速缓冲（简称IBP），是Innodb最重要的组成部分

    • InnoDB不依赖OS,而自己缓存了所有数据，把索引和数据（包括索引数据，行数据，等等）放在内存里面。这点跟MyISAM有差别MyISAM只是把索引放在key buffer里面，数据还是用OS的page cache进行缓存

    • 应该把innodb_buffer_pool_size设置得大一些，建议设置为可用RAM的50%~80%

        • 如果前端使用连接池 那么设置能70%会相对安全一点，因为前端要有连接池 就不会有特别大的连接连接进来，PGA分配的内存相对就没那么大，所以我们可以留更多的内存给SGA

        • 但是前端如果有大量连接并发的时候，我们建议innodb_buffer_pool_size设置小一点 50%

    • 查询或更新的时候会对IBP加锁，影响并发，innodb会把内存分成多分，来缓解并发带来的影响

    • IBP有一块buffer用于插入缓冲，在插入的时候，先写入内存，之后再合并后顺序写入磁盘。在合并到磁盘上的时候，会引发较大的IO操作，对实时操作造成影响（看上去是抖动，tps变低）

    • show global status like 'innodb_buffer_pool_%' 查看IBP状态们单位是page(16kb)

    • Innodb_buffer_pool_wait_free 如果较大，需要加大IBP设置

2. innodb_buffer_pool_instances 

        如果内存不超过8G，就没有必要分多个instance

        分多个instance时，最好每个instance至少2G以上8G以下

        innodb_buffer_pool_instances也不要太多，一般不超过8

image.png

image.png

innodb_buffer_pool 现在默认值是128M（老版本默认8M，坑很多）

[MySQL FAQ]系列 — 数据不算大，备份却非常慢

http://imysql.com/2009/09/18/mysql-faq-why-backup-is-so-slow.html

3.Adaptive Hash Index

自适应哈希索引，用来管理buffer pool的哈希索引

  • adaotive index hash, size= innodb_buffer_pool / 64, 随着buffer的频繁更新，会随之上升

  • system dictionary hash, size=innodb_buffer_size / 256，基本固定

  • memory for sync_array, which is used for syncronization primitives, size=OS_THREADS（当前线程数） * 152

  • memory for os_events, which are also used for syncronization primitives, size=OS_THREADS * 216

  • memory for locking system, size = 5*4*NBLOCKS(NBLOCK, innodb buffer pool的block数量)，随着并发、行锁增加，会随之上升

概念：

  • OS_THREADS=如果innodb_buffer_pool_size >= 1000Mb, 则为:50000, 否则如果innodb_buffer_pool_size >= 8Mb,则为:10000, 否则为:1000（*nixes平台下通用）

  • NBLOCKS = innodb_buffer_pool_size/8192

image.png

image.png

如果innodb_buffer_pool > 1000MB, OS_THREADS*368=17.5MB

否则如果innodb_buffer_pool > 8MB , 3,5m

image.png

我们只能设置innodb_buffer_pool_size为一个合适值 因为其他几个我们没有办法控制

4.innodb_additional_mem_pool_size

数据字典以及内部数据结构缓存，表数据量越多，相应的内容需要越大。

默认8M，通常设置为8~32M足够，一般建议设置为16M，如果确实不够，那么会从系统中请求增加分配内存，并且错误日志中会提醒，目前至少还未发生过。

5.innodb_log_buffer_size

show global status查看Innodb_log_waits是否大于0， 是的话，就需要提高innodb_buffer_pool_size，否则维持原样。

show global status查看30~60秒钟Innodb_os_log_written 的间隔差异值，即可计算出innodb_buffer_pool_size设置多大合适。