第08章-优化服务器设置

8.1 MySQL配置的工作原理

配置文件位置

which mysqld

8.1.1 语法、作用域、动态性

8.1.2 设置变量的副作用

即使非常小的排序操作，排序缓存也会分配全部大小的内存 （震惊）

可以这么做：

1
2
3

set @@session.sort_buffer_size := <value>;
-- 业务sql
set @@session.sort_buffer_size := DEFAULT;

8.1.3 入门

在改配置之前，应该优化查询和schema

用户高峰和低谷对配置的影响

8.1.4 通过基准测试迭代优化

控制变量法

8.2 什么不该做

8.3 创建MySQL配置文件

数据的位置：/var/lib/mysql

InnoDB基础配置

设置缓冲池大小的方法：

  • 从服务器内存总量开始
  • 减去系统的内存占用
  • 减去其他程序的占用
  • 减去MySQL自身需要的内存，例如为每个查询操作分配的一些缓冲
  • 减去足够让操作系统缓冲InnoDB日志文件的内存，至少是足够缓冲最近经常访问的部分。留一些内存至少可以缓冲二进制日志的最后一部分。
  • 减去其他配置的MySQL缓冲和缓存需要的内存
  • 除以105%，这差不多接近InnoDB管理缓存池增加的自身管理开销（？？？）
  • 四舍五入，向下取整

一个192GB内存的例子

建议：当配置缓存区时，宁可小一点，也不大一点。小了只是影响效率，而大了可能会导致内存交换、磁盘抖动、内存耗尽和硬件死机

open_files_limit

8.3.1 检查MySQL服务器状态变量

8.4 配置内存的使用

8.4.1 MySQL可以使用多少内存

系统的glibc库也可能限制每次分配的内存大小

8.4.2 每个连接需要的内存

MySQL保持一个连接只需要少量的线程

知道高峰期MySQL消耗多少内存是非常有用的

8.4.3 为操作系统保留内存

8.4.4 为缓存分配内存

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

8.4.5 InnoDB缓冲池 （Buffer Pool）

InnoDB缓冲池不仅仅缓存索引，它还会缓存行的数据、自适应哈希索引、插入缓存（Insert Buffer）、锁，以及其他内部数据结构。

InnoDB还是用缓冲池来帮助延迟写入，这样就能合并多个写入操作，然后一起顺序写回

预热和关闭大的缓冲池会花费很长的时间，例如大量脏页在缓冲池里，关闭时可能会花费较长时间，也可以强制快速关闭，但是重启时就必须做更多的恢复工作

innodb_max_dirty_pages_pct innodb_buffer_pool_pages_dirty

8.4.6 MyISAM 键缓存

8.4.7 线程缓存

线程缓存保存一个未使用的连接，避免重复创建

thread_cache_size thread_connected

thread_connected和thread_cacahe_size的一个参考数量：

  • 100~120：20
  • 500~700：200

8.4.8 表缓存

缓存相关表.frm文件的解析结果，加上一些其他数据（依赖于存储引擎）

table_open_cache和table_definition_cache

8.4.9 InnoDB数据字典（data dictionary）

当InnoDB打开一张表，就增加了一个对应的对象到数据字典，每张表可能占用4KB或者更多，当表关闭时也不会从数据字典中移除它们

第一次打开表的时候会计算统计信息，这需要很多IO操作

innodb_use_sys_stats_table选项来持久化存储统计信息到磁盘

innodb_stats_on_metadata 选项避免耗时的表统计信息刷新

如果设置了innodb_file_per_table选项，innodb任意时刻可以保持打开.ibd文件的数量也有限制

innodb为每个.ibd文件使用单个、全局的文件描述符。所以最好把innodb_open_files的值设置得足够大

8.5 配置MySQL的IO行为

8.5.1 InnoDB IO配置

innodb io

InnoDB事务日志

InnoDB把日志随机IO变成顺序IO，一旦日志安全的写到磁盘，事务就持久化了，即使变更还没写到数据文件，InnoDB可以重放日志并且恢复已经提交的事务。

