快乐学习
前程无忧、中华英才非你莫属!

Day17-解决MySQL_ERROR参考指南(配置选项对服务器的影响)

MySQL服务器提供大量选项,可以通过多种方式来对这些选项进行设置,例如:在my.cnf配置文件中进行设置,在使用命令行启动服务器的时候进行设置,或者在服务器正在运行的时候使用变量来对它们进行设置。大多数MySQL服务器变量都允许动态设置,并且在通常情况下,一个变量对应一个配置选项。有些选项是GLOBAL类型的,一些选项只适用于某个特定的存储引擎,而另外的一些可以称为会话级别,适用于连接和某些特定的活动,比如复制。本章并不是MySQL服务器选项的通用指南,但它涉及一些可以创建或能够产生变化的选项,这能够帮助你解决MySQL服务器发生的一些问题。  在本章开始前,我们需要在某些方面达成一致。我将使用变量和选项来表示服务器选项。MySQL使用独立的语法选项及变量,例如:选项名称的拼写形式通常使用连字符(选项-名称),而对应的变量名称的拼写形式则使用下划线(变量_名称)。通常情况下,这两种拼写形式在MySQL服务器的配置文件中和命令行都支持,但变量仅仅支持使用“变量_名称”语法的形式。因此,在本书中,只要涉及的变量支持语法“变量_名称”这种拼写形式,我们就会使用这种拼写形式。我们可以根据变量的用途来把它们分成多个不同的组:用来设置服务器的配置目录。限制对硬件资源的使用,改变mysqld应该如何应对一个或多个场景等。依照它们分配时间的不同,它们也可以分为不同的组,例如:当服务器启动的时候,一个线程连接创建的时候,或者当服务器启动一个特定操作的时候。
 
3.1 服务器选项
 
我使用服务器选项选项这个术语,是因为这一个词就能够解释其所有的功能,例如:向服务器指定目录或文件,提醒服务器是否打开一个特定的日志等诸如此类的功能。这些选项通常不会产生什么问题。我只发现过两个典型的故障排除情景是由这种选项导致的:当一个选项指向了一条错误路径或者打开一个特定功能的时候,或者在启动后,改变了mysqld命令的运行方式。如果问题发生于第二种情况下,那么你可能很难判断造成MySQL问题的根本原因,因为你根本无法知道这以前发生过什么改变。当某选项使用错误路径的时候,你通常能够在服务器启动的时候注意此类问题。例如,如果你对datadir选项指定一条错误路径,那么mysqld会拒绝启动并输出有关的错误消息:
 
但是,当然,如果你在系统启动文件并用守护进程的方式启动mysqld,那么你无法在命令行中看到这条消息。在那种情况中,用户通常会注意到这样的问题:在初次尝试连接mysql时失败,而随着这个问题出现的错误类似以下内容:
 
 
 该错误只是向你说明服务器没有运行。在这种情况下,需要检查错误日志文件中的信息,或者,如果没有任何错误日志文件,那么需要检查操作系统日志中有关mysqld的消息。MySQL的错误日志文件之前列出的一样的错误消息。而对于一些自动脚本、操作系统记录的消息则可能不同,比如从MySQL安装mysql.server然后通过mysql.server启动mysqld失败的问题。还可以在系统进程清单中检查MySQL服务器是否正在运行。这里有一个Linux下的例子,这个例子显示mysqld没有出现在系统进程列表中的任何位置: 
 
 
mysqladmin实用工具有一个ping命令,它能够报告MySQL服务器当前的状态是运行还是停止:
 
 
 
有几个选项在指向特定的路径时候不会影响到MySQL服务器的启动,但也可以简单地关闭特定选项。例如,让我们来看看InnoDB引擎启动失败是什么样的:
 
 
 服务器已经成功启动,但是InnoDB引擎没有成功加载:
 
关闭错误日志使我们能够在控制台上看到错误消息,但在生产环境中,错误日志是用来查看错误消息的地方。所以,如果你发现你所需要的某个功能不存在,那么请先检查你的错误日志文件。了解你所需要的功能是否会影响到服务器的工作,是非常重要的。例如,当InnoDB引擎处于一个不可用的状态时,如果SQL模式不包含NO_ENGINE_SUBSTITUTION,我们仍然能够成功创建引擎为InnoDB的表:
 
 
 
 上面这个例子表明,检查警告信息始终是很有必要的。在这个例子中,我们在创建表的时候使用了错误的存储引擎,因为我们在尝试启动InnoDB引擎的时候发生了错误:
 
 
 
 如果你没有检查警告信息,那么使用这个表的用户可能只有在当这个问题影响到整个应用程序之后才会发现。从上一章中可以知道,MyISAM引擎与InnoDB引擎的锁定方式是不同的,所以如果一个应用程序基于InnoDB引擎的优势编写,却使用了MyISAM的表,这将会带来严重的问题。并且我还没谈到不存在事务的情况呢!如果某个依赖的功能出现问题,该功能是否存在于服务器实例上。其他主要的配置问题涉及的选项是用来更改MySQL服务器运行方式的,虽然它们的主要目的并不相同。在对MySQL服务器选项进行配置的时候,你可能会希望它能为你提供一个或多个功能,但不要期望它们能够影响你的查询。一个微不足道的例子是二进制日志对于创建存储函数的影响。当我们启用该功能后,你可以期望二进制日志会存储所有修改数据的事件,但你可能无法意识到它的副作用。首先,我将演示如何在不使用二进制日志的情况下在MySQL服务器上创建一个虚拟的存储函数:
 
 
 
 然后,我用账户sveta来连接服务器并创建存储函数:
 
 
 
目前看起来一切正常,但当我使用log_bin选项启动mysqld服务器后,事情会发生一些变化:
 
 然后尝试创建相同的存储函数: 
错误消息清楚地说明了问题出在哪里。我希望通过这个例子告诉你,选项是如何改变MySQL服务器运行方式的,尽管这一选项的主要功能并不会影响到用户查询。通常情况下,当用户遇到这一问题的时候,问题产生的原因并不明确,而且很容易引起混淆。
 
 

 
 
