50台服务器MySQL的负载

第八章 优化服务器设置

• 8.1 创建MySQL配置文件
• 8.2 配置基础知识

• 8.2.1 语法，作用域及动态性
• 8.2.2 设置变量的副作用
• 8.2.3 开始配置

• 8.3 通用调优原则

• 8.3.1 内存使用调优
• 8.3.2 MyISAM键缓存
• 8.3.3 InnoDB缓冲池
• 8.3.4 线程缓存
• 8.3.5 表缓存
• 8.3.6 InnoDB数据字典

• 8.4 MySQL I/O调优

• 8.4.1 MyISAM的 I/O调优
• 8.4.2 InnoDB I/O调优

• 8.5 MySQL并发调优

• 8.5.1 MyISAM并发调优
• 8.5.2 InnoDB并发调优

MySql的默认配置不适用于使用大量资源，因为其通用性很高。

不要期望改变配置文件会带来巨大的性能提升。提升大小取决于工作负载，通常可以通过选择适当的配置参数得到两到三倍的性能提升。在这时候，性能提升就是增量的。为了更大得提升，通常要检查服务器架构，查询及应用程序的架构。

MySQL有大量可以修改的参数－－但不应该随便去修改。通常只需要把基本的项配置正确（大部分情况下只有很少一些参数是真正重要的），应该将更多的时间花在schema的优化、索引，以及查询设计上。在正确地配置了MySQL的基本配置项之后，再花力气去修改其它配置项的收益通常就比较小了。

8.1 创建MySQL配置文件

建议不要使用操作系统的安装包自带的配置文件，最好从头开始创建一个配置文件。（首先要确定MySQL使用了哪个配置文件！）

8.2 配置基础知识

• 首先要知道MySQL从什么地方获取配置信息。(可以用启动脚本 --defaults-file=配置文件位置)
• 配置文件被分成了若干部分，每部分第一行都是 [程序名]

mysql程序会读取和程序名同名的部分，并且许多客户端会读取client部分，这儿也是放置通用设置的地方。

服务器通常会读取mysqld部分。

8.2.1 语法，作用域及动态性

配置设置都是小写的，使用下划线或破折号分割单词。

配置设置有几种作用域。一些设置在整个服务器内都有效(全局域);另外一些针对每个连接(会话域);还有一些只对对象有效。许多会话域的变量和全局变量是一样的，可以认为是全局变量提供了默认值。如果修改了会话域变量的值，它只会在当前的连接内有效，连接关闭后值就消失了。

值得注意的例子：

• query_cache_size变量是全局性的
• sort_buffer_size变量有全局性的默认值，但是可以在会话中设置
• join_buffer_size有全局的默认值，并且可以在会话中进行设置。

除了在配置文件中设置变量，也可以在服务器运行的时候对某些值(不是全部值)进行设置。MySQL把它们叫做动态变量。

eg：
mysql> SET sort_buffer_size = <value>;
mysql> SET GLOBAL sort_buffer_size = <value>;

如果动态的设置了变量，那么它们在MySQL关闭之后就会丢失。如果想保留这些设置，就应该同时更新配置文件。

变量使用不同的单位，应该要知道每个变量的正确单位是什么。eg:table_cache定义了能被缓存的表的数量，不是被缓存的字节数。

在配置文件或命令行参数中， 很多变量都能使用前缀，比如1MB。

当使用SET命令时，必须使用字面值1048576或者1024x1024。在配置文件中无法使用表达式。

8.2.2 设置变量的副作用

动态设置变量有出人意料的副作用，比如清空缓冲区。在线更改的设置可能会导致服务器做大量的工作。

8.2.3 开始配置

设置变量的时候要小心。更大的值不总是好事情，如果将值设置得太高，很容易引发诸多问题：耗尽内存,导致服务器使用交换区,耗尽地址空间等。

应该总是使用监控系统来衡量改动是提升了还是损害了服务器总体性能。

在对配置进行调优之前，应该对查询和结构进行调优，进行一些最基本的优化，比如添加索引。调优是个渐进的过程。除非硬件，工作负载及数据是完全静止的，否则就要在随后的工作中对配置再次进行调整。

这意味着并不需要一次性把服务器性能调到最好。

