关于MySQL性能调优你必须了解的15个重要变量(小结)

前言:

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 status\G来查看目前查询处理情况,查找类似如下行:

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是默认的配置。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们。

(0)

相关推荐

  • 10个MySQL性能调优的方法

    MYSQL 应该是最流行了 WEB 后端数据库.WEB 开发语言最近发展很快,PHP, Ruby, Python, Java 各有特点,虽然 NOSQL 最近越來越多的被提到,但是相信大部分架构师还是会选择 MYSQL 来做数据存储. MYSQL 如此方便和稳定,以至于我们在开发 WEB 程序的时候很少想到它.即使想到优化也是程序级别的,比如,不要写过于消耗资源的 SQL 语句.但是除此之外,在整个系统上仍然有很多可以优化的地方. 1. 选择合适的存储引擎: InnoDB 除非你的数据表使用来做

  • mysql sql语句性能调优简单实例

     mysql sql语句性能调优简单实例 在做服务器开发时,有时候对并发量有一定的要求,有时候影响速度的是某个sql语句,比如某个存储过程.现在假设服务器代码执行过程中,某个sql执行比较缓慢,那如何进行优化呢? 假如现在服务器代码执行如下sql存储过程特别缓慢: call sp_wplogin_register(1, 1, 1, '830000', '222222'); 可以按如下方法来进行调试: 1. 打开mysql profiling: 2. 然后执行需要调优的sql,我们这里执行两条sq

  • 关于MySQL性能调优你必须了解的15个重要变量(小结)

    前言: MYSQL 应该是最流行了 WEB 后端数据库.虽然 NOSQL 最近越来越多的被提到,但是相信大部分架构师还是会选择 MYSQL 来做数据存储.本文作者总结梳理MySQL性能调优的15个重要变量,又不足需要补充的还望大佬指出. 1.DEFAULT_STORAGE_ENGINE 如果你已经在用MySQL 5.6或者5.7,并且你的数据表都是InnoDB,那么表示你已经设置好了.如果没有,确保把你的表转换为InnoDB并且设置default_storage_engine为InnoDB. 为

  • 使用MySQL的Explain执行计划的方法(SQL性能调优)

    目录 前言 1. explain的使用 2. explain字段详解 id列 select_type列 table列 partitions列 type列 system const eq_ref ref ref_or_null index_merge range index ALL possible_keys列 key列 key_len列 ref列 rows列 filtered列 Extra列 Using where Using index Using filesort Using tempora

  • MySQL参数调优实例探究讲解

    目录 一.MySQL 参数调优 1.一些生产常用查询命令 2.分析慢查询 3.分析连接数 4.缓冲区调优 Key_buffer_size 5.临时表调优 Created_tmp_tables 6.分析 Open Table 使用情况 7.查看 MySQL进程使用情况 8.查询缓存 query cache 9.分析SQL排序与锁及文件使用与表扫描情况 二.MySQL优化参数实战 总结 一.MySQL 参数调优 1.一些生产常用查询命令 -- 查看数据库里正在执行的sql语句 show proces

  • 关于redis状态监控和性能调优详解

    前言 对于任何应用服务和组件,都需要一套完善可靠谱监控方案. 尤其redis这类敏感的纯内存.高并发和低延时的服务,一套完善的监控告警方案,是精细化运营的前提. 本文主要给大家介绍了关于redis状态监控和性能调优的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧. 1.redis-benchmark redis基准信息,redis服务器性能检测 例如: 检测redis服务器性能,本机6379端口的实例,100个并发连接,100000个请求 redis-benchmark

  • SQL Server性能调优之缓存

    在执行任何查询时,SQL Server都会将数据读取到内存,数据使用之后,不会立即释放,而是会缓存在内存Buffer中,当再次执行相同的查询时,如果所需数据全部缓存在内存中,那么SQL Server不会产生Disk IO操作,立即返回查询结果,这是SQL Server的性能优化机制. 一,主要的内存消费者(Memory Consumer) 1,数据缓存(Data Cache) Data Cache是存储数据页(Data Page)的缓冲区,当SQL Server需要读取数据文件(File)中的数

  • AngularJS进行性能调优的7个建议

    AnglarJS作为一款优秀的Web框架,可大大简化前端开发的负担.近日Sebastian Fröstl在一篇博文<AngularJS Performance Tuning for Long Lists>中表示AnglarJS在处理包含复杂数据结构的大型列表时,其运行速度会非常慢.他在文中同时分享了解决方案.下面为该文的译文. AnglarJS很棒,但当处理包含复杂数据结构的大型列表时,其运行速度就会非常慢.这是我们将核心管理页面迁移到AngularJS过程中遇到的问题.这些页面在显示500行

  • web性能优化之javascript性能调优

    JavaScript 是一个比较完善的前端开发语言,在现今的 web 开发中应用非常广泛,尤其是对 Web 2.0 的应用.随着 Web 2.0 越来越流行的今天,我们会发现:在我们的 web 应用项目中,会有大量的 JavaScript 代码,并且以后会越来越多.JavaScript 作为一个解释执行的语言,以及它的单线程机制,决定了性能问题是 JavaScript 的软肋,也是 web 软件工程师们在写 JavaScript 需要高度重视的一个问题,尤其是针对 Web 2.0 的应用.绝大多

  • Java性能调优概述

    程序性能的主要表现点: 执行速度:程序的反映是否迅速,响应时间是否足够短 内存分配:内存分配是否合理,是否过多地消耗内存或者存在内存泄漏 启动时间:程序从运行到可以正常处理业务需要花费多少时间 负载承受能力:当系统压力上升时,系统的执行速度.响应时间的上升曲线是否平缓 衡量程序性能的主要指标: 执行时间:程序从运行到结束所使用的时间 CPU时间:函数或者线程占用CPU的时间 内存分配:程序在运行时占用内容的空间 磁盘吞吐量:描述I/O的使用情况 网络吞吐量:描述网络的使用情况 响应时间:系统对用

随机推荐