MySQL性能调优 – 你必须了解的15个重要变量

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
公共DNS(含HTTPDNS解析),每月1000万次HTTP解析
简介: MySQL性能调优 – 你必须了解的15个重要变量**前言:MYSQL 应该是最流行了 WEB 后端数据库。虽然 NOSQL 最近越來越多的被提到,但是相信大部分架构师还是会选择 MYSQL 来做数据存储。

MySQL性能调优 – 你必须了解的15个重要变量

QQ_20190714155338

**前言:

MYSQL 应该是最流行了 WEB 后端数据库。虽然 NOSQL 最近越來越多的被提到,但是相信大部分架构师还是会选择 MYSQL 来做数据存储。本文作者总结梳理MySQL性能调优的15个重要变量,又不足需要补充的还望大佬指出。**

1.DEFAULT_STORAGE_ENGINE

如果你已经在用MySQL 5.6或者5.7,并且你的数据表都是InnoDB,那么表示你已经设置好了。如果没有,确保把你的表转换为InnoDB并且设置default_storage_engine为InnoDB。

为什么?简而言之,因为InnoDB是MySQL(包括Percona Server和MariaDB)最好的存储引擎 – 它支持事务,高并发,有着非常好的性能表现(当配置正确时)。这里有详细的版本介绍为什么

2.INNODB_BUFFER_POOL_SIZE

这个是InnoDB最重要变量。实际上,如果你的主要存储引擎是InnoDB,那么对于你,这个变量对于MySQL是最重要的。

基本上,innodb_buffer_pool_size指定了MySQL应该分配给InnoDB缓冲池多少内存,InnoDB缓冲池用来存储缓存的数据,二级索引,脏数据(已经被更改但没有刷新到硬盘的数据)以及各种内部结构如自适应哈希索引。

根据经验,在一个独立的MySQL服务器应该分配给MySQL整个机器总内存的80%。如果你的MySQL运行在一个共享服务器,或者你想知道InnoDB缓冲池大小是否正确设置,详细请看这里。

3.INNODB_LOG_FILE_SIZE

InnoDB重做日志文件的设置在MySQL社区也叫做事务日志。直到MySQL 5.6.8事务日志默认值innodb_log_file_size=5M是唯一最大的InnoDB性能杀手。从MySQL 5.6.8开始,默认值提升到48M,但对于许多稍繁忙的系统,还远远要低。

根据经验,你应该设置的日志大小能在你服务器繁忙时能存储1-2小时的写入量。如果不想这么麻烦,那么设置1-2G的大小会让你的性能有一个不错的表现。这个变量也相当重要,更详细的介绍请看这里。

在进入下一个变量之前,让我们来快速提及一下innodb_log_buffer_size。“快速提及”是因为它常常不好理解并且往往被过度关注了。事实上大多数情况下你只需要使用小的缓冲 – 在事务被提交并写入到硬盘前足够保存你的小事务更改了。

当然,如果你有大量的大事务更改,那么,更改比默认innodb日志缓冲大小更大的值会对你的性能有一定的提高,但是你使用的是autocommit,或者你的事务更改小于几k,那还是保持默认的值吧。

4.INNODB_FLUSH_LOG_AT_TRX_COMMIT

默认下,innodb_flush_log_at_trx_commit设置为1表示InnoDB在每次事务提交后立即刷新同步数据到硬盘。如果你使用autocommit,那么你的每一个INSERT, UPDATE或DELETE语句都是一个事务提交。

同步是一个昂贵的操作(特别是当你没有写回缓存时),因为它涉及对硬盘的实际同步物理写入。所以如果可能,并不建议使用默认值。

两个可选的值是0和2:

  • 0表示刷新到硬盘,但不同步(提交事务时没有实际的IO操作)
  • 2表示不刷新和不同步(也没有实际的IO操作)

所以你如果设置它为0或2,则同步操作每秒执行一次。所以明显的缺点是你可能会丢失上一秒的提交数据。具体来说,你的事务已经提交了,但服务器马上断电了,那么你的提交相当于没有发生过。

显示的,对于金融机构,如银行,这是无法忍受的。不过对于大多数网站,可以设置为innodb_flush_log_at_trx_commit=0|2,即使服务器最终崩溃也没有什么大问题。毕竟,仅仅在几年前有许多网站还是用MyISAM,当崩溃时会丢失30s的数据(更不要提那令人抓狂的慢修复进程)。

