MySQL數(shù)據(jù),查詢QPS,TPS數(shù)據(jù)方式
更新時間:2025年02月27日 09:29:37 作者:會飛的土撥鼠呀
文章詳細介紹了查詢MySQL數(shù)據(jù)庫QPS和TPS的方法和工具,包括直接通過命令行、PerformanceSchema、mysqladmin、Prometheus、自動化腳本等,同時,還提供了優(yōu)化建議,如索引優(yōu)化、SQL調(diào)優(yōu)、事務控制和配置調(diào)優(yōu)
以下是針對 MySQL 數(shù)據(jù)庫 QPS(Queries Per Second)和 TPS(Transactions Per Second)數(shù)據(jù) 的詳細查詢方法和工具推薦,包含具體命令和示例:
一、直接通過 MySQL 命令行查詢
1. 查詢 QPS
-- 獲取當前總查詢次數(shù)(所有類型) SHOW STATUS LIKE 'Queries'; -- 計算過去 N 秒的 QPS(例如 N=5) SELECT (NOW() - INTERVAL 5 SECOND) AS 時間差, (SHOW STATUS LIKE 'Queries') - prev_queries AS 當前查詢總數(shù), ROUND((當前查詢總數(shù) / 時間差.total_seconds), 2) AS QPS FROM (SELECT SHOW STATUS LIKE 'Queries' AS prev_queries) AS subquery, (SELECT NOW() - INTERVAL 5 SECOND AS time_diff) AS time_diff;
2. 查詢 TPS
-- 獲取當前事務提交次數(shù) SHOW STATUS LIKE 'Com_commit'; -- 計算過去 N 秒的 TPS(例如 N=5) SELECT (NOW() - INTERVAL 5 SECOND) AS 時間差, (SHOW STATUS LIKE 'Com_commit') - prev_commits AS 當前提交總數(shù), ROUND((當前提交總數(shù) / 時間差.total_seconds), 2) AS TPS FROM (SELECT SHOW STATUS LIKE 'Com_commit' AS prev_commits) AS subquery, (SELECT NOW() - INTERVAL 5 SECOND AS time_diff) AS time_diff;
二、使用 Performance Schema(推薦)
1. 啟用 Performance Schema
-- 檢查是否已啟用 SHOW VARIABLES LIKE 'performance_schema'; -- 若未啟用,在 `my.cnf` 中添加: [mysqld] performance_schema = ON
2. 查詢 QPS(按語句類型統(tǒng)計)
SELECT event_name AS 查詢類型, COUNT_STAR AS 總執(zhí)行次數(shù), AVG_TIMER_WAIT AS 平均延遲(微秒), SUM_TIMER_WAIT AS 總延遲(微秒) FROM performance_schema.events_statements_summary_by_digest WHERE event_name IN ('SELECT', 'INSERT', 'UPDATE', 'DELETE') ORDER BY COUNT_STAR DESC;
3. 查詢 TPS(按事務提交統(tǒng)計)
SELECT event_name AS 事務類型, COUNT_STAR AS 提交次數(shù), AVG_TIMER_WAIT AS 平均延遲(微秒) FROM performance_schema.events_statements_summary_by_digest WHERE event_name = 'COMMIT';
三、使用 mysqladmin 工具
1. 查看實時狀態(tài)
# 查看所有關鍵狀態(tài)變量(包含 QPS 和 TPS) mysqladmin -u root -p status # 僅查看 QPS、TPS 和連接數(shù) mysqladmin -u root -p extended-status | grep -E 'Queries|Com_commit|Threads_connected'
四、使用 Prometheus + mysql_exporter
1. 部署 mysql_exporter
# 下載并運行 mysql_exporter wget https://github.com/prometheus/mysqld_exporter/releases/download/v1.16.0/mysqld_exporter-1.16.0.linux-amd64.tar.gz tar xvfz mysqld_exporter-*.tar.gz cd mysqld_exporter-*/ ./mysqld_exporter # Prometheus 配置(`prometheus.yml`) scrape_configs: - job_name: 'mysql' static_configs: - targets: ['localhost:9104']
2. 查詢 QPS 和 TPS
# QPS:所有查詢速率(每秒執(zhí)行次數(shù)) rate(mysql_statements_total{statement_type=~"SELECT|INSERT|UPDATE|DELETE"}[5m]) # TPS:事務提交速率(每秒提交次數(shù)) rate(mysql_statements_total{statement_type="COMMIT"}[5m])
3. 可視化儀表盤
- 在 Grafana 中添加 MySQL 監(jiān)控面板,展示實時 QPS、TPS、慢查詢等指標。
五、自動化腳本(Python 示例)
import mysql.connector import time def get_mysql_stats(host, user, password, db): conn = mysql.connector.connect( host=host, user=user, password=password, database=db ) cursor = conn.cursor() # 查詢 QPS cursor.execute("SHOW STATUS LIKE 'Queries'") queries_total = int(cursor.fetchone()[1]) # 查詢 TPS cursor.execute("SHOW STATUS LIKE 'Com_commit'") commits_total = int(cursor.fetchone()[1]) cursor.close() conn.close() return queries_total, commits_total if __name__ == "__main__": host = 'localhost' user = 'root' password = 'password' db = 'test' prev_queries, prev_commits = get_mysql_stats(host, user, password, db) while True: time.sleep(5) current_queries, current_commits = get_mysql_stats(host, user, password, db) qps = (current_queries - prev_queries) / 5 tps = (current_commits - prev_commits) / 5 print(f"[{time.strftime('%Y-%m-%d %H:%M:%S')}] QPS={qps:.2f}, TPS={tps:.2f}") prev_queries, prev_commits = current_queries, current_commits
六、其他工具
1. Percona Toolkit 的 pt-query-digest
# 分析慢查詢?nèi)罩静⒔y(tǒng)計 QPS/TPS pt-query-digest --slow-log=/var/log/mysql/slow.log --output=report
2. MySQL Workbench 的監(jiān)控面板
- 打開 Server Status 選項卡,直接查看 Queries per second 和 Transactions per second。
七、優(yōu)化建議
- 索引優(yōu)化:缺失索引會導致全表掃描,顯著降低 QPS。
- SQL 調(diào)優(yōu):避免復雜子查詢,使用
EXPLAIN
分析執(zhí)行計劃。 - 事務控制:減少長事務占用鎖資源,適當調(diào)整隔離級別。
- 配置調(diào)優(yōu):修改
innodb_buffer_pool_size
、query_cache_size
等參數(shù)。
總結
- 輕量級監(jiān)控:直接使用
SHOW STATUS
或mysqladmin
。 - 精細化分析:啟用
Performance Schema
或集成 Prometheus。 - 長期優(yōu)化:結合慢查詢?nèi)罩竞退饕治龉ぞ撸ㄈ?
EXPLAIN
)提升性能。
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。