MySQL 性能：識別長查詢


MySQL 支持的任何應用程序都可以從經過微調的數據庫服務器中受益。 多年來，Liquid Web Heroic 支持團隊遇到了許多情況。一些小的調整對網站和應用程序的性能產生了很大的影響。本系列文章概述了一些對性能影響最大的一般性建議。

預檢

本文適用於大多數基於 Linux 的 MySQL VPS 服務器。這包括但不限於運行各種流行 Linux 發行版的傳統專用和雲 VPS 服務器。本文適用於以下 Liquid Web 系統類型：

  • 核心託管 CentOS 6x/7x
  • 具有核心管理的 Ubuntu 14.04/16.04
  • 完全託管的 CentOS 6/7 cPanel
  • 完全託管的 CentOS 7 Plesk Onyx 17
  • 自我管理的 Linux 服務器

注意 選擇不提供直接支持的自我管理系統可以使用此處描述的技術，但 Liquid Web Heroic 支持團隊無法為這些服務器類型提供直接幫助。

本系列文章假設您熟悉以下基本系統管理概念：

  • SSH 連接和標準基本導航 Linux命令行shell環境.
  • 打開、編輯和保存文件 維姆 或者您選擇的系統編輯器。
  • MySQL交互模式 和通用的 MySQL 查詢語法。

什麼是 MySQL 優化？

MySQL 優化這個術語沒有很好的定義。對於個人、管理員、團體和公司，它可以有不同的含義。 在這個 MySQL 優化系列文章中，我們將 MySQL 優化定義為：配置已配置的 MySQL 或 MariaDB 服務器以避免本系列文章中描述的常見瓶頸。

什麼是瓶頸？

與汽水瓶瓶頸類似，技術術語瓶頸是應用程序或服務器配置中的一個點，少量流量或數據可以毫無問題地通過該點。但是，它會阻止或阻止大量相同類型的流量或數據，並且開箱即用無法正常工作。請參閱下面的配置瓶頸示例。

在此示例中，服務器可以處理 10 個並發連接。但是，此配置僅接受 5 個連接。只要一次連接不超過 5 個，就不會出現此問題。但是，當流量增加到 10 個連接時，由於服務器配置中的資源未使用，其中一半開始失敗。上面的示例顯示了瓶頸的形狀，以修復瓶頸的優化配置命名。

什麼時候應該優化 MySQL 數據庫？

理想情況下，應在降低生產力之前定期進行數據庫性能調整。最佳實踐是每週或每月審核一次數據庫性能，以確保問題不會對您的應用程序產生不利影響。性能問題最明顯的症狀是：

  • 查詢堆積在 MySQL 進程表中並且永遠不會完成。
  • 使用數據庫的應用程序或網站速度很慢。
  • 連接超時錯誤，尤其是在高峰時段。

在繁忙的系統上同時運行多個查詢是正常的，但是當這些查詢經常需要很長時間才能完成時就會出現問題。確切的閾值因係統和應用程序而異，但如果平均查詢時間超過幾秒鐘，其他網站和應用程序將變慢。這些減速可能從很小的時候開始，直到大的流量峰值達到特定的瓶頸時才會被注意到。

識別性能問題

了解如何檢查 MySQL 進程表對於診斷遇到的特定瓶頸非常重要。有幾種查看進度表的方法，具體取決於您的特定服務器和配置。為簡潔起見，本系列將重點介紹最常用的方法。 安全外殼 (SSH) 訪問：

方法 1. 使用 MySQL 進程表

利用’mysql-管理員“帶有標誌的命令行工具”進程列表‘ 還’過程‘縮寫。 （添加標誌’統計數據‘ 還’狀態‘ 簡而言之，顯示自 MySQL 上次重新啟動以來已執行的查詢的統計信息。 )

命令：

mysqladmin proc stat

輸出：

 +-------+------+-----------+-----------+---------+------+-------+
 | Id    | User | Host      | db        | Command | Time | State | Info               | Progress |
 +-------+------+-----------+-----------+---------+------+-------+--------------------+----------+
 | 77255 | root | localhost | employees | Query   | 150  |       | call While_Loop2() | 0.000    |
 | 77285 | root | localhost |           | Query   | 0    | init  | show processlist   | 0.000    |
 +-------+------+-----------+-----------+---------+------+-------+--------------------+----------+
 Uptime: 861755  Threads: 2  Questions: 20961045  Slow queries: 0  Opens: 2976  Flush tables: 1  Open tables: 1011  Queries per second avg: 24.323

筆記：親： 當與 shell 接口一起使用時，輸出可以很容易地通過管道傳輸到其他腳本和工具。什麼時候： 進程表中的信息列總是被截斷的，所以對於長查詢不提供完整的查詢。

