【金沙官网线上】MySQL优化

1、活动/峰值连接数
(图1)中当前活动的连接为1个,自MySQL服务启动以来,最高连接数为54;当最高连接数接近或等于(图2)中的max_connections时,应适当增加max_connections,需要注意的是,不要一下子增加过多,建议每次增加50,观察一段时间,不够再继续增加。

MySQL数据库是 IO 密集型的程序,和其他数据库一样,主要功能就是数据的持久化以及数据的管理工作。本文侧重通过优化MySQL 数据库缓存参数如查询缓存,表缓存,日志缓存,索引缓存,innodb缓存,插入缓存,以及连接参数等方式来对MySQL数据库进行优化。

2、线程缓存命中率
(图1)中线程缓存命中率为99.78%,若这个值小于90%,建议适当增加(图2)中的thread_cache_size,建议每次增加8。

缓存参数

这里先引用一句话,从内存中读取一个数据的时间消耗是微秒级别,而从普通硬盘上读取是毫秒级别,二者相差3个数量级。可见,想对MySQL数据库进行优化,合理调配缓存参数显得更为直接

3、索引命中率
(图1)中索引命中率为99.50%,若这个值小于95%,建议适当增加(图2)中的key_buffer_size,建议每次增加64,需要说明的是,若您的数据库使用的是Innodb引擎,可忽略这个选项

表缓存

相关参数: table_open_cache

指定表缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值,如果发现open_tables等于table_cache,并且opened_tables在不断增长,那么就需要增加table_open_cache的值了。注意,不能盲目地把这个参数设置得很大,如果设置太大,会引起文件描述符不足,造成性能不稳定或者数据库连接失败。建议为512

4、Innodb索引命中率 
(图1)中Innodb索引命中率为100%,若这个值小于95%,建议适当增加(图2)中的innodb_buffer_pool_size,建议每次增加64,需要说明的是,若您的数据库没有使用Innodb引擎,可忽略这个选项

查询缓存

相关参数: query_cache_size / query_cache_type

QC(注:查询缓存简称) 主要用来缓存 MySQL 中的 结果集,也就是一条SQL语句执行的结果集,所以仅仅只能针对select 语句。如果启用了QC 功能,MySQL在接到select 请求后,如果该语句满足QC的要求,MySQL 会直接根据HASH算法将接收到的select 语句以字符串方式进行hash,然后到QC中直接查找,如果已经在缓存中,该select 请求就会直接将数据返回,从而省略了后面所有的步骤(如 SQL语法解析,优化器优化以及存储引擎请求数据等),极大的提高性能。

当然,QC也有一个致命的缺陷,就是当表中数据有变化时,所有引用到该表的 QC 缓存全部失效。所以,当数据变化非常频繁的情况下,使用QC 反而得不偿失。

QC 的使用需要多个参数配合,其中最为关键的是 query_cache_size 和 query_cache_type ,前者设置缓存记录集的内存大小,后者设置在何场景下使用QC 。

在以往的经验来看,中等规模的网站,query_cache_size 设置 256MB 足够了。当然,还可以通过计算QC的命中率来进行调整。

 Qcache_hits / (Qcache_hits + Qcache_inserts)  * 100%

 query_cache_type有三种选择:0(OFF,不使用QC),1(ON,默认使用QC ),2(DEMAND,默认不使用QC)。

为什么加上“默认”?MySQL还支持动态使用缓存的SQL语法,如下:

 # 强制使用缓存
SELECT SQL_CACHE id FROM table

# 强制不使用缓存
SELECT SQL_NO_CACHE id FROM table

 日志缓存

相关参数:binlog_cache_size

用于在打开了二进制日志(binlog)记录功能的环境中,是 MySQL 用来提高 binlog 的记录效率而设计的一个在短时间内缓存binlog 数据的内存缓存。

如果数据库中没有大事务,写入不是特别频繁,2MB~4MB是一个合适的选择。但是如果数据库大事务较多,写入比较频繁,可适当加大。使用的时候,还可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够,是否有大量的binlog_cache由于内存大小不够而使用临时文件来缓存了。

5、查询缓存命中率
MySQL查询缓存是个比较受争议的功能,个人建议当你有在使用redis、memcached等缓存软件时,在(图2)中将query_cache_size设为0可以将其关闭,当你没有使用缓存软件,有多余的内存使用,且数据库瓶颈明显存在时,可以尝试开启查询缓存,这是个非常依赖数据表结构及SQL语句优化的功能,若数据表结构和SQL语句都针对查询缓存进行过优化,它的效果还是很不错的。

索引缓存

相关参数:key_buffer_size

这个是对MyISAM表性能影响最大的一个参数,用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果有足够的内存,这个缓存区域大小可以设为所有的 MyISAM表的索引大小的总和,即 data 目录下所有*.MYI文件大小的总和。

