vlambda博客
学习文章列表

Mysql 8.X常见参数调优设置



01

通用类


1.1、key_buffer_size

含义:用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写)。

影响:对于MyISAM表的影响不是很大,MyISAM会使用系统的缓存来存储数据,所以大量使用MyISAM表的机器内存很快就会耗尽。但是,如果你将该值设得过大(例如,大于总内存的50%),系统将转换为页并变得极慢。MySQL依赖操作系统来执行数据读取时的文件系统缓存,因此你必须为文件系统缓存留一些空间。

建议:先设置为内存的25%,观察性能变化。

操作:

SHOW GLOBAL STATUS like '%key_read%'SHOW VARIABLES like '%key_buffer_size%'

计算索引未命中缓存率:key_cache_miss_rate = Key_reads / Key_read_requests * 100%

# 根据key_cache_miss_rate数据判断:>0.1% 则要适当调大key_buffer_size的值<0.1% 适合<0.01% 分配的key_buffer_size过大,浪费,适当调小。

1.2、table_open_cache

含义:为所有线程打开表的数量。

影响:增加该值能增加mysqld要求的文件描述符的数量。可以避免频繁的打开数据表产生的开销。打开一个表的开销可能很大,因为MyISAM会把MYI文件的文件头标识为正在使用,所以在内存中做这个操作比较好。因为每个线程都需要打开表,连接数越大这个值要越大。

建议:我们有300多个表的话,大约2048差不多了。

操作:

SHOW VARIABLES like '%table_open_cache%' SHOW GLOBAL STATUS like "%table_open_cache%"SHOW GLOBAL STATUS like "open%table%"

table_open_cache合理值的建议:

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

1.3、thread_cache_size

含义:缓存可重用的线程数。

影响:这个参数设置线程的缓存,线程的创建和销毁的开销可能很大,因为每个线程的连接/断开都需要。如果应用程序中有大量的跳跃并发连接并且线程较多的话,就要加大它的值。它的目的是在通常的操作中无需创建新线程。

建议:通常至少设置为16。

经常创建新的连接的情况下,提⾼该值可提⾼mysql性能,因为减少了连接的分配,但使⽤了java的连接池等,性能提升没那么显著。

操作:

show VARIABLES like 'thread%'SHOW GLOBAL STATUS like "%Thread%"SHOW GLOBAL STATUS like "CONNECTIONs"

合理值的建议:

Thread Cache 命中率:Thread_Cache_Hit = (Connections – Threads_created)/Connections *100%;#一般在系统稳定运行一段时间后,ThreadCache命中率应该保持在90%左右才算正常。

1.4、back_log

含义:在MySQL的连接请求等待队列中允许存放的最大连接请求数。系统默认值为50。

影响:如果系统在一个短时间内有很多连接则需要增大该参数的值,该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。试图设定 back log高于你的操作系统的限制将是无效的。

建议:对于 Linux系统推荐设置为小于512的整数。

show VARIABLES like '%back_log%'

1.5、sort_buffer_size

含义:为每个需要进行排序的线程分配该大小的一个缓冲区。

影响:增加这值加速ORDER BY或GROUP BY操作。不过该参数对应的分配内存是每连接独占的,如果有100个连接,那么实际分配的总共排序缓冲区大小为100×sort_buffer_size。

建议:一般设置为2M观察变化再调整。

1.6、read_buffer_size

含义:顺序查询操作所能使用的缓冲区大小。

影响:和sort_buffer_size一样,该参数对应的分配内存也是每连接独享。

建议:一般设置为2M再观察变化。

1.7、read_rnd_buffer_size

含义:随机查询操作所能使用的缓冲区大小。

影响:每个线程独享。

建议:一般设置为2M再观察变化。

1.8、slow_query_log

含义:慢日志相关,为优化提供相关的

show VARIABLES like '%slow_query_log%'show VARIABLES like '%long_query_time%'



02


MyISAM

2.1、myisam_sort_buffer_size

MyISAM表发生变化时重新排序所需的缓冲。

一般64M足矣。



03

InnoDB类

3.1、innodb_buffer_pool_size

对InnoDB的效率影响很大。因为InnoDB会把尽可能多的数据和索引缓存在缓冲区,这个类似与Oracle的Buffer Pool。

合理值的建议

如果只采用InnoDB,可以把这个参数调大一点,大约内存的70%左右。

当然,如果数据量不会暴增并且不是特别大,这个参数还是不要太大了,浪费空间。

show VARIABLES like '%innodb_buffer_pool_size%'
SELECT SUM(data_length+index_length) /POWER(1024,3)Total_InnoDB_BytesFROM information_schema.tables WHERE engine='InnoDB';
#innodb_buffer_pool_size始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数
show VARIABLES like '%innodb_buffer_pool_read%'
#innodb_buffer_pool性能Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100innodb_buffer_pool_reads:表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。innodb_buffer_pool_read_requests:表示从内存中读取逻辑的请求数。
#InnoDB buffer pool 命中率innodb_buffer_pool_read_requests/(innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100

3.2、innodb_additional_pool_size

对数据库性能影响不是很大,至少内存足够的机器上不会有什么影响。

根据MySQL手册,对于2G内存的机器,推荐值是20M; 32G内存的,推荐100M

3.3、innodb_log_file_size

在高写入负载尤其是数据集很大的时候,这个值非常重要,值越高性能越好,不过可能会增加数据恢复的时候。我设置为128M。

3.4、innodb_log_buffer_size

默认的设置在中等强度的写入负载及短事物处理时,性能还可以。但是存在大量更新操作或者负载较大时,就要慢慢增加这个参数的值了。不过不要设置太大,会浪费内存。它每秒都会刷新一次,所以不用设置超过1s所需的内存空间,16M足够了。

show VARIABLES like '%innodb_log_buffer_size%'

3.5、innodb_flush_log_at_trx_commit

这个参数对InnoDB及其重要,设置不好的话会比MyISAM慢1000倍!默认是1,这就是说每次更新事务都会被提交到磁盘,这是非常消耗资源的,硬盘和内存的速度是明显数量级的差距。

设置为0是最快的,但是很不安全,全部在缓存中,一掉电全没了。

设置为1很不好,每次都去写硬盘,没有必要。

设置为2是比较好的,日志不刷新到磁盘上,只刷新到操作系统缓存上。然后每秒钟写缓存。相对于现在数据库每秒4K条左右的SQL,性能已经可以提高不少。

当innodb_flush_log_at_trx_commit和sync_binlog都为1时,是最安全的。在mysql服务崩溃或者操作系统崩溃的情况下,binlog只可能最多丢失一个事务。鱼和熊掌不可兼得,双1模式下,会导致频繁的io操作,故也是最慢的一种方式。

当innodb_flush_log_at_trx_commit设置为0时,在mysql进程崩溃的情况下,会导致上一秒的事务数据丢失。

当innodb_flush_log_at_trx_commit设置为2时,在mysql服务崩溃并不会导致事务数据丢失。只有在操作系统崩溃或者掉电的情况下,会导致上一秒的事务数据丢失。






浦东图书馆