MySQL查看資料庫性能常用命令和實戰教學

来源:https://www.cnblogs.com/LoveBB/archive/2023/03/08/17195702.html
-Advertisement-
Play Games

MySQL查看資料庫性能常用命令 # 列出MySQL伺服器運行各種狀態值 show global status; # 查詢MySQL伺服器配置信息語句 show variables; # 慢查詢 show variables like '%slow%'; # MySQL伺服器最大連接數 show v ...


MySQL查看資料庫性能常用命令

# 列出MySQL伺服器運行各種狀態值
show global status;
# 查詢MySQL伺服器配置信息語句
show variables;
# 慢查詢
show variables like '%slow%';
# MySQL伺服器最大連接數
show variables like 'max_connections'; 
# 伺服器響應的最大連接數
show global status like 'Max_used_connections';
# 查看試圖連接到MySQL(不管是否連接成功)的連接數
show status like 'connections';
# 創建臨時表
show global status like 'created_tmp%'; 
# MySQL伺服器對臨時表的配置
show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size'); 
# 打開表的數量
show global status like 'open%tables%'; 
# table高速緩存的數量
show variables like 'table_open_cache'; 
# 查看MySQL伺服器的線程信息
show global status like 'Thread%';
# 查看當前運行的sql
SELECT * FROM `information_schema`.`PROCESSLIST` WHERE `info` IS NOT NULL and TIME > 0
# 當客戶端斷開之後，伺服器處理此客戶的線程將會緩存起來以響應下一個客戶而不是銷毀(前提是緩存數未達上限)
show variables like 'thread_cache_size'; 
# 查詢緩存
show global status like 'qcache%'; 
# 查詢緩存適用於特定的場景，建議充分測試後，再考慮開啟，避免引起性能下降或引入其他問題
show variables like 'query_cache%'; 
# 排序使用情況
show global status like 'sort%'; 
# 文件打開數
show global status like 'open_files'; 
# 表鎖情況
show global status like 'table_locks%'; 
# 表掃描情況
show global status like 'handler_read%'; 
# 伺服器完成的查詢請求
show global status like 'com_select'; 
# 查詢當前MySQL本次啟動後的運行統計時間
show status like 'uptime';
# 查看本次MySQL啟動後執行的select語句的次數
show status like 'com_select';
# 查看本次MySQL啟動後執行insert語句的次數
show global status like 'com_insert';
# 查看本次MySQL啟動後執行update語句的次數
show global status like 'com_update';
# 查看本次MySQL啟動後執行delete語句的次數
show global status like 'com_delete';
# 查看立即獲得的表的鎖的次數
show status like 'table_locks_immediate';
# 查看不能立即獲得的表的鎖的次數。如果該值較高，並且有性能問題，你應首先優化查詢，然後拆分表或使用複製
show status like 'table_locks_waited';
# 查看查詢時間超過long_query_time秒的查詢的個數
show status like 'slow_queries';
# 通過mysql自帶profiling(性能分析)工具可以診斷某個sql各個執行階段消耗的時間，每個執行階段在cpu disk io等方面的消耗情況。
show variables like '%profiling%';
# 
show profiles
show profile for query 2;
show profile cpu, block io for query 2;

實戰

查詢伺服器狀態和配置

# 列出MySQL伺服器運行各種狀態值
show global status;

慢查詢

# 慢查詢
show variables like '%slow%';

  • 配置中關閉了記錄慢查詢，打開之後，你可以分析慢查詢日誌，找出有問題的SQL語句，慢查詢時間不宜設置過長，否則意義不大，最好在5秒以內。打開慢查詢日誌可能會對系統性能有一點點影響，如果你的MySQL是主-從結構，可以考慮打開其中一臺從伺服器的慢查詢日誌，這樣既可以監控慢查詢，對系統性能影響又小。

連接數

MySQL: ERROR 1040: Too many connections

一種是訪問量確實很高，MySQL伺服器抗不住，這個時候就要考慮增加從伺服器分散讀壓力，另外一種情況是MySQL配置文件中max_connections值過小： 　

# MySQL伺服器最大連接數
show variables like 'max_connections'; 

  • 這台MySQL伺服器最大連接數是5275，然後查詢一下伺服器響應的最大連接數：
# 伺服器響應的最大連接數
show global status like 'Max_used_connections';

  • MySQL伺服器過去的最大連接數是62，沒有達到伺服器連接數上限5275，應該沒有出現1040錯誤，比較理想的設置是