方法二：使用 MySQL 進程表

我跑過去了。”顯示進程列表。‘ 從 MySQL 交互模式提示符運行查詢。 （添加’滿的‘ 命令修飾符禁用截斷 信息 支柱。在查看長查詢時這是必要的。 )

命令：

show processlist;

輸出：

MariaDB [(none)]> show full processlist;
 +-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+
 | Id    | User | Host      | db        | Command | Time | State | Info                  | Progress |
 +-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+
 | 77006 | root | localhost | employees | Query   |  151 | NULL  | call While_Loop2()    |    0.000 |
 | 77021 | root | localhost | NULL      | Query   |    0 | init  | show full processlist |    0.000 |
 +-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+

親： 要查看更長查詢的完整查詢，請使用 full 修飾符。什麼時候： MySQL 交互模式不允許您訪問 shell 界面中可用的腳本和工具。.

使用慢查詢日誌

MySQL 中另一個有價值的工具是包含的慢查詢日誌記錄功能。此功能是定期查找長時間運行查詢的首選方法。有幾個指令可用於調整此功能。但是，最常見的設置是：

看表

慢查詢日誌啟用/禁用慢查詢日誌記錄
慢查詢日誌文件慢查詢日誌文件名和路徑
long_query_time定義慢查詢的時間（秒/微秒）

這些說明 [mysqld] MySQL 配置文件部分位於此處： /etc/my.cnf 另外，需要重啟 MySQL 服務才能生效。有關格式，請參見下面的示例。

警告：慢查詢日誌文件存在大磁盤空間問題，需要持續關注，直到禁用慢查詢日誌功能。 請記住，long_query_time 指令越低，慢查詢日誌填滿磁盤分區的速度就越快。

[mysqld]
 log-error=/var/lib/mysql/mysql.err
 innodb_file_per_table=1
 default-storage-engine=innodb
 innodb_buffer_pool_size=128M
 innodb_log_file_size=128M
 max_connections=300
 key_buffer_size = 8M
 slow_query_log=1
 slow_query_log_file=/var/lib/mysql/slowquery.log
 long_query_time=5

啟用慢查詢日誌記錄後，您應該定期跟進以查看需要調整以提高性能的不規則查詢。分析慢查詢日誌文件，直接解析看它包含什麼。以下示例顯示運行時間超過配置的 5 秒的示例查詢的統計信息。

溫暖的啟用慢查詢日誌記錄功能會降低性能。這是由於分析每個查詢所需的額外例程以及將所需查詢寫入日誌文件所需的 I/O。因此，在生產系統上禁用慢查詢日誌記錄被認為是最佳實踐。如果您正在積極尋找可能影響您的應用程序或網站的令人討厭的查詢，則應僅在有限的時間內啟用慢查詢日誌記錄。

# Time: 180717  0:23:28
 # [email protected]: root[root] @ localhost []
 # Thread_id: 32  Schema: employees  QC_hit: No
 # Query_time: 627.163085  Lock_time: 0.000021  Rows_sent: 0  Rows_examined: 0
 # Rows_affected: 0
 use employees;
 SET timestamp=1531801408;
 call While_Loop2();

或者，您可以使用 mysqldumpslow 命令行工具。它解析慢查詢日誌文件，並對除數字和字符串數據值之外的類似查詢進行分組。

~ $ mysqldumpslow -a /var/lib/mysql/slowquery.log
 Reading mysql slow query log from /var/lib/mysql/slowquery.log
 Count: 2  Time=316.67s (633s)  Lock=0.00s (0s)  Rows_sent=0.5 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost
 call While_Loop2()

（有關用法，請參閱 MySQL 文檔 – mysqldumpslow — 總結慢查詢日誌文件)

您需要了解的關於高可用性的知識

結論是

這結束了我們的數據庫優化系列的第 1 部分，並為基準測試提供了堅實的基礎。數據庫問題可能很複雜，但本系列分析了這些概念，並為您提供了通過數據庫轉換、表轉換和索引來優化數據庫的方法。

我們能幫你什麼嗎？

我們以成為 Hosting™ 最樂於助人的人而自豪！

我們的支持團隊由經驗豐富的 Linux 工程師和才華橫溢的系統管理員組成，他們對許多網絡託管技術有深入的了解，尤其是本文中討論的技術。

如果您對此信息有任何疑問，我們可以每天 24 小時、每週 7 天、每年 365 天為您解答有關本文的任何問題。

如果您是完全託管的 VPS 服務器、雲專用服務器、VMWare 私有云、私有父服務器、託管雲服務器或專用服務器的所有者，並且不習慣按照記錄的步驟操作，請致電 @800.580。致電 4985。 一個 和…聊天 或支持票以協助此過程。

Related Posts