当然InnoDB最后还是必须把变更写到数据文件，因为日志有固定的大小。（环形日志）

随机写改顺序写

整体日志文件大小受控于innodb_log_file_size和innodb_log_files_in_group。 通常不需要修改默认的日志数量，只修改每个日志文件的大小即可。 要修改日志文件大小，需要完全关闭MySQL

日志大小对恢复的影响

InnoDB变更任何数据时，会写一条变更记录到内存日志缓冲区。在缓冲区满的时候、事务提交的时候、每一秒钟这三个条件任意一个满足，InnoDB都会刷写缓冲区的内容到磁盘日志文件。

如果有大事务，增加日志缓冲区大小（默认1MB）可帮助减少IO

变量innodb_log_buffer_size可以控制日志缓冲区的大小，推荐范围是1MB~8MB，除非需要写很大的BLOB记录

show innodb status 监控innodb的日志和日志缓冲区的IO性能 ，通过观察Innodb_os_log_written状态变量来查看innodb对日志文件写出了多少数据。

InnoDB怎样刷新日志缓存?当InnoDB把日志缓冲刷新到磁盘日志文件时，先会使用一个mutex锁住缓冲区，刷新到所需的位置，然后移动剩下的条目到缓冲区的前面。当mutex释放时，可能有超过一个事务已经准备好刷新其日志。

InnoDB有一个Group Commit功能，可以在一个IO操作内提交多个事务

日志缓冲必须被刷新到持久化存储，以确保提交的事务完全被持久化了。

innodb_flush_log_at_trx_commit:

  • 0：每秒一次
  • 1：将日志缓冲写到日志文件，并且每次事务提交都刷新到持久化存储（确保不丢失事务）
  • 2： 每次提交时把日志缓冲写到日志文件，但是并不刷新。InnoDB每秒做一次刷新。如果MySQL进程挂了2不会丢失任何事务。如果服务器断电了，则还是可能丢失一些事务。（在操作系统缓冲中未写磁盘）

高性能事务能处理的最佳配置是把innodb_flush_log_at_trx_commit设置为1 ，并且把日志文件放到一个有电池保护的写缓存的RAID卷中。

InnoDB怎样打开和刷新日志以及数据文件 innodb_flush_method，linux下默认值为fdatasync。这个选项既影响日志文件，也影响数据文件。

innodb通常用fsync()替代fdatasync()。fdatasync()只刷新文件的数据，而不包括元数据（最后修改时间，等等）。因此fsync()会导致更多的IO。

innodb_file_per_table选项会导致每个文件独立地做fsync()，这意味着写多个表不能合并到一个IO操作，这可能导致InnoDB执行更多的fsync()操作。

InnoDB表空间 innodb把数据保存在表空间内，本质上是一个由一个或多个磁盘文件组成的虚拟文件系统。innodb使用表空间实现很多功能，并不只是存储表和索引。它还保存了回滚日志（旧版本行），insert buffer、doublewrite buffer、其他内部数据结构。

通过innodb_data_file_path配置项可以定制表空间文件，这些文件都放在innodb_data_home_dir指定的目录下。一个例子：

1
2

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G

为了允许表空间在超过分配的空间时还能增长，可以像这样配置最后一个文件自动扩展：

1

...ibdata3:1G:autoextend:max:2G

默认行为是创建单个10MB的自动扩展文件，限制自动扩展文件最多到2GB

建议关闭自动扩展功能，因为回收空间麻烦。最佳方式是关机，并导出数据，然后修改配置，导入数据。

innodb_file_per_table选项让innodb为每张表使用一个文件，它在数据字典存储为“表名.ibd”的数据。这使得删除一张表时回收空间简单多了，并可以容易地分散到不同的磁盘上。然而把数据放到多个文件，总体说可能导致更多的空间浪费，因为单个innodb表空间的内部碎片浪费分布到了多个.ibd文件。

