Overview of Diagnostic Stored Procedures

The MySQL sys schema provides specialized stored procedures for performance analysis and SQL execution tracking. These tools enable database administrators to generate comprehensive system reports, monitor specific queries, and analyze thread-level activiteis without external monitoring tools.

Generating System-Wide Performance Reports

Using diagnostics() for Instance Health Assessment

The diagnostics() stored procedure produces a detailed health report of your MySQL instance, capturing metrics across multiple dimensions including configuration, status variables, and resource utilization.

-- Capture 90 seconds of metrics with 45-second sampling intervals
SET @output_file = '/var/lib/mysql-files/instance_health_report.txt';
SET @include_config = 'all';
SET @include_status = 'full';

CALL sys.diagnostics(@include_config, @include_status, 90, 45);

This procedure writes its output to the console, which you can redirect to a file for later analysis. The report includes InnoDB metrics, replication status, and memory allocation patterns.

Tracing Individual Query Performance

Monitoring Specific SQL Statements with ps_trace_statement_digest()

When troubleshooting slow or problematic queries, ps_trace_statement_digest() captures execution statistics using the query's digest hash. This is particularly useful for identifying performance regressions in production environments.

-- Generate digest for target query
SET @query_text = 'SELECT * FROM user_activity WHERE user_id = ?';
SET @hash_value = STATEMENT_DIGEST(@query_text);

-- Trace for 120 seconds with 50ms sampling intervals
-- Parameters: (digest, duration, interval, reset_stats, enable_consumers)
CALL sys.ps_trace_statement_digest(@hash_value, 120, 0.05, TRUE, TRUE);

The procedure outputs a timeline of wait events, I/O operations, and lock acquisitions that occured during query execution.

Comparative Workload Analysis

Creating Performance Snapshots with statement_performance_analyzer()

This procedure enables before-and-after analysis by capturing two snapshots of the Performance Schema's statement statistics and computing the delta between them.

-- Step 1: Isolate current session to avoid measurement interference
CALL sys.ps_setup_disable_thread(CONNECTION_ID());

-- Step 2: Prepare analysis schema
CREATE SCHEMA IF NOT EXISTS perf_analysis;

-- Step 3: Initialize snapshot storage
CALL sys.statement_performance_analyzer('create_table', 'perf_analysis.snapshot_store', NULL);

-- Step 4: Capture baseline snapshot
CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);

-- Step 5: Persist baseline
CALL sys.statement_performance_analyzer('store', 'perf_analysis.snapshot_store', NULL);

-- Step 6: Run workload simulation
DO SLEEP(45);
-- Execute your application load here: sysbench, custom scripts, etc.

-- Step 7: Capture post-workload snapshot
CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);

-- Step 8: Generate delta report
CALL sys.statement_performance_analyzer('delta', 'perf_analysis.snapshot_store', 'perf_analysis.delta_results');

-- Step 9: Cleanup temporary objects
CALL sys.statement_performance_analyzer('cleanup', NULL, NULL);
DROP TABLE IF EXISTS perf_analysis.snapshot_store;

The delta report highlights statements with the highest execution time increases, lock wait accumulation, and resource consumption changes.

Thread-Level Execution Profiling

Capturing Thread Activities via ps_trace_thread()

For analyzing complex stored procedures or multi-statement transactions, ps_trace_thread() records all activities of a specific connection thread, producing a visual execution graph.

-- Identify the thread to monitor
SELECT THREAD_ID INTO @target_thread
FROM performance_schema.threads
WHERE PROCESSLIST_ID = CONNECTION_ID();

-- Configure trace parameters
SET @trace_duration = 75;  -- seconds
SET @sample_interval = 0.5; -- seconds
SET @output_path = CONCAT('/tmp/thread_trace_', @target_thread, '_', UNIX_TIMESTAMP(), '.dot');

-- Initiate thread tracing
CALL sys.ps_trace_thread(
    @target_thread,
    @output_path,
    @trace_duration,
    @sample_interval,
    TRUE,   -- reset performance data before tracing
    TRUE,   -- enable required consumers automatically
    FALSE   -- disable debug mode
);

-- The .dot file can be rendered using Graphviz to visualize the execution timeline

This approach is invaluable for debugging application connection pools and stored procedure performance issues, as it captures every statement, wait event, and stage transition within the monitored thread.