实际上对配置文件花费大量时间也许会收效甚微，只要让配置保持“够好”就行了，除非忘记了某项重要的设置，否则就不需要再次改动它。

当更改了查询或架构的时候，就可以回过头再次修改配置文件。

8.3 通用调优原则

可以把调整配置文件看成一个两步的过程：
在安装的时候使用适当的初始值，然后基于工作负载进行细节调整。

8.3.1 内存使用调优

配置MySQL正确地使用内存对性能至关重要。

MySQL内存消耗有两种范畴：

• 可以控制的：为特定工作使用多少内存。
• 不可控的：MySQL使用多少内存来运行服务器，解析查询及管理内部运行。

可以用下面的方式进行内存调优：

1.决定MySQL能使用的内存的绝对上限
特定系统存在内存使用上限，比如32位和64位机器

2.决定MySQL为每个连接使用多少内存，比如排序缓冲区和临时表
MySQL只需要很少的内存保持连接开启，一定基本内存执行查询。需要在MySQL工作负载处于顶峰的时候为它分配足够的内存，否则查询变慢，甚至失败

3.决定操作系统需要多少内存来很好的运行自身，包括机器上的其他程序，比如周期性工作…，使服务器不主动把虚拟内存保存在磁盘上。

4.假定上面的工作都已完成，就可以把剩余的内存非配给MySQL的缓存，比如InnoDB的缓存池

如果服务器是MySQL专用的，就不需要为操作系统或用于处理查询的缓存保留任何内存。
MySQL缓存比其他东西需要更多的内存。他使用缓存来避免磁盘访问。
对于大部分用户来说，下面这些缓存是最重要的：

• 操作系统为MyISAM的数据提供的缓存
• MyISAM键缓存
• InnoDB缓存池
• 查询缓存
• 无法手工配置的缓存，例如二进制日志和表定义文件的操作系统缓存

如果只使用一个存储引擎，服务器调优就容易的多。如果只使用InnoDB,就可以给MyISAM分配最少的资源（MySQL某些内部操作需要MyISAM）。

8.3.2 MyISAM键缓存

key_buffer_size - 这对MyISAM表来说非常重要，是用来设置整个MySQL中常规Key Cache的大小。一般来说，如果MySQL运行在32位平台，此值建议不超过2GB，如果是64位平台上则不用考虑此限制，但也最好不要超过4GB。

合理的值取决于索引大小、数据量以及负载 – 记住，MyISAM表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。尽管如此，需要总是检查是否所有的 key_buffer 都被利用了， – .MYI 文件只有 1GB，而 key_buffer 却设置为 4GB 的情况是非常少的。这么做太浪费了。如果你很少使用MyISAM表，那么也保留低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。

8.3.3 InnoDB缓冲池

如果大部分都是InnoDB表，InnoDB缓冲池或许比其他任何东西更需要内存。InnoDB缓冲池并不仅仅缓存索引：它还缓存行的数据、自适应哈希索引、插入缓存（Insert buffer）、锁、以及其他内部数据结构。InnoDB还使用缓冲池来帮助延迟写入，这样就能合并多个写入操作，然后一起顺序地写回。总之InnoDB严重依赖缓冲池，你必须确认为它分配了足够的内存。

如果数据量不大，并且不会快速增长，就没必要为缓冲池分配过多的内存。把缓冲池配置得比需要缓存的表和索引还要大很多实际上没有什么意义。当然，对一个迅速增长的数据库做超前的规划没有问题，但有时我们也会看到一个巨大的缓冲池只缓存了一点点数据，这就没有必要了。

很大的缓冲池也会带一些挑战，例如，预热和关闭都会花费很长的时间。如果有很多脏页在缓冲池里，InnoDB关闭时可能会花费较长的时间把脏页写回数据文件。当然也可以强制快速关闭，但是重启时就必须做更多的恢复工作。

当脏页的百分比超过了innodb_max_dirty_pages_pct阈值，InnoDB将快速地刷写脏页，尝试让脏页的数量更低。当事务日志没有足够的空间剩余时，InnoDB将进入“激烈刷写”模式，这就是大日志可以提升性能的一个原因。

如果不能快速预热，可以在重启后立即进行全表扫描或者索引扫描，把索引载入缓冲池。也可以使用init_file设置，把SQL放在一个文件里，然后当MySQL启动的时候来执行。

