MySQL Performance Schema Monitoring

MySQL Performance Schema Monitoring

The Performance Schema is MySQL's built-in instrumentation framework that provides detailed insights into server execution at runtime. It captures performance data without significantly impacting server performance, making it the go-to tool for monitoring and troubleshooting MySQL deployments.

🎯 What is Performance Schema?

Performance Schema collects data about server events, providing visibility into:

💡 Key Benefit: Performance Schema provides real-time performance data with minimal overhead (typically 5-10%), unlike the slow query log which only captures queries exceeding a threshold.

🔧 Enabling and Configuring Performance Schema

Check Current Status

-- Check if Performance Schema is enabled
SHOW VARIABLES LIKE 'performance_schema';

-- Check Performance Schema sizing
SHOW VARIABLES LIKE 'performance_schema_%';

-- View enabled instruments
SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_instruments
WHERE NAME LIKE '%statement%'
LIMIT 20;

-- View enabled consumers
SELECT * FROM performance_schema.setup_consumers;

Configuration

# /etc/mysql/mysql.conf.d/performance_schema.cnf

[mysqld]
# Enable Performance Schema (default: ON in MySQL 5.7+)
performance_schema = ON

# Memory allocation settings
performance_schema_events_statements_history_size = 100
performance_schema_events_statements_history_long_size = 10000
performance_schema_events_waits_history_size = 20
performance_schema_events_waits_history_long_size = 15000

# Digest sizing
performance_schema_digests_size = 25000
performance_schema_max_digest_length = 4096

# Statement truncation
performance_schema_max_sql_text_length = 4096

Enable/Disable Specific Instruments

-- Enable all statement instruments
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

-- Enable wait instruments
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/%';

-- Enable history consumers
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%history%';

-- Persist settings (MySQL 8.0+)
SET PERSIST performance_schema = ON;

📊 Essential Monitoring Queries

Top Queries by Execution Time

-- Find slowest queries by total time
SELECT
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT/1000000000000, 4) AS total_time_sec,
    ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_time_ms,
    ROUND(MAX_TIMER_WAIT/1000000000, 2) AS max_time_ms,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent,
    ROUND(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
    AND DIGEST_TEXT NOT LIKE 'SHOW%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

Queries with Full Table Scans

-- Queries doing full table scans
SELECT
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    SUM_NO_INDEX_USED AS no_index_count,
    SUM_NO_GOOD_INDEX_USED AS no_good_index_count,
    ROUND(SUM_TIMER_WAIT/1000000000000, 4) AS total_time_sec,
    SUM_ROWS_EXAMINED AS rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE (SUM_NO_INDEX_USED > 0 OR SUM_NO_GOOD_INDEX_USED > 0)
    AND SCHEMA_NAME IS NOT NULL
ORDER BY SUM_NO_INDEX_USED DESC
LIMIT 20;

Queries with Temporary Tables

-- Queries creating temporary tables (potential optimization targets)
SELECT
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    SUM_CREATED_TMP_TABLES AS tmp_tables,
    SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables,
    ROUND(SUM_CREATED_TMP_DISK_TABLES / NULLIF(SUM_CREATED_TMP_TABLES, 0) * 100, 2) AS disk_tmp_pct,
    ROUND(SUM_TIMER_WAIT/1000000000000, 4) AS total_time_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 20;

Queries with Errors

-- Queries generating errors
SELECT
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    SUM_ERRORS AS error_count,
    SUM_WARNINGS AS warning_count,
    ROUND(SUM_ERRORS / NULLIF(COUNT_STAR, 0) * 100, 2) AS error_pct
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_ERRORS > 0
ORDER BY SUM_ERRORS DESC
LIMIT 20;

🔍 Wait Analysis

Top Wait Events

-- What is MySQL waiting on?
SELECT
    EVENT_NAME,
    COUNT_STAR AS wait_count,
    ROUND(SUM_TIMER_WAIT/1000000000000, 4) AS total_wait_sec,
    ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_wait_ms,
    ROUND(MAX_TIMER_WAIT/1000000000, 2) AS max_wait_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
    AND EVENT_NAME != 'idle'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

I/O Waits by File

-- Which files have the most I/O wait?
SELECT
    FILE_NAME,
    EVENT_NAME,
    COUNT_STAR AS io_count,
    ROUND(SUM_TIMER_WAIT/1000000000000, 4) AS total_wait_sec,
    ROUND(SUM_NUMBER_OF_BYTES_READ / 1024 / 1024, 2) AS read_mb,
    ROUND(SUM_NUMBER_OF_BYTES_WRITE / 1024 / 1024, 2) AS write_mb
FROM performance_schema.file_summary_by_instance
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

Lock Waits

-- Current lock waits
SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_TRANSACTION_ID
JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_TRANSACTION_ID;

-- Lock wait summary
SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_STAR AS lock_count,
    ROUND(SUM_TIMER_WAIT/1000000000000, 4) AS total_wait_sec
FROM performance_schema.table_lock_waits_summary_by_table
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

📈 Connection and Thread Analysis

Connection Statistics

-- Connections by user
SELECT
    USER,
    HOST,
    CURRENT_CONNECTIONS,
    TOTAL_CONNECTIONS,
    ROUND(MAX_SESSION_CONNECT_ATTRS_SIZE / 1024, 2) AS max_attrs_kb
FROM performance_schema.accounts
WHERE TOTAL_CONNECTIONS > 0
ORDER BY CURRENT_CONNECTIONS DESC;

-- Connection errors
SELECT *
FROM performance_schema.host_cache
WHERE COUNT_ERRORS > 0;

Thread States

-- What are threads doing right now?
SELECT
    THREAD_ID,
    NAME AS thread_name,
    TYPE,
    PROCESSLIST_ID,
    PROCESSLIST_USER AS user,
    PROCESSLIST_HOST AS host,
    PROCESSLIST_DB AS db,
    PROCESSLIST_COMMAND AS command,
    PROCESSLIST_STATE AS state,
    PROCESSLIST_TIME AS time_sec,
    LEFT(PROCESSLIST_INFO, 100) AS query_preview
FROM performance_schema.threads
WHERE TYPE = 'FOREGROUND'
    AND PROCESSLIST_ID IS NOT NULL
ORDER BY PROCESSLIST_TIME DESC;

Active Queries

-- Currently executing queries
SELECT
    ps.ID AS process_id,
    ps.USER,
    ps.HOST,
    ps.DB,
    ps.COMMAND,
    ps.TIME AS running_seconds,
    ps.STATE,
    LEFT(ps.INFO, 200) AS query,
    t.PROCESSLIST_STATE AS detailed_state
FROM information_schema.PROCESSLIST ps
LEFT JOIN performance_schema.threads t
    ON ps.ID = t.PROCESSLIST_ID
WHERE ps.COMMAND != 'Sleep'
    AND ps.INFO IS NOT NULL
ORDER BY ps.TIME DESC;

🔧 Memory Analysis

Memory Usage by Code Area

-- Memory allocation by component
SELECT
    SUBSTRING_INDEX(EVENT_NAME, '/', 2) AS code_area,
    ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED) / 1024 / 1024, 2) AS current_mb,
    ROUND(SUM(HIGH_NUMBER_OF_BYTES_USED) / 1024 / 1024, 2) AS high_water_mb
