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 的優化其實主要是參數的優化調整。一般情況下，參數優化并不能帶來質的飛躍，除非原來的參數設置非常不合理。為了減少性能問題，我們應該在系統設計和開發階段下功夫。