Max_used_connections / max_connections * 100% ≈ 85%
  • 最大連接數占上限連接數的85%左右，如果發現比例在10%以下，MySQL伺服器連接數上限設置的過高了。這台伺服器設置連接上限有些偏高。

臨時表

# 創建臨時表
show global status like 'created_tmp%'; 

  • 每次創建臨時表，Created_tmp_tables增加，如果是在磁碟上創建臨時表，Created_tmp_disk_tables也增加。

  • Created_tmp_files：表示MySQL服務創建的臨時文件文件數，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

比如上面的伺服器 Created_tmp_disk_tables / Created_tmp_tables * 100% = 1.26%，應該相當好了。

Open Table情況

# 打開表的數量
show global status like 'open%tables%'; 

  • Open_tables：表示打開表的數量，Opened_tables：表示打開過的表數量，如果Opened_tables數量過大，說明配置中table_cache(5.1.3之後這個值叫做table_open_cache)值可能太小，我們查詢一下伺服器table_cache值： 　　
# table高速緩存的數量
show variables like 'table_open_cache'; 

  • Open_tables / Opened_tables * 100% >= 81%

  • Open_tables / table_open_cache * 100% <= 70%

  • 比較合適的值為：

    Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100% <= 95%

進程使用情況

# 查看MySQL伺服器的線程信息
show global status like 'Thread%'; 

  • 如果我們在MySQL伺服器配置文件中設置了thread_cache_size，當客戶端斷開之後，伺服器處理此客戶的線程將會緩存起來以響應下一個客戶而不是銷毀(前提是緩存數未達上限)。Threads_created表示創建過的線程數，如果發現Threads_created值過大的話，表明MySQL伺服器一直在創建線程，這也是比較耗資源，可以適當增加配置文件中thread_cache_size值，查詢伺服器thread_cache_size配置：
# 當客戶端斷開之後，伺服器處理此客戶的線程將會緩存起來以響應下一個客戶而不是銷毀(前提是緩存數未達上限)
show variables like 'thread_cache_size'; 

查詢緩存(query cache)

# 查詢緩存
show global status like 'qcache%'; 

Qcache_free_blocks：緩存中相鄰記憶體塊的個數。數目大說明可能有碎片。FLUSH QUERY CACHE會對緩存中的碎片進行整理，從而得到一個空閑塊。
Qcache_free_memory：緩存中的空閑記憶體。
Qcache_hits：每次查詢在緩存中命中時就增大
Qcache_inserts：每次插入一個查詢時就增大。命中次數除以插入次數就是不中比率。
Qcache_lowmem_prunes：緩存出現記憶體不足並且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間來看;如果這個數字在不斷增長，就表示可能碎片非常嚴重，或者記憶體很少。(上面的 free_blocks和free_memory可以告訴您屬於哪種情況)
Qcache_not_cached：不適合進行緩存的查詢的數量，通常是由於這些查詢不是 SELECT 語句或者用了now()之類的函數。
Qcache_queries_in_cache：當前緩存的查詢(和響應)的數量。
Qcache_total_blocks：緩存中塊的數量

  • 我們再查詢一下伺服器關於query_cache的配置
# 查詢緩存適用於特定的場景，建議充分測試後，再考慮開啟，避免引起性能下降或引入其他問題
show variables like 'query_cache%'; 

query_cache_limit：超過此大小的查詢將不緩存
query_cache_min_res_unit：緩存塊的最小大小
query_cache_size：查詢緩存大小
query_cache_type：緩存類型，決定緩存什麼樣的查詢
query_cache_wlock_invalidate：當有其他客戶端正在對MyISAM表進行寫操作時，如果查詢在query cache中，是否返回cache結果還是等寫操作完成再讀表獲取結果。
query_cache_min_res_unit的配置是一柄”雙刃劍”，預設是4KB，設置值大對大數據查詢有好處，但如果你的查詢都是小數據查詢，就容易造成記憶體碎片和浪費。
　　查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
　　如果查詢緩存碎片率超過20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數據量的話。
　　查詢緩存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%
　　查詢緩存利用率在25%以下的話說明query_cache_size設置的過大，可適當減小;查詢緩存利用率在80%以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多。
　　查詢緩存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%
　　示例伺服器 查詢緩存碎片率 = 100%，查詢緩存利用率 = 15.9%，查詢緩存命中率 = 0%，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。

文件打開數

# 文件打開數
show global status like 'open_files'; 


# 查看 mysqld進程能夠打開的操作系統文件描述符(fd)的最大數量
show variables like 'open_files_limit';

  • 比較合適的設置：