FROM performance_schema.memory_summary_global_by_event_name
GROUP BY SUBSTRING_INDEX(EVENT_NAME, '/', 2)
HAVING current_mb > 1
ORDER BY current_mb DESC;

Memory by Connection

-- Memory usage per connection
SELECT
    t.PROCESSLIST_USER AS user,
    t.PROCESSLIST_HOST AS host,
    ROUND(SUM(m.CURRENT_NUMBER_OF_BYTES_USED) / 1024 / 1024, 2) AS current_mb
FROM performance_schema.memory_summary_by_thread_by_event_name m
JOIN performance_schema.threads t ON m.THREAD_ID = t.THREAD_ID
WHERE t.TYPE = 'FOREGROUND'
GROUP BY t.PROCESSLIST_USER, t.PROCESSLIST_HOST
ORDER BY current_mb DESC;

📋 Creating Monitoring Views

Comprehensive Query Statistics View

CREATE VIEW v_query_stats AS
SELECT
    SUBSTRING(DIGEST_TEXT, 1, 100) AS query_sample,
    COUNT_STAR AS executions,
    ROUND(SUM_TIMER_WAIT/1000000000000, 4) AS total_time_sec,
    ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_ms,
    ROUND(MAX_TIMER_WAIT/1000000000, 2) AS max_ms,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_returned,
    ROUND(SUM_ROWS_EXAMINED / NULLIF(SUM_ROWS_SENT, 0), 0) AS exam_to_sent_ratio,
    SUM_CREATED_TMP_DISK_TABLES AS disk_tmp_tables,
    SUM_NO_INDEX_USED AS full_scans,
    SCHEMA_NAME AS schema_name
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
    AND COUNT_STAR > 0;

System Health View

CREATE VIEW v_system_health AS
SELECT
    'Queries Executed' AS metric,
    SUM(COUNT_STAR) AS value
FROM performance_schema.events_statements_summary_global_by_event_name
UNION ALL
SELECT
    'Avg Query Time (ms)',
    ROUND(AVG(AVG_TIMER_WAIT)/1000000000, 2)
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE COUNT_STAR > 0
UNION ALL
SELECT
    'Full Table Scans',
    SUM(SUM_NO_INDEX_USED)