那么,0和2之间的实际区别是什么?性能明显的差异是可以忽略不计,因为刷新到操作系统缓存的操作是非常快的。所以很明显应该设置为0,万一MySQL崩溃(不是整个机器),你不会丢失任何数据,因为数据已经在OS缓存,最终还是会同步到硬盘的。

5.SYNC_BINLOG

已经有大量的文档写到sync_binlog,以及它和innodb_flush_log_at_trx_commit的关系,下面我们来简单的介绍下:

a) 如果你的服务器没有设置从服务器,而且你不做备份,那么设置sync_binlog=0将对性能有好处。

b) 如果你有从服务器并且做备份,但你不介意当主服务器崩溃时在二进制日志丢失一些事件,那么为了更好的性能还是设置为sync_binlog=0.

c) 如果你有从服务器并且备份,你非常在意从服务器的一致性,以及能及时恢复到一个时间点(通过使用最新的一致性备份和二进制日志将数据库恢复到特定时间点的能力),那么你应该设置innodb_flush_log_at_trx_commit=1,并且需要认真考虑使用sync_binlog=1。

问题是sync_binlog=1代价比较高 – 现在每个事务也要同步一次到硬盘。你可能会想为什么不把两次同步合并成一次,想法正确 – 新版本的MySQL(5.6和5.7,MariaDB和Percona Server)已经能合并提交,那么在这种情况下sync_binlog=1的操作也不是这么昂贵了,但在旧的mysql版本中仍然会对性能有很大影响。

6.INNODB_FLUSH_METHOD

将innodb_flush_method设置为O_DIRECT以避免双重缓冲.唯一一种情况你不应该使用O_DIRECT是当你操作系统不支持时。但如果你运行的是Linux,使用O_DIRECT来激活直接IO。

不用直接IO,双重缓冲将会发生,因为所有的数据库更改首先会写入到OS缓存然后才同步到硬盘 – 所以InnoDB缓冲池和OS缓存会同时持有一份相同的数据。特别是如果你的缓冲池限制为总内存的50%,那意味着在写密集的环境中你可能会浪费高达50%的内存。如果没有限制为50%,服务器可能由于OS缓存的高压力会使用到swap。

简单地说,设置为innodb_flush_method=O_DIRECT。

7.INNODB_BUFFER_POOL_INSTANCES

MySQL 5.5引入了缓冲实例作为减小内部锁争用来提高MySQL吞吐量的手段。

在5.5版本这个对提升吞吐量帮助很小,然后在MySQL 5.6版本这个提升就非常大了,所以在MySQL5.5中你可能会保守地设置innodb_buffer_pool_instances=4,在MySQL 5.6和5.7中你可以设置为8-16个缓冲池实例。

你设置后观察会觉得性能提高不大,但在大多数高负载情况下,它应该会有不错的表现。

对了,不要指望这个设置能减少你单个查询的响应时间。这个是在高并发负载的服务器上才看得出区别。比如多个线程同时做许多事情。

8.INNODB_THREAD_CONCURRENCY

你可能会经常听到应该设置innodb_thread_concurrency=0然后就不要管它了。不过这个只在低负载服务器使用时才正确。然后,如果你的服务器的CPU或者IO使用接受饱和,特别是偶尔出现峰值,这时候系统想在超载时能正常处理查询,那么强烈建议关注innodb_thread_concurrency。

InnoDB有一种方法来控制并行执行的线程数 – 我们称为并发控制机制。大部分是由innodb_thread_concurrency值来控制的。如果设置为0,并发控制就关闭了,因此InnoDB会立即处理所有进来的请求(尽可能多的)。

在你有32CPU核心且只有4个请求时会没什么问题。不过想像下你只有4CPU核心和32个请求时 – 如果你让32个请求同时处理,你这个自找麻烦。因为这些32个请求只有4 CPU核心,显然地会比平常慢至少8倍(实际上是大于8倍),而然这些请求每个都有自己的外部和内部锁,这有很大可能堆积请求。

下面介绍如何更改这个变量,在mysql命令行提示符执行:

