15 個有用的 MySQL/MariaDB 效能調整和最佳化技巧（轉載的一篇好文）

標籤：

MySQL 是一個強大的開源關聯式資料庫管理系統（簡稱 RDBMS）。它發佈於 1995 年（20年前）。它採用結構化查詢語言 (SQL)（SQL），這可能是資料庫內容管理中最流行的選擇。最新的 MySQL 版本是 5.6.25，於 2015 年 5 月 29 日發布。

關於 MySQL 一個有趣的事實是它的名字來自於 Michael Widenius（MySQL 的創始人）的女兒“ My”。儘管有許多關於 MySQL 有趣的傳聞，不過本文主要是向你展示一些有用的實踐，以協助你管理你的 MySQL 伺服器。

2009 年 4 月，MySQL 被 Oracle 收購。其結果是MySQL 社區分裂，建立了一個叫 MariaDB 的分支 。建立該分支的主要原因是為了保持這個項目可以在 GPL 下的自由。

今天，MySQL 和 MariaDB 是用於類似 WordPress、Joomla、Magento 和其他 web 應用程式的最流行的 RDMS 之一（如果不是最多的）。

這篇文章將告訴你一些基本的，但非常有用的關於如何最佳化 MySQL/MariaDB 效能的技巧。注意，本文假定您已經安裝了 MySQL 或 MariaDB。如果你仍然不知道如何在系統上安裝它們，你可以按照以下說明去安裝：

在 RHEL/CentOS 7 上安裝 LAMP

在 Fedora 22 上安裝 LAMP

在 Ubuntu 15.04 安裝 LAMP

在 Debian 8 上安裝 MariaDB

在 Gentoo Linux 上安裝 MariaDB

在 Arch Linux 上安裝 MariaDB

重要提示： 在開始之前，不要盲目的接受這些建議。每個 MySQL 設定都是不同的，在進行任何更改之前需要謹慎考慮。

你需要明白這些：

MySQL/MariaDB 設定檔位於 /etc/my.cnf。 每次更改此檔案後你需要重啟 MySQL 服務，以使更改生效。

這篇文章使用 MySQL 5.6 版本。

 

1. 啟用 InnoDB 的每張表一個資料檔案設定

首先，有一個重要的解釋， InnoDB 是一個儲存引擎。MySQL 和 MariaDB 使用 InnoDB 作為預設儲存引擎。以前，MySQL 使用系統資料表空間來儲存資料庫中的表和索引。這意味著伺服器唯一的目的就是資料庫處理，它們的儲存盤不用於其它目的。

InnoDB 提供了更靈活的方式，它把每個資料庫的資訊儲存在一個 .ibd 資料檔案中。每個 .idb 檔案代表它自己的資料表空間。通過這樣的方式可以更快地完成類似 “TRUNCATE” 的資料庫操作，當刪除或截斷一個資料庫表時，你也可以回收未使用的空間。

這樣配置的另一個好處是你可以將某些資料庫表放在一個單獨的存放裝置。這可以大大提升你磁碟的 I/O 負載。

MySQL 5.6及以上的版本預設啟用 innodb_file_per_table。你可以在 /etc/my.cnf 檔案中看到。該指令看起來是這樣的：

innodb_file_per_table=1

 

2. 將 MySQL 資料庫資料存放區到獨立分區上

注意：此設定只在 MySQL 上有效, 在 MariaDB 上無效。

有時候作業系統的讀/寫會降低你 MySQL 伺服器的效能，尤其是如果作業系統和資料庫的資料位元於同一塊磁碟上。因此，我建議你使用單獨的磁碟（最好是 SSD）用於 MySQL 服務。

要完成這步，你需要將新的磁碟串連到你的電腦/伺服器上。對於這篇文章，我假定磁碟掛在到 /dev/sdb。

 

下一步是準備新的分區：# fdisk /dev/sdb

現在按 “N” 來建立新的分區。接著按 “P”，使其建立為主要磁碟分割。在此之後，從 1-4 設定分區號。之後，你可以選擇分區大小。這裡按 enter。在下一步，你需要配置分區的大小。

如果你希望使用全部的磁碟，再按一次 enter。否則，你可以手動設定新分區的大小。準備就緒後按 “w” 儲存更改。現在，我們需要為我們的新分區建立一個檔案系統。這可以用下面命令輕鬆地完成：

# mkfs.ext4 /dev/sdb1

現在我們會掛載新分區到一個目錄。我在根目錄下建立了一個名為 “ssd” 的目錄：

# mkdir /ssd/

掛載新分區到剛才建立的目錄下：

# mount /dev/sdb1 /ssd/

你可以在 /etc/fstab 檔案中添加如下行設定為開機自動掛載：

/dev/sdb1 /ssd ext3 defaults 0 0

 

現在我們將 MySQL 移動到新磁碟中

首先停止 MySQL 服務：

