MySQL8.0内存相关参数介绍

朱雀 2022-10-31 14:58 175阅读 0赞

# MySQL8.0内存相关参数介绍 #

首先我们给出[MySQL][]内存使用的计算公式：

MySQL理论上使用的内存 = 全局共享内存 + max\_connections×线程独享内存。

也就是：innodb\_buffer\_pool\_size + innodb\_log\_buffer\_size + thread\_cache\_size +table\_open\_cache + table\_definition\_cache +key\_buffer\_size + max\_connections \*（ thread\_stack+ sort\_buffer\_size+join\_buffer\_size + read\_buffer\_size+read\_rnd\_buffer\_size+ binlog\_cache\_size+tmp\_table\_size）

下面我们按照全局内存参数与线程独享参数分类，简单介绍下相关参数的作用。

## ##

### 全局共享内存 ###

innodb\_buffer\_pool\_size

innodb\_buffer\_pool\_size这个参数是对Mysql数据库最重要的参数之一，它对 InnoDB 存储引擎的作用类似于 Key Buffer Cache 对 MyISAM 存储引擎的影响，主要区别是 InnoDB Buffer Pool 不仅仅缓存索引数据，会缓存表的数据，而且完全按照数据文件中的数据快结构信息来缓存，这一点和 Oracle SGA 中的 database buffer cache 类似，因此在SHOW ENGINE innodb status中查到的Buffer pool size要乘以16K。

可以通过 (Innodb\_buffer\_pool\_read\_requests - Innodb\_buffer\_pool\_reads) / Innodb\_buffer\_pool\_read\_requests \* 100% 计算得到 InnoDB Buffer Pool 的命中率。

innodb\_change\_buffering

change buffering是MySQL5.5加入的新特性，change buffering是insert buffer的加强，insert buffer只针对insert有效，change buffering对insert、delete、update(delete+insert)、purge都有效。当修改一个索引块(secondary index)时的数据时，索引块在buffter pool中不存在，修改信息就会被cache在change buffer中，当通过索引扫描把需要的索引块读取到buffer pool时，会和change buffer中修改信息合并，再择机写回disk。

目的还是为了减少随机IO带来性能损耗，说明白了：把随机IO尽量变成顺序IO。现在SSD盛行，在SSD上随机访问和顺序访问性能几乎差不多的情况下，change buffering特性不会带来多大的性能提升，但对于廉价的机械硬盘，这个参数还是能帮助提高性能的。

change buffering由参数innodb\_change\_buffering控制:

*  all: buffer inserts, delete-marking operations, and purges.
 *  none: Do not buffer any operations.
 *  inserts: Buffer insert operations.
 *  deletes: Buffer delete-marking operations.
 *  changes: Buffer both inserts and delete-marking.
 *  purges: Buffer the physical deletion operations that happen in the background.

注意这个内存是在Innodb的buffer pool中分配的，计算总内存的时候不用算它。

innodb\_change\_buffer\_max\_size

表示change buffer在buffer pool中的最大占比，默认25%，最大50%。如果系统中有严重的insert、update并且还有活跃的delete时，就增大max\_size；针对不更改数据的纯报表系统，可以减小该参数值。

innodb\_log\_buffer\_size

这是 InnoDB 存储引擎的事务日志所使用的缓冲区。为了提高性能，也是先将信息写入 Innofb Log Buffer 中，当满足 innodb\_flush\_log\_trx\_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。innodb\_flush\_log\_trx\_commit 参数可以设置为0，1，2，解释如下：

*  0：log buffer中的数据将以每秒一次的频率写入到logfile中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失；
 *  1：在每次事务提交的时候将log buffer 中的数据都会写入到logfile，同时也会触发文件系统到磁盘的同步，该模式是最安全的，但也是最慢的一种方式；
 *  2：事务提交会触发log buffer 到logfile的刷新，但并不会触发磁盘文件系统到磁盘的同步，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

thread\_cache\_size

线程池缓存大小，当客户端断开连接后将当前线程缓存起来，当在接到新的连接请求时快速响应无需创建新的线程 。这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。可以通过(Connections - Threads\_created) / Connections \* 100% 计算出连接线程缓存的命中率。也可以通过如下几个MySQL状态值来适当调整线程池的大小：

