第六章 优化服务器设置--高性能MySQL 施瓦茨--读书笔记

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

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

6.1配置基础知识

　　1 首先要知道MySQL从什么地方获取配置信息。(可以用启动脚本 --defaults-file=配置文件位置)

　　2 配置文件被分成了若干部分，每部分第一行都是 [程序名]

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

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

6.1.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。在配置文件中无法使用表达式。

...其他细节见原文

6.12设置变量的副作用

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

...其他细节见原文。

6.13 开始配置

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

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

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

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

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

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

6.2 通用调优原则

可以把调整配置文件看成一个两步的过程：

　　在安装的时候使用适当的初始值，然后基于工作负载进行细节调整。

 

6.2.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）。

6.2.2 MyISAM键缓存

最重要的是 key_buffer_size，

创建多个命名缓冲区...

缓存命中率: 100 - （ （Key_reads * 100)  / Key_read_requests )

缓存使用百分比：100 - （ （Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size)

...

即使没有使用MyISAM表，也要给key_buffer_size设置少量的内存，比如32MB。MySQL有时会使用MyISAM执行一些内部操作，比如给有GROUP BY的查询提供临时表。

MyISAM数据块大小

......

6.2.3 InnoDB缓冲池

如果主要使用InnoDB，InnoDB缓冲池也许比其他东西需要更多内存。

InnoDB缓冲池不仅仅保存索引，它还保存了行数据及自适应的哈希索引，插入缓冲区，锁及其它内部结构。InnoDB也使用了缓冲池帮助延迟写入，这样它可以合并更多的写入然后顺序的执行。

InnoDB严重依赖于缓冲池，并且应该给他分配足够的内存。

MySQL手册建议在专用服务器上80%的物理内存分配给缓冲池。实际上如果机器内存很大的话，还能分配更多。

InnoDB没有LOAD INDEX INTO CACHE等价的命令，需要给服务器暖身，使他为繁重得负载做好准备，可以使用查询进项全表或全索引扫描。

少数情况下，很大的缓冲池（比如50G)会导致长时间得延迟......

可以改变innodb_max_dirty_pages_pct的值，让InnoDB改变保留在缓冲池中被修改的页面的数量。...

6.2.4线程缓存

线程缓存保存了和当前连接无关的线程。这些线程可以提供新连接使用。

...

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

thread_cache_size定义了MySQL能采缓存中保存的线程数量。可以通过观察threads_created变量的值，以确定线程缓存是否足够大。

每秒创建的线程数量少于10个，缓存的大小就是足够的。

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

.......

 6.2.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解决这个问题。

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

6.2.6 InnoDB数据字典

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

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

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

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

6.3 MySQL I/O调优

一些配置选项可以影响MySQL把数据同步到硬盘和进行恢复的方式。
他们通常对性能有很大影响。因为这其中涉及了昂贵得I/O操作。他们代表了性能和数据安全的折中。
通常来说，保证数据被立即而连续地写入磁盘代价是很高的。
如果愿意承担数据不能被真正写入的风险，可以增加并发减少I/O等待时间，但是要决定能承受多大风险。

6.3.1 MyISAM I/O调优 -- 因不用此存储引擎，此处忽略

6.3.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改变数据的时候，他会把这次改动的记录写到日志缓冲里面。日志缓冲保存在内存中。缓冲写满，事务提交或每一秒钟，不管哪种情况发生，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

 

6.4 MySQL并发调优

 

6.4.1 MyISAM并发调优 --越过

 

6.4.2 InnoDB并发调优

 

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

 

innodb_thread_concurrency变量 限制了一次多少线程进入内核。
并发 = CPU数量x硬盘的数量x2

 

innodb_thread_sleep_delay