这个选项的优点：文件的大小就是表的大小，方便查看；缺点：更差的drop table性能

drop table慢的原因：

  • 删除表需要从文件系统层去掉文件，这对文件系统来说可能会很慢
  • 当打开这个选项，每张表都在innodb中使用自己的表空间。结果是，移除表空间实际上需要innodb锁定和扫描缓存池，查找属于这个表空间的页面。在一个有着庞大的缓冲池的服务器上做这个操作是很慢的

innodb_lazy_drop_table

最终建议：使用innodb_file_per_table并且给共享表空间设置大小范围

行的旧版本和表空间： innodb把旧版本存在共享表空间，如果开启可重复读事务隔离级别，表空间可能会持续增长。清理线程处理速度可能跟不上旧版本行数增加的速度。（？）

show innodb status;

计算潜在的没有被清理的事务

控制写入速度，可以设置innodb_max_purge_lag 变量为一个大于0的值，这个值表示innodb开始延迟后面的语句更新数据之前，可以等待被清除的最大事务数量。

牢记，没有清理的行版本会对所有的查询产生影响，因为它们事实上使得表和索引更大了

doublewrite buffer innodb用双写缓冲来避免页没写完整所导致的数据损坏。当一个磁盘写操作不能完整地完成时，不完整的页写入就可能发生，16KB的页可能只有一部分被写到磁盘上。

双写缓冲是表空间一个特殊保留区域，在一些连续的块中足够保存100个页。本质上是一个最近写回的页面的备份拷贝。当InnoDB从缓冲池刷新页面到磁盘时，首先把它们写到双写缓冲区，然后再把它门写到其所属的数据区域中。这可以保证每个页面的写入都是原子并且持久化的。

这意味着每个页都要写两遍，但因为innodb写页面到双缓冲区是顺序的，并且只调用一次fsync()刷新到磁盘，所以对性能的冲击是比较小的。

如果有一个不完整的页写到了双写缓冲，那么恢复的时候，将使用磁盘中的页替换双写缓冲区中的损坏页；如果写到了双写缓冲区，但写磁盘时失败，那么恢复时将把双写缓冲区中的页替换磁盘中的。检查损坏的方法是页的校验值

innodb_doublewrite

其他IO配置项

sync_binlog选项控制MySQL怎么刷新二进制日志到磁盘。默认值是0，意味着MySQL并不刷新，它由操作系统决定什么时候持久化。这个值如果比0大，则定义了两次刷新到磁盘的动作之间间隔多少次二进制日志写操作。如果设置了autocommit，每个独立的语句都是一次写，否则就是一个事务一次写。把它设置成0和1以外的值很少见。

如果没有设置sync_binlog为1，那么崩溃后可能导致二进制日志没有同步事务数据，这可以轻易导致复制中断，并且使得及时恢复变得不可能

刷新二进制日志缓存其实比InnoDB事务日志要昂贵得多

如果希望使用expire_logs_days选项来自动清理旧的二进制日志，就不要用rm命令去删除。解决方法是手动重新同步“主机名-bin.index”文件

8.5.2 MyISAM的IO配置

8.6 配置MySQL并发

8.6.1 InnoDB并发配置

如果在InnoDB并发方面有问题，解决方案通常是升级服务器

innodb_thread_concurrency 并发值 = CPU数量 x 磁盘数量 x 2

Innodb使用两段处理来尝试让线程尽可能高效地进入内核。两段策略减少了因操作系统调度引起的上下文切换。线程第一次休眠innodb_thread_sleep_delay微妙，然后重试。如果仍然不能进入内核，则放入一个等待线程队列，让操作系统来处理。

第一阶段默认休眠时间是10000微秒。当CPU有大量线程处于“进入队列前的休眠”状态，因而没有被充分利用时，改变这个值在高并发环境里可能会有帮助。如果有大量的小查询，默认值可能也太大了。

