在 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_packet 定義可以發送的數據包的最大大小。 如果 max_allowed_packet 設置太低,查詢可能太慢。我們建議您將數據包值設置為最大數據包大小。
設置臨時表容量
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 替換為數據庫名稱
- 使用測試工具檢查 MySQL / MariaDB 性能
我們建議您定期檢查 MySQL / MariaDB 數據庫的性能。這使您可以輕鬆訪問性能報告和改進。有許多可用的工具,但 mysqltuner 是最好的工具之一。
運行以下命令下載工具
$ wget https://github.com/major/MySQLTuner-perl/tarball/master
解壓文件
$ tar xvzf master
轉到您的項目目錄並運行以下腳本。
$ cd major-MySQLTuner-perl-7aa57fa
$ ./mysqltuner.pl
輸出:
結論是
在本文中,您學習瞭如何使用不同的技術優化 MySQL/MariaDB。謝謝你的閱讀。