MySQL Server 优化

上一小节我们介绍了 SQL 语句的优化思路，接下来我们继续从实战角度，从安装、日志、内存、并发四个方面学习 MySQL Server 的优化方法。

1. 安装配置优化

1.1 版本选择

一般推荐选择二进制发行版，原因有如下好处：

• 安装方式简单，可以快速完成部署；
• 经过MySQL官方的测试、验证和编译，稳定性较好。

1.2 关闭 numa

--以CentOS 6为例，在kernel一行后面添加numa=off
vi /boot/grub/grub.conf

1.3 limits.conf配置

vi /etc/security/limits.conf
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535

1.4 关闭大页内存

echo "never" > /sys/kernel/mm/transparent_hugepage/enabled
echo "never" > /sys/kernel/mm/transparent_hugepage/defrag

2. 日志配置优化

2.1 innodb_flush_log_at_trx_commit和sync_binlog

innodb_flush_log_at_trx_commit和sync_binlog这两个参数是控制MySQL磁盘写入策略以及数据安全性的关键参数。

innodb_flush_log_at_trx_commit：

• 0：参数值为0时，由mysql的main_thread每秒将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。
• 1：参数值为1时，每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。
• 2：参数值为2时，每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并由存储引擎的main_thread 每秒将日志刷新到磁盘。

sync_binlog：

• 0：参数值为0时，存储引擎不进行binlog的刷新到磁盘，而由操作系统的文件系统控制缓存刷新。
• 1：参数值为1时，每提交一次事务，存储引擎调用文件系统的sync操作进行一次缓存的刷新，这种方式最安全，但性能较低。
• n：当提交的日志组=n时，存储引擎调用文件系统的sync操作进行一次缓存的刷新。

innodb_flush_log_at_trx_commit和sync_binlog都为 1 时是最安全的，在 MySQL 服务崩溃或服务器crash 的情况下，binary log 只有可能丢失最多一个语句或者一个事务，但双 1 模式也是最慢的，会导致频繁的 IO 操作。

实际使用时，需要综合考量这两个参数，可以针对不同的业务场景进行压力测试，找到平衡点。

2.2 innodb_log_buffer_size

innodb_log_buffer_size 是 InnoDB 重做日志的缓存池大小，默认是 8MB。如果有大量更新操作，可以适当增加其大小，避免过多的磁盘操作。

3. 内存优化

3.1 innodb_buffer_pool_size 的设置

innodb_buffer_pool_size 是用来缓存 InnoDB 的数据和索引的内存空间。在专用的数据库服务器上，一般分配 75% 的内存给到 InnoDB 的缓存池。

innodb buffer pool 的使用情况可以通过如下命令查看：

mysql> show status like '%innodb_buffer_pool%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_resize_status      |           |
| Innodb_buffer_pool_pages_data         | 5123      |
| Innodb_buffer_pool_bytes_data         | 83935232  |
| Innodb_buffer_pool_pages_dirty        | 0         |
| Innodb_buffer_pool_bytes_dirty        | 0         |
| Innodb_buffer_pool_pages_flushed      | 284       |
| Innodb_buffer_pool_pages_free         | 125933    |
| Innodb_buffer_pool_pages_misc         | 0         |
| Innodb_buffer_pool_pages_total        | 131056    |
| Innodb_buffer_pool_read_ahead_rnd     | 0         |
| Innodb_buffer_pool_read_ahead         | 0         |
| Innodb_buffer_pool_read_ahead_evicted | 0         |
| Innodb_buffer_pool_read_requests      | 10187     |
| Innodb_buffer_pool_reads              | 5056      |
| Innodb_buffer_pool_wait_free          | 0         |
| Innodb_buffer_pool_write_requests     | 2575      |
+---------------------------------------+-----------+
18 rows in set (0.03 sec)

可以计算出缓存池的命中率为：

Innodb_buffer_pool_read_hits = (( 1 - Innodb_buffer_pool_reads ) / Innodb_buffer_pool_read_requests ) * 100%

如果命中率太低，这时就需要考虑增加 innodb_buffer_pool_sized 的值了。

4.并发优化

4.1 max_connections

max_connections 表示连接到 MySQL 的最大会话数量。一般业务系统，设置成 500-1000 足够使用。

可以通过如下语句调整 max_connections 的大小：

• 临时：在 MySQL 中直接用命令行执行：

  mysql> show variables like 'max_connections';
  +--------------------------+-------+
  | Variable_name            | Value |
  +--------------------------+-------+
  | max_connections          | 500   |
  +--------------------------+-------+
  1 row in set (0.00 sec)
  
  mysql> set global max_connections=1000;
  Query OK, 0 rows affected (0.00 sec)
  

• 永久：将以下参数添加至配置文件 my.cnf，并重启 MySQL：

  max_connections=1000
  

4.2 thread_cache_size

thread_cache_size 表示缓存一定数量的线程以备重用，可以加快连接 MySQL 的速度。

那么如何判断 thread_cache_size 的设置是否合理呢？通过如下公式计算 thread cache 的命中率，一般命中率高于 90% 才是合理的。

thread_cache_hits =  (( 1 - Thread_created ) / connections ) * 100%

5. 小结

本小节主要介绍了 MySQL Server 优化的四种方法：

1. 安装配置优化；
2. 日志配置优化；
3. 内存优化；
4. 并发优化。

MySQL Server 的优化其实主要是参数的优化调整。一般情况下，参数优化并不能带来质的飞跃，除非原来的参数设置非常不合理。为了减少性能问题，我们应该在系统设计和开发阶段下功夫。