8.3.4 线程缓存

线程缓存保存了和当前连接无关的线程。这些线程可以提供新连接使用。当新的连接创建时，会在线程缓存中删除一个线程并且将其分配给这个新的连接。当连接关闭时如果还有空间的话，MySQL又会把线程放回缓存。如果没有空闲将会销毁这个缓存。

只要缓存中有自由的线程，MySQL就能很快的响应连接请求，因为它不需要为每个连接都创建新的线程。

thread_cache_size定义了MySQL能采缓存中保存的线程数量。一般不需要重新配置，可以观察threads_created变量的值，以确定线程缓存是否足够大。当每秒创建的新线程数量很少有少于10个时，通常应该保持线程缓存足够大。

一个好办法是观察Threads_connected的值，并且把thread_cache_size的值设置的足够大，以处理波动的负载。

8.3.5 表缓存

表缓存存储了能表示表的对象。缓存中每个对象都包含了解析表后生成的.frm文件和其他数据，对象中的其他东西依赖于标的存储引擎。

表缓存有助于重用资源。

表缓存的设计有点以MyISAM为中心，对于InnoDB来说，表缓存没那么重要，InnoDB在很多方面都不会依赖于它（例如保存文件描述符，InnoDB为此有自己的表缓存）。但是，InnoDB还是可以从解析后的.frm文件中获益。

表缓存被分成两个部分：一部分为打开表。另一部分为表的定义（通过table_open_cache和talbe_definition_cache定义)…通常可以吧table_definition_cache的值设置的足够高，以缓存所有表的定义。

Opened_tables的值很大或者正在上升，就说明表缓存不够大，应该增加系统变量table_cache的值（在MySQL中时table_open_cache)

如果MySQL不能打开更多文件的错误提示，更改open_files_limit解决这个问题。

线程和表缓存其实都不会使用太多内存，他们的好处在于可以保存资源。在高并发条件下，提供高效率。

8.3.6 InnoDB数据字典

InnoDB自己有对每个表的缓存，叫做"表定义缓存"或者"数据字典"，它是不可配置的。

当InnoDB打开一个表的时候，它就向字典中添加一个相应的对象。表关闭时，他不会被从字典中删除。

如果用innode_file_per_table选项，那么对InnoDB任何时候能打开的.ibd文件数量还有另一个限制。这是由InnoDB存储引擎处理的，不是MySQL服务器，它受innodb_open_files的控制。

InnoDB为每个.idbw文件使用全局文件描述符。给innodb_open_files设置足够大，这样服务器就可以保留所有同时打开的.ibd文件。

8.4 MySQL I/O调优

一些配置选项可以影响MySQL把数据同步到硬盘和进行恢复的方式。

他们通常对性能有很大影响。因为这其中涉及了昂贵得I/O操作。他们代表了性能和数据安全的折中。
通常来说，保证数据被立即而连续地写入磁盘代价是很高的。

如果愿意承担数据不能被真正写入的风险，可以增加并发减少I/O等待时间，但是要决定能承受多大风险。

8.4.1 MyISAM的 I/O调优

MyISAM通常在每次写操作之后就把索引变更刷新磁盘。如果需要在一张表上做多次变更的时候，毫无疑问批量操作将会更快。

• 一种办法是用LOCK TABLES延迟写入，直到解锁这些表。这种情况我们可以精确控制那些被延迟，以及什么时候将其刷到磁盘。
• 一种办法是通过设置 delay_key_write变量，也可以延迟索引的写入。如果这么做，修改的键缓冲块直到表被关闭才会刷新。

8.4.2 InnoDB I/O调优

可以控制InnoDB如何恢复，还能控制他如何打开表及刷写数据，他们极大地影响了恢复和总体性能。
innoDB恢复过程是自动的并且在InnoDB启动的时候总会运行，但是还是可以影响他的行为。

它有复杂的链式缓冲区和文件，使它可以改进性能并且保证ACID属性，并且链上每一个环节都是可配置的。

对于普通使用者，一个很重要的配置是InnoDB日志文件的大小，InnoDB如何刷写日志缓冲区，以及InnoDB如何执行I/O.