SET global innodb_thread_concurrency=X;
对于大多数工作负载和服务器,设置为8是一个好开端,然后你可以根据服务器达到了这个限制而资源使用率利用不足时逐渐增加。可以通过show engine innodb statusG来查看目前查询处理情况,查找类似如下行:

22 queries inside InnoDB, 104 queries in queue
9.SKIP_NAME_RESOLVE

这一项不得不提及,因为仍然有很多人没有添加这一项。你应该添加skip_name_resolve来避免连接时DNS解析。

大多数情况下你更改这个会没有什么感觉,因为大多数情况下DNS服务器解析会非常快。不过当DNS服务器失败时,它会出现在你服务器上出现“unauthenticated connections” ,而就是为什么所有的请求都突然开始慢下来了。

所以不要等到这种事情发生才更改。现在添加这个变量并且避免基于主机名的授权。

10.INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX

  • innodb_io_capacity:用来当刷新脏数据时,控制MySQL每秒执行的写IO量。
  • innodb_io_capacity_max: 在压力下,控制当刷新脏数据时MySQL每秒执行的写IO量

首先,这与读取无关 – SELECT查询执行的操作。对于读操作,MySQL会尽最大可能处理并返回结果。至于写操作,MySQL在后台会循环刷新,在每一个循环会检查有多少数据需要刷新,并且不会用超过innodb_io_capacity指定的数来做刷新操作。这也包括更改缓冲区合并(在它们刷新到磁盘之前,更改缓冲区是辅助脏页存储的关键)。

第二,我需要解释一下什么叫“在压力下”,MySQL中称为”紧急情况”,是当MySQL在后台刷新时,它需要刷新一些数据为了让新的写操作进来。然后,MySQL会用到innodb_io_capacity_max。

那么,应该设置innodb_io_capacity和innodb_io_capacity_max为什么呢?

最好的方法是测量你的存储设置的随机写吞吐量,然后给innodb_io_capacity_max设置为你的设备能达到的最大IOPS。innodb_io_capacity就设置为它的50-75%,特别是你的系统主要是写操作时。

通常你可以预测你的系统的IOPS是多少。例如由8 15k硬盘组成的RAID10能做大约每秒1000随机写操作,所以你可以设置innodb_io_capacity=600和innodb_io_capacity_max=1000。许多廉价企业SSD可以做4,000-10,000 IOPS等。

这个值设置得不完美问题不大。但是,要注意默认的200和400会限制你的写吞吐量,因此你可能偶尔会捕捉到刷新进程。如果出现这种情况,可能是已经达到你硬盘的写IO吞吐量,或者这个值设置得太小限制了吞吐量。

11.INNODB_STATS_ON_METADATA

如果你跑的是MySQL 5.6或5.7,你不需要更改innodb_stats_on_metadata的默认值,因为它已经设置正确了。

不过在MySQL 5.5或5.1,强烈建议关闭这个变量 – 如果是开启,像命令show table status会立即查询INFORMATION_SCHEMA而不是等几秒再执行,这会使用到额外的IO操作。

从5.1.32版本开始,这个是动态变量,意味着你不需要重启MySQL服务器来关闭它。

12.INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN & INNODB_BUFFER_POOL_LOAD_AT_STARTUP

innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup这两个变量与性能无关,不过如果你偶尔重启mysql服务器(如生效配置),那么就有关。当两个都激活时,MySQL缓冲池的内容(更具体地说,是缓存页)在停止MySQL时存储到一个文件。当你下次启动MySQL时,它会在后台启动一个线程来加载缓冲池的内容以提高预热速度到3-5倍。

两件事:

第一,它实际上没有在关闭时复制缓冲池内容到文件,仅仅是复制表空间ID和页面ID – 足够的信息来定位硬盘上的页面了。然后它就能以大量的顺序读非常快速的加载那些页面,而不是需要成千上万的小随机读。

第二,启动时是在后台加载内容,因为MySQL不需要等到缓冲池内容加载完成再开始接受请求(所以看起来不会有什么影响)。

从MySQL 5.7.7开始,默认只有25%的缓冲池页面在mysql关闭时存储到文件,但是你可以控制这个值 – 使用innodb_buffer_pool_dump_pct,建议75-100。

这个特性从MySQL 5.6才开始支持。

13.INNODB_ADAPTIVE_HASH_INDEX_PARTS

