MySQL服务器配置优化

背景

MySQL有大量可以修改的参数-但不应该随便去修改。通常只需要把基本的项配置正确， 应该将更多的时间花在schema的优化、索引，以及查询设计上

MySQL配置的工作原理

• 配置文件位置
  • /etc/my.cnf
  • /etc/mysql/my.cnf
• 配置文件通常分成多个部分，每个部分的开头是一个用方括号括起来的分段名称，MySQL程序通常读取跟它同名的分段部分，许多客户端程序海水读取client部分，这是一个存放公用设置的地方。服务器通常读取mysqld这一段。一定要确认配置项放在了文件正确的分段中，否则配置是不会生效的

语法、作用域和动态性

• 配置项设置都使用小写，单词之间用下划线或横线隔开。下面的例子是等价的，并且可能在命令行和配置文件中都看到这两种格式
  • /usr/sbin/mysqld --auto-increment-offset=5
  • /usr/sbin/mysqld --auto_increment_offset=5
  • 我们建议使用一种固定的风格。
• 配置项可以有多个作用域
  • 服务器级(全局作用域)
  • 每个连接(会话作用域)
  • 对象级
• 设置变量的副作用

创建MySQL配置文件

没有一个适合所有场景的“最佳配置文件”。

• MySQL编译的默认设置并不都是靠谱的，虽然其中大部分都比较合适。它们被设计为不要使用大量的资源。
• 大多数配置的默认值已经是最佳配置了，所以最好不好改动太多配置

InnoDB缓冲池(Buffer Pool)

• 如果大部分都是InnoDB表，InnoDB缓冲池比其他任何东西更需要内存。
• InnoDB缓冲池缓存有：
  • 索引
  • 行的数据
  • 自适应哈希索引
  • 插入缓冲(Insert Buffer)
  • 锁
  • 其他内部数据结构
  • 帮助延迟写入，合并多个写入操作，然后有一起顺序写回
• 很大的缓冲池，当预热或者关闭都会花费很长时间，如果有很多脏页在缓冲池里，因为要在关闭之前把脏页写回数据文件，如果快速关闭，启动时，需要做更多的恢复工作。
• 设置innodb_max_dirty_pages_pct 可以降低阈值，提前触发刷脏页的动作，减少脏页的数量
  • InnoDB默认通过一个后台线程来刷新脏页，并且会合并写入，更高效地顺序写出到磁盘。

总结

如果使用的是InnoDB，最重要的选项是下面两个

• innodb_buffer_pool_size 内存的75%
• innodb_log_file_size