InnoDB事务日志
InnoDB使用日志来减少提交事务的开销。它不是在每次事务提交的时候
就把缓冲池刷写磁盘上,而是记录了事务。
事务对数据和索引做出的改变通常会被映射到表空间的随机位置，所以将这些改变写到磁盘上会引起随机I/O.
作为一条原则，随机I/O比顺序I/O开销要高得多，因为它需要时间在磁盘上找到正确的位置，并且还要等磁头移到相应位置上。

InnoDB使用自身的日志把随机I/O转换为顺序I/O。一旦日志被记录到磁盘上,事务就是持久的了,尽管这时修改还没有被写到数据文件中.如果发生了一些坏事（比如断电）,InnoDB可以回放日志并回复提交了的事务。

InnoDB最终要把改变写到数据文件中，因为日志大小是固定的。它以循环的方式写日志，当记录达到日志底部，就会又从顶部开始。它不会覆盖改变没有被应用到数据文件的记录，因为这会消除提交的事务唯一持久性的记录。

InnoDB使用后台线程智能地把改变写入到文件中。实际上，事务日志把随机数据文件I/O转换为顺序日志文件和数据文件I/O.把刷写工作变成后台进行可以让查询更迅速。

日志文件大小由innodb_log_file_size和innodb_log_files_in_group控制，并且他们对写入的性能影响极大。这两个文件默认大小都是5MB,总计为10MB。对于高性能负载，这个大小是不够的。

InnoDB使用多个文件作为一组循环日志。通常不需要修改默认的日志数量，只修改每个日志文件的大小即可。要修改日志文件大小，需要完全关闭MySQL，将旧的日志文件移到其他地方保存，重新配置参数。

要确定理想的日志文件大小，必须权衡正常数据变更的开销和崩溃恢复需要的时间，如果日志太小，InnoDB必然将做更多的检查点，导致更多的日志写。如果日志太大，在崩溃恢复时InnoDB可能不得不做大量的工作。

当InnoDB变更任何数据时，会写一条变更记录到内存日志缓冲区中。在缓冲满的时候，事务提交的时候，或者每一秒钟，这三个条件无论哪个先达到，InnoDB都会刷新缓冲区的内容到磁盘日志文件。变量innodb_log_buffer_size可以控制日志缓冲区的大小，默认为1M。通常不需要把日志缓冲区设置得非常大。推荐的范围是1~8M。作为一个经验法则，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。

InnoDB怎么刷新日志缓冲？当InnoDB把日志缓冲刷新到磁盘日志文件时，会先使用一个Mutex锁住缓冲区，刷新到所需要的位置，然后移动剩下的条目到缓冲区的前面。日志缓冲必须被刷新到持久化存储，以确保提交的事务完全被持久化了。如果和持久相比更在乎性能，可以修改innodb_flush_log_at_trx_commit变量来控制日志缓冲刷新的频繁程度。可能的设置如下：

0：每秒一次把日志缓冲写到日志文件，但是事务提交时不做任何事。
1：将日志缓冲写到日志文件，然后每次事务提交都刷新到持久化存储（默认并且最安全的设置），该设置保证不会丢失任何已提交的事务。
2：每秒钟做一次刷新，但每次提交时把日志缓冲写到日志文件，但是不刷新到持久化存储。

“把日志缓冲写到日志文件”和“把日志刷新到持久化存储”是不同的。在大部分操作系统中，把缓冲写到日志只是简单地把数据从InnoDB的内存缓冲转移到了操作系统的缓存，也是在内存里，并没有真正把数据写到持久化存储。

如果MySQL崩溃了或者断电了，设置0和2通常会导致最多1秒的数据丢失，因为数据可能存在于操作系统的缓存中。

相反，把日志刷新到持久化存储意味着InnoDB请求操作系统把数据刷出缓存，并且确认写到磁盘了，这是一个阻塞IO的调用，直到数据被完全写回才会完成，当写数据到磁盘比较慢，而该配置项设置为1时，可能明显地降低InnoDB每秒可以提交的事务数。

日志文件大小和日志缓冲

在InnoDB改变数据的时候，他会把这次改动的记录写到日志缓冲里面。日志缓冲保存在内存中。缓冲写满，事务提交或每一秒钟，不管哪种情况发生，InnoDB都会把缓冲区写到磁盘上的日志文件中。如有大型事务，就可以增加缓冲文件来减少I/O动作。用innodb_log_buffer_size控制。除非写入大量巨型BLOB记录，否则缓冲区1MB到8MB足够了。（够1秒用的就行)

