MySQL Server 優(yōu)化

上一小節(jié)我們介紹了 SQL 語句的優(yōu)化思路，接下來我們繼續(xù)從實戰(zhàn)角度，從安裝、日志、內(nèi)存、并發(fā)四個方面學(xué)習(xí) MySQL Server 的優(yōu)化方法。

1. 安裝配置優(yōu)化

1.1 版本選擇

一般推薦選擇二進(jìn)制發(fā)行版，原因有如下好處：

• 安裝方式簡單，可以快速完成部署；
• 經(jīng)過MySQL官方的測試、驗證和編譯，穩(wěn)定性較好。

1.2 關(guān)閉 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 關(guān)閉大頁內(nèi)存

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

2. 日志配置優(yōu)化

2.1 innodb_flush_log_at_trx_commit和sync_binlog

innodb_flush_log_at_trx_commit和sync_binlog這兩個參數(shù)是控制MySQL磁盤寫入策略以及數(shù)據(jù)安全性的關(guān)鍵參數(shù)。

innodb_flush_log_at_trx_commit：

• 0：參數(shù)值為0時，由mysql的main_thread每秒將存儲引擎log buffer中的redo日志寫入到log file，并調(diào)用文件系統(tǒng)的sync操作，將日志刷新到磁盤。
• 1：參數(shù)值為1時，每次事務(wù)提交時，將存儲引擎log buffer中的redo日志寫入到log file，并調(diào)用文件系統(tǒng)的sync操作，將日志刷新到磁盤。
• 2：參數(shù)值為2時，每次事務(wù)提交時，將存儲引擎log buffer中的redo日志寫入到log file，并由存儲引擎的main_thread 每秒將日志刷新到磁盤。

sync_binlog：

• 0：參數(shù)值為0時，存儲引擎不進(jìn)行binlog的刷新到磁盤，而由操作系統(tǒng)的文件系統(tǒng)控制緩存刷新。
• 1：參數(shù)值為1時，每提交一次事務(wù)，存儲引擎調(diào)用文件系統(tǒng)的sync操作進(jìn)行一次緩存的刷新，這種方式最安全，但性能較低。
• n：當(dāng)提交的日志組=n時，存儲引擎調(diào)用文件系統(tǒng)的sync操作進(jìn)行一次緩存的刷新。

innodb_flush_log_at_trx_commit和sync_binlog都為 1 時是最安全的，在 MySQL 服務(wù)崩潰或服務(wù)器crash 的情況下，binary log 只有可能丟失最多一個語句或者一個事務(wù)，但雙 1 模式也是最慢的，會導(dǎo)致頻繁的 IO 操作。

實際使用時，需要綜合考量這兩個參數(shù)，可以針對不同的業(yè)務(wù)場景進(jìn)行壓力測試，找到平衡點。

2.2 innodb_log_buffer_size

innodb_log_buffer_size 是 InnoDB 重做日志的緩存池大小，默認(rèn)是 8MB。如果有大量更新操作，可以適當(dāng)增加其大小，避免過多的磁盤操作。

3. 內(nèi)存優(yōu)化

3.1 innodb_buffer_pool_size 的設(shè)置

innodb_buffer_pool_size 是用來緩存 InnoDB 的數(shù)據(jù)和索引的內(nèi)存空間。在專用的數(shù)據(jù)庫服務(wù)器上，一般分配 75% 的內(nèi)存給到 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.并發(fā)優(yōu)化

4.1 max_connections

max_connections 表示連接到 MySQL 的最大會話數(shù)量。一般業(yè)務(wù)系統(tǒng)，設(shè)置成 500-1000 足夠使用。

可以通過如下語句調(diào)整 max_connections 的大小：

• 臨時：在 MySQL 中直接用命令行執(zhí)行：

  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)
  

• 永久：將以下參數(shù)添加至配置文件 my.cnf，并重啟 MySQL：

  max_connections=1000
  

4.2 thread_cache_size

thread_cache_size 表示緩存一定數(shù)量的線程以備重用，可以加快連接 MySQL 的速度。

那么如何判斷 thread_cache_size 的設(shè)置是否合理呢？通過如下公式計算 thread cache 的命中率，一般命中率高于 90% 才是合理的。

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

5. 小結(jié)

本小節(jié)主要介紹了 MySQL Server 優(yōu)化的四種方法：

1. 安裝配置優(yōu)化；
2. 日志配置優(yōu)化；
3. 內(nèi)存優(yōu)化；
4. 并發(fā)優(yōu)化。

MySQL Server 的優(yōu)化其實主要是參數(shù)的優(yōu)化調(diào)整。一般情況下，參數(shù)優(yōu)化并不能帶來質(zhì)的飛躍，除非原來的參數(shù)設(shè)置非常不合理。為了減少性能問題，我們應(yīng)該在系統(tǒng)設(shè)計和開發(fā)階段下功夫。