mysql 8.0优化配置_MySQL · 8.0版本更新 · 性能优化篇-阿里云开发者社区

ゞ浴缸里的玫瑰 2022-10-27 02:18 146阅读 0赞

本文主要总结下MySQL在8.0版本和性能相关的一些改动，随着新的小版本的发布，本文将不断进行更新，直到正式GA。

|已更新版本|

| MySQL 8.0.0

MySQL 8.0.0

这个问题最早是facebook的工程师Domas报的一个bug，InnoDB使用多线程来进行Undo Purge操作，但分配undo的策略不太合理，直接轮询分配。这意味着如果从一张表上删除大量数据，这N个purge线程可能产生大量的索引锁冲突(例如索引页合并及重组织)

在WL\#9387中，在parse undo log时，通过table\_id进行分组存储，在分发时确保同一个table id的记录被分配给同一个线程。(参考函数 trx\_purge\_attach\_undo\_recs)

当然这也意味着合理的不会产生冲突的单表操作, 无法利用到多线程purge了，也算是一个弊端。

这个算是众望所归的改进了，由Percona贡献的补丁(bug\#75534)，主要是对InnoDB的buffer pool mutex这个大锁进行了拆分，降低锁冲突：

Name

Desc

buf\_pool\_t::LRU\_list\_mutex

用于保护LRU链表，例如从LRU链表上刷脏或驱逐Page

buf\_pool\_t::free\_list\_mutex

保护free list及withdraw list(online resize)

buf\_pool\_t::zip\_free\_mutex

保护zip\_free数组，该数组用于维护对压缩表产生的非标准page size的内存维护, ref buf/buf0buddy.cc

buf\_pool\_t::zip\_hash\_mutex

保护zip\_hash, 其中存储压缩页block

buf\_pool\_t::flush\_state\_mutex

保护init\_flush, n\_flush, no\_flush等数组

分配空闲block(buf\_LRU\_get\_free\_block):

从free list获取: buf\_pool\_t::free\_list\_mutex

从unzip\_lru/lru上驱逐一个空闲page，需要buf\_pool\_t::LRU\_list\_mutex

批量扫描LRU(buf\_do\_LRU\_batch): buf\_pool\_t::LRU\_list\_mutex

批量扫描FLUSH\_LIST(buf\_do\_flush\_list\_batch): buf\_pool\_t::flush\_list\_mutex

脏页加入到flush\_list(buf\_flush\_insert\_into\_flush\_list): buf\_pool\_t::flush\_list\_mutex

脏页写回磁盘后，从flush list上移除(buf\_flush\_write\_complete): buf\_pool\_t::flush\_state\_mutex/flush\_list\_mutex

从LRU上驱逐Page(buf\_LRU\_free\_page):buf\_pool\_t::LRU\_list\_mutex， 及buf\_pool\_t::free\_list\_mutex(buf\_LRU\_block\_free\_non\_file\_page)

buf\_flush\_LRU\_list\_batch 使用mutex\_enter\_nowait 来获取block锁，如果获取失败，说明正被其他session占用，忽略该block.

有些变量的修改从通过buf\_pool\_t::mutex保护，修改成通过memory barrior来保护(os\_rmb or os\_wmb), 例如下面几个函数中均有体现：

btr\_search\_enable()

buf\_resize\_thread()

buf\_get\_withdraw\_depth()

buf\_LRU\_get\_free\_block()

通过对锁的拆分，降低了全局大锁的竞争，提升了buffer pool的扩展性，这个特性其实在Percona Server中很多年前就有了， 但直到MySQL8.0版本才合并进来。

主要是用于为优化器提供更准确的信息，即数据是存在与磁盘还是内存中， 这样优化器可以更准确的做出代价计算。

增加一个全局对象(buf\_stat\_per\_index\_t)来管理所有的索引页计数

为了避免引入新的全局锁开销，实现并使用一个lock-free的hash结构("include/ut0lock\_free\_hash.h)来存储索引信息，key值为索引id。(目前索引id具有唯一性，但不排除未来可能发生改变)。

增加计数:

Page刚从磁盘读入内存 (buf\_page\_io\_complete --> buf\_page\_monitor)

创建一个新的page (btr\_page\_create)

递减计数: Page从LRU上释放时进行递减(buf\_LRU\_block\_remove\_hashed)

增加新的information\_schema.innodb\_cached\_indexs 打印每个索引在内存中的page个数，其结构如下：

mysql> show create table INNODB\_CACHED\_INDEXES\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: INNODB\_CACHED\_INDEXES

Create Table: CREATE TEMPORARY TABLE \`INNODB\_CACHED\_INDEXES\` (

\`SPACE\_ID\` int(11) unsigned NOT NULL DEFAULT '0',

\`INDEX\_ID\` bigint(21) unsigned NOT NULL DEFAULT '0',

\`N\_CACHED\_PAGES\` bigint(21) unsigned NOT NULL DEFAULT '0'

) ENGINE=MEMORY DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

\#\#\# 和表名/索引名关联

SELECT

tables.name AS table\_name,

indexes.name AS index\_name,

cached.n\_cached\_pages AS n\_cached\_pages

FROM

information\_schema.innodb\_cached\_indexes AS cached,

information\_schema.innodb\_sys\_indexes AS indexes,

information\_schema.innodb\_sys\_tables AS tables

WHERE

cached.index\_id = indexes.index\_id

AND

indexes.table\_id = tables.table\_id;

增加选项，可以动态关闭死锁检测，这对诸如热点更新这样的场景效果显著，之前已专门写了篇博客，感兴趣的自取

这个优化来自alisql的贡献，主要是优化了InnoDB Redo的扩展性问题，通过双buffer机制，允许在写日志到磁盘的同时，也允许进行mtr commit，具体参阅我写的这篇月报

为了减少对Btree的锁占用，InnoDB在读取数据时实际上是有一个小的缓存buffer。对于连续记录扫描，InnoDB在满足比较严格的条件时采用row cache的方式连续读取8条记录(并将记录格式转换成MySQL Format)，存储在线程私有的row\_prebuilt\_t::fetch\_cache中；这样一次寻路就可以获取多条记录，在server层处理完一条记录后，可以直接从cache中取数据而无需再次寻路，直到cache中数据取完，再进行下一轮。

在WL\#7093中引入了新的接口，由于优化器可以估算可能读取的行数，因此可以提供给存储引擎一个更合适大小的row buffer来存储需要的数据。大批量的连续数据扫描的性能将受益于更大的record buffer。

Record buffer由优化器来自动决定是否开启，增加新的类Record\_buffer进行管理, Record buffer的大小最大不超过128KB， 目前是hard code的，不可以配置。

判断及分配record buffer函数: set\_record\_buffer， 并通过新的API接口(handler::ha\_set\_record\_buffer)传到引擎层

buffer本身是引擎无关的，在sever层分配，通过handler成员m\_record\_buffer传递到引擎层。

增加新的接口，判断是否支持Record buffer， 目前仅InnoDB支持，需要满足如下条件 (ref set\_record\_buffer):

access type 不是 ref, ref\_or\_null, index\_merge, range, index 或者ALL

不是临时表

不是loose index scan

进入InnoDB引擎层判断((row\_prebuilt\_t::can\_prefetch\_records))

return select\_lock\_type == LOCK\_NONE // 只读查询

&& !m\_no\_prefetch // 允许prefetch

&& !templ\_contains\_blob // 没有BLOB, TEXT, JSON, GEOMETRY这些大列