15 個有用的 MySQL/MariaDB 性能調整和優化技巧

MySQL 性能優化

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

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

這篇文章將告訴你一些基本的，但非常有用的關於如何優化 MySQL/MariaDB 性能的技巧。注意，本文假定您已經安裝了 MySQL 或 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 -p
mysql> set global max_connections = 300;

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

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

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

mysql> show status like &apos;Threads_created&apos;;
mysql> show status like &apos;Connections&apos;;

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

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 clients
skip-name-resolve

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

8. 配置 MySQL 的查詢緩存容量

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

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

在你的 MySQL 配置文件中添加以下設置：

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M

9. 配置臨時表容量和內存表最大容量

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

這會影響資料庫的性能。管理員通常建議在伺服器上設置這兩個值為每 GB 內存給 64M。

[mysqld]
tmp_table_size= 64M
max_heap_table_size= 64M

10. 啟用 MySQL 慢查詢日誌

記錄慢查詢可以幫助你定位資料庫中的問題並幫助你調試。這可以通過在你的 MySQL 配置文件中添加以下值來啟用：

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_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。它們都是可以使用超大文件和大容量存儲卷的企業級日誌型文件系統。

下面你可以找到一些關於這三個文件系統的有用信息：

文件系統	XFS	Ext4	Btrfs
文件系統最大容量	8EB	1EB	16EB
最大文件大小	8EB	16TB	16EB

我們的這篇文章詳細介紹了 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 伺服器。一如往常，如果你有任何疑問或評論，請在下面的評論部分提交。

via: http://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/

作者：Marin Todorov 譯者：strugglingyouth 校對：ictlyh

本文由 LCTT 原創翻譯，Linux中國榮譽推出

本文轉載來自 Linux 中國: https://github.com/Linux-CN/archive

對這篇文章感覺如何？

太棒了

不錯

愛死了

不太好

感覺很糟

Rain

雨落清風。心向陽

1. 啟用 InnoDB 的每張表一個數據文件設置

2. 將 MySQL 資料庫數據存儲到獨立分區上

下一步是準備新的分區：

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

3. 優化使用 InnoDB 的緩衝池

4. 在 MySQL 中避免使用 Swappiness

5. 設置 MySQL 的最大連接數

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

7. 禁用 MySQL 的 DNS 反向查詢

8. 配置 MySQL 的查詢緩存容量

9. 配置臨時表容量和內存表最大容量

10. 啟用 MySQL 慢查詢日誌

11. 檢查 MySQL 的空閑連接

12. 為 MySQL 選擇正確的文件系統

13. 設置 MySQL 允許的最大數據包

14. 測試 MySQL 的性能優化

15. 優化和修復 MySQL 資料庫

分享

對這篇文章感覺如何？

You may also like

Leave a reply 取消回復

More in:Linux中國

中文操作系統論壇

關注 LinuxStory

開源學村

特別關注

最新文章

最熱標籤