一旦线程进入内核，它会有一定数量的ticket，可以让它"免费"返回内核，不需再做并发检查。 innodb_concurrency_tickets选项控制ticket数量。它很少需要修改，除非有很多运行时间极长的查询。票据是按查询授权的，不是按事务。一旦查询完成，它没用完的票据就销毁了。

除了缓冲池和其他结构的瓶颈，还有另一个提交阶段的并发瓶颈，这个时候IO非常密集。 innodb_commit_concurrency变量控制有多少个线程可以在同一时间提交。

8.6.2 MyISAM并发配置

8.7 基于工作负载的配置

配置服务器一个目标是把它定制得符合特定的工作负载。这需要精通所有类型的服务器活动的数量、类型，以及频率。不仅仅是查询语句，包括其他活动。

第一件事是熟悉服务器，了解什么样的查询跑在上面，用例如innotop这样的工具监控它，用pt-query-digest来创建查询报告。

当服务器满载情况下，尝试记录所有的查询语句，查看哪种类型的查询语句占用资源最多。同时创建processlist快照，通过state或者command字段来聚合它们。

8.7.1 优化BLOB 和 TEXT场景

BLOB 和 TEXT对MySQL来说是特殊类型的场景（我们把所有BLOB和TEXT都简单称为BLOB类型，因为它们属于相同类型的数据）

BLOB只能使用磁盘临时表

两种方法解决：

  • 通过substring()函数把值转换为varchar
  • 让临时表更快一些
    • 让mysql放在基于内存的文件系统（tmpfs）

如果blob非常大，并且用的是innodb，也许可以调大innodb日志缓冲大小

对于很长的变长列（例如，blob，text，长字符），innodb存储一个768字节的前缀在行内。如果列的值的比前缀长，InnoDB会在行外分配扩展存储空间来存储剩下的部分。它会分配一个完整的16KB的页。InnoDB一次只为一个列分配一个页的扩展存储空间，直到使用了超过32个页以后，就会一次性分配64个页面。

如果总的行长（包括大字段的完整长度）比InnoDB的最大行长限制要短（比8KB小一些），InnoDB将不会分配扩展存储空间，即使大字段（Long Column）的长度超过了前缀长度。

最后，当InnoDB更新存储在扩展存储空间中的大字段时，将不会在原来的位置更新。而是会在扩展存储空间中写一个新值到一个新的位置，并且不会删除旧的值。

后果：

  • 大字段在InnoDB中可能浪费大量空间。（例如有一个只稍微超过了32个页的大小，实际上就需要使用96个页面）
  • 扩展存储禁用自适应哈希（因为自适应哈希是完全的内存结构）
  • 太长的值可能使得在查询中作为where条件不能使用索引，因而执行很慢
  • 把大字段共享一个扩展空间性能更佳
  • 有时候把大字段用compress()压缩后再存储为blob，性能更加

8.7.2 优化排序（FileSort）

  • max_length_for_sort_data
  • max_sort_length

8.8 完成基本配置

剩下的配置：

  • tmp_table_size和max_heap_table_size
    • 控制使用Memory引擎的内存临时表能使用多大的内存
  • max_connections
  • max_used_connections
  • thread_cache_size
    • threads_connected
    • threads_created
    • slow_launch_threads
  • table_cache_size
    • open_tables

8.9 安全和稳定的设置

  • expire_logs_days
    • 如果启用二进制日志，应该打开这个选项。可以让服务器在指定的天数之后清理旧的二进制日志
  • max_allowed_packet
    • 防止服务器发送太大的包，也会控制多大的包可以被接收
  • max_connect_errors
  • skip_name_resolve
    • 禁用DNS查找
  • sql_mode
  • sysdate_is_now
  • read_only
  • skip_slaves_start
  • slave_net_timeout
  • sync_master_info, sync_relay_log, sync_relay_log_info

8.10 高级InnoDB设置

  • innodb
  • innodb_autoinc_lock_mode
  • innodb_buffer_pool_instances
  • innodb_io_capacity
  • innodb_read_io_threads和innodb_write_io_threads
  • innodb_strict_mode
  • innodb_old_blocks_time