FROM performance_schema.events_statements_summary_by_digest
UNION ALL
SELECT
    'Temp Tables on Disk',
    SUM(SUM_CREATED_TMP_DISK_TABLES)
FROM performance_schema.events_statements_summary_by_digest
UNION ALL
SELECT
    'Current Connections',
    COUNT(*)
FROM performance_schema.threads
WHERE TYPE = 'FOREGROUND' AND PROCESSLIST_ID IS NOT NULL;

🚀 Automated Monitoring Script

-- Procedure to capture performance snapshot
DELIMITER //

CREATE PROCEDURE sp_capture_performance_snapshot()
BEGIN
    -- Create snapshot table if not exists
    CREATE TABLE IF NOT EXISTS performance_snapshots (
        id BIGINT AUTO_INCREMENT PRIMARY KEY,
        snapshot_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        digest VARCHAR(64),
        query_sample TEXT,
        exec_count BIGINT,
        total_time_sec DECIMAL(15,4),
        avg_time_ms DECIMAL(15,2),
        rows_examined BIGINT,
        rows_sent BIGINT,
        tmp_disk_tables BIGINT,
        full_scans BIGINT,
        INDEX idx_time (snapshot_time),
        INDEX idx_digest (digest)
    );

    -- Insert current snapshot
    INSERT INTO performance_snapshots (
        digest, query_sample, exec_count, total_time_sec,
        avg_time_ms, rows_examined, rows_sent, tmp_disk_tables, full_scans
    )
    SELECT
        DIGEST,
        LEFT(DIGEST_TEXT, 500),
        COUNT_STAR,
        ROUND(SUM_TIMER_WAIT/1000000000000, 4),
        ROUND(AVG_TIMER_WAIT/1000000000, 2),
        SUM_ROWS_EXAMINED,
        SUM_ROWS_SENT,
        SUM_CREATED_TMP_DISK_TABLES,
        SUM_NO_INDEX_USED
    FROM performance_schema.events_statements_summary_by_digest
    WHERE COUNT_STAR > 10
        AND SCHEMA_NAME IS NOT NULL;

    -- Cleanup old snapshots (keep 7 days)
    DELETE FROM performance_snapshots
    WHERE snapshot_time < DATE_SUB(NOW(), INTERVAL 7 DAY);
END //

DELIMITER ;

-- Schedule the snapshot
CREATE EVENT evt_performance_snapshot
    ON SCHEDULE EVERY 1 HOUR
    ON COMPLETION PRESERVE
DO
    CALL sp_capture_performance_snapshot();

⚠️ Troubleshooting with Performance Schema

Find Long-Running Queries

-- Queries running longer than 60 seconds
SELECT
    ps.ID,
    ps.USER,
    ps.HOST,
    ps.DB,
    ps.TIME AS seconds_running,
    ps.STATE,
    ps.INFO AS query
FROM information_schema.PROCESSLIST ps
WHERE ps.COMMAND != 'Sleep'
    AND ps.TIME > 60
ORDER BY ps.TIME DESC;

Identify Connection Issues

-- Connection errors by host
SELECT
    IP,
    HOST,
    COUNT_HANDSHAKE_ERRORS,
    COUNT_AUTHENTICATION_ERRORS,
    COUNT_MAX_USER_CONNECTIONS_ERRORS,
    FIRST_ERROR_SEEN,
    LAST_ERROR_SEEN
FROM performance_schema.host_cache
WHERE COUNT_ERRORS > 0;

Reset Statistics

-- Reset statement statistics
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

-- Reset wait statistics
TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name;

-- Reset all statistics
CALL sys.ps_truncate_all_tables(FALSE);

✅ Best Practices Checklist

Configuration

• [ ] Enable Performance Schema in production
• [ ] Configure appropriate history sizes
• [ ] Enable relevant instruments for your needs
• [ ] Monitor Performance Schema memory usage

Monitoring

• [ ] Track top queries regularly
• [ ] Alert on high wait times
• [ ] Monitor for full table scans
• [ ] Review temporary table usage

Operations

• [ ] Reset statistics after major changes
• [ ] Capture snapshots for trending
• [ ] Correlate with application metrics
• [ ] Document baseline performance

🎓 Conclusion

Performance Schema is an indispensable tool for MySQL monitoring and optimization. By understanding its capabilities and implementing proper monitoring queries, you can quickly identify and resolve performance bottlenecks in your database.

💡 Pro Tip: Combine Performance Schema data with the sys schema views for easier analysis. The sys schema provides user-friendly formatting of Performance Schema data.

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in MySQL, SQL optimization, and enterprise database solutions.

Related Articles

• MySQL Slow Query Log Analysis
• MySQL Index Optimization Strategies
• MySQL Query Optimization Expert Tips

Need help with MySQL performance monitoring? Contact us for expert database consulting.