《高性能MySQL》（七）优化服务器设置

一. 引文

“我的服务器有32GB内存，12核CPU，怎样配置会最好？”很可惜，优化配置并非只看硬件情况，需要符合工作负载、数据以及应用需求等。

MySQL有很多可修改的参数，大部分都不需要修改默认值，精力应该更多放在优化schema、索引以及查询设计上。确保基本的配置，如InnoDB的Buffer Pool和日志文件缓存大小等设置为安全值即可。

最好从查询语句和响应时间来分析问题，而不是由配置项入手，大部分人都是使用默认配置，也间接证明了默认值经过了大量的测试。

二. MySQL配置原理

2.1 配置项

MySQL获取配置信息的方式：

• 命令行参数：启动时输入。
• 配置文件：默认路径为 /etc/my.cnf 或 /etc/mysql/my.cnf 。

查看当前使用的配置文件路径：

$ which mysqld
/usr/sbin/mysqld
$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order: /etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf

配置项可以由多个作用域，有些是服务器级（全局作用域），有些是连接级的（会话作用域），剩下则是对象级的。改变会话级变量，只影响当前连接，并且关闭后会失效。

• query_cache_size变量为全局。
• sort_buffer_size变量默认为全局相同，但每个线程也可以设置。
• join_buffer_size变量也有全局默认且每个线程可以设置，但若一个查询中关联多张表，可以为每个关联分配一个关联缓冲（join buffer），所以每个查询可能有多个关联缓冲。

2.2 动态设置变量

动态设置变量：

SET           sort_buffer_size = <value>;
SET GLOBAL    sort_buffer_size = <value>;
SET         @@sort_buffer_size = <value>;
SET @@session.sort_buffer_size = <value>;
SET  @@global.sort_buffer_size = <value>;
// 检查配置
SHOW GLOBAL VARIABLES

动态设置变量可能会导致意外的副作用，如从缓冲中刷新脏块，务必小心可以在线修改的设置，可能会导致数据库执行大量的工作。

• key_buffer_size：一次性为键缓冲区（key buffer / key cache）分配所有指定的空间。但操作系统不会真的立刻分配内存，而是使用时才分配。
  • MySQL允许创建多个键缓存，若把非默认键缓存变量设置为0，MySQL会丢弃缓存在其上的索引转而使用默认键缓存，并在没有引用时删除该缓存。
  • 为不存在的键设置变量，会创建新缓存。
  • 对一个已存在键缓存设置非零值，会刷新其内容，但会阻塞所有操作直到刷新完毕。
• table_cache_size：不会立即生效，在线程打开表时检查此值，若大于缓存中表的数量，线程可以把新打开的表放入缓存，若小于则将缓存中不常用的删除。
• thread_cache_size：不会立即生效，在下次有连接关闭时产生效果，检查缓存中是否还有空间来缓存线程，如果有则缓存改线程供后面重用；如果没有则销毁该线程。但缓存中线程数和使用的内存不会立即减少，只有在新连接删除缓存的一个现场并使用后才减少。
  • MySQL只在关闭连接时才在缓存中增加线程，只在创建新连接时才从缓存中删除线程。
• query_cache_size：MySQL在启动时一次性分配并且初始化这块内存。修改此变量会立即删除所有缓存的查询，重新分配这片缓存到指定大小，并且重新初始化内存。因为要逐个清理缓存的查询，可能会花费较长时间，在完成初始化前都无法提供服务。
• read_buffer_size：只有在查询需要使用时才会为该缓存分配内存，并且一次性分配该参数指定大小的全部内存。
• read_rnd_buffer_size：只会在有查询需要使用时才会为该缓存分配内存。只分配需要的内存大小、
• sort_buffer_size：只会在有查询需要做排序操作时为该缓存分配内存，一旦排序立即分配指定大小的全部内存。
• ……

对于一次性分配指定大小内存的配置项，很容易导致内存浪费，所以尽量不要使用，使用也尽量在查询需要时在连接级别单独调大。内存分配是一个极其昂贵的操作，包括了地址空间的分配。如果查询需要使用一个较大的排序缓存才能很好的执行，应该在查询前增加sort_buffer_size的值，执行后恢复为DEFAULT。

---

参考：

🔗 《高性能MySQL》