Open_files / open_files_limit * 100% <= 75%

表鎖情況

　mysql> show global status like 'table_locks%';

  • Table_locks_immediate：表示立即釋放表鎖數，Table_locks_waited：表示需要等待的表鎖數。
  • 如果Table_locks_immediate / Table_locks_waited > 5000，最好採用InnoDB引擎，因為InnoDB是行鎖而MyISAM是表鎖，對於高併發寫入的應用InnoDB效果會好些。示例中的伺服器Table_locks_immediate / Table_locks_waited = 235，MyISAM就足夠了。

表掃描情況

# 表掃描情況
show global status like 'handler_read%'; 


# 伺服器完成的查詢請求
show global status like 'com_select'; 

  • 計算表掃描率：
    　　表掃描率 = Handler_read_rnd_next / Com_select
    　　如果表掃描率超過4000，說明進行了太多表掃描，很有可能索引沒有建好，增加read_buffer_size值會有一些好處，但最好不要超過8MB。
  • 上面伺服器中表掃描率=20411
作者：天下沒有收費的bug 出處：https://www.cnblogs.com/LoveBB/ 本文版權歸作者和博客園共有，歡迎轉載，但未經作者同意必須在文章頁面給出原文鏈接，否則保留追究法律責任的權利。
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ssh 遠程連接方式總結
    無論你是後端程式員還是前端程式員，都避免不了和Linux打交道。上篇介紹了Linux常用的20個命令其中的10個，本文繼續介紹剩下的10個命令。 11.man 命令 manual的縮寫，即使用手冊的意思，是Linux下最核心的命令之一，用來列出一份完整的說明 語法格式 : man [參數] 常用參數 ...
  • CentOS7.9 搭建內部yum源伺服器同步阿裡yum源
    CentOS7.9 搭建內部yum源伺服器同步阿裡yum源 環境說明： 1、企業內網需求：企業內部許多伺服器是不能連接互聯網，但每台centos主機安裝軟體包時都配置本地yum源很麻煩，也缺少第三方yum源，因此在內網搭建一臺yum源伺服器滿足這一需求，同時網路層面策略只允許此yum源伺服器每周日晚 ...
  • 動態切換JDK8和JDK17
    一、下載安裝兩個版本的JDK 官網下載地址：Java Downloads | Oracle 下載安裝成功後，安裝路徑E:\Java\上 JDK8 有兩個包一個jdk1.8.0_231，一個jre1.8.0_231。JDK17只有一個jdk-17.0.5 二、JDK的環境配置 切換環境變數 在系統變數 ...
  • Liunx Vim常用命令
    Liunx Vim常用命令 1、打開命令： vi / vim + filename(文件名) 2、退出命令： 強制退出不保存修改的內容 :q! 退出並且保存修改的內容 :wq 強制保存修改的內容然後退出（修改了只讀文件會用到） :wq! 註意：輸入法需要改為英文，中文不生效！ 3、游標移動命令 個人 ...
  • 一次慘痛教訓讓我寫了個Windows定期備份文件腳本
    前言 說實話在寫這篇文章的時候，鹹魚不禁又想起了那件男默女淚的往事 我喜歡做筆記，我覺得好記性不如爛筆頭，所以在我的學生以及職業生涯階段，我用過四款筆記應用——Onenote、語雀、印象筆記、Typora 其中我個人覺得體驗最好的非 Typora 莫屬了 ​ 在 2022 年的時候，由於 Typor ...
  • 不愧是微軟出品的工具，逆天！
    系統增強工具PowerToys 下載地址：https://github.com/microsoft/PowerToys 什麼是 Windows 系統中，最好的輔助工具？ PowerToys 一定可以獲得提名。PowerToys 是一款來自微軟的系統增強工具，就像是一個神奇的系統外掛，整套軟體由若幹子 ...
  • 通用表表達式
    通用表表達式 通用表表達式（Common Table Expression、CTE）是一個臨時的查詢結果或者臨時表，可以 在其他 SELECT、INSERT、UPDATE 以及 DELETE 語句中使用。通用表表達式只在當前語句中 有效，類似於子查詢。 使用 CTE 的主要好處包括： 提高複雜查詢的 ...
  • 明解Redis（一）概述
    一、Redis簡介 Redis全稱為 Remote Dictionary Server（遠程詞典服務），開源（BSD許可）的，使用 C 語言開發的。 Redis是記憶體中的數據結構存儲系統，它可以用作資料庫、緩存和消息中間件。 支持多種類型的數據結構，如 字元串（strings）， 散列（hashes ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類