-- 獲取當(dāng)前總查詢(xún)次數(shù)（所有類(lèi)型）
SHOW STATUS LIKE 'Queries';

-- 計(jì)算過(guò)去 N 秒的 QPS（例如 N=5）
SELECT 
    (NOW() - INTERVAL 5 SECOND) AS 時(shí)間差,  
    (SHOW STATUS LIKE 'Queries') - prev_queries AS 當(dāng)前查詢(xún)總數(shù),
    ROUND((當(dāng)前查詢(xún)總數(shù) / 時(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;

-- 獲取當(dāng)前事務(wù)提交次數(shù)
SHOW STATUS LIKE 'Com_commit';

-- 計(jì)算過(guò)去 N 秒的 TPS（例如 N=5）
SELECT 
    (NOW() - INTERVAL 5 SECOND) AS 時(shí)間差,  
    (SHOW STATUS LIKE 'Com_commit') - prev_commits AS 當(dāng)前提交總數(shù),
    ROUND((當(dāng)前提交總數(shù) / 時(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;

-- 檢查是否已啟用
SHOW VARIABLES LIKE 'performance_schema';

-- 若未啟用，在 `my.cnf` 中添加：
[mysqld]
performance_schema = ON

SELECT 
    event_name       AS 查詢(xún)類(lèi)型,  
    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;

SELECT 
    event_name       AS 事務(wù)類(lèi)型,  
    COUNT_STAR        AS 提交次數(shù),  
    AVG_TIMER_WAIT    AS 平均延遲（微秒）
FROM 
    performance_schema.events_statements_summary_by_digest
WHERE 
    event_name = 'COMMIT';

# 查看所有關(guān)鍵狀態(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'

# 下載并運(yùn)行 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']

# QPS：所有查詢(xún)速率（每秒執(zhí)行次數(shù)）
rate(mysql_statements_total{statement_type=~"SELECT|INSERT|UPDATE|DELETE"}[5m])

# TPS：事務(wù)提交速率（每秒提交次數(shù)）
rate(mysql_statements_total{statement_type="COMMIT"}[5m])

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()
    
    # 查詢(xún) QPS
    cursor.execute("SHOW STATUS LIKE 'Queries'")
    queries_total = int(cursor.fetchone()[1])
    
    # 查詢(xún) 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

# 分析慢查詢(xún)?nèi)罩静⒔y(tǒng)計(jì) QPS/TPS
pt-query-digest --slow-log=/var/log/mysql/slow.log --output=report