MySQL存储引擎-----内存结构
内存结构
InnoDB Buffer Pool(IBP)
缓冲池是主内存中的一个区域，在InnoDB访问表和索引数据时会在其中进行 缓存。缓冲池允许直接从内存中处理经常使用的数据，从而加快了处理速度。在专用服务器上，通常将多达50-75％的物理内存分配给缓冲池。
作用
用来缓冲、缓存，MySQL的数据页（data page ）和索引页、UNDO。MySQL中最大的、最重要的内存区域
管理
查询缓冲池大小（默认128M）
mysql> select @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 134217728 | +---------------------------+ 1 row in set (0.00 sec)
查询实例个数
mysql> select @@innodb_buffer_pool_instances; +--------------------------------+ | @@innodb_buffer_pool_instances | +--------------------------------+ | 1 | +--------------------------------+ 1 row in set (0.00 sec)
配置buffer_pool
临时设置
mysql> set global innodb_buffer_pool_size=268435456; Query OK, 0 rows affected (0.01 sec) mysql> select @@innodb_buffer_pool_size/1024/1024; +-------------------------------------+ | @@innodb_buffer_pool_size/1024/1024 | +-------------------------------------+ | 256.00000000 | +-------------------------------------+ 1 row in set (0.00 sec)
永久设置
vim /etc/my.cnf
#添加参数
[mysqld]
innodb_buffer_pool_size=256M
怎么判断buffer_pool的大小是否合适？
mysql> show global status like 'Innodb_buffer_pool_wait_free'; +------------------------------+-------+ | Variable_name | Value | +------------------------------+-------+ | Innodb_buffer_pool_wait_free | 0 | +------------------------------+-------+ 1 row in set (0.00 sec)
当Innodb_buffer_pool_wait_free的值大于10的时候，就需要修改
产生不够用的情景
1. 设置太小。 2. 大事务。 3. chpt触发不及时。 4. IO比较慢
buffer pool的LRU算法
buffer pool使用LRU（最近最少使用）算法管理内存中的数据页。该算法将buffer pool分割成两部分列表来保存热点数据：
将大量页面保留在new的子列表中（5/8）。old的子列表包含较少使用的页面（3/8）。old页面中尾部的数据页将会被驱逐。
默认情况下，该算法的操作如下：
-
当
InnoDB将页面读入缓冲池时，它首先将其插入中点（old子列表的head部分）。可以读取页面，因为它是用户的操作（例如SQL查询）所必需的，或作为InnoDB自动执行的read-ahead（预读）操作的一部分 -
访问old子列表中的页面 使其变为“ young ”，将其移至新子列表的开头。如果由于用户启动的操作而需要读取页面，则将立即进行首次访问，并使页面年轻。
-
随着数据库的运行，通过移至列表的尾部，缓冲池中未被访问的页面将“ 老化 ”。new和old子列表中的页面都会随着其他页面的更新而老化。随着将数据页插入midpoint，旧子列表中的页面也会老化。最终，未使用的页面到达旧子列表的尾部并被逐出
默认情况下，被访问的数据也会加入到new的部分，但当mysqldump或查询语句是全表扫描的时候，会把数据页加载到buffer pool，并驱逐等量数据页。
监控mysql的buffer pool
mysql> show engine innodb status\G ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 274726912 Dictionary memory allocated 389445 Buffer pool size 16384 Free buffers 15076 Database pages 1307 Old database pages 502 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 1165, created 142, written 156 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 1307, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
监控指标描述
| 名称 | 描述 |
| Total memory allocated |
缓冲池分配的总内存（以字节为单位） |
| Dictionary memory allocated |
InnoDB数据字典分配的总内存，以字节为单位。 |
| Buffer pool size |
buffer pool的数据页大小，默认16k |
| Free buffers |
buffer pool中空闲数据页大小。当顺序写的时候可以用到15/16k，随机写的时候用到50% |
| Database pages |
缓冲池LRU列表的页面总大小。 |
| Old database pages |
缓冲池old子列表的页面总大小。 |
| Modified db pages |
缓冲池中当前修改的页面数。 |
| Pending reads |
等待读入缓冲池的缓冲池页面数 |
| Pending writes LRU |
从LRU列表的底部开始写入的缓冲池中的旧脏页数 |
| Pending writes flush lis |
检查点期间要刷新的缓冲池页面数 |
| Pending writes single page |
缓冲池中暂挂的独立页面写入数。 |
| Pages made young |
缓冲池LRU列表中变年轻的页面总数（已移至“ new ”页面的子列表的开头） |
| Pages made not young |
缓冲池LRU列表中未设置为年轻的页面总数（保留在“ old ”子列表中但未设置为年轻的页面） |
| youngs/s |
每秒平均访问缓冲池LRU列表中的旧页面所导致的页面变小 |
| non-youngs/s |
每秒平均访问缓冲池LRU列表中的旧页面导致的页面不年轻 |
| Pages read |
从缓冲池读取的页面总数。 |
| Pages created |
在缓冲池中创建的页面总数。 |
| Pages written |
从缓冲池写入的页面总数。 |
| reads/s |
每秒平均缓冲池页面读取数。 |
| creates/s |
每秒创建的每秒平均缓冲池页面数 |
| writes/s |
每秒平均缓冲池页面写入数 |
| Buffer pool hit rate |
从缓冲池内存与磁盘存储读取的页面的缓冲池页面命中率 |
| young-making rate |
页面访问导致页面变年轻的平均命中率 |
| not (young-making rate) |
页面访问未使页面变年轻的平均命中率 |
| Pages read ahead |
预读操作的每秒平均数 |
| Pages evicted without access |
每秒从缓冲池访问而未访问的页面的平均值 |
| Random read ahead |
随机预读操作的每秒平均数 |
| LRU len |
缓冲池LRU列表的页面总大小 |
| unzip_LRU len |
缓冲池unzip_LRU列表的页面总大小 |
| I/O sum |
最近50秒内访问的缓冲池LRU列表页面的总数。 |
| I/O cur |
已访问的缓冲池LRU列表页面的总数。 |
| I/O unzip sum |
已访问的缓冲池unzip_LRU列表页面的总数。 |
| I/O unzip cur |
已访问的缓冲池unzip_LRU列表页面的总数。 |
change buffer
缓存辅助索引由于DML语句（如：INSERT， UPDATE或 DELETE）导致change buffer变更的数据。
Change buffer 功能是临时缓冲辅助索引需要的数据更新。与聚簇索引不同，辅助索引值不是惟一的，辅助索引的插入时通常是随机的。删除和更新辅助索引会影响到索引树中不相邻的索引页。
在表上执行insert的时候，由于辅助索引列是未排序的，需要大量I/O才能使辅助索引保持最新。 当相关索引页不在 buffer pool中，change buffer 将merge缓存到辅助索引条目 ，从而避免了不立即从磁盘读取页面而避免了昂贵的I / O操作。当页面加载到缓冲池中时，缓冲的更改将合并，更新的页面随后将刷新到磁盘。
当我们需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。
当有大量DML操作的业务，开启change buffer会减少磁盘I/O。但是，更改缓冲区占用了缓冲池的一部分，从而减少了可用于缓存数据页的内存。如果表中的辅助索引列较少，可以考虑关闭change buffer。
配置change buffer
通过 innodb_change_buffering 参数来配置change buffer，可用值如下表：
| 可选值 | 作用 |
| all | 默认值，记录insert、update、delete操作 |
| none | 不缓冲任何操作 |
inserts |
缓冲区插入操作。 |
deletes |
缓冲区删除标记操作。 |
changes |
缓冲插入和删除标记操作。 |
purges |
缓冲在后台发生的物理删除操作。 |
innodb_change_buffer_max_size 变量允许将change buffer 的最大大小配置为 buffer pool 总大小的百分比。默认情况下， innodb_change_buffer_max_size设置为25。最大设置为50。当表具有大量插入的时候，merge跟不上buffer pool的更新，change buffer将达到innodb_change_buffer_max_size的值
监控change buffer
1、查看change buffer状态信息
通过SHOW ENGINE INNODB STATUS来查看
------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 3 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s
查看change buffer指标
通过查询 INFORMATION_SCHEMA.INNODB_METRICS来获取
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem='change_buffer'; +---------------------------------+-------------------------------------------------------+ | NAME | COMMENT | +---------------------------------+-------------------------------------------------------+ | ibuf_merges_insert | Number of inserted records merged by change buffering | | ibuf_merges_delete_mark | Number of deleted records merged by change buffering | | ibuf_merges_delete | Number of purge records merged by change buffering | | ibuf_merges_discard_insert | Number of insert merged operations discarded | | ibuf_merges_discard_delete_mark | Number of deleted merged operations discarded | | ibuf_merges_discard_delete | Number of purge merged operations discarded | | ibuf_merges | Number of change buffer merges | | ibuf_size | Change buffer size in pages | +---------------------------------+-------------------------------------------------------+ 8 rows in set (0.00 sec)
查询change buffer元数据
通过INFORMATION_SCHEMA.INNODB_BUFFER_PAGE 查询
mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE -> WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages, -> (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages, -> (SELECT ((change_buffer_pages/total_pages)*100)) -> AS change_buffer_page_percentage; +---------------------+-------------+-------------------------------+ | change_buffer_pages | total_pages | change_buffer_page_percentage | +---------------------+-------------+-------------------------------+ | 2 | 8192 | 0.0244 | +---------------------+-------------+-------------------------------+ 1 row in set (0.17 sec)
查询锁信息
mysql> SELECT * FROM performance_schema.setup_instruments -> WHERE NAME LIKE '%wait/synch/mutex/innodb/ibuf%'; +-------------------------------------------------------+---------+-------+------------+------------+---------------+ | NAME | ENABLED | TIMED | PROPERTIES | VOLATILITY | DOCUMENTATION | +-------------------------------------------------------+---------+-------+------------+------------+---------------+ | wait/synch/mutex/innodb/ibuf_bitmap_mutex | NO | NO | | 0 | NULL | | wait/synch/mutex/innodb/ibuf_mutex | NO | NO | | 0 | NULL | | wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | NO | NO | | 0 | NULL | +-------------------------------------------------------+---------+-------+------------+------------+---------------+ 3 rows in set (0.37 sec)
AHI 自适应hash索引 （建议关闭此功能）
自动评估"热"的内存索引page,生成HASH索引表。 帮助InnoDB快速读取索引页。加快索引读取的效果。 相当与索引的索引。
只适用于等值查询，例如=， <=>，in
查看开启和关闭
mysql> show variables like '%ap%hash_index'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_adaptive_hash_index | ON | +----------------------------+-------+ 1 row in set (0.00 sec)
关闭
set global innodb_adaptive_hash_index=off
Log Buffer（redo buffer）
日志缓冲区是用于保存要写入磁盘上的日志文件的数据的存储区
管理日志缓冲
查询大小（默认16M）
mysql> select @@innodb_log_buffer_size/1024/1024; +------------------------------------+ | @@innodb_log_buffer_size/1024/1024 | +------------------------------------+ | 16.00000000 | +------------------------------------+ 1 row in set (0.00 sec)
大小设置
和innodb_log_file_size有关，1-N倍 设置方式 ： vim /etc/my.cnf innodb_log_buffer_size=33554432 重启生效： [root@db01 data]# /etc/init.d/mysqld restart show global status like '%innodb%log%';
posted on 2020-09-15 00:50 hopeless-dream 阅读(169) 评论(0) 编辑 收藏 举报