3.2 可更改服务器运行方式的变量
 
 
另一组变量会影响MySQL服务器如何处理用户的输入。我将举一个简单的例子来清楚地说明设置这种类型的变量而带来的效果。在这个例子中,我将SQL模式设置为STRICT_TRANS_TABLES使无效数据插入事务性表的尝试被拒绝,而不是被忽略。然而,我们可以预计,对于这种非事务表,服务器会尽可能地修复SQL语句,而不是将其拒绝: 
 
 
 
 
我故意执行一个错误的INSERT操作,省略了字段b的值。这种情况下,我期望服务器会为b字段写入一个空值。但是,即使该表使用MyISAM存储引擎,本次写入依然失败,并输出了对应的错误消息。MySQL参考手册说明了此时服务器的行为(参见http://dev.mysql.com/doc/refman/5.1/en/ server-sql-mode.html)。STRICT_TRANS_TABLES:如果一个值不能插入一个事务性表,那么中止该语句。对于非事务性表,如果该问题发生在一个影响到表中单行数据的语句中或是发生在一个影响到表中多行数据的语句中的数据的第一行,那么中止该语句。这里的INSERT为单行语句,所以服务器会拒绝纠正它。但这看起来并不直观,这个选项到底是什么?如果你发现你不期望出现的服务器行为,那么请仔细检查你的配置选项。
 
 

 
 
3.3 有关硬件资源限制的选项
 
 本分类中的配置选项能够对硬件资源的利用施加限制。它们通常有两个用途:优化性能及限制某些操作。当你希望对客户端与服务器之间的流量施加一些限制,或者防止拒绝服务攻击时,刚才提到的两种用途中,后者对该场景非常有用。它能更好地让特定用户得到更优雅的错误,因为资源不足要好过mysqld进程终止,因为mysqld进程终止后便不能处理所有传入的请求。本章最后将描述调整这一类选项时应该遵循的策略。这里希望指出的情景是,这些变量的不同设置可能会带来不同和意想不到的服务器行为。与往常一样,我会通过实际的例子来讲解。在我日复一日的工作中,我发现很多用户忽略了max_allowed_packet变量的值。这个变量的设置能够限制服务器和客户端之间传递的单个数据包的字节数。在这个例子中,为了达到演示效果,我将max_allowed_packet值设置为一个低于1MB的值。
 
这一次,错误消息很清晰地告诉了你为什么会发生这一错误,但有时也不是那么清晰。
 
得到的错误消息是否清晰,取决于处理你发送查询的服务器,通常发生在你从非常大的表或BLOB类型的字段中获取数据的时候。如果你开始收到有效查询的语法错误,请检查查询获取的数据量的大小是否超过了max_allowed_packet。
 

 
3.4 使用--no-defaults选项
 
用心记住每个变量是什么,是很难的。虽然我每天都在和这些变量打交道,但我依然无法记住它们。我用的一个解决方案是,在选项列表中,一个个排除它们。但这并不简单。MySQL的5.1版拥有291个变量,我安装的过时的5.5版则有321个变量,并且没有人能够保证变量的数量不会在未来继续增多。如果你使用自定义存储引擎,那么这个存储引擎也可以有自己专用的选项。检查MySQL服务器是否没有指定选项是较容易的,即,如果它的选项全部使用默认值。如果你对于msyqld如何在未配置自定义选项的场景下运行有一个粗略的猜测,那么你就可以使用--no-defaults选项启动MySQL服务器并与你设置了自定义选项的情况进行比较。   警告--no-defaults选项和--defaults-file选项都必须指定为传递给mysqld的第一个选项。否则,服务器将会无法识别它们。如果结果是不同的,那么可以在添加选项之前逐个使用测试方案,并检查服务器的错误行为是否复现。一旦你找到了这个错误是由哪个变量引起的,你就可以参考配置变量的文档,并进行相应的调整。
 
 

 
3.5 性能选项
 
 
这一类选项通常不会引发错误,但它们有可能对服务器的性能产生巨大的影响。通常可以在不同的真实负载的生产服务器上调整这些选项,直到找到一个适合特定环境的性能选项配置组合。然而,当你选择了这些选项的时候,有一种可能出现的情况会导致错误,所以从配置文件中移除或降低性能配置选项的权重是有意义的。这个场景是:你的服务器遇到一个资源不足错误。最常见的情况涉及缺少内存或者文件描述符。如果服务器存在此类问题,可以使用上一节中的--no-defaults办法来找明设置了太大的选项
 
3.6 欲速则不达
 
这个流行的英文谚语在许多其他语种中有相同含义的说法。俄国有一种说法可以翻译为“慢慢地滑行,进一步到达”。我认为这条睿智的谚语也同样适用于调整MySQL服务器的时候,至少是当你无法100%确定你知道自己在做什么的时候。除非你100%肯定错误是什么,然后逐个添加选项,并每次都对自己的配置进行测试这意味着,如果你认为一组配置选项可以改变MySQL服务器的行为,并使它更好地为你的应用程序提服务,那么你可以改变一个选项,然后进行测试,如果结果是令你振奋的,那么你可以继续添加其他选项,以此类推,直到你已经检查完毕每一个相关选项。这可能是一个很缓慢的过程,但如果在这一过程中发生错误,你可以放心的回滚到本次变化之前,并能够迅速地让你的服务器回到工作状态。在你调整内存缓冲区或者其他选项以限制对硬件资源的利用时,这种方式是非常重要的。但这种方式也同样能够用于服务器行为配置选项的修改。即使你对变量到底在做什么有着良好的认知,这也仅仅能让你可以更容易地发现和修复某个错误,而不是在几十种选项择中找出错误的根源。   警告当你在使用该方法的时候,需要保存每一个测试结果。例如,如果你正在尝试提升服务器的性能,那么你需要运行基准测试或测试查询执行时间在配置选项改变前后的变化,然后重复同样的测试,再去修改每个选项。
 
 

 
 
 
3.7 SET语句
 
 
 
MySQL支持两种形式的变量:SESSION及GLOBAL。会话级别的变量设置只会对当前链接生效,而不会影响其他连接。GLOBAL变量配置后将应用此后创建的所有连接。但设置一个GLOBAL变量,并不会影响当前连接[1],所以如果需要在当前连接使用一个新的变量值,那么应该同时设置SESSION变量和GLOBAL变量。可以通过这样的SQL语句来设置SESSION变量:
 
 
 
 把SESSION放在中括号中是因为这样可以省略关键字,这里的SESSION指的是set命令使用的默认SESSION。你可以通过这样的SQL语句来设置GLOBAL变量:
 
 
 当测试选项的时候,我建议你尽可能只使用SESSION变量来进行测试。当你得到满意的结果后,就可以使用一个GLOBAL变量来改变正在运行的服务器的配置,然后再修改对应的配置文件,以便在服务器重新启动后也能够应用此值。
 
 
提示
 
当你想使用一个特定查询求来检查选项的效果的时候,使用SET SESSION命令也是很有帮助的。在这种情况下,可以在查询前设置变量,然后再进行测试,使用SQL语句SET [SESSION] variable_name=DEFAULT来让变量恢复到默认值。如果多个线程之间共享一个选项,那么可以设置一个GLOBAL变量,然后检查服务器的运行状态。在你满意后再修改配置文件,这些修改包括新的变量以及对它们的设置。这种方式允许你测试变量的改变而无须中断应用程序,因为这会使你所需要的变更对程序的影响推迟到一个预定的服务器重启时间。存在个别选项是不能动态设置的。在这种情况下,即使你仅仅希望测试一下选项改变带来的效果,你也必须重新启动MySQL服务器才能使变更生效。
 
 

 
3.8 如何检查变更是否存在一些影响
 
一些状态变量能够显示服务器的当前运行状态。相对于配置变量,这些状态变量并不会影响服务器的运行行为,但它们包含mysqld进程正在发生的行为的具体信息。状态变量是只读的,即,它们的改变来自MySQL服务器,而不是用户。它们通常会显示比如执行了多少次查询,查询的类型,网络流量,索引是如何使用的(可以在1.6.5节中找到一个例子),缓冲区的使用情况,整个实例中有多少表打开,有多少临时表创建了,以及其他很多有用的信息。这里不会描述每一个变量的信息,但如1.6.5节那样,在本章稍后介绍状态变量时,我会注意哪个状态变量要监控。在变量变更上下文中,状态变量主要在3.9.2节非常有用,第6章会讲述如何从MySQL配置中获取状态信息。像其他变量一样,状态变量可以应用到个别会话以及所有会话(全局)上。会话变量会显示当前会话的状态,而全局变量则会显示自服务器启动后或者自上一次执行了FLUSH STATUS命令后的状态。  
 
提示
 
有些变量不是独立的,例如,当query_cache_size变量设置为0时,控制查询缓存是否开启的变量是无用的。调整类似选项的时候,需要考虑整组带来的影响,而不是单独只考虑一个变量。当更改服务器的选项时,它会使状态变量产生变化。例如,如果你修改了表缓存的选项,那么你应该查看一下Open_tables和Opened_tables这两个状态变量的值。在设置正确的情况下,Opened_tables的值不应该增长,而所有的Open_tables都应该处于缓存中。  警告某些情况下,由于某个变量的值设置得太大或者太小,会导致该变量的值被服务器丢弃。如果你怀疑你的变更并没有对服务器的运行造成影响,那么你需要通过查询“SHOW [SESSION|GLOBAL] VARIABLES LIKE '变量名称'”或查询“SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.[SESSION| GLOBAL]_VARIABLES WHERE VARIABLE_NAME='变量名称'”来对你的设置进行检查。
 
 

 
3.9 变量介绍
 
既然你已经熟悉无风险的服务器变量调优方法,我们就准备介绍几个重要的变量。这不是一个完整的指南,但是进一步研究的一个出发点。你可以以任意顺序阅读本节其余内容:从开始到结尾通读它,检查你当前最感兴趣的特定主题,或甚至跳过它,并且当你遇到问题时作为参考来使用它。我不会介绍每个选项,但会集中介绍我发现经常误用的变量或者需要扩大认识的变量。  提示起初,我怀疑我是否需要用本书一节的篇幅来介绍个别变量,因为每一个变量都在其他资源上详细介绍。但MySQL参考手册中没有从故障排查方法特别介绍它们,所以我决定对它们提供最简短的描述。
 
 
 

 
3.9.1 影响服务器与客户端行为的选项
 
 
本节介绍与同步复制、连接、存储引擎相关的一般服务器选项。在后面的集合中,我将仅仅涵盖MyISAM与InnoDB存储引擎。
 
1.服务器相关选项这些选项影响所有的连接与语句。
 
限制与max_*变量在3.3节中你已经看到了max_allowed_packet如何影响应用程序。另外一些选项限制了结果集的大小。例如:group_concat_max_len,它限制GROUP_CONCAT函数可以返回的字节数。
 
如下比较:
 
 
 与
 
在本书中,我减少group_concat_max_len值来显示一个适合本书的示例。在现实生活中,问题通常发生在当用户在大数据集上执行此函数而其默认值却太小的时候。我不会介绍max*组的每一个变量。如果你发现mysqld限制了你发送语句或返回结果的大小,你只须检查它们的值。
 
权限
你需要考虑到另一种可能是,如果语句失败,你的用户是否有执行它的权限,或特定数据库或者表的权限。例如:local_infile选项可以允许或者禁止执行LOAD DATA LOCAL INFILE查询。MySQL服务器通常会给出明确的错误消息来解释为什么一种或另一种操作是不允许的。
 
 
SQL模式
MySQL服务器定义的SQL模式可以改变服务器如何对待客户端的输入。你已经看到NO_ENGINE_SUBSTITUTION与STRICT_TRANS_TABLES能影响应用程序。其他模式能改变其他行为。下面是使用ANSI_QUOTES模式的另外一个示例。该模式告诉MySQL服务器使用ANSI SQL标准中定义的引号,而不使用MySQL本身默认的引号。这里介绍的问题不会发生在使用此模式的时刻,但会发生在用户依靠默认空模式希望服务器会错误地拒绝ANSI引号的时刻。
 
 
在第一种情况下,默认值起作用,MySQL将“f1”作为一个字符串然并且将其转换为双精度(DOUBLE)。这是默认SQL模式的有效值,但与用户期望的不同。 
当转化为双精度时,把f1的值转换为0,0是不等于1的。在ANSI_QUOTES模式下,f1被认为是一个字段名,所以查询能正常执行。这是一个很常见的问题,在包含多个条件的复杂查询中很容易被忽视。当遇到“奇怪”的查询结果时,请检查SQL模式并分析它是否影响查询。本书包含了几个关于SQL模式的示例,以显示它们如何使不同的服务器行为有不同的影响。我推荐你研究SQL模式列表以及在MySQL参考手册中它们是如何做的。
 
值得一提的一个细节是,从5.1.38版本开始,InnoDB插件有一个innodb_strict_mode选项,如果选项开启,它能严格检查插入InnoDB表中的数据,此作用与严格的SQL模式非常类似,不过略有不同。所以,如果使用InnoDB插件,需要检查此变量的介绍。该选项默认是关闭的。字符集与排序规则当latin1字符集不适合你的需要时,对于必须使用MySQL来存储非英语语言数据的用户来说,理解这些变量是至关重要的。  提示字符集是匹配字符或符号与表示它的字节序列的图。排序规则是一个排序规则,一个字符集可以有多个排序规则。本主题非常大,所以这里不会面面俱到,但是我会给你几个开始点。在MySQL中支持字符集与排序规则相当好,但是它的很多方面需要调整,所以人们对它们常常感到困惑。当你怀疑一个与字符集相关的错误时候,我推荐你认真研究MySQL参考手册中关于字符集与排序规则的章节。通常,在那里能找到问题的答案。
 
下面的一个示例显示,仅仅更改表的排序规则如何影响数据:
 
 
为什么连接(join)查询失败?我们在第一个表中指定了latin1_german2_ci排序规则,而第二表在连接中使用默认的排序规则。稍后会回到该示例,但是首先我将显示两个对于诊断此问题非常有用的查询。
 
 
每当你怀疑某些关于字符集与排序规则的事情有错误时,请运行上面两个查询,然后分析查询的结果与上下文。通常的安全规则是获取所有的character_set_* 变量、collation_*变量,并创建选项相同的任何表与一起协同工作的连接。设置客户端选项最简单方式是用SET NAMES 语句。当然,当需要不同的字符集与排序规则时,可以使用它,但是你应该明白它们的影响。如果我们回到collation_connection示例中,不同的排序规则是造JOIN接查询不能执行的根本原因。可以改变变量的值来证实这一点:
当在排序或者比较过程中遇到问题时,请检查字符集选项与表的定义。操作系统处理lower_case*参数lower_case_filesystem与lower_case_table_names选项跟字符集选项的作用非常相似。这些变量确定操作系统如何处理数据库对象的大小写情况。最好不要修改它们的值,特别是操作系统不区分大小写时。这可能导致意外的结果,如下面的示例:
  
我已经把输出的重要部分用粗体显示。外键定义指向table1表,但是该表名不存在。你不能通过在不区分大小写的操作系统(如Windows)上的转储在区分大小写的操作系统上(如Linux)来还原。在所有语句上同样最好使用一致性表命名方式并且在不区分大小写的操作系统上不混合使用大小写名称。初始SQL这些选项确定服务器在不同的时间是否应该自动执行某些SQL语句。init_file指向包含在服务器启动时应该执行的SQL语句的一个文件。init_connect包含在每个客户端连接时需要执行的一个SQL字符串。init_slave包含当一个服务器作为从服务器启动其SQL线程时需要执行的一个SQL字符串。当使用这些选项时,有两种典型的错误用法。第一个问题是你很容易忘记你所配置的选项。通常情况下,选项用来设置连接的一些默认值。所以当使用默认选项时,如果你得到了与它们应该返回的结果不同的结果,请检查你是否已经设置了这类变量的一个或多个。同样可以执行SHOW [GLOBAL] VARIABLES来找到连接使用的默认值。如果通过编程API来连接,请检查使用相同API的变量值,因为其他环境中,例如:MySQL命令行客户端,可能使用不同的配置文件因而有不同的默认值。init_connect选项的内容仅仅在连接用户没有SUPER权限时才执行。
 
这样做是为了让具有SUPER权限的用户即使在init_connect内容有错误的情况下也能连接。这是另外一个常见的使用错误,当用户作为SUPER用户连接并期望init_connect的内容执行时。open_files_limit这个重要选项限制MySQL服务器同时打开文件句柄的数量。限制数量越高,打开的表文件与临时表越多,因此处理的并发连接量数越多。如果这个限制在你的环境中设置得太低,在你试图连接、打开一个表或者执行一个需要创建临时表的查询时就会出现错误。因为此地选项的设置反映了硬件的限制,所以下一章将进一步介绍它。log_warnings当此选项打开(非零)时,就会在服务器的错误日志文件中写入警告信息。它们不是在SQL执行期间发出的警告,而是显示服务器内到底是怎么回事的调试消息。如果设置为2,此选项告诉服务器记录连接错误。当你正在对客户端无法连接或正在失去连接的情况做故障排除时,这非常重要。日志并非总是能找到问题所在,但其警告消息往往对要做什么能给出一些启发。当使用同步复制时,在主服务器上开启此选项非常重要,因为你能确定从服务器I/O线程何时失去连接。反过来,它是网络故障的一种症状,这在将来可能导致更严重的问题。当在从服务器上设置为1(默认值)时,它将输出自己的诊断消息,例如:在二进制日志和中继日志中的位置及其复制状态。从5.1.38版本开始,在基于语句模式的同步复制中,需要启用此选项,以便在从服务器输出不安全语句的信息。(5.1.38版本之前,从服务器在任何情况下都会输出此类消息。)从5.1.38版本开始,可以关闭此选项(设置为0),来丢弃你确定不需要该消息的日志。
 
 
2.复制选项这些选项确定了主从服务器之间的关系。
 
 
binlog-*与replicate-*过滤器通过binlog-do-*、replicate-do-*、binlog-ignore-*与replicate-ignore-* 选项,在复制过程中,MySQL有能力过滤对象。binlog-*选项减少在主服务器上写入二进制日志文件的事件,而replicate-*指定在从服务器上记录到二进制日志。从服务器还有replicate-wild-do-*与replicate-wild-ignore-*选项,二者允许通过模式匹配,指定哪些应该或哪些不应该同步。关于这些选项最常见的问题是:大家忘记他们指定了这些选项。在binlog-do-*、replicate-do-*、binlog-ignore-*与replicate-ignore-*中指定的过滤器当且仅当显式调用USE dbname时才生效。问题的特征是:某个特定查询没有复制。在从服务器上报告“unknown table xxx on query”错误。当你使用基于语句的复制(SBR)并且发现一些查询要么没有复制要么复制报错,请检查你是否设置了这些选项并且是否使用USE dbname。使用这些选项的变体replicate-wild-*总是好的,因为这些不依赖调用USE。二进制日志格式binlog_format变量允许你选择复制的格式:STATEMENT、ROW、或MIXED。这是一个动态变量,它能在SESSION级别调整。对于某个特定查询,如果你不想使用当前默认的模式,你可以使用binlog_format='row' 或 SET binlog_format='statement' 临时切换格式。binlog_direct_non_transactional_updates此选项指定何时非事务表更新应该写入二进制日志。默认情况下,当使用事务时,MySQL将非事务表的更新写入事务缓存里,仅当事务提交后,才把缓存刷新到二进制日志里。这样做以便从服务器更有可能与主服务器数据最终一致,即使依赖事务表中的数据来更新非事务表,并且主服务器在许多并发线程中同步更新相同的表。但这种解决方案会导致一些问题(当另外一个事务造成的更改依赖于使用非事务表中未提交的并行事务修改的数据时)。2.7.2节中的示例介绍了相似问题。如果遇到了这样的问题,可以打开这个选项。在打开之前,请务必确定非事务表中的数据不被使用事务表的语句更改。这是一个动态变量,可以在SESSION级别改变它,所以,可以在特定语句下使用它。它的工作原理决定了它只能在基于语句模式的复制中才生效。log_bin_trust_function_creators这个选项告诉mysqld当用户没有SUPER权限却试图在主用品创建一个不确定性的函数时,不要触发警告。请参见3.1节中默认行为的范例。binlog_cache_size与类似选项此条目包括以下选项:binlog_cache_size  binlog_stmt_cache_size   max_binlog_cache_size       max_binlog_stmt_cache_size
 
在写入二进制日志之前,这些缓存保存在事务期间提交的事务与非事务语句。如果达到了max_binlog_cache_size,语句将会中止并报告“Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage”错误。检查Binlog_cache_use、Binlog_stmt_cache_use、Binlog_cache_disk_use与Binlog_ stmt_cache_disk_use状态变量来找出binlog缓存使用的频率以及事务大小超过Binlog_ cache_use与Binlog_stmt_cache_use的频率。当事务大小操作缓存大小时,将会创建临时表来存储事务缓存。slave_skip_errors此选项允许从服务器SQL线程即使遇到某类错误时还能运行。例如,主服务器运行在宽松的SQL模式而从服务器却有一个严格的SQL模式,当插入字符串到整数字段时,由于数据格式不一致,而报告1366 (ERROR 1366 (HY000): Incorrect integer value)错误,可以设置slave_skip_errors,以便从服务器不会出现故障。这个选项可能导致主服务器从服务器数据不一致而又很难诊断,所以,如果你遇到这样的问题,请检查该选项是否没有设置。read_only
 
该选项使从服务器服务器只读。这意味着,仅仅只有从库SQL线程才能更新其数据,而其他连接只能读数据。该选项对于保持从服务器数据的一致很重要。然而,这个选项并不能限制具有SUPER权限的用户更改表。另外,所有用户仍允许创建临时表。
 
 
 
3.引擎选项本节介绍的选项具体到特定的存储引擎。
 
我将会在此讨论MyISAM和InnoDB的一些选项。与性能相关的选项在随后的小节里概述。在排除故障的情况下,你应该熟悉并仔细检查你使用的存储引擎的所有选项。InnoDB选项。我们将从InnoDB存储引擎的选项开始。innodb_autoinc_lock_mode此选项定义InnoDB在插入自增字段时用到的锁定方式。有三种模式:traditional(在5.1版本之前使用)consecutive(从5.1版本开始作为默认模式)和interleaved。consecutive是最安全的。使用另外两个模式往往以获取更好的性能,traditional方式也使用在向后兼容时。这里不说明不同锁类型之间的区别,因为MySQL参考手册详细说明了各自是如何工作的。然而牢记一点:如果你的应用程序设置自增值的方式让你感到意外,检查此模式并验证不同值是如何影响自增的。实际上我并不推荐从安全的consecutive模式转换至任何其他模式,但在某些场景下是可以接受的。innodb_file_per_tableInnoDB默认在共享表空间中存放表和索引数据。使用此选项,你可以告知它将表的索引和数据存放在单独的文件里。共享表空间仍然用来存放表定义。此选项在设置后创建的表上生效;之前创建的表依然使用共享表空间。使用这个变量实际上是个很好的做法,因为它有助于InnoDB表更有效地运作。除了能让你看到表实际占用的空间,它也能让你利用MySQL企业备份创建一个不完全备份,甚至在不同的MySQL安装版本上恢复一张表(使用Chris Calender博客上描述的方式,参见http://www.chriscalender.com/?p=28)。innodb_table_locks此变量定义了InnoDB是如何处理LOCK TABLES语句发出的表锁请求。默认(当设置了)立刻返回并且内部将表锁住。当关闭时(设置为0),它会接收LOCK TABLE语句,线程直到所有锁释放后才从LOCK TABLES … WRITE返回。
 
innodb_lock_wait_timeout
 
这是InnoDB等待行锁直到放弃的秒数。在innodb_lock_wait_timeout秒后,它会返回错误“ERROR 1205 (HY000):Lock wait timeout exceeded;try restarting transaction”至客户端。我经常看到人们将这个变量设得很大来防止查询失败,这只会导致更严重的问题,因为许多阻塞的事务会互相锁住。尝试在应用程序层处理锁等待错误,并不要将它设置得过高。此参数的最佳值取决于应用程序,通常应该大约为正常事务所消耗的时间。它的默认值是50秒,对于需要立即返回结果的应用程序有些大。大多数网店正是如此。innodb_rollback_on_timeout当查询因锁等待错误而中断时,只有最后一条语句回滚了,整个事务还没有中止。如果将选项设置为1你将会改变此行为。这种情况下事务会在锁等待超时后立刻回滚。innodb_use_native_aio在InnoDB 1.1版本插件中引入此选项,指定InnoDB是否应该使用Linux下原生的AIO接口,或者是自己来实现,称作“模拟AIO”。如果设置innodb_use_native_aio,InnoDB将分发I/O请求至内核。这提高了可扩展性因为比起模拟AIO新内核能够处理更多的并行I/O请求。此选项默认开启,在正常操作下不应该改变。如果你遇到操作系统异步I/O子系统的问题,阻止InnoDB启动,你可以将它关闭。典型的错误消息提示你将此选项关掉:error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory.Innodb_locks_unsafe_for_binlog此变量定义InnoDB如何使用间隙锁来搜索和扫描索引。默认值(设为0)下,间隙锁开启。如果设为1,大多数操作下会禁用间隙锁。其工作原理类似于隔离级别中的READ COMMITTED,但由于不太好调节应尽量避免。即使它允许你来处理锁问题,当并行事务插入新行至间隙时它也会带来新的问题。所以推荐用READ COMMITTED替代它。这个变量不能设置为SESSION级别,它会影响所有事务。MYISAM选项。这里只讨论两个选项,下一节讨论剩下的部分。Myisam_data_pointer_size当不指定MAX_ROWS参数创建MyISAM表时设置使用的默认指针大小。默认值是6,允许的范围是2~7。指针越大,表能容纳的行越多。默认的6允许你创建最大256TB的表。如果你使用的MyISAM表遇到一个“Table is full”错误,这意味着对于表数据指针设置过小(参考边栏“表可以有多大”)。表可以有多大?可以使用myisamchk–dvi查看对于特定的指针大小表的精确大小与如果使用FIXED行格式它会存储多少行数据。
 
myisam_recover_options此选项告知MySQL服务器检查,每次它打开一个MyISAM表时,表是否损坏或者没有正常关闭。如果检查未通过,MySQL在表上运行CHECK TABLE,如有必要将修复表。可能的值有OFF、DEFAULT(不是默认选项,但表示恢复方法不使用备份,强制进行一个快速检查)、BACKUP(创建表数据的.MYD文件的一个备份)。FORCE(指示服务器运行恢复操作,即使有丢失.MYD文件中一行或多行数据的风险),以及QUICK(如果未检测到表中有删除的块告知服务器不要运行恢复动作)。你可以同时使用两种或多种选项。此选项最常见的值,设置为BACKUP、FORCE,因为它能修复所有错误,并且安全因为它创建了一个备份文件。默认这个选项是关闭的。
 
 
4.连接相关的选项从故障排查角度来看这些选项的首要关注点在超时。
 
 
我也会讨论跟安全相关并且通常导致(或解决)问题的一些选项。超时。你已经熟悉了innodb_lock_wait_timeout,它中断那些等待行锁的查询。类似的参数有lock_wait_timeout,它适用于元数据锁。这个锁对所有需要元数据锁的操作有效:DML(数据操纵语言语句,如INSERT、UPDATE和DELETE),DDL、LOCK TABLES等。默认值是3 153 600秒,也就是一年。所以默认情况下,有效MDL锁永远不会解除。然而,可以更改值为大于1秒的任何值。它是一个动态变量,可以在合适级别更改。还有一系列超时相关参数,它们和你所运行的查询不相关,限制结果集、客户端数据和授权包的等待时间。
这些参数包括以下几个。connect_timeout这个超时使用在MySQL服务器和客户端交换授权包时。从5.1版本开始,此值默认设置为10秒。interactive_timeout交互式客户端在断开连接之前等待活动多长时间,即服务器等待多久来读取下一条命令。该术语“交互式客户端”是那些直接运行人们发的查询的客户端。例如,MySQL命令行客户端(mysql)是交互式的,而Web应用程序默认则不是。编写应用程序时,如果你考虑交互性你需要明确地指定。wait_timeout在断开连接前等待任何客户端中活动的时间。如果客户端是交互式的并且interactive_ timeout的值不同于wait_timeout,则以interactive_timeout为准。net_read_timeout从客户端写入MySQL服务器等待应答的时间。例如,此超时会在客户端执行大的插入操作时起作用。net_write_timeout客户端从服务器中读取时等待应答的时间。例如,当客户端发送一个SELECT查询读取结果,如果客户端等待一段时间未收到数据,这个超时会断开此连接。如果客户端需要在处理结果前做一些工作,检查工作的持续时间是否长于这个超时。碰到绝大多数这些限制的现象就是“MySQL server has gone away”错误或者“Lost connection to MySQL server during query”错误。
 
connect_timeout是例外。如果你遇到了这个限制,你会得到“Lost connection to MySQLserver at 'reading authorization packet'”错误。当从服务器I/O线程不能连接主服务器时也会得到类似错误。如你遇到了前面提到过的限制,不要盲目增加它们;找寻产生问题的真正原因。如果超时是由不稳定的网络造成,你应该修复网络,而不是增加超时时间。这里列出当你怀疑超时问题后可以采取的一些行动:临时增加*timeout变量,重新运行应用程序。如果超时频率这时变小,你可以肯定是超时时间的问题,但需要找到真正产生错误的原因。这可能是运行很久的应用程序,对大表访问慢或者不稳定的网络。与安全相关的选项。这些选项控制权限及MySQL服务器安全相关的其他方面。skip-grant-tables
 
当客户端连接服务器时如果缺乏正确的用户权限另一个授权问题会出现。1.9节讨论过一点。这里我只想教大家如何在忘记密码的情况下拯救自己。你需要使用skip-grant-tables选项启动服务器,手动编辑mysql数据库中的权限表,然后执行FLUSH PRIVILEGES语句。在这之后,新权限生效。不要忘记去掉skip-grant-tables选项重启服务器。否则,在重启后任何人都能连接上你的服务器。要安全地完成这个操作,包含skip-grant-tables以及skip_networking选项,这样当访问MySQL服务器没有限制时只允许本地连接。safe-user-create不允许使用GRANT语句来创建用户,除非用户有mysql.user表的INSERT权限才可以。secure_auth不允许早于4.1版的客户端连接现代服务器。选择4.1版本是因为当时新的安全模型添加到了连接协议中。secure_file_priv限制LOAD_FILE函数以及LOAD DATA和SELECT… INTO OUTFILE语句只能使用指定目录。
 
 

 
 
3.9.2 与性能相关的选项
 
这里会对影响性能的选项进行一个简短的概述。同样,我不会面面俱到,而只介绍那些最常用的。和上一节不同的是,这些选项不会导致不同的结果[2]。首先讨论影响服务器整体行为的选项,然后是一些特定于引擎的选项。
 
1.缓冲区和最大值
第一组选项控制服务器内部使用的内存总量,以及内存使用的上限。
 
join_buffer_size
 
这是为连接操作分配的最小缓存大小,这些连接使用普通索引扫描、范围扫描,或者连接不使用索引。两表之间进行全连接时分配缓存。因此,连接两个表的一条查询分配一块缓存,连接3个表的一个分配两块查询缓存,以此类推。这个选项可在SESSION级使用,能对于特定连接设置。为了查出是否需要增加join_buffer_size,可以检查Select_scan状态选项,它包括第一张表执行完整扫描的连接数量,同样Select_full_range_join,它包含使用范围搜索的连接数量。这些状态变量的值不会随着join_buffer_size的值的变化而变化,这样你可以利用它们来查出是否需要大的join_buffer_size,而不是衡量该值改变后的有效性。
 
net_buffer_length
 
服务器在客户端连接后立刻创建的缓存大小,用来保存请求和结果。根据需要这个大小可以增长至max_allowed_packet。正常情况下不用改变默认值(16384字节),但当设置max_connections选项时要记住此值。
 
query_prealloc_size
 
此缓存为语句解析和执行而分配。语句间缓存是不释放的。如果运行复杂查询,增加缓存是合理的,这样mysqld不会在执行查询的时候在分配内存上耗时。增加此大小到最大查询的字节数。
 
read_buffer_size
每个完成顺序扫描线程会分配为每次表扫描此缓存。
 
read_rnd_buffer_size
此变量用来控制在排序和发送结果至客户端之间存放读取结果的大小。大的值能提高包含ORDER BY的查询的性能。
 
 
sort_buffer_size
每个线程需要排序的时候会分配此缓存。查明你是否需要增加此缓存的大小,检查sort_merge_passes状态变量。也可以检查sort_range、sort_rows和sort_scan来查明你执行了多少个排序操作。这些状态变量显示了排序操作的数量。为了找出缓存合适的大小,需要检查一条或多条查询排序的行数,并乘以行大小。或者简单地设置不同的值直到sort_merge_passes停止增长。  提示sort_buffer_size缓存经常会分配,所以大的GLOBAL值会降低性能而不是增加性能。因此,最好不要设置此选项为GLOBAL变量,而是当需要时使用SET SESSION增加它。sql_buffer_result当此变量设置时,服务器会把每条SELECT语句的结果缓存到临时表中。当客户端查询需要长时间获取结果时这有助于提前释放表锁。在把结果存储到临时表中之后,服务器会释放原表上的锁,在第一个客户端仍获取结果时让其他线程可访问。为了找出查询是否在发送结果集上消耗过多时间,执行SHOW PROCESSLIST来检查查询在“Sendingdata”状态下的时间。 
 
提示在SHOW PROCESSLIST输出里“Sending data”状态代表线程正在读取和处理行,并发送数据至客户端。正如你所看到的,这比文字描述的更加复杂,并不一定意味着查询卡在发送数据状态下。
 
thread_cache_size
为将来使用缓存起来的线程数量。当一个客户端断开连接时,通常其线程也被销毁。如果该选项设置为正值N,那么连接断开后N个线程将被缓存起来。在具有良好线程实现的系统上,该选项不能显著地提高性能,但是,对于一个应用使用成百上千连接的情况,还是很有用的。
 
thread_stack
 
每个线程的栈大小。如果该变量设置过小,将会限制SQL语句的复杂性、存储过程的递归深度,以及服务器上其他内存消耗型的操作。对于大部分安装来说,默认值(32位系统是192KB;64位系统是256KB)就可以。如果有类似"Thread stack overrun"的错误消息,请增大该参数。
 
tmp_table_size
 
内存中,内部临时表的最大值。服务器默认设置为max_heap_table_size和tmp_table_size二者中的最小值。如果你有足够的内存,并且Created_tmp_disk_tables状态变量在增大,请增大该变量。把需要临时表的所有结果集放在内存中,可以大大提高性能。
 
query_cache_size
 
MySQL服务器存储查询及其结果集的缓存大小。增大该值,可以提高性能,因为查询插入到缓存里,接下来,招行相同的查询不需要查询解析、优化和执行,就可以从缓存中取到结果集。但是,该变量不要设置过大,因为当需要从缓存中删除查询时,即修改表数据,互斥体争用将阻塞并行查询,尤其是多核计算机和高并发环境下,超过8个用户会话并行访问查询缓存。该变量的合理值是小于100MB,尽管可以接受突然宕机而设置得大一点。  提示最佳实践是query_cache_size设置得偏小些,使用FLUSH QUERY CACHE定期整理碎片,而不是增大该值。为了确定查询缓存是否有效,可以查看Qcache_free_blocks、Qcache_free_memory、Qcache_hits、Qcache_inserts、Qcache_lowmem_prunes、Qcache_not_cached、Qcache_ queries_in_cache和Qcache_total_blocks状态变量。
 
table_definition_cache
 
存储在缓存中的表定义的数量。当表数量很大时,可以增大该值。如果需要,可以调整该值,以便最近的表刷新(FLUSH TABLES)后,保持Opened_table_definitions小于或等于Open_table_definitions。
 
table_open_cache
 
存储在缓存中的表描述符的数量。调整该值,以便Opened_tables仍小于或等于Open_tables。
 
 
 
2.控制优化器的选项
 
 
这些变量可以在SESSION级别设置,所以,你可以实验它们是怎样影响特定查询的。
 
optimizer_prune_level
如果该变量设为on,优化器删除即时搜索发现的不太有效的计划;如果设置为off,优化器使用详尽的搜索。默认值为1(on)。如果你怀疑优化器选择的不是最优计划,你可以改变它的值。
optimizer_search_depth
优化器搜索的最大深度。该值越大,优化器越有可能为复杂的查询找到最优计划。提高该值的代价就是优化器在搜索计划时的时间开销增大。如果设置为0,服务器会自动选择一个合理的值,默认值为62(最大值)。
 
optimizer_switch
该变量控制各种优化器特性,在这里略提一下。合理使用该参数,需要知道优化器的工作原理并且具有丰富的经验。
 
index_merge
启用或禁用索引合并优化,该优化几个从合并扫描中获取行记录,并把结果合并为一条记录。在EXPLAIN的结果输出中,Merge列显示的就是这个选项。
 
index_merge_intersection
启用或禁用索引合并交叉访问算法,当where从句包含key表示的范围条件并且与AND关键字时,将会使用该算法。如:key_col1< 10 AND key_col2 = 'foo'
即使key_col2 = 'foo'得到唯一值,优化器也把它当做范围条件,参见MYSQL手册中的“The Range AccessMethod for Single-Part Indexes”一节,(http://dev.mysql.com/doc/refman/5.5/ en/range-access-single-part.html)。
 
index_merge_union
启用或禁用索引合并联合访问算法,当where从句包含key表示的范围条件和OR关键字时,将会用到该算法。如: index_merge_sort_union启用或禁用索引合并排序联合访问算法,当where从句包含key表示的范围条件和OR关键字时,将使用该算法,但是,不会应用索引合并联合访问算法,如: max_join_size对于估计可能超过一定限制的select语句,(该选项)阻止优化器进行优化(如,查看多于max_join_size的行记录)。当调试找出没有使用索引的查询时,该选项会有很大帮助。max_length_for_sort_data当无法使用索引时,如果对ORDER BY进行条件优化,MySQL将使用文件排序算法。该算法有两个变体。原始算法读取所有匹配的行记录,在缓存里存储战对的键值和行指针,该缓存大小受sort_buffer_size限制。缓存中的值排序后,该算法再次读取表记录,但是,这次按照一定顺序读取。该算法的缺点是两次读取行记录。改进后的方法是读取整个行记录到缓冲区,然后排序键值,从缓冲区中读取行记录。该方法的问题是结果集通常超过sort_buffer_size,所以,对于大数据集,磁盘I/O操作使得该算法很慢。
 
max_length_for_sort_data
变量限制键值和行记录指针对的大小,因此原始算法适用于键值和行指针对中额外列的总大小超过了该限制的情况。磁盘活动多,并且CPU活动少是需要调低该变量的一个信号。更多详情可查看MYSQL手册中的“ORDER BY Optimization”部分。
 
max_seeks_for_key
根据表扫描必须检查的记录行数量,为使用键值而不是表扫描设置阈值。设置该参数为一个较小的值,比如100,在表扫描时,可以强制优化器优先查看索引。
max_sort_length
设置对BLOB或TEXT值排序时用到的初始字节数,后面的部分将被忽略。
 

3.与引擎相关的选项
 
这部分变量将会影响特定存储引擎的性能,本书只考虑InnoDB和MyISAM的选项。InnoDB选项。如同之前一样,首先介绍InnoDB存储引擎的选项。
 
innodb_adaptive_hash_index
禁用或启用(默认)InnoDB自适应散列索引,很多情况下,启用该选项是有好处的,但是存在少量已知的例外,自适应散列索引可能降低性能。例如,当类似的查询结果数量巨大,并且该索引占用30%或更多缓冲池时。可以在InnoDB监控器的输出里查看该信息,因为InnoDB可能会改进,所以这里不全部描述它们,不过,如果你遭遇到糟糕的性能,建议搜索Wed上的实际测试用例。
innodb_additional_mem_pool_size
该缓冲池存放数据字典和内部数据结构信息。一般情况下,表越多,该选项也应该越大,因为当该选项过小时,InnoDB会在错误日志中记录错误消息,所以你可以等看到错误日志再调整该值。
innodb_buffer_pool_size
InnoDB为存储数据、索引、表结构、自适应散列索引等分配的内存大小,这是影响innodb性能最重要的选项。可以将其设置为物理内存的80%。理想情况下,该缓冲区足够大,以致可以包含所有活动的InnoDB表和额外空间。同时,也要把其他缓冲区计算在内,寻找一个好的平衡。匹配Innodb_buffer_pool_%的,状态变量显示InnoDB缓冲池的当前状态。innodb_buffer_pool_instances该选项设置InnoDB缓冲池应切分的实例数量。每个实例有它自己的空闲列表、刷新列表、使用LRU算法的存储对象的列表,以及其他数据结构,并且受到自身互斥体的保护。设置该变量大于1,可以提高大型系统的并发性能。每个实例的(缓冲区)大小是innodb_buffer_pool_size/ innodb_buffer_pool_instances,并且至少是1GB。如果innodb_buffer_pool_size小于1GB,该选项不生效。
  
innodb_buffer_pool_instances切分缓冲区互斥体,所以,如果有8个或者更多并行SESSION同时访问InnoDB缓冲池,该选项可以设置为4~16。该选项取决于innodb_buffer_pool_size值和可用的内存。innodb_checksums默认情况,InnoDB使用校验和验证磁盘上所有页。该选项可以立即确定数据文件是否由于磁盘坏道或者其他原因而损坏。通常需要开启该功能,但是,在很少的情况下,当不关心数据时(比如,只读从服务器,不提供(线上)服务,只做备份),关闭该功能,可以提升性能。innodb_commit_concurrency可以同时提交事务的线程数量,默认值为0(没有限制)。innodb_thread_concurrencyInnoDB内部同时运行的线程数量,不要把它和MySQL服务器创建的连接线程的数量混淆。默认值是0:不限制并行或不检查并行。尽管大量线程并行运行一般意味着高性能,但是,如果同时并行运行很多用户会话,你可能遇到互斥体争用,如果同时运行的用户线程不超过16个,通常不用担心该参数。如果有更多用户SESSION,可以通过查询Performance Schema或者SHOW ENGINE INNODBMUTEX来监控互斥锁。如果出现互斥体争用,可以尝试限制该变量为16或32,或者把mysqld进程放置到Linux的任务集里或者Solaris的处理器集里,同时限制它为更少的内核而不是所有内核。最佳实践是使用超过8核的系统。或者,也可以使用线程池插件(见以下边栏)。线程池插件从5.5.16版本开始,MySQL商业发行版包含了线程池插件。默认情况下,MySQL为每个用户连接创建新线程。如果创建大量用户连接,那么并行运行大量线程时,上下文切换的开销变得很高,这会导致资源争用。例如,对InnoDB而言,这会增加持有互斥体的时间。线程池插件为处理线程提供了一种替代方案。它把所有连接线程按组编排,其数量受到thread_pool_size变量的制约,这样可以确保在任何时候[3]每组只有一个线程在运行。这个模型可以降低开销和大大提升性能。可以在MYSQL中手册获取线程池插件的更多信息。
 
innodb_concurrency_tickets
当允许一个线程进入InnoDB时,它接收innodb_concurrency_tickets张并发“票”(ticket),这些票允许线程离开和重新进入InnoDB,直到它使用完这些票。默认值是500。用完这些票后,把线程放置到等待队列中,以获取一组新票。innodb_doublewrite默认情况下,InnoDB分两次存储数据:第一次写入双写缓冲,第二次写入数据文件。像innodb_checksums一样,对于数据安全不是最重要的场景,这个安全选项可以关闭off,以提升性能。  
 
警告当设置时,innodb_doublewrite变量可以防止InnoDB数据损坏,因此,除非绝对需要,不要设置为off。
 
Innodb_dblwr_writes和Innodb_dblwr_pages_written状态变量分别显示两次写操作的数量和写页的数量。innodb_flush_log_at_trx_commit定义何时把更改写入(重做)日志文件以及刷新到磁盘。如果设置为1(默认值),在每个事务提交时更改均会写入和刷新到磁盘。为了得到更好的性能,可以设置该值为0(每秒写入日志文件和刷新到磁盘,而每个事务提交时,不做操作)或者2(每次提交事务时写入日志文件,但是,每秒刷新到磁盘)。注意,只有该选项为1时才符合ACID事务要求的。Innodb_os_log_fsyncs状态变量存储fsync()到日志文件的操作次数。Innodb_os_log_ pending_fsyncs包含挂起的fsync()写次数。Innodb_log_writes和Innodb_os_log_ pending_writes分别包含写入次数和挂起的写次数。innodb_flush_method默认情况下,fdatasync()是用来刷新数据文件的,fsync()是用来刷新日志文件到磁盘的,该值可以更改为以下值中的一个:O_DSYNC操作系统使用O_SYNC打开和刷新日志文件,同时使用fsync()刷新数据文件。O_DIRECT操作系统使用O_DIRECT打开数据文件,并且使用fsync()刷新数据文件。更改innodb_flush_method变量的值,或者提升性能或者降低性能,所以,在(生产)环境中,需要谨慎测试它。innodb_io_capacity后台InnoDB任务执行的I/O活动的上限。对于大多数现代系统来说,默认值200是个不错的选择。但是,可以根据系统的I/O吞吐量,调整该值。在快速存储器上,增大该值,才有意义。innodb_log_buffer_sizeInnoDB用来把日志文件写入磁盘的缓冲区大小。当缓冲区满时,必须等待日志刷新到磁盘上后,才能继续进行操作。增大该参数,可以减少磁盘I/O操作,但是,只有在存在大量事务时,这才有意义。状态变量Innodb_log_waits包含缓冲区因太小而需要的I/O等待次数。innodb_log_file_size每个日志文件的大小。大日志文件降低检查点的活动,节省磁盘I/O。但是,大日志文件显著地延缓崩溃恢复过程[4]。从1MB到不超过innodb_buffer_pool_size/log_ files_in_group的值是有合理的。所有日志文件加起来不能超过4GB。最佳实践是在不同的磁盘上存储日志文件、数据文件、如果使用的话,还有二进制日志文件,这样,即使一个设备故障,也不会同时丢失所有文件。innodb_open_files仅当使用innodb_file_per_table时,该变量才有意义。innodb_open_files是InnoDB可以同时打开的.ibd文件的数量。默认值是300,该值增大到InnoDB所有的表的数量是有意义的。innodb_read_io_threadsInnoDB读操作可以使用的I/O线程数。这些操作处理预读:I/O请求以异步方式将一组页数据装进InnoDB的缓冲池,然后清空和插入缓冲操作。默认值是4。innodb_write_io_threadsInnodb从缓冲池中写脏数据的I/O线程数量。默认值是4。innodb_stats_method服务器在收集索引值的统计信息时,处理null的方式。这会影响索引的基数,优化器因此生成的查询计划。innodb_stats_on_metadata启用该变量(默认值),每次执行元信息语句时,比如,SHOW TABLE STATUS或者SHOW INDEX,或者当查询InnodB表的INFORMATION_SCHEMA或统计数据时,innoDB都会更新统计信息。如果启用该变量,这些查询将和每次查询后执行ANALYZE TABLE有一样的效果。如果服务器频繁调用这些语句或者查询具有大量表的数据库时,可以禁用该变量。但是,当禁用该变量时,表统计数据将过时。innodb_stats_sample_pagesMYSQL 优化器用来计算索引分布统计信息的抽样索引页的数量,例如调用ANALYZE TABLE。如果你怀疑基数计算不合理,请增大该值(默认值是8)。但是,如果启用innodb_stats_on_metadata,增大该值,打开表的时间会增加。MyISAM选项。这一节将讨论哪些选项会影响MyISAM存储引擎的性能。myisam_max_sort_file_size当MyISAM重建索引时,使用的临时文件的最大值。默认值是2GB。如果超过该值,MySQL会使用健缓存,这会降低重建索引的速度。临时文件是磁盘文件,所以,它受到磁盘空间的限制。myisam_use_mmap当设置该变量时,MySQL服务器在读写MyISAM表时使用内存映射机制,默认对于这些操作使用系统调用。尽管myisam_use_mmap经常大大提高性能,但是,存在几个已知的bug,所以,设置该变量后,要测试应用程序。myisam_mmap_size内存映射压缩的MyISAM表可以使用的最大内存值。默认值很大:对于32位系统,4 294 967 295;对于64位系统,18 446 744 073 709 547 520。如果使用很多压缩的MyISAM表,减小该变量(的值),可以避免交换(内存)。myisam_sort_buffer_sizeREPAIR TABLE、CREATE INDEX或ALTER TABLE操作期间,排序或者创建MyISAM索引时,分配的缓冲区大小。myisam_stats_method收集索引值统计信息时,服务器处理NULL的方式。这影响索引的基数,以及优化器因此生成的查询计划。bulk_insert_buffer_sizeMyISAM批量插入时使用的特殊树状缓存的大小,包括:INSERT …SELECT、INSERT … VALUES (…), (…), …和LOAD DATA INFILE语句。
key_buffer_size缓存MyISAM表的索引块,并在线程之间共享(这些索引块)。该变量控制这个缓冲区的大小。可以创建多个键缓冲区,请在MySQL参考手册中查找和阅读该变量的描述信息。preload_buffer_size用来预加载索引的缓冲区大小。3.9.3 计算选项的安全值通过增大缓冲区或最大值,试图优化服务器性能时,全面考虑内存使用情况是很重要的。大量缓冲区能因“内存不足”错误而导致服务器崩溃。这一节将提供一些公式,帮助计算是否超出了可用内存。这部分不介绍选项本身,可以参考前面的章节或者MySQL手册,获取详细介绍。计算依赖该选项何时分配,以及是否共享,所以,本节把它分成几个相关类别。1.服务器级选项这些选项是GLOBAL的,影响所有连接和查询,一部分是服务器启动时分配的,而另一部分是后来分配的,比如查询缓存,初始值是0,(后续)不断增长,直至最大值。MySQL服务器达到所有的限制,分配所有允许的内存,会花费很长时间。因此,需要计算mysqld获得的RAM大小,和所有的缓冲区大小保证不要超过它。下面是服务器级内存缓冲区分配选项列表:query_cache_sizeinnodb_additional_mem_pool_sizeinnodb_buffer_pool_sizeinnodb_log_buffer_sizekey_buffer_size使用下面的公式计算需要为这些缓冲区分配多少RAM(单位是MB)。
 
 
 服务器还有限制文件描述符数量和缓存线程数量的选项,该计算可以忽略它们,因为分配给它们的内存仅仅是系统的一个指针大小乘以分配项的数量,这个数量足够小,以至于在现代系统上可以忽略。在这里列举一下,仅供参考:thread_cache_sizetable_definition_cachetable_open_cacheinnodb_open_files
 
 
.线程级选项这些选项是基于每个线程分配(内存)的,这样,服务器分配max_connections*sum(thread options)。设置max_connections和这些选项,保证物理内存总量-max_connections* sum(thread options)-服务器级选项大于0。留一些内存给第三类选项和后台操作,这些操作不受这些变量控制。线程级选项列表如下:net_buffer_lengththread_stackquery_prealloc_sizebinlog_cache_sizebinlog_stmt_cache_size使用下面的公式计算需要为它们分配多少内存(单位是MB): 或者,如果使用5.5.9之前的版本(binlog_stmt_cache_size变量在这个版本中引入)。
 
3.为特定操作分配的缓冲区当服务器执行特殊操作时,根据需要分配的缓冲区。很难计算出分配的内存大小。分析查询,找出哪些需要很多资源,然后像下面这样计算:缓冲区大小*为特定查询分配的缓冲区数量*并行执行的查询数量对于所有变量,这样计算,求出总和。只要对于大多数查询是足够的,保持这些选项(的值)小一点。如果一个特定查询需要更多内存,只对于这个会话增加这个变量的值。例如,对于一个每周执行一次的统计查询,如果需要设置max_join_size很高,GLOBAL级设置它是没有意义的,运行该查询前,请设置该参数。即使这样谨慎,也不要忘记从GLOBAL出发使用内存。以下选项针对每个线程分配一次(缓冲区):read_rnd_buffer_sizesort_buffer_sizemyisam_mmap_sizemyisam_sort_buffer_sizebulk_insert_buffer_size
preload_buffer_size对于每个线程分配多次(缓冲区)的选项是:join_buffer_sizeread_buffer_sizetmp_table_size可以使用以下公式计算MySQL为每个选项分配的内存的最大数量(单位是MB):
 
 
 
 
 从这个公式中去除不适合你的环境的选项。为了总结本章内容,这里给出一个综合的公式,计算MySQL安装时最多可以使用的内存(单位是MB):
 
或者,对于5.5.9之前的版本,对应的公式如下所示。
请注意,只有当变量值足够小时,公式才起作用。如果变量值很大,要么转变每个变量为MB,要么强制把它们转换为无符号整数。如果任何变量超过最大的无符号整数值(18 446 744 073 709 547 5207),即使强制转换为无符号整数也无济于事。不考虑可能的溢出,因为我要可读的和清晰的公式。如果你用不到那些缓冲区或特性,请从公式中去掉,这也是有意义的。例如,不使用myisam_mmap_size的默认值,取而代之的是使用线程可用的MyISAM表的最大值。
 
 
[1] 存在少数例外,例如SET GLOBAL general_log=1。通常这一类例外没有等效的SESSION。[2] 有个例外是EXPLAIN语句中的优化器参数。[3] 这不是硬性限制,有事每组不止一个线程在
 
[4] 这并不总是100%正确,因为InnoDB Plugin 1.0.7引入了加速崩溃恢复措施

打赏
赞(0) 打赏
未经允许不得转载:同乐学堂 » Day17-解决MySQL_ERROR参考指南(配置选项对服务器的影响)

特别的技术,给特别的你!

联系QQ:1071235258QQ群:710045715

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续给力更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏

error: Sorry,暂时内容不可复制!