当 Threads\_cached 越来越少 但 Threads\_connected 始终不降，且 Threads\_created 持续升高，可适当增加 thread\_cache\_size 的大小。

table\_open\_cache

table\_open\_cache指定表高速缓存的大小，用来缓存表文件的文件句柄信息。当我们的客户端程序提交Query给MySQL的时候，MySQL需要对Query所涉及到的每一个表都取得一个表文件句柄信息，如果没有Table Cache，那么MySQL就不得不频繁的进行打开关闭文件操作，无疑会对系统性能产生一定的影响，每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。注意，这里设置的是可以缓存的表文件句柄信息的数目，而不是内存空间的大小。

通过检查峰值时间的状态值Open\_tables和Opened\_tables，可以决定是否需要增加table\_open\_cache的值。其中Open\_tables是当前正在打开表的数量，Opened\_tables是所有已经打开表的数量。注意，不能盲目地把table\_open\_cache设置成很大的值，设置太大超过了shell的文件描述符(通过ulimit -n查看)，造成文件描述符不足，从而造成性能不稳定或者连接失败。如果发现open\_tables等于table\_open\_cache，并且opened\_tables在不断增长，那么你就需要增加table\_open\_cache的值了(上述状态值可通过SHOW GLOBAL STATUS LIKE 'Open%tables'获得)。如果Open\_tables的值已经接近table\_cache的值，且Opened\_tables还在不断变大，则说明mysql正在将缓存的表释放以容纳新的表，此时可能需要加大table\_cache的值。对于大多数情况，比较适合的值：

*  Open\_tables / Opened\_tables >= 0.85
 *  Open\_tables / table\_cache <= 0.95

建议把MySQL数据库放在生产环境中试运行一段时间，然后把参数的值调整得比Opened\_tables的数值大一些，并且保证在比较高负载的极端条件下依然比Opened\_tables略大。

table\_definition\_cache

table\_definition\_cache和table\_open\_cache类似，前者缓存frm文件，关于后者，文档中并没有说明，应该是ibd/MYI/MYD；

状态值：

Open\_table\_definitions：表定义文件.frm被缓存的数量

Opened\_table\_definitions：历史上总共被缓存过的,frm文件数量

key\_buffer\_size

key\_buffer\_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key\_read\_requests和Key\_reads，可以知道key\_buffer\_size设置是否合理。比例key\_reads /key\_read\_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key\_read%'获得）。key\_buffer\_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created\_tmp\_disk\_tables得知详情。

max\_connections

MySQL的最大连接数，增加该值增加mysqld 要求的文件描述符的数量。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。数值过小会经常出现ERROR 1040: Too many connections错误，可以过’conn%’通配符查看当前状态的连接数量，以定夺该值的大小。max\_used\_connections / max\_connections \* 100% （理想值≈ 85%） 如果max\_used\_connections跟max\_connections相同 那么就是max\_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。

## ##

### 线程/会话/连接独享内存 ###

binlog\_cache\_size

为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存，可以提高记录bin-log的效率，默认32K，没有大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。

数据库binlog\_cache\_size的使用情况，可以查看：Binlog\_cache\_disk\_use表示因为我们binlog\_cache\_size设计的内存不足导致缓存二进制日志用到了临时文件的次数，Binlog\_cache\_use 表示 用binlog\_cache\_size缓存的次数

tmp\_table\_size和max\_heap\_table\_size

tmp\_table\_size规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp\_table\_size和max\_heap\_table\_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下，默认:

mysql> show variables like "tmpdir";
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | tmpdir        | /tmp/ |
    +---------------+-------+

优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的。如果需要的话并且你有很多group by语句，并且你有很多内存，增大tmp\_table\_size(和max\_heap\_table\_size)的值。这个变量不适用与用户创建的内存表(memory table)。

可以比较内部基于磁盘的临时表的总数和创建在内存中的临时表的总数（Created\_tmp\_disk\_tables和Created\_tmp\_tables），一般的比例关系是:

Created\_tmp\_disk\_tables/Created\_tmp\_tables<5%

max\_heap\_table\_size定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max\_heap\_table\_size = xxx。

[MySQL]: https://cloud.tencent.com/product/cdb?from=10680