MySQL data, query QPS, TPS data method

-- Get the current total number of queries（All types）
SHOW STATUS LIKE 'Queries';

-- Calculate the past N In seconds QPS（For example N=5）
SELECT 
    (NOW() - INTERVAL 5 SECOND) AS Time difference,  
    (SHOW STATUS LIKE 'Queries') - prev_queries AS The total number of current queries,
    ROUND((The total number of current queries / Time difference.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;

-- Get the current transaction commits
SHOW STATUS LIKE 'Com_commit';

-- Calculate the past N In seconds TPS（For example N=5）
SELECT 
    (NOW() - INTERVAL 5 SECOND) AS Time difference,  
    (SHOW STATUS LIKE 'Com_commit') - prev_commits AS The current total number of submissions,
    ROUND((The current total number of submissions / Time difference.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;

-- Check if it is enabled
SHOW VARIABLES LIKE 'performance_schema';

-- If not enabled，exist `` Added in：
[mysqld]
performance_schema = ON

SELECT 
    event_name       AS Query Type,  
    COUNT_STAR        AS Total number of executions,  
    AVG_TIMER_WAIT    AS Average delay（Microseconds）,  
    SUM_TIMER_WAIT    AS Total delay（Microseconds）
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 Transaction type,  
    COUNT_STAR        AS Number of submissions,  
    AVG_TIMER_WAIT    AS Average delay（Microseconds）
FROM 
    performance_schema.events_statements_summary_by_digest
WHERE 
    event_name = 'COMMIT';

# View all key state variables (including QPS and TPS)mysqladmin -u root -p status

# View only QPS, TPS, and connection countmysqladmin -u root -p extended-status | grep -E 'Queries|Com_commit|Threads_connected'

# Download and run mysql_exporterwget /prometheus/mysqld_exporter/releases/download/v1.16.0/mysqld_exporter-1.16.
tar xvfz mysqld_exporter-*.
cd mysqld_exporter-*/
./mysqld_exporter

# Prometheus configuration (``)scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']

# QPS: All query rates (number of executions per second)rate(mysql_statements_total{statement_type=~"SELECT|INSERT|UPDATE|DELETE"}[5m])

# TPS: Transaction commit rate (number of commits per second)rate(mysql_statements_total{statement_type="COMMIT"}[5m])

import 
import time

def get_mysql_stats(host, user, password, db):
    conn = (
        host=host,
        user=user,
        password=password,
        database=db
    )
    cursor = ()
    
    # Query QPS    ("SHOW STATUS LIKE 'Queries'")
    queries_total = int(()[1])
    
    # Query TPS    ("SHOW STATUS LIKE 'Com_commit'")
    commits_total = int(()[1])
    
    ()
    ()
    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:
        (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"[{('%Y-%m-%d %H:%M:%S')}] QPS={qps:.2f}, TPS={tps:.2f}")
        
        prev_queries, prev_commits = current_queries, current_commits

# Analyze slow query logs and count QPS/TPSpt-query-digest --slow-log=/var/log/mysql/ --output=report