如果你运行着一个大量SELECT查询的MySQL服务器(并且已经尽可能优化),那么自适应哈希索引将下你的下一个瓶颈。自适应哈希索引是InnoDB内部维护的动态索引,可以提高最常用的查询模式的性能。这个特性可以重启服务器关闭,不过默认下在mysql的所有版本开启。

这个技术非常复杂,在大多数情况下它会对大多数类型的查询直到加速的作用。不过,当你有太多的查询往数据库,在某一个点上它会花过多的时间等待AHI锁和闩锁。

如果你的是MySQL 5.7,没有这个问题 – innodb_adaptive_hash_index_parts默认设置为8,所以自适应哈希索引被切割为8个分区,因为不存在全局互斥。

不过在mysql 5.7前的版本,没有AHI分区数量的控制。换句话说,有一个全局互斥锁来保护AHI,可能导致你的select查询经常撞墙。

所以如果你运行的是5.1或5.6,并且有大量的select查询,最简单的方案就是切换成同一版本的Percona Server来激活AHI分区。

14.QUERY_CACHE_TYPE

如果人认为查询缓存效果很好,肯定应该使用它。好吧,有时候是有用的。不过这个只在你在低负载时有用,特别是在低负载下大多数是读取,小量写或者没有。

如果是那样的情况,设置query_cache_type=ON和query_cache_size=256M就好了。不过记住不能把256M设置更高的值了,否则会由于查询缓存失效时,导致引起严重的服务器停顿。

如果你的MySQL服务器高负载动作,建议设置query_cache_size=0和query_cache_type=OFF,并重启服务器生效。那样Mysql就会停止在所有的查询使用查询缓存互斥锁。
**
15.TABLE_OPEN_CACHE_INSTANCES
**
从MySQL 5.6.6开始,表缓存能分割到多个分区。

表缓存用来存放目前已打开表的列表,当每一个表打开或关闭互斥体就被锁定 – 即使这是一个隐式临时表。使用多个分区绝对减少了潜在的争用。

从MySQL 5.7.8开始,table_open_cache_instances=16是默认的配置。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
24天前
|
存储 关系型数据库 MySQL
MySQL 8.0特性-自增变量的持久化
【11月更文挑战第8天】在 MySQL 8.0 之前,自增变量(`AUTO_INCREMENT`)的行为在服务器重启后可能会发生变化,导致意外结果。MySQL 8.0 引入了自增变量的持久化特性,将其信息存储在数据字典中,确保重启后的一致性。这提高了开发和管理的稳定性,减少了主键冲突和数据不一致的风险。默认情况下,MySQL 8.0 启用了这一特性,但在升级时需注意行为变化。
|
2月前
|
存储 关系型数据库 MySQL
提高MySQL查询性能的方法有很多
提高MySQL查询性能的方法有很多
172 7
|
2月前
|
存储 自然语言处理 关系型数据库
MySQL 自定义变量并声明字符编码
MySQL 自定义变量并声明字符编码
114 1
|
2月前
|
存储 关系型数据库 MySQL
提高MySQL的查询性能
提高MySQL的查询性能
72 4
|
1月前
|
缓存 监控 关系型数据库
如何根据监控结果调整 MySQL 数据库的参数以提高性能?
【10月更文挑战第28天】根据MySQL数据库的监控结果来调整参数以提高性能,需要综合考虑多个方面的因素
73 1
|
1月前
|
监控 关系型数据库 MySQL
如何监控和诊断 MySQL 数据库的性能问题?
【10月更文挑战第28天】监控和诊断MySQL数据库的性能问题是确保数据库高效稳定运行的关键
63 1
|
1月前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
64 1
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
185 1
|
2月前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
93 1
|
2月前
|
SQL 关系型数据库 MySQL
MySQL中的 where 1=1会不会影响性能?看完官方文档就悟了!
本文探讨了在Mybatis中使用`where 1=1`进行动态SQL拼接是否会影响性能。通过MySQL官方资料和实际测试表明,`where 1=1`在MySQL 5.7及以上版本中会被优化器优化,因此对性能影响不大。文中详细对比了`where 1=1`与`<where>`标签的使用方法,并建议根据MySQL版本和团队需求选择合适的方式。最后,推荐查找官方资料以确保技术路线正确。
47 4