聊聊MySQL配置。 大多数开发者可能不太会关注MySQL的配置,毕竟在基本配置没有问题的情况下,把更多的精力放在schema设计、索引优化和SQL优化上,是非常务实的策略。这时,如果再花力气去优化配置项,获得的收益通常都比较小。更多的时候,基于安全因素的考量,普通开发者很少能够接触到生产环境的MySQL配置。正是这样,导致开发者(包括我)对MySQL的配置不甚了解,希望本文能帮你更好的了解MySQL配置。 如果让你在某种环境上安装配置MySQL,你会怎么做?安装后,直接copy修改示例配置文件,应该是大多数人的做法。但强烈建议不要怎么做,首先,示例配置文件有非常多注释掉的配置项,它可能会诱使你打开一个你并不了解的配置,而且这些注释还不一定准确。其次,MySQL的一些配置对于现代化的硬件和工作负载来说,有点过时了。 MySQL有非常多的配置项可以修改,但大多数情况下,你都不应该随便修改它,因为错误或者没用的配置导致的潜在风险非常大,而且还很难定位问题。确保基本配置正确,然后小心诊断问题,确认问题恰好可以通过某个配置项解决,紧接着再修改这个配置吧。 其实,创建一个好的配置,最快方法不是从学习配置项开始,也不是问哪个配置项应该怎么设置或者怎么修改开始,更不是从检查服务器行为和询问哪个配置项可以提升性能开始。最好是从理解MySQL内核和行为开始,然后利用这些知识来指导你配置MySQL。 就从理解MySQL配置的工作原理开始吧。 MySQL配置的工作原理MySQL从哪儿获得配置信息:命令行参数和配置文件。类Unix系统中,配置文件一般位于 /etc/my.cnf 或者 /etc/mysql/my.cnf。在启动时,可以通过命令行参数指定配置文件的位置,当然命令行中也可以指定其它参数,服务器会读取配置文件的内容,删除所有注释和换行,然后和命令行选项一起处理。 任何打算长期使用的配置项都应该写入配置文件,而不是在命令行中指定。一定要清楚的知道MySQL使用的配置文件位置,在修改时不能想当然,比如,修改了/etc/my.cnf的配置项,但MySQL实际并未使用这个配置文件。如果你不知道当前使用的配置文件路径,可以尝试:
一个典型的配置文件包含多个部分,每个部分的开头是一个方括号括起来的分段名称。MySQL程序通常读取跟它同名的分段部分,比如,许多客户端程序读取client部分。服务器通常读取mysqld这一段,一定要确认配置项放在了文件正确的分段中,否则配置是不会生效的。 MySQL每一个配置项均使用小写,单词之间用下划线或者横线隔开,虽然我们常用的分隔符是下划线,但如果在命令行或者配置文件中见到如下配置,你要知道,它们其实是等价的:
配置项可以有多个作用域:全局作用域、会话作用域(每个连接作用不同)、对象作用域。很多会话级配置项跟全局配置相等,可以认为是默认值,如果改变会话级配置项,它只影响改动的当前连接,当连接关闭时,所有的参数变更都会失效。下面有几个示例配置项:
配置文件中的变量(配置项)有很多(但不是所有)可以在服务器运行时修改,MySQL把这些归为动态配置变量:
动态的设置变量,MySQL关闭时这些变量都会失效。如果在服务器运行时修改了变量的全局值,这个值对当前会话和其他任何已经存在的会话都不起效果,这是因为会话的变量值是在连接创建时从全局值初始化而来的。注意,在配置修改后,需要确认是否修改成功。 你可能注意到,上面的示例中,有些使用“=”,有些使用“:=”。对于set命令本身来说,两种赋值运算符没有任何区别,在命令行中使用任一运算符符,均可以生效。而在其他语句中,赋值运算符必须是“:=”,因为在非set语句中“=”被视为比较运算符。具体可以参考如下示例:
有一些配置使用了不同的单位,比如 还有一些配置可以指定后缀单位,比如 小心翼翼的配置MySQL们常常动态的修改配置,但请务必小心,因为它们可能导致数据库做大量耗时的工作,从而影响数据库的整体性能。比如从缓存中刷新脏块,不同的刷新方式对I/O的影响差别很大(后文会具体说明)。最好把一些好的习惯作为规范合并到工作流程中去,就比如: 好习惯1:不要通过配置项的名称来推断一个变量的作用不要通过配置项的名称来推断一个变量的作用,因为它可能跟你想象的完全不一样。比如:
这两个配置都是在扫描MyISAM表时有效,且MySQL会为每个线程分配内存。对于前者,MySQL只会在查询需要使用时才会为该缓存分配内存,并且一次性分配该参数指定大小的全部内存,而后者同样是需要时才分配内存,但只分配需要的内存大小而不是参数指定的数值, 好习惯2:不要轻易在全局修改会话级别的配置对于某些会话级别的设置,不要轻易的在全局增加它们的值,除非你确认这样做是对的。比如:
好习惯3:配置变量时,并不是值越大越好配置变量时,并不是值越大越好,而且如果设置的值太高,可能更容易导致内存问题。在修改完成后,应该通过监控来确认变量的修改对服务器整体性能的影响。 好习惯4:规范注释,版本控制在配置文件中写好注释,可能会节省自己和同事大量的工作,一个更好的习惯是把配置文件置于版本控制之下。 说完了好习惯,再来说说不好的习惯。 坏习惯1:根据一些“比率”来调优一个经典的按“比率”调优的经验法则是,缓存的命中率应该高于某个百分比,如果命中率过低,则应该增加缓存的大小。这是非常错误的意见,大家可以仔细思考一下:缓存的命中率跟缓存大小有必然联系吗?(分母变大,值就变大了?)除非确实是缓存太小了。关于MyISAM键缓冲命中率,下文会详细说明。 坏习惯2:随便使用调优脚本尽量不要使用调优脚本!不同的业务场景、不同的硬件环境对MySQL的性能要求是不一样的。比如有些业务对数据的完整性要求较高,那么就一定要保证数据不丢失,出现故障后可恢复数据,而有些业务却对数据的完整性要求没那么高,但对性能要求更高。因此,即使是同一个变量,在这两个不同场景下,其配置的值也应该是不同的。那你还能放心的使用网上找到的脚本吗 ?
给你一个基本的MySQL配置前面已经说到,MySQL可配置性太强,看起来需要花很多时间在配置上,但其实大多数配置的默认值已经是最佳的,最好不要轻易改动太多的配置,你甚至不需要知道某些配置的存在。这里有一个最小的示例配置文件,可以作为服务器配置文件的一个起点,其中有一些配置项是必须的。本节将为你详细剖析每个配置有何作用?为什么要配置它?怎么确定合适的值?
分段MySQL配置文件的格式为集中式,通常会分成好几部分,可以为多个程序提供配置,如[client]、[mysqld]、[mysql]等等。MySQL程序通常是读取与它同名的分段部分。
例如服务器mysqld通常读取[mysqld]分段下的相关配置项。如果配置项位置不正确,该配置是不会生效的。 GENERAL首先创建一个用户mysql来运行mysqld进程,请确保这个用户拥有操作数据目录的权限。设置默认端口为3306,有时为了安全,可能会修改一下。默认选择Innodb存储引擎,在大多数情况下是最好的选择。但如果默认是InnoDB,却需要使用MyISAM存储引擎,请显式地进行配置。许多用户认为其数据库使用了某种存储引擎但实际上却使用的是另外一种,就是因为默认配置的问题。 接着设置数据文件的位置,这里把pid文件和socket文件放到相同的位置,当然也可以选择其它位置,但要注意的是不要将socket文件和pid文件放到MySQL编译的默认位置,因为不同版本的MySQL,这两个文件的默认路径可能会不一致,最好明确地设置这些文件的位置,以免版本升级时出现问题。
DATA STORAGE
为缓存分配内存接下来有许多涉及到缓存的配置项,缓存设置多大,最直接的因素肯定是服务器内存的大小。如果服务器只运行MySQL,所有不需要为OS以及查询处理保留的内存都可以用在MySQL缓存。为MySQL缓存分配更多内存,可以有效的避免磁盘访问,提升数据库性能。大部分情况来说最为重要的缓存:
还有一些其他缓存,但它们通常不会使用太多内存。关于查询缓存,前面文章(参考本系列的第一篇)已有介绍,大多数情况下我们不建议开启查询缓存,因此上文的配置中 如果只使用单一存储引擎,配置服务器就会简单许多。如果只使用MyISAM表,就可以完全关闭InnoDB,而如果只使用InnoDB,就只需要分配最少的资源给MyISAM(MySQL内部系统表使用MyISAM引擎)。但如果是混合使用各种存储引擎,就很难在他们之间找到恰当的平衡,因此只能根据业务做一个猜测,然后在运行中观察服务器运行状况后做出调整。 MyISAMkey-buffer-size
假设整个数据库中表的索引大小为X,肯定不需要把缓存设置得比X还大,所以当前的索引大小就成为这个配置项的重要依据。可以通过下面两种方式来查询当前索引的大小:
你可能会问,刚创建好的数据库,根本就没什么数据,索引文件大小为0,那如何配置键缓存大小?这时候只能根据经验值:不超过为操作系统缓存保留内存的25% ~ 50%。设置一个基本值,等运行一段时间后,根据运行情况来调整键缓存大小。总结来说,索引大小与OS缓存的25%~50%两者间取小者。当然还可以计算键缓存的使用情况,如果一段时间后还是没有使用完所有的键缓存,就可以把缓冲区调小一点,计算缓存区的使用率可以通过以下公式:
键缓存块大小是一个比较重要的值,因为它影响MyISAM、OS缓存以及文件系统之间的交互。如果缓存块太小,可能会碰到写时读取(OS在写数据之前必须先从磁盘上读取一些数据),关于写时读取的相关知识,大家可以自行查阅。 关于缓存命中率,这里再说一点。缓存命中率有什么意义?其实这个数字没太大的作用。比如99%和99.9%之间看起来差距很小,但实际上代表了10倍的差距。缓存命中率的实际意义与应用也有很大关系,有些应用可以在命中率99%下良好的工作,有些I/O密集型应用,可能需要99.99%。所以从经验上来说,每秒未命中次数这个指标实际上会更有用一些。比如每秒5次未命中可能不会导致IO繁忙,但每秒100次缓存未命中则可能出现问题。 MyISAM键缓存的每秒未命中次数可以通过如下命令监控:
最后,即使没有使用任何MyISAM表,依然需要将 myisam-recover
可以设置多个值,每个值用逗号隔开,比如配置文件中的 因此,在默认使用InnoDB存储引擎时,数据库中只有非常小的MyISAM表时,只需要配置 SAFETY基本配置设置到位后,MySQL已经比较安全了,这里仅仅列出两个需要注意的配置项,如果需要启用一些使服务器更安全和可靠的设置,可以参考MySQL官方手册,但需要注意的是,它们其中的一些选项可能会影响性能,毕竟保证安全和可靠需要付出一些代价。 max-allowed-packet
max-connect-errors这个变量是一个MySQL中与安全相关的计数器值,它主要防止客户端暴力破解密码。如果某一个客户端尝试连接MySQL服务器失败超过n次,则MySQL会无条件强制阻止此客户端连接,直到再次刷新主机缓存或者重启MySQL服务器。 这个值默认为10,太小了,有时候网络抽风或者应用配置出现错误导致短时间内不断尝试重连服务器,客户端就会被列入黑名单,导致无法连接。如果在内网环境,可以确认没有安全问题可以把这个值设置的大一点,默认值太容易导致问题。 LOGGING接下来看下日志的配置,对于MySQL来说,慢日志和bin-log是非常重要的两种日志,前者可以帮助应用程序监控性能问题,后者在数据同步、备份等方面发挥着非常重要的作用。 关于bin-log的3个配置, sync-binlog
需要注意的是,在5.7.7之前的版本,这个选择的默认值为0,而后默认值为1,也就是最安全的策略。对于高并发的性能,需要关注这一点,防止版本升级后出现性能问题。 剩下的4个配置项就没太多要说的。
CACHES AND LIMITStmp-table-size && max-heap-table-size这两个配置控制使用Memory引擎的内存临时表可以使用多大的内存。如果隐式内存临时表的大小超过这两个值,将会被转为磁盘MyISAM表(隐式临时表由服务器创建,用户保存执行中的查询的中间结果)。 如果查询语句没有创建庞大的临时表(通过合理的索引和查询设计来避免),可以把这个值设大一点,以免需要把内存临时表转换为磁盘临时表。但要谨防这个值设置得过大,如果查询确实会创建很大的临时表,那么还是使用磁盘比较好,毕竟并发数一起来,所需要的内存就会急剧增长。 应该简单的把这两个变量设为同样的值,这里选择了32M,可以通过仔细检查 query-cache-type && query-cache-size看前面 max-connections用于设置用户的最大连接数,保证服务器不会应为应用程序激增的连接而不堪重负。如果应用程序有问题,或者服务器遇到连接延迟问题,会创建很多新连接。但如果这些连接不能执行查询,那打开一个连接没什么好处,所以被“太多的连接”错误拒绝是一种快速而且代价小的失败方式。 在服务器资源允许的情况下,可以把 thread-cache-size线程缓存保存那些当前没有与连接关联但是准备为后面新连接服务的线程。当一个新的连接创建时,如果缓存中有线程存在,MySQL则从缓存中删除一个线程,并且把它分配给这个新连接。当连接关闭时,如果线程缓存还有空间的话,MySQL又会把线程放回缓存。如果没有空间的话,MySQL会销毁这个线程。只要MySQL在缓存里还有空闲的线程,它就可以迅速响应连接请求,因为这样就不用为每个连接创建新线程。 如何判断这个值该设置多大? 观察 open-files-limit在类Uinux系统上我们把它设置得尽可能大。现代OS中打开句柄开销都很小,如果此参数设置过小,可能会遇到“打开的文件太多( table_cache_size表缓存跟线程缓存类似,但存储的对象是表,其包含表.frm文件的解析结果和一些其他数据。准确的说,缓存的数据依赖于存储引擎,比如,对于MyISAM,缓存表的数据和索引的文件描述符。表缓存对InnoDB的存储引擎来说,重要性会小很多,因为InnoDB不依赖它来做那么多的事。 从5.1版本及以后,表缓存就被分为两个部分:打开表缓存和定义表缓存,分别通过 INNODBInnoDB应该是使用最广发的存储引擎,最重要的配置选项是下面这两个: innodb-buffer-pool-size如果大部分是InnoDB表,那么InnoDB缓冲池或许比其他任何东西都更需要内存,InnoDB缓冲池缓冲的数据:索引、行数据、自适应哈希索引、插入缓冲、锁以及其他内部数据结构。InnoDB还使用缓冲池来帮助延迟写入,这样就可以合并多个写入操作,然后一起顺序写入,提升性能。总之,InnoDB严重依赖缓冲池,必须为其分配足够的内存。 当然,如果数据量不大且不会快速增长,就没有必要为缓冲池分配过多的内存,把缓冲池配置得比需要缓存的表和索引还要大很多,实际上也没有什么意义。很大的缓冲池也会带来一些挑战,例如,预热和关闭都会花费很长的时间。如果有很多脏页在缓冲池里,InnoDB关闭时可能会花很长时间来把脏页写回数据文件。虽然可以快速关闭,但是在启动时需要做更多的恢复工作,也就是说我们无法同时加速关闭和重启两个操作。当有一个很大的缓冲池,重启服务需要花费很长时间(几小时或者几天)来预热,尤其是磁盘很慢的时候,如果想加快预热时间,可以在重启后立刻进行全表扫描或者索引扫描,把索引载入缓冲池。 可以看到示例的配置文件中把这个值配置为12G,这不是一个标准配置,需要根据具体的硬件来估算。那如何估算? 前面的小节,我们说到,MySQL中最重要的缓存有5种,可以简单的使用下面的公式计算: InnoDB缓冲池 = 服务器总内存 - OS预留 - 服务器上的其他应用占用内存 - MySQL自身需要的内存 - InnoDB日志文件占用内存 - 其它内存(MyISAM键缓存、查询缓存等) 具体来看,至少需要为OS保留1~2G内存,如果机器内存大的话可以预留多一些,建议2GB和总内存的5%为基准,以较大者为准,如果机器上还运行着一些内存密集型任务,比如,备份任务,那么可以为OS再预留多一些内存。不要为OS缓存增加任何内存,因为OS通常会利用所有剩下的内存来做文件缓存。 一般来说,运行MySQL的服务器很少会运行其他应用程序,但如果有的话,请为这些应用程序预留足够多的内存。 MySQL自身运行还需要一些内存,但通常都不会太大。需要考虑MySQL每个连接需要的内存,虽然每个连接需要的内存都很少,但它还要求一个基本量的内存来执行任何给定的查询,而且查询过程中还需要为排序、GROUP BY等操作分配临时表内存,因此需要为高峰期执行大量的查询预留足够的内存。这个内存有多大?只能在运行过程中监控。 如果大部分表都是InnoDB,MyISAM键缓存配置一个很小值足矣,查询缓存也建议关闭。 公式中就剩下InnoDB日志文件了,这就是我们接下来要说的。 innodb-log-file-size && innodb-log-files-in-group如果对InnoDB数据表有大量的写入操作,那么选择合适的 InnoDB使用一个后台线程智能地刷新这些变更到数据文件。实际上,事务日志把数据文件的随机I/O转换为几乎顺序地日志文件和数据文件I/O,让刷新操作在后台可以更快的完成,并且缓存I/O压力。 整体的日志文件大小受控于 修改日志文件的大小,需要完全关闭MySQL,然后将旧的日志文件迁移到其他地方,重新配置参数,然后重启。重启时需要将旧的日志迁移回来,然后等待MySQL恢复数据后,再删除旧的日志文件,请一定要查看错误日志,确认MySQL重启成功后再删除旧的日志文件。 想要确定理想的日志文件大小,需要权衡正常数据变更的开销,以及崩溃时恢复需要的时间。如果日志太小,InnoDB将必须要做更多的检查点,导致更多的日志写,在极个别情况下,写语句还会被拖累,在日志没有空间继续写入前,必须等待变更被刷新到数据文件。另一方面,如果日志太大,在崩溃时恢复就得做大量的工作,这可能增大恢复时间。InnoDB会采用checkpoint机制来刷新和恢复数据,这会加快恢复数据的时间,具体可以参考: innodb-flush-log-at-trx-commit前面讨论了很多缓存,InnoDB日志也是有缓存的。当InnoDB变更任何数据时,会写一条变更记录到日志缓存区。在缓冲慢的时候、事务提交的时候,或者每一秒钟,InnoDB都会将缓冲区的日志刷新到磁盘的日志文件。如果有大事务,增加日志缓冲区大小可以帮助减少I/O,变量 既然存在缓冲区,怎样刷新日志缓冲就是我们需要关注的问题。日志缓冲必须刷新到磁盘,以确保提交的事务完全被持久化。如果和持久化相比,更在乎性能,可以修改innodb-flush-log-at-trx-commit变量来控制日志缓冲刷新的频率。
1是最安全的设置,保证不会丢失任何已经提交的事务,这也是默认的设置。0和2最主要的区别是,如果MySQL挂了,2不会丢失事务,但0有可能,2在每次事务提交时,至少将日志缓冲刷新到操作系统的缓存,而0则不会。如果整个服务器挂了或者断电了,则还是可能会丢失一些事务。 innodb-flush-method前面都在讨论使用什么样的策略刷新、以及何时刷新日志或者数据,那InnoDB具体是怎样刷新数据的?使用 这个选项既会影响日志文件,也会影响数据文件,而且有时候对不同类型的文件的处理也不一样,导致这个选项有些难以理解。如果有一个选项来配置日志文件,一个选项来配置数据文件,应该会更好,但实际上它们混合在同一个配置项中。这里只介绍类Unix操作系统下的选项。 fdatasyncInnoDB调用
这些优化在特定的场景下才会起作用, 0_DIRCET这个设置不影响日志文件并且不是所有的类Unix系统都有效,但至少在Linux、FreeBSD以及Solaris是支持的。这个设置依然使用fsync来刷新文件到磁盘,但是它完全关闭了操作系统缓存,并且是所有的读和写都直接通过存储设置,避免了双重缓冲。如果存储设备支持写缓冲或预读,那么这个选项并不会影响到设备的设置,比如RAID卡。 0_DSYNC这个选项使得所有的写同步,即只有数据写到磁盘后写操作才返回,但它只影响日志文件,而不影响数据文件。 说完了每个配置的作用,最后是一些建议:如果使用类Unix操作系统并且RAID控制器带有电池保护的写缓存,建议使用0_DIRECT,如果不是,默认值或者0_DIRECT都可能是最好的选择。 innodb-file-per-table最后一个配置,说说InnoDB表空间,InnoDB把数据保存在表空间内,它本质上是一个由一个或者多个磁盘文件组成的虚拟文件系统。InnoDB表空间并不只是存储表和索引,它还保存了回滚日志、插入缓冲、双写缓冲以及其他内部数据结构,除此之外,表空间还实现了很多其它的功能。可以通过innodb-data-file-path配置项定制表空间文件,
这里在3个文件中创建了3G表空间,为了允许表空间在超过了分配的空间时还能增长,可以像这样配置最后一个文件自动扩展
总结MySQL有太多的配置项,这里没有办法一一列举,重要的是了解每个配置的工作原理,从一个基础配置文件开始,设置符合服务器软硬件环境与工作负载的基本选项。 参考资料[1] Baron Scbwartz 等著;宁海元 周振兴等译;高性能MySQL(第三版); 电子工业出版社, 2013
|
|