读高性能MySQL（第4版）笔记05_优化服务器设置

1.除非遇到异常情况，否则不需要调整配置1.1.不要“调优”服务器，不要使用比率、公式或“调优脚本”作为设置配置变量的基础1.1.1.在互联网上搜索配置建议并不总是一个好主意，你会在博客、论坛等找到很多糟糕的建议1.1.2.很难判断谁是真正的专家1.1.3.不要相信流行的内存消耗公式1.2.可靠的、有信誉的MySQL服务提供商通常比简单的互联网搜索结果更安全，因为那些需要拥有满意的客户的人可能正在做正确的事情1.2.1.即使是他们的建议，在没有经过测试和理解的情况下进行应用也可能是危险的，因为它可能针对的是一种与你不同的情况，而你却没有理解1.3.MySQL的不同版本会删除、弃用和更改一些选项，欲了解详细信息请查看相关文档1.4.应该始终通过阅读相关的官方手册来检查任何更改并仔细测试1.5.MySQL有许多可以更改但不应该更改的设置1.5.1.MySQL的默认设置是有充分理由的1.5.2.修改配置的潜在缺点可能是巨大的1.5.3.很多默认设置都是安全的，很多人都会直接使用。这使默认设置成为测试最彻底的设置。当没必要改变这些设置而改变它们时，可能会引起意想不到的错误1.5.4.节省时间和避免麻烦的好方法是使用默认设置，除非你明确知道不应该使用默认设置1.6.更好的做法是正确地配置基本设置（在大多数情况下，只有少数设置是重要的），并将更多的时间花在schema优化、索引和查询设计上1.7.如果问题是由服务器的某个部分引起的，而该部分的行为可以通过配置选项进行纠正，那么可能需要对其进行更改1.8.应该只在发现它们解决的特定性能问题时，才设置它们1.9.如果需要改进配置，应该会在查询响应时间中体现出来1.10.最好从查询及其响应时间开始分析，而不是从配置选项开始1.10.1.节省很多时间，避免很多问题2.专用数据库服务器2.1.可以设置的最佳选项是innodb_dedicated_server2.1.1.可以处理90%的性能配置2.1.2.配置了4个额外的变量（innodb_buffer_pool_size、innodb_log_file_size、innodb_log_files_in_group和innodb_flush_method）2.1.3.通常会占用50%～75%的内存2.1.3.1.MySQL只需要少量的内存就能保持一个连接（通常是一个相关的专用线程）打开2.2.无法使用innodb_dedicated_server2.2.1.innodb_buffer_pool_size2.2.1.1.InnoDB缓冲池大小2.2.1.2.需要的内存比其他任何组件都多2.2.1.3.不仅缓存索引，还缓存行数据、自适应哈希索引、更改缓冲区、锁和其他内部结构等2.2.1.4.InnoDB严重依赖缓冲池，应该确保为其分配足够的内存2.2.1.5.大型缓冲池会带来一些挑战，比如更长的关闭时间和预热时间2.2.1.6.当MySQL再次启动时，缓冲池缓存是空的，也称为冷缓存2.2.1.7.默认情况下，innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup这两个配置可以配合使用，以在启动时预热缓存池2.2.2.innodb_log_file_size2.2.2.1.日志文件大小2.2.3.解决了我们所看到的绝大多数实际配置问题2.3.应该设置一些安全选项2.3.1.通常不会提高性能，只会避免问题3.MySQL的配置3.1.需要永久使用的任何设置都应该写入全局配置文件，而不是在命令行中指定3.2.将所有配置文件保存在一个地方也是一个好主意，这样可以方便地检查它们3.3.一定要知道服务器的配置文件在哪里3.3.1.Debian服务器上默认不存在/etc/my.cnf，而是会在/etc/mysql/my.cnf中查找配置3.4.配置文件采用标准INI格式，被分为多个部分，每个部分都以一行包含在方括号中的该部分名称开头3.5.配置设置全部用小写字母书写，单词之间以下画线或短横线分隔3.5.1.建议选择一种风格并始终如一地使用它3.6.全局作用域3.7.会话作用域3.8.许多会话作用域的变量都有相应的全局变量，可以将相应的全局变量的值视为会话变量的默认值3.9.动态配置变量3.9.1.很多变量（但不是全部）还可以在服务器运行时进行更改3.9.2.如果重新启动MySQL，即使使用了SET GLOBAL来更改全局变量，它也将恢复到配置文件中的状态3.9.3.必须同时管理MySQL的配置文件和运行时配置，并确保它们保持同步3.10.MySQL 8.0引入了一个名为持久化系统变量的新功能3.10.1.新的语法SET PERSIST允许在运行时设置一次值，MySQL将把这个设置写入磁盘，以便在下次重启后继续使用该值3.11.table_open_cache3.11.1.设置此变量不会立即生效：下一次线程打开表时，MySQL会检查变量的值3.11.2.如果该值大于缓存中的表的数目，线程可以将新打开的表插入缓存3.11.3.如果该值小于缓存中的表的数目，MySQL将从缓存中删除未使用的表3.12.thread_cache_size3.12.1.设置此变量不会立即生效：下一次关闭连接时，MySQL会检查缓存中是否有空间来存储线程3.12.2.如果有，则缓存线程以供其他连接将来重用3.12.3.如果没有，则将线程终止而不是缓存它3.12.4.只有当查询需要时，MySQL才会为该缓冲区分配内存，而且会立即分配此变量指定的整块内存3.12.5.每个处于线程缓存或休眠状态的线程通常使用大约256KB内存3.12.6.通常应该保持线程缓存足够大，这样Threads_created就不会经常增加3.13.open_files_limit选项3.13.1.典型的Linux系统中，我们将其设置得尽可能大3.13.2.在现代操作系统中，打开文件句柄的成本很低3.13.3.如果这个设置不够大，就会看到经典的24号错误，“too many openfiles”3.14.设置变量时要小心。并不总是越多越好3.15.理想情况下，应该使用版本控制系统来跟踪配置文件的更改3.16.MySQL并不是一个严格控制内存分配的数据库服务器3.16.1.事实是，你不能给MySQL的内存消耗设定上限4.I/O行为4.1.InnoDB不仅允许你控制其恢复方式，还允许控制其打开和刷新数据的方式，这将极大地影响恢复和总体性能4.2.InnoDB使用日志来降低提交事务的成本4.3.InnoDB假定它使用的是传统的磁盘，随机I/O比顺序I/O的开销要大很多，因为随机I/O需要在磁盘上寻找正确的位置，并等待将所需的磁盘部分旋转到磁头下4.4.InnoDB最终必须将更改的数据写入数据文件4.4.1.日志的大小固定，采取的是循环写入的方式4.4.1.1.当到达日志的末尾时，它会环绕到日志的开头4.4.1.2.如果日志记录中包含的更改尚未应用于数据文件，则无法覆盖日志记录，因为这将删除已提交事务的唯一永久记录4.5.日志文件的总大小由innodb_log_file_size和innodb_log_files_in_group控制，这对写入性能非常重要4.6.当缓冲区满了、事务提交时，或者每秒1次（这三个条件以先满足者为准），InnoDB会将缓冲区刷新到磁盘上的日志文件中4.7.不需要将缓冲区设置得太大4.7.1.建议的范围是1～8M B4.8.innodb_flush_log_at_trx_commit4.8.1.14.8.1.1.每次事务提交时，将日志缓冲区写入日志文件，并将其刷新到持久存储中4.8.1.2.默认的（也是最安全的）设置4.8.1.3.保证你不会丢失任何已提交的事务，除非磁盘或操作系统“假装”进行刷新操作（没有将数据真正写入磁盘）4.8.1.3.1.如果驱动器断电，数据仍可能丢失4.8.2.04.8.2.1.每秒定时将日志缓冲区写入日志文件，并刷新日志文件，但在事务提交时不做任何操作4.8.3.24.8.3.1.与0设置最重要的区别是，如果只是MySQL进程崩溃，设置为2不会丢失任何事务。但是，如果整个服务器崩溃或断电，仍然可能丢失事务4.8.4.设置为0和2通常会导致最多1秒的数据丢失，因为数据可能只存在于操作系统的缓存中4.9.高性能事务需求的最佳配置是将innodb_flush_log_at_trx_commit设置为1，并将日志文件放在具有备用电池的写缓存和SSD的RAID卷上，这既安全又非常快4.10.最好为日志文件和数据文件分别提供一个配置选项，但目前是组合在一起的4.11.如果你使用的是类UNIX操作系统，并且RAID控制器有备用电池的写缓存，我们建议使用O_DIRECT4.12.如果不是，则default或O_DIRECT都可能是最佳选择，具体取决于应用程序5.InnoDB表空间5.1.表空间本质上是一个虚拟文件系统，由磁盘上的一个或多个文件组成5.2.包含了Undo日志（重新创建旧行版本所需的信息）、修改缓冲区、双写缓冲区和其他内部结构5.3.对日志文件也非常严格5.4.innodb_file_per_table5.4.1.提供了额外的可管理性和可视性5.4.2.通过检查单个文件来查找表的大小要比使用SHOWTABLE STATUS快得多5.4.3.SHOW TABLE STATUS必须执行更复杂的工作来确定为一个表分配了空间5.4.4.会使DROPTABLE性能变差。严重时可能导致服务器范围内明显的停顿5.4.4.1.先将.ibd文件链接到一个大小为零的文件，然后手动删除该文件，而不是等待MySQL来删除5.4.4.2.从8.0.23版本开始，这应该不再是一个问题了5.5.表空间在写操作频繁的环境中可能会变得非常大5.5.1.要限制写操作，请将innodb_max_purge_lag变量设置为0以外的值5.5.1.1.表示在InnoDB开始延迟更多修改数据的查询之前，可以等待清除的最大事务数5.5.1.2.设置innodb_max_purge_lag变量也会降低性能5.5.2.未清除的行版本会影响所有查询，因为它们会使表和索引变大5.5.3.清除线程不能跟上进度，性能就会下降5.5.4.默认的可重复读取事务隔离级别，InnoDB将无法删除行的旧版本，因为未提交的事务仍需要能够看到它们5.5.5.清除过程是多线程的，但如果遇到清除延迟问题（innodb_purge_threads和innodb_purge_batch_size），则可能需要针对工作负载进行调优5.5.6.如果Undo日志很大，并且表空间因此而增长，你可以强制MySQL放慢速度来让InnoDB的清理线程跟上5.5.6.1.InnoDB会不断地写入数据并填充磁盘，直到磁盘空间耗尽或者表空间达到所定义的上限5.6.sync_binlog选项控制MySQL如何将二进制日志刷新到磁盘，默认值是15.6.1.MySQL将执行刷新并保持二进制日志的持久性和安全性5.6.2.不建议设置为任何其他值6.MySQL并发6.1.如果遇到InnoDB并发问题，并且运行的MySQL版本低于5.7，解决方案通常是升级服务器6.2.如果你发现自己遇到了并发性瓶颈，最好的选择是对数据进行分片6.2.1.如果分片不可行，那么可能需要限制并发性6.3.限制并发性的最基本方法是使用innodb_thread_concurrency变量，该变量限制了内核中同时可以有多少线程6.3.1.首先将innodb_thread_concurrency设置为与可用CPU核数相同的值，然后根据需要调整大小7.安全设置7.1.安全性和可靠性的保障成本往往更高7.2.max_connect_errors7.2.1.如果网络暂时出现问题、出现应用程序或配置错误，或者存在另一个问题导致连接无法在短时间内成功完成，则客户端可能会被阻止连接，并且在刷新主机缓存之前无法再次连接7.2.2.默认设置（100）非常小，因此该问题很容易发生7.2.3.如果启用了skip_name_resolve，则max_connect_errors选项将无效，因为其行为取决于主机缓存，而主机缓存被skip_name_resolve禁用7.3.skip_name_resolve7.3.1.DNS是MySQL连接过程中的一个薄弱环节7.3.2.某个时间点出现DNS故障的概率几乎是确定性的7.4.max_connections7.4.1.设置得足够高，以满足你认为将要经历的正常负载的连接需求，并且额外保留一些连接以便管理服务器时可以登录7.4.2.默认值为151，这对于很多应用程序来说都不够7.4.3.如果没有使用持久连接，但是应用程序没有正常断开连接，也会出现服务器连接占满的情况7.4.4.可以显示服务器是否在某个时刻出现了连接高峰。如果到达max_connections，则客户端可能至少被拒绝了一次7.5.sql_mode7.5.1.最好让MySQL在大多数方面保持原样，而不要试图让它像其他数据库服务器那样运行7.5.2.在计划升级数据库时，一定要检查对默认sql_mode的更改7.6.sysdate_is_now7.6.1.如果你不是明确地希望SYSDATE()函数具有不确定性行为（这会破坏复制并使从备份中进行的时间点恢复变得不可靠），那么可以启用该选项并使其行为具有确定性7.7.read_only7.7.1.可防止未经授权的用户对副本进行更改，副本应仅通过复制而不是从应用程序接收更改7.7.2.建议将副本设置为只读模式7.8.super_read_only7.8.1.可阻止拥有SUPER权限的用户写入数据7.8.2.启用此功能后，唯一可以将更改写入数据库的就是复制7.8.3.建议启用super_read_only7.8.4.防止你意外地使用管理员账户将数据写入只读副本，从而引起数据不同步8.高级InnoDB设置8.1.innodb_autoinc_lock_mode8.1.1.控制InnoDB如何生成自动递增的主键值8.2.innodb_buffer_pool_instances8.2.1.将缓冲池划分为多个段，这可能是提高多核机器上MySQL在高并发工作负载下可伸缩性最重要的方法之一8.2.2.多个缓冲池对工作负载进行分区，这样一些全局互斥体就不会成为争用热点8.3.innodb_io_capacity8.3.1.告知InnoDB有多少I/O容量可供其使用8.4.innodb_read_io_threads8.5.innodb_write_io_threads8.6.如果你有很多硬盘和高并发工作负载，并且发现线程很难跟上，那么可以增加线程的数量，或者简单地将它们设置为执行I/O操作的物理磁盘数量8.7.innodb_strict_mode8.7.1.InnoDB在某些情况下抛出错误而不是警告，尤其是无效或可能导致危险的CREATE TABLE选项8.8.innodb_old_blocks_time8.8.1.一个由两部分组成的缓冲池LRU列表，设计目的是防止临时查询将长期多次使用的页面驱逐出去8.8.2.默认情况下，它被设置为08.8.3.将其设置为一个较小的值如1000（1秒），这在基准测试中被证明是非常有效的