读高性能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_server
2.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_server
2.2.1. innodb_buffer_pool_size
2.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_size
2.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_cache
3.11.1. 设置此变量不会立即生效：下一次线程打开表时，MySQL会检查变量的值
3.11.2. 如果该值大于缓存中的表的数目，线程可以将新打开的表插入缓存
3.11.3. 如果该值小于缓存中的表的数目，MySQL将从缓存中删除未使用的表
3.12. thread_cache_size
3.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 B
4.8. innodb_flush_log_at_trx_commit
4.8.1. 1
4.8.1.1. 每次事务提交时，将日志缓冲区写入日志文件，并将其刷新到持久存储中
4.8.1.2. 默认的（也是最安全的）设置
4.8.1.3. 保证你不会丢失任何已提交的事务，除非磁盘或操作系统“假装”进行刷新操作（没有将数据真正写入磁盘）
4.8.1.3.1. 如果驱动器断电，数据仍可能丢失
4.8.2. 0
4.8.2.1. 每秒定时将日志缓冲区写入日志文件，并刷新日志文件，但在事务提交时不做任何操作
4.8.3. 2
4.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_DIRECT
4.12. 如果不是，则default或O_DIRECT都可能是最佳选择，具体取决于应用程序
5. InnoDB表空间
5.1. 表空间本质上是一个虚拟文件系统，由磁盘上的一个或多个文件组成
5.2. 包含了Undo日志（重新创建旧行版本所需的信息）、修改缓冲区、双写缓冲区和其他内部结构
5.3. 对日志文件也非常严格
5.4. innodb_file_per_table
5.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如何将二进制日志刷新到磁盘，默认值是1
5.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_errors
7.2.1. 如果网络暂时出现问题、出现应用程序或配置错误，或者存在另一个问题导致连接无法在短时间内成功完成，则客户端可能会被阻止连接，并且在刷新主机缓存之前无法再次连接
7.2.2. 默认设置（100）非常小，因此该问题很容易发生
7.2.3. 如果启用了skip_name_resolve，则max_connect_errors选项将无效，因为其行为取决于主机缓存，而主机缓存被skip_name_resolve禁用
7.3. skip_name_resolve
7.3.1. DNS是MySQL连接过程中的一个薄弱环节
7.3.2. 某个时间点出现DNS故障的概率几乎是确定性的
7.4. max_connections
7.4.1. 设置得足够高，以满足你认为将要经历的正常负载的连接需求，并且额外保留一些连接以便管理服务器时可以登录
7.4.2. 默认值为151，这对于很多应用程序来说都不够
7.4.3. 如果没有使用持久连接，但是应用程序没有正常断开连接，也会出现服务器连接占满的情况
7.4.4. 可以显示服务器是否在某个时刻出现了连接高峰。如果到达max_connections，则客户端可能至少被拒绝了一次
7.5. sql_mode
7.5.1. 最好让MySQL在大多数方面保持原样，而不要试图让它像其他数据库服务器那样运行
7.5.2. 在计划升级数据库时，一定要检查对默认sql_mode的更改
7.6. sysdate_is_now
7.6.1. 如果你不是明确地希望SYSDATE()函数具有不确定性行为（这会破坏复制并使从备份中进行的时间点恢复变得不可靠），那么可以启用该选项并使其行为具有确定性
7.7. read_only
7.7.1. 可防止未经授权的用户对副本进行更改，副本应仅通过复制而不是从应用程序接收更改
7.7.2. 建议将副本设置为只读模式
7.8. super_read_only
7.8.1. 可阻止拥有SUPER权限的用户写入数据
7.8.2. 启用此功能后，唯一可以将更改写入数据库的就是复制
7.8.3. 建议启用super_read_only
7.8.4. 防止你意外地使用管理员账户将数据写入只读副本，从而引起数据不同步
8. 高级InnoDB设置
8.1. innodb_autoinc_lock_mode
8.1.1. 控制InnoDB如何生成自动递增的主键值
8.2. innodb_buffer_pool_instances
8.2.1. 将缓冲池划分为多个段，这可能是提高多核机器上MySQL在高并发工作负载下可伸缩性最重要的方法之一
8.2.2. 多个缓冲池对工作负载进行分区，这样一些全局互斥体就不会成为争用热点
8.3. innodb_io_capacity
8.3.1. 告知InnoDB有多少I/O容量可供其使用
8.4. innodb_read_io_threads
8.5. innodb_write_io_threads
8.6. 如果你有很多硬盘和高并发工作负载，并且发现线程很难跟上，那么可以增加线程的数量，或者简单地将它们设置为执行I/O操作的物理磁盘数量
8.7. innodb_strict_mode
8.7.1. InnoDB在某些情况下抛出错误而不是警告，尤其是无效或可能导致危险的CREATE TABLE选项
8.8. innodb_old_blocks_time
8.8.1. 一个由两部分组成的缓冲池LRU列表，设计目的是防止临时查询将长期多次使用的页面驱逐出去
8.8.2. 默认情况下，它被设置为0
8.8.3. 将其设置为一个较小的值如1000（1秒），这在基准测试中被证明是非常有效的
来源:https://www.cnblogs.com/lying7/p/17692156.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]