在 Joomla、WordPress、Drupal 和 Typo 3 等 Web 託管和 CMS 系統中，MySQL 和 MariaDB 是使用最廣泛的關係數據庫管理系統 (RDMS)。本文介紹如何加速和優化您網站的 MySQL 和 MariaDB 數據庫服務器。

將 MySQL 數據保存在單獨的分區中

從優化和保證的角度來看，將數據庫數據存儲在單獨的捲上始終是最好的主意。這些卷專用於高速存儲卷，例如 SSD 和 NVMe。如果系統發生故障，數據庫是安全的。分區卷由高速存儲卷組成，可提高性能。

設置 MySQL 最大連接數

MySQL / MariaDB 使用說明書 最大連接數 指定服務器上當前允許的並發連接數。過多的連接會消耗大量內存並增加 CPU 負載。對於較小的站點，您可以將連接數指定為 100-200，但對於較大的站點，您可能需要 500-800 或更多。這個 最大連接數 您可以使用 SQL 查詢進行動態更改。在此示例中，將值設置為 200。

$ mysql -u root -p
mysql> set global max_connections=200;

輸出：

為 MySQL 啟用慢查詢日誌記錄

記錄長時間運行的查詢可以更輕鬆地解決數據庫問題。可以通過將以下行添加到 MySQL / MariaDB 配置文件來啟用慢查詢日誌記錄：

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

在這裡，第一個變量啟用慢查詢日誌記錄

第二個變量定義日誌文件目錄

第三個變量定義了完成一個 MySQL 查詢所需的時間

重啟mysql/mariadb服務並查看日誌

$ systemctl restart mysql
$ systemctl restart mariadb
$ tail -f /var/lib/mysql/mysql-slow-query.log

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

MySQL 將數據分成數據包。 Max_allowed_pa​​cket 定義可以發送的數據包的最大大小。 如果 max_allowed_pa​​cket 設置太低，查詢可能太慢。我們建議您將數據包值設置為最大數據包大小。

設置臨時表容量

tmp_table_size 是用於嵌入式內存表的最大空間。如果表大小超過指定的限制，它將被轉換為磁盤上的 MyISAM 表。 在 MySQL/MariaDB 中，您可以在配置文件中添加以下變量來設置臨時表的大小。我們建議將此值設置為服務器上每 1GB 內存 64M。

[mysqld]

tmp_table_size=64M

重啟mysql服務

$ systemctl restart mysql
$ systemctl restart mariadb

設置內存表的最大大小。

Max_heap_table_size 是 MySQL 用來配置最大內存表大小的變量。為避免磁盤寫入，內存表的最大容量應與臨時表的容量相同。我們建議將此值設置為服務器上每 1GB 內存 64M。將以下行添加到您的 MySQL 配置文件並重新啟動服務。

[mysqld]

max_heap_table_size=64M

重新啟動數據庫服務器以使更改生效。

$ systemctl restart mysql
$ systemctl restart mariadb

禁用 MySQL 反向查找

當收到新連接時，MySQL/MariaDB 將執行 DNS 查找以解析用戶的 IP 地址。如果 DNS 配置無效或 DNS 服務器出現問題，這可能會導致延遲。 要禁用 DNS 查找，請將以下行添加到 MySQL 配置文件並重新啟動 MySQL 服務。

[mysqld]

skip-name-resolve

重新啟動服務。

$ systemctl restart mysql
$ systemctl restart mariadb

避免 MySQL 中的 Swappiness

當系統用完物理內存時，Linux 內核會將部分內存移動到磁盤的一個特殊分區，稱為“交​​換”空間。在這種情況下，系統將信息寫入磁盤而不是釋放內存。系統內存比磁盤存儲快，所以我們建議禁用交換。可以使用以下命令禁用交換：

$ sysctl -w vm.swappiness=0

輸出：

設置匯率

增加 InnoDB 緩衝池大小

MySQL / MariaDB 有一個帶有緩衝池的 InnoDB 引擎，用於緩存和索引內存中的數據。緩衝池有助於使 MySQL / MariaDB 查詢運行得相對較快。選擇正確的 InnoDB 緩衝池大小需要一些系統內存知識。最好的辦法是將 InnoDB 緩衝池大小值設置為 RAM 的 80%。

例子。

  • 系統內存 = 4GB
  • 緩衝池大小 = 3.2GB

將以下行添加到 MySQL 配置文件並重新啟動服務

[mysqld]

Innodb_buffer_pool_size 3.2G

重新啟動數據庫。

$ systemctl restart mysql
$ systemctl restart mariadb

查詢緩存大小處理

MySQL / MariaDB 查詢緩存指令用於緩存所有重用相同數據的查詢。我們建議您將該值設置為 64MB。這可以合理地增加到較小的網站。我們不建議將查詢緩存大小值增加到 GB，因為它會降低數據庫性能。 將以下行添加到 my.cnf 文件。

[mysqld]

query_cache_size=64M

檢查空閒連接

資源被空閒連接消耗，如果可能，應該終止或刷新。這些連接保持“睡眠”狀態，並且可以長時間停留在那裡。使用以下命令檢查空閒連接：

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

此查詢列出休眠進程。通常在 PHP 中，當使用 mysql_pconnect 時會觸發此事件。這將打開 MySQL 連接、執行查詢、刪除身份驗證並保持連接打開。利用 等待超時 指令和空閒連接可能會被中斷。默認是 等待超時 是 28,800 秒，可以縮短到最短時間範圍，例如 60 秒。 將以下行添加到 my.cnf 文件

[mysqld]

wait_timeout=60

MySQL數據庫優化與修復

如果服務器意外關閉，MySQL / MariaDB 表可能會崩潰。數據庫表崩潰還有其他原因，例如在復製過程運行時訪問數據庫或文件系統突然崩潰。在這種情況下，有一個名為“”的特殊工具。mysql檢查“檢查、修復和優化數據庫中的所有表。

使用以下命令執行修復和優化活動：

對於所有數據庫：

$ mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

對於特定數據庫：

$ mysqlcheck -u root -p --auto-repair --check --optimize dbname

將 dbname 替換為數據庫名稱

  1. 使用測試工具檢查 MySQL / MariaDB 性能

我們建議您定期檢查 MySQL / MariaDB 數據庫的性能。這使您可以輕鬆訪問性能報告和改進。有許多可用的工具，但 mysqltuner 是最好的工具之一。

運行以下命令下載工具

$ wget https://github.com/major/MySQLTuner-perl/tarball/master

解壓文件

$ tar xvzf master

轉到您的項目目錄並運行以下腳本。

$ cd major-MySQLTuner-perl-7aa57fa
$ ./mysqltuner.pl

輸出：

使用 mysqltuner 優化 MySQL 和 MariaDB

結論是

在本文中，您學習瞭如何使用不同的技術優化 MySQL/MariaDB。謝謝你的閱讀。

By Archives, Desktop