可以通过检查SHOW INNODB STATUS命令的LOG部分检测InnoDB的日志和日志缓冲性能，还可以观察Innodb_os_log_written了解InnoDB向日志写入了多少数据。

InnoDB如何刷写日志缓冲
日志缓冲区必须被刷写到持久性存储中，以保证提交了的事务能完全持久化。如果比起持久性，更在意性能，可以改变innodb_log_at_trx_commit的值来控制日志缓存被刷写到什么地方及刷写频率

InnoDB 如何打开并清写日志和数据文件
innodb_flush_method选项让你可以配置InnoDB实际与文件系统进行交互的方式。

InnoDB表空间
InnoDB把数据保存在表空间中。
表空间实际上是快月了磁盘上的一个或多个文件的虚拟文件系统。
InnoDB出于很多考虑使用表空间，不仅为了存储表和索引。它保留了自己的撤销日志，插入缓存，双写缓存，以及表空间的其他内部结构。

配置表空间。可以使用innodb_data_file_path定义表空间文件。这些文件都在innodb_data_home_dir定义的目录中。

eg:
    innodb_data_home_dir = /var/lib/mysql/data
    innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend

autoextend:表空间耗尽后自动增长。
max:限制延伸文件最大为xx

innodb_file_per_table:使InnoDB为每个表使用一个文件，它在数据库目录中以“表名.ibd”保存数据。

旧数据版本和表空间。InnoDB的表空间在写入负荷很重的环境中会增长得很大。
有的问题不是出在没提交的事务上，而是出在工作负载上，清理进程是单线程的，它跟不上需要被清理的老数据的数量。

SHOW INNODB STATUS的输出有助于锁定问题
如果有很多未被清理的事物并且表空间因它而增长，就可以强制mysql变慢，以使清理线程能跟上数据的变化。
为了减缓写入，可以把innodb_max_purge_lag设置为0以外的值。这个值表示等待清理的事物的最大数量，一单超过这个值，InnoDB就会延迟更新数据的事物，要知道负载才能决定这个值的最佳大小。

双写缓冲
InnoDB在对页面进行部分写入的时候使用了双缓冲，防止数据损坏。
部分写入发生在磁盘写入没有全部完成，并且只有16KB页面的一部分被写入的时候。
有很多原因(崩溃，缺陷等)会导致数据被部分写入,双写缓存在这种情况下保护了数据。

另外的I/O调优 Sync_binlog

8.5 MySQL并发调优

8.5.1 MyISAM并发调优

MyISAM再删除的时候不会重新整理表，只会将其标记为删除，在表上留下“空洞”，在插入行的时候利用这些空洞，没有空洞的时候才会插入末尾。

尽管MyISAM是表级锁，但是依然可以一边读取数据一边追加新行。这样只能读取查询时候的数据，新插入的数据不可见以避免不一致读。

MyISAM不能支持MVCC，所以除非在末尾插入，否则不能支持并发插入。

通过设置concurrent_insert这个变量，可以配置MyISAM打开并发插入。
0：MyISAM不允许并发插入，所有的插入都会对表加上互斥锁。
1：默认值，在表中没有空洞时，允许鬓发插入。
2：在MySQL 5.0之后的版本才有效。强制并发插入在表的末尾。如果没有表读取数据时，MySQL将新行放入空洞，这种情况会使得表更加碎片化。

8.5.2 InnoDB并发调优

InnoDB是为高并发设计的，但是并不完美。InnoDB的结构仍然基于有限内存，单CPU和单磁盘系统。
InnoDB某些方面的性能在高并发条件下下降的很快，唯一的解决办法是限制并发。
通过SHOW INNODB STATUS 输出中的 SEMAPHORES部门来确认是否发生了并发问题。
InnoDB用自己的“线程调度”程序来控制线程如何进入InnoDB的内核访问数据，以及进入内核之后可以执行的动作。
innodb_thread_concurrency变量 限制了一次多少线程进入内核。
并发 = CPU数量x磁盘的数量x2
另外，也可以通过线程池(Thread Pool)来限制并发。

innodb_thread_sleep_delay