注意,由于 MyISAM 引擎只会缓存索引块到内存中,而不会缓存表数据库块。所以,查询SQL语句一定要尽可能让过滤条件都在索引中,以便使用到索引缓存来提高查询效率。

计算索引缓存未命中的概率:

 Key_reads / Key_read_requests * 100%

6、创建临时表到磁盘
(图1)中创建临时表到磁盘的比例是0.42%,这说明大部分临时表创建到内存了,不会过多增加磁盘IO的开销,建议,当比例大于2%时适当增加(图1)中的tmp_cache_size,建议每次增加32,当比例大于60%时,放弃吧,有些开源程序并没有专门优化过SQL语句,所以在运行过程中会开启大量临时表,加多少缓存都是不够用的。

插入缓存

相关参数:bulk_insert_buffer_size

用于使用 MyISAM引擎,用来缓存批量插入数据的时候临时缓存写入数据。当我们使用如下几种数据写入语句的时候,会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据文件,默认8M,建议不要超过32M

 insert … select …
insert … values (…),(…),(…),…
load data infile… into… /* 非空表 */

7、已打开的表
当(图1)中的已打开的表接近或等于(图2)中的table_open_cache时,可以适当增加table_open_cache,但若设置过大可能导致您的程序频繁中断MySQL连接,建议在1024以内,最大不要超过2048。

InnoDB缓存

相关参数:innodb_buffer_pool_size / innodb_additional_mem_pool_size

innodb_buffer_pool_size参数是影响InnoDB存储引擎性能的最为关键的一个参数,设置用于缓存 InnoDB 索引及数据块的内存区域大小,类似于 MyISAM 存储引擎的 key_buffer_size 参数,当然,可能更像是 Oracle 的 db_cache_size。简单来说,当操作一个 InnoDB 表的时候,返回的所有数据或者去数据过程中用到的任何一个索引块,都会涉及到这个内存区域。

innodb_buffer_pool_size 参数设置了 InnoDB 存储引擎需求最大的一块内存区域的大小,直接关系到 InnoDB存储引擎的性能,所以如果有足够的内存,尽可能加大该参数的值,将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中。

当然,可以通过计算缓存命中率,并根据命中率来调整这个参数的大小:

  (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%

innodb_additional_mem_pool_size 参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小。随着数据库对象越来越多,需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。这个参数的大小是相对稳定的,没有必要预留非常大的值。如果InnoDB引擎用光了这个池内的内存,InnoDB引擎就开始从操作系统申请内存,并往MySQL错误日志写警告信息。默认值是1MB,当发现错误日志中已经有相关的警告信息时,就应该适当的增加该参数的大小。

innodb_log_buffer_size 参数是 InnoDB 存储引擎的事务日志所使用的缓冲区。类似于 Binlog Buffer,InnoDB 在写事务日志的时候,为了提高性能,也是先将信息写入 Innofb Log Buffer 中,当满足 innodb_flush_log_trx_commit 参数所设置的相应条件(或者日志缓冲区写满)之后,才会将日志写到文件(或者同步到磁盘)中。可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。

innodb_flush_log_trx_commit 参数对 InnoDB引擎日志的写入性能有非常关键的影响。该参数可以设置为0,1,2,如下:

0:log buffer中的数据将以每秒一次的频率写入到log file中,且同时会进行文件系统到磁盘的同步操作,但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作;

1:在每次事务提交的时候将log buffer 中的数据都会写入到log file,同时也会触发文件系统到磁盘的同步;

2:事务提交会触发log buffer 到log file的刷新,但并不会触发磁盘文件系统到磁盘的同步。此外,每秒会有一次文件系统到磁盘同步操作。

此外,MySQL文档中还提到,这几种设置中的每秒同步一次的机制,可能并不会完全确保非常准确的每秒就一定会发生同步,还取决于进程调度的问题。实际上,InnoDB 能否真正满足此参数所设置值代表的意义正常 Recovery 还是受到了不同 OS 下文件系统以及磁盘本身的限制,可能有些时候在并没有真正完成磁盘同步的情况下也会告诉 mysqld 已经完成了磁盘同步。

innodb_max_dirty_pages_pct 参数用来控制在 InnoDB 缓冲池(Buffer Pool) 中可以不用写入数据文件中的脏页(Dirty Page) 的比例(已经被修但还没有从内存中写入到数据文件的脏数据)。这个比例值越大,从内存到磁盘的写入操作就会相对减少,所以能够一定程度下减少写入操作的磁盘IO。但是,如果这个比例值过大,当数据库崩溃(Crash)之后重启的时间可能就会很长,因为会有大量的事务数据需要从日志文件恢复出来写入数据文件中。同时,过大的比例值同时可能也会造成在达到比例设定上限后的 flush 操作“过猛”而导致性能波动很大。如果这个参数设置过大,将会导致MySQL启动时间过长,关闭时间也过长。

本文由金沙官网线上发布于数据库,转载请注明出处:【金沙官网线上】MySQL优化

您可能还会对下面的文章感兴趣: