調整和優化 Mysql 和 Mariadb 數據庫的 13 個技巧


在 Joomla、WordPress、Drupal、Typo 3、MySQL 和 MariaDB 等 Web 託管和 CMS 系統中，使用最廣泛的關係數據庫管理系統 (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 中，您可以在配置文件中添加以下變量來設置臨時表大小。我們建議將此值設置為每 GB 服務器內存 64M。

[mysqld]

tmp_table_size=64M

重啟mysql服務

$ systemctl restart mysql
$ systemctl restart mariadb

設置內存表的最大大小。

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

[mysqld]

max_heap_table_size=64M

重新啟動數據庫服務器以應用更改。

$ systemctl restart mysql
$ systemctl restart mariadb

禁用 MySQL 的反向 DNS 查找

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

[mysqld]

skip-name-resolve

重新啟動服務。

$ systemctl restart mysql
$ systemctl restart mariadb

避免 MySQL 中的可交換性

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

$ 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 連接，運行查詢，刪除身份驗證，並使連接保持打開狀態。利用 等待超時 指令，空閒連接可能會被中斷。默認是 等待超時 28800 秒，可以縮短到最短時間範圍，例如 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。謝謝你的閱讀。

Related Posts