龙空技术网

mysql8内存相关配置

linux运维菜 439

前言:

眼前咱们对“mysql 内存模式”大概比较注意,小伙伴们都需要学习一些“mysql 内存模式”的相关资讯。那么小编也在网络上网罗了一些有关“mysql 内存模式””的相关知识,希望我们能喜欢,各位老铁们一起来学习一下吧!

简介

在使用MySQL的时候,如果内存没有调整好,就很可能出现OOM。

innodb_buffer_pool_size

目前MySQL默认是使用innnodb存储引擎。innodb_buffer_pool_size这个参数是对Mysql数据库最重要的参数之。

InnoDB Buffer Pool 不仅仅缓存索引数据,会缓存表的数据,而且完全按照数据文件中的数据块结构信息来缓存。

查看参数:

 show variables like 'innodb_buffer_pool_size';

这里的单位是byte,在MySQL8上这个参数可以动态修改

set global innodb_buffer_pool_size = 2147483648 ; 

innodb_change_buffering

change buffering是insert buffer的加强,insert buffer只针对insert有效,change buffering对insert、delete、update(delete+insert)、purge都有效。

这个参数的目的还是为了减少随机IO带来性能损耗,随机IO在机械硬盘上表现不是很好,不过在SSD上,随机io跟顺序io倒是差不了多少,所以如果使用SSD这个参数就关系不大。

change buffering由参数innodb_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中分配的。

set global innodb_change_buffering = all;

innodb_change_buffer_max_size

这个参数表示change buffer在buffer pool中的最大占比,默认25%,最大50%。

如果系统中有严重的变更操作时,就增大max_size。

set global innodb_change_buffer_max_size = 30 ;
innodb_log_buffer_size

InnoDB 存储引擎的事务日志所使用的缓冲区,默认是16M。

为了提高性能,也是先将信息写入 Innofb Log Buffer 中,当满足 innodb_flush_log_at_trx_commit 参数所设置的相应条件或者日志缓冲区写满之后,才会将日志写到文件(或者同步到磁盘)中。

set innodb_log_buffer_size = 33554432;
innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit 参数可以设置为0,1,2,解释如下:

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

默认是1。

set global innodb_flush_log_at_trx_commit = 2 ;
thread_cache_size

线程池缓存大小,当客户端断开连接后将当前线程缓存起来,当在接到新的连接请求时快速响应无需创建新的线程 。

查看当前线程状态:

 show global status like 'Thread%';
+-------------------------+-------+| Variable_name           | Value |+-------------------------+-------+| Threadpool_idle_threads | 0     || Threadpool_threads      | 0     || Threads_cached          | 0     || Threads_connected       | 1     || Threads_created         | 1     || Threads_running         | 2     |+-------------------------+-------+6 rows in set (0.00 sec)

可以通过(Connections - Threads_created) / Connections * 100 计算出连接线程缓存的命中率。当 Threads_cached 越来越少 但 Threads_connected 始终不降,且 Threads_created 持续升高,可适当增加 thread_cache_size 的大小。默认是9。

set global thread_cache_size = 10;
table_open_cache

table_open_cache指定表高速缓存的大小,用来缓存表文件的文件句柄信息。

通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_open_cache的值。

show global status like 'Open%tables';

比较适合的值:

Open_tables / Opened_tables >= 0.85Open_tables / table_cache <= 0.95

默认值是4000,这个值可以等数据库跑一段时间再观察是否要调整。

set global table_open_cache = 5000;
max_connections

MySQL的连接都是会消耗一定的资源的。这个参数是MySQL的最大连接数,增加该值增加mysqld 要求的文件描述符的数量。

数值过小会经常出现ERROR 1040: Too many connections错误。

查看连接和最大连接

show status like 'Max_used_connections';show variables like 'max_connections';

max_used_connections / max_connections * 100% (理想值≈ 85%) 如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了,低于10%则设置过大。

set global max_connections = 200 ;
binlog_cache_size

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

set global binlog_cache_size = 65536;
总结

以上都是一些常用的参数,没有一个是绝对了,需要根据实际情况去调整。

标签: #mysql 内存模式 #mysql 内存模式 表