# service mysqld stop

我建議你同時停止 Apache/nginx，以防止任何試圖寫入資料庫的操作：

# service httpd stop# service nginx stop

現在複製整個 MySQL 目錄到新分區中:

# cp /var/lib/mysql /ssd/ -Rp

這可能需要一段時間，具體取決於你的 MySQL 資料庫的大小。一旦這個過程完成後重新命名 MySQL 目錄：

# mv /var/lib/mysql /var/lib/mysql-backup

然後建立一個符號連結：

# ln -s /ssd/mysql /var/lib/mysql

現在啟動你的 MySQL 和 web 服務：

# service mysqld start# service httpd start# service nginx start

以後你的資料庫將使用新的磁碟訪問。

 

3. 最佳化使用 InnoDB 的緩衝池

InnoDB 引擎在記憶體中有一個緩衝池用於快取資料和索引。這當然有助於你更快地執行 MySQL/MariaDB 查詢語句。選擇合適的記憶體大小需要一些重要的決策並對系統的記憶體消耗有較多的認識。

下面是你需要考慮的:

其它的進程需要消耗多少記憶體。這包括你的系統進程，頁表，通訊端緩衝。

你的伺服器是否專門用於 MySQL 還是你運行著其它非常消耗記憶體的服務。

在一個專用的機器上，你可能會把 60-70％ 的記憶體配置給 innodb_buffer_pool_size。如果你打算在一個機器上運行更多的服務，你應該重新考慮專門用於 innodb_buffer_pool_size 的記憶體大小。

你需要設定 my.cnf 中的此項:

innodb_buffer_pool_size

 

4. 在 MySQL 中避免使用 Swappiness

“交換”是一個當系統移動部分記憶體到一個稱為 “交換空間” 的特殊磁碟空間時的過程。通常當你的系統用完實體記憶體後就會出現這種情況，系統將資訊寫入磁碟而不是釋放一些記憶體。正如你猜測的磁碟比你的記憶體要慢得多。

該選項預設情況下是啟用的：

# sysctl vm.swappiness vm.swappiness = 60

運行以下命令關閉 swappiness：

# sysctl -w vm.swappiness=0

 

5. 設定 MySQL 的最大串連數

max_connections 指令告訴你當前你的伺服器允許多少並發串連。MySQL/MariaDB 伺服器允許有 SUPER 許可權的使用者在最大串連之外再建立一個串連。只有當執行 MySQL 請求的時候才會建立串連，執行完成後會關閉串連並被新的串連取代。

請記住，太多的串連會導致記憶體的使用量過高並且會鎖住你的 MySQL 伺服器。一般小網站需要 100-200 的串連數，而較大可能需要 500-800 甚至更多。這裡的值很大程度上取決於你 MySQL/MariaDB 的使用方式。

你可以動態地改變 max_connections 的值而無需重啟MySQL伺服器：

# mysql -u root -pmysql> set global max_connections = 300;

 

6. 配置 MySQL 的線程緩衝數量

thread_cache_size 指令用來設定你伺服器緩衝的線程數量。當用戶端中斷連線時，如果當前線程數小於thread_cache_size，它的線程將被放入緩衝中。下一個請求通過使用緩衝池中的線程來完成。

要提高伺服器的效能，你可以設定 thread_cache_size 的值相對高一些。你可以通過以下方法來查看線程快取命中率：

mysql> show status like ‘Threads_created‘;mysql> show status like ‘Connections‘;

你可以用以下公式來計算線程池的命中率：

100 - ((Threads_created / Connections) * 100)

如果你得到一個較低的數字，這意味著大多數 mysql 串連使用新的線程，而不是從緩衝載入。在這種情況下，你需要增加thread_cache_size。

這裡有一個好處是可以動態地改變 thread_cache_size 而無需重啟 MySQL 服務。你可以通過以下方式來實現：

mysql> set global thread_cache_size = 16;

 

7. 禁用 MySQL 的 DNS 反向查詢

預設情況下當新的串連出現時，MySQL/MariaDB 會進行 DNS 查詢解析使用者的 IP 位址/主機名稱。對於每個用戶端串連，它的 IP 都會被解析為主機名稱。然後，主機名稱又被反解析為 IP 來驗證兩者是否一致。

當 DNS 配置錯誤或伺服器出現問題時，這很可能會導致延遲。這就是為什麼要關閉 DNS 的反向查詢的原因，你可以在你的設定檔中添加以下選項去設定：

[mysqld]# Skip reverse DNS lookup of clientsskip-name-resolve

更改後你需要重啟 MySQL 服務。

 

8. 配置 MySQL 的查詢快取容量

如果你有很多重複的查詢並且資料不經常改變 – 請使用緩衝查詢。 人們常常不理解 query_cache_size 的實際含義而將此值設定為 GB 級，這實際上會降低伺服器的效能。

背後的原因是，在更新過程中線程需要鎖定緩衝。通常設定為 200-300 MB應該足夠了。如果你的網站比較小的，你可以嘗試給 64M 並在以後及時去增加。

在你的 MySQL 設定檔中添加以下設定：

query_cache_type = 1query_cache_limit = 256Kquery_cache_min_res_unit = 2kquery_cache_size = 80M

 

9. 配置暫存資料表容量和記憶體表最大容量

tmp_table_size 和 max_heap_table_size 這兩個變數的大小應該相同，它們可以讓你避免磁碟寫入。tmp_table_size 是內建記憶體表的最大空間。如果表的大小超出限值將會被轉換為磁碟上的 MyISAM 表。

這會影響資料庫的效能。管理員通常建議在伺服器上設定這兩個值為每 GB 記憶體給 64M。

[mysqld]tmp_table_size= 64Mmax_heap_table_size= 64M

 

10. 啟用 MySQL 慢查詢日誌

記錄慢查詢可以協助你定位元據庫中的問題並協助你調試。這可以通過在你的 MySQL 設定檔中添加以下值來啟用：

slow-query-log = 1slow-query-log-file = /var/lib/mysql/mysql-slow.loglong_query_time = 1

第一個變數啟用慢查詢日誌，第二個告訴 MySQL 實際的記錄檔儲存位置。使用 long_query_time 來定義完成 MySQL 查詢多少用時算長。

 

11. 檢查 MySQL 的空閑串連

空閑串連會消耗資源，可以的話應該被終止或者重新整理。空閑串連是指處於 “sleep” 狀態並且保持了很長一段時間的串連。你可以通過運行以下命令查看空閑串連：

# mysqladmin processlist -u root -p | grep “Sleep”

這會顯示處於睡眠狀態的進程列表。當代碼使用持久串連到資料庫時會出現這種情況。使用 PHP 調用 mysql_pconnect 可以開啟這個串連，執行完查詢之後，刪除認證資訊並保持串連為開啟狀態。這會導致每個線程的緩衝都被儲存在記憶體中，直到該線程結束。

首先你要做的就是檢查代碼問題並修複它。如果你不能訪問正在啟動並執行代碼，你可以修改 wait_timeout 變數。預設值是 28800 秒，而你可以安全地將其降低到 60 ：

wait_timeout=60

 

12. 為 MySQL 選擇正確的檔案系統

選擇正確的檔案系統對資料庫至關重要。在這裡你需要考慮的最重要的事情是 - 資料的完整性，效能和易管理性。

按照 MariaDB 的建議，最好的檔案系統是XFS、ext4 和 Btrfs。它們都是可以使用超大檔案和大型存放區卷的企業級日誌型檔案系統。

下面你可以找到一些關於這三個檔案系統的有用資訊：

檔案系統XFSExt4Btrfs檔案系統最大容量8EB1EB16EB最大檔案大小8EB16TB16EB

我們的這篇文章詳細介紹了 Linux 檔案系統的利與弊: Linux 檔案系統解析。

 

13. 設定 MySQL 允許的最大資料包

MySQL 把資料拆分成包。通常一個包就是發送到用戶端的一行資料。max_allowed_packet 變數定義了可以被發送的最大的包。

此值設定得過低可能會導致查詢速度變得非常慢，然後你會在 MySQL 的錯誤記錄檔看到一個錯誤。建議將該值設定為最大包的大小。

 

14. 測試 MySQL 的效能最佳化

你應該定期檢查 MySQL/MariaDB 的效能。這將協助你瞭解資源的使用方式是否發生了改變或需要進行改進。

有大量的測試載入器可用，但我推薦你一個簡單易用的。該工具被稱為 mysqltuner。

使用下面的命令下載並運行它：

# wget https://github.com/major/MySQLTuner-perl/tarball/master# tar xf master# cd major-MySQLTuner-perl-993bc18/# ./mysqltuner.pl

你將收到有關 MySQL 使用的詳細報告和推薦提示。下面是預設 MariaDB 安裝的輸出範例：

MySQL 效能最佳化

 

15. 最佳化和修複 MySQL 資料庫

有時候 MySQL/MariaDB 資料庫中的表很容易崩潰，尤其是伺服器意外關機、檔案系統突然崩潰或複製過程中仍然訪問資料庫。幸運的是，有一個稱為 ‘mysqlcheck‘ 的免費開源工具，它會自動檢查、修複和最佳化 Linux 中資料庫的所有表。

# mysqlcheck -u root -p --auto-repair --check --optimize --all-databases# mysqlcheck -u root -p --auto-repair --check --optimize databasename

就是這些！我希望上述文章對你有用，並協助你最佳化你的 MySQL 伺服器。一如往常，如果你有任何疑問或評論，請在下面的評論部分提交。

15 個有用的 MySQL/MariaDB 效能調整和最佳化技巧（轉載的一篇好文）