Forum Topic

Thanks for your participation and contribution.


Back to MySQL Queries

Performance Troubleshooting Queries

Post a reply
361 views
i90runner1
i90runner1
SELECT VERSION();

SHOW VARIABLES LIKE 'performance_schema';

SELECT * 
FROM INFORMATION_SCHEMA.ENGINES
    WHERE ENGINE='PERFORMANCE_SCHEMA';

SHOW TABLES FROM performance_schema;    

USE performance_schema;

SELECT * 
FROM setup_timers;

-- Find Query with High Execution Time
SELECT DIGEST_TEXT AS query,
       COUNT_STAR AS exec_count,
       SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000) AS exec_time_total,
       SEC_TO_TIME(MAX_TIMER_WAIT/1000000000000) AS exec_time_max,
       (AVG_TIMER_WAIT/1000000000) AS exec_time_avg_ms,
       SUM_ROWS_SENT AS rows_sent,
       SUM_ROWS_EXAMINED AS rows_scanned
  FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC

Last updated

i90runner1
i90runner1
-- User IO SQL

SELECT 
    IF(ISNULL(performance_schema.threads.PROCESSLIST_ID),
        SUBSTRING_INDEX(performance_schema.threads.NAME,
                '/',
                -(1)),
        CONCAT(performance_schema.threads.PROCESSLIST_USER,
                '@',
                performance_schema.threads.PROCESSLIST_HOST)) AS user,
    SUM(performance_schema.events_waits_summary_by_thread_by_event_name.COUNT_STAR)/1000000 AS total_sec,
    SUM(performance_schema.events_waits_summary_by_thread_by_event_name.SUM_TIMER_WAIT)/1000000 AS total_latency_sec,
    MIN(performance_schema.events_waits_summary_by_thread_by_event_name.MIN_TIMER_WAIT)/1000000 AS min_latency_sec,
    AVG(performance_schema.events_waits_summary_by_thread_by_event_name.AVG_TIMER_WAIT)/1000000 AS avg_latency_sec,
    MAX(performance_schema.events_waits_summary_by_thread_by_event_name.MAX_TIMER_WAIT)/1000000 AS max_latency_sec
FROM
    (performance_schema.events_waits_summary_by_thread_by_event_name
    LEFT JOIN performance_schema.threads ON ((performance_schema.events_waits_summary_by_thread_by_event_name.THREAD_ID = performance_schema.threads.THREAD_ID)))
WHERE
    ((performance_schema.events_waits_summary_by_thread_by_event_name.EVENT_NAME LIKE 'wait/io/file/%')
        AND (performance_schema.events_waits_summary_by_thread_by_event_name.SUM_TIMER_WAIT > 0))
GROUP BY user
ORDER BY SUM(performance_schema.events_waits_summary_by_thread_by_event_name.SUM_TIMER_WAIT) DESC;
i90runner1
i90runner1

-- File IO SQL


SELECT 
    IF(ISNULL(performance_schema.threads.PROCESSLIST_ID),
        SUBSTRING_INDEX(performance_schema.threads.NAME,
                '/',
                -(1)),
        CONCAT(performance_schema.threads.PROCESSLIST_USER,
                '@',
                performance_schema.threads.PROCESSLIST_HOST)) AS user,
    SUM(performance_schema.events_waits_summary_by_thread_by_event_name.COUNT_STAR)/1000000 AS total_sec,
    SUM(performance_schema.events_waits_summary_by_thread_by_event_name.SUM_TIMER_WAIT)/1000000 AS total_latency_sec,
    MIN(performance_schema.events_waits_summary_by_thread_by_event_name.MIN_TIMER_WAIT)/1000000 AS min_latency_sec,
    AVG(performance_schema.events_waits_summary_by_thread_by_event_name.AVG_TIMER_WAIT)/1000000 AS avg_latency_sec,
    MAX(performance_schema.events_waits_summary_by_thread_by_event_name.MAX_TIMER_WAIT)/1000000 AS max_latency_sec
FROM
    (performance_schema.events_waits_summary_by_thread_by_event_name
    LEFT JOIN performance_schema.threads ON ((performance_schema.events_waits_summary_by_thread_by_event_name.THREAD_ID = performance_schema.threads.THREAD_ID)))
WHERE
    ((performance_schema.events_waits_summary_by_thread_by_event_name.EVENT_NAME LIKE 'wait/io/file/%')
        AND (performance_schema.events_waits_summary_by_thread_by_event_name.SUM_TIMER_WAIT > 0))
GROUP BY user
ORDER BY SUM(performance_schema.events_waits_summary_by_thread_by_event_name.SUM_TIMER_WAIT) DESC;
i90runner1
i90runner1

-- Frequent Queries

SELECT
    performance_schema.events_statements_summary_by_digest.DIGEST_TEXT AS query,
    IF(((performance_schema.events_statements_summary_by_digest.SUM_NO_GOOD_INDEX_USED > 0)
            OR (performance_schema.events_statements_summary_by_digest.SUM_NO_INDEX_USED > 0)),
        'Full Scan',
        '') AS full_scan,
    performance_schema.events_statements_summary_by_digest.COUNT_STAR AS exec_count,
    ROUND(IFNULL((performance_schema.events_statements_summary_by_digest.SUM_ROWS_SENT / NULLIF(performance_schema.events_statements_summary_by_digest.COUNT_STAR,
                            0)),
                    0),
            0) AS rows_sent_avg,
    ROUND(IFNULL((performance_schema.events_statements_summary_by_digest.SUM_ROWS_EXAMINED / NULLIF(performance_schema.events_statements_summary_by_digest.COUNT_STAR,
                            0)),
                    0),
            0) AS rows_examined_avg,
    ROUND(IFNULL((performance_schema.events_statements_summary_by_digest.SUM_ROWS_AFFECTED / NULLIF(performance_schema.events_statements_summary_by_digest.COUNT_STAR,
                            0)),
                    0),
            0) AS rows_affected_avg,
    performance_schema.events_statements_summary_by_digest.AVG_TIMER_WAIT AS avg_latency,
    performance_schema.events_statements_summary_by_digest.SUM_CREATED_TMP_TABLES AS tmp_tables,
    performance_schema.events_statements_summary_by_digest.SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables,
    performance_schema.events_statements_summary_by_digest.SUM_SORT_ROWS AS rows_sorted,
    performance_schema.events_statements_summary_by_digest.LAST_SEEN AS last_seen
FROM
    performance_schema.events_statements_summary_by_digest
ORDER BY exec_count DESC
i90runner1
i90runner1

-- Long Running Queries

SELECT
    performance_schema.events_statements_summary_by_digest.DIGEST_TEXT AS query,
    IF(((performance_schema.events_statements_summary_by_digest.SUM_NO_GOOD_INDEX_USED > 0)
            OR (performance_schema.events_statements_summary_by_digest.SUM_NO_INDEX_USED > 0)),
        'Full Scan',
        '') AS full_scan,
    performance_schema.events_statements_summary_by_digest.COUNT_STAR AS exec_count,
    ROUND(IFNULL((performance_schema.events_statements_summary_by_digest.SUM_ROWS_SENT / NULLIF(performance_schema.events_statements_summary_by_digest.COUNT_STAR,
                            0)),
                    0),
            0) AS rows_sent_avg,
    ROUND(IFNULL((performance_schema.events_statements_summary_by_digest.SUM_ROWS_EXAMINED / NULLIF(performance_schema.events_statements_summary_by_digest.COUNT_STAR,
                            0)),
                    0),
            0) AS rows_examined_avg,
    ROUND(IFNULL((performance_schema.events_statements_summary_by_digest.SUM_ROWS_AFFECTED / NULLIF(performance_schema.events_statements_summary_by_digest.COUNT_STAR,
                            0)),
                    0),
            0) AS rows_affected_avg,
    performance_schema.events_statements_summary_by_digest.SUM_TIMER_WAIT AS total_latency,
    performance_schema.events_statements_summary_by_digest.MAX_TIMER_WAIT AS max_latency,
    performance_schema.events_statements_summary_by_digest.AVG_TIMER_WAIT AS avg_latency,
    performance_schema.events_statements_summary_by_digest.SUM_CREATED_TMP_TABLES AS tmp_tables,
    performance_schema.events_statements_summary_by_digest.SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables,
    performance_schema.events_statements_summary_by_digest.SUM_SORT_ROWS AS rows_sorted,
    performance_schema.events_statements_summary_by_digest.LAST_SEEN AS last_seen
FROM
    performance_schema.events_statements_summary_by_digest
ORDER BY MAX_TIMER_WAIT DESC
i90runner1
i90runner1

-- Temp Tables Query

SELECT 
    performance_schema.events_statements_summary_by_digest.DIGEST_TEXT AS query,
    IF(((performance_schema.events_statements_summary_by_digest.SUM_NO_GOOD_INDEX_USED > 0)
        OR (performance_schema.events_statements_summary_by_digest.SUM_NO_INDEX_USED > 0)),
    'Full Scan',
    '') AS full_scan,
    performance_schema.events_statements_summary_by_digest.COUNT_STAR AS exec_count,
    performance_schema.events_statements_summary_by_digest.SUM_TIMER_WAIT AS total_latency,
    performance_schema.events_statements_summary_by_digest.AVG_TIMER_WAIT AS avg_latency,
    performance_schema.events_statements_summary_by_digest.SUM_CREATED_TMP_TABLES AS memory_tmp_tables,
    performance_schema.events_statements_summary_by_digest.SUM_CREATED_TMP_DISK_TABLES AS disk_tmp_tables,
    ROUND(IFNULL((performance_schema.events_statements_summary_by_digest.SUM_CREATED_TMP_TABLES / NULLIF(performance_schema.events_statements_summary_by_digest.COUNT_STAR,
                            0)),
                    0),
            0) AS avg_tmp_tables_per_query,
    ROUND((IFNULL((performance_schema.events_statements_summary_by_digest.SUM_CREATED_TMP_DISK_TABLES / NULLIF(performance_schema.events_statements_summary_by_digest.SUM_CREATED_TMP_TABLES,
                            0)),
                    0) * 100),
            0) AS tmp_tables_to_disk_pct,
    performance_schema.events_statements_summary_by_digest.LAST_SEEN AS last_seen
FROM
    performance_schema.events_statements_summary_by_digest
WHERE
    (performance_schema.events_statements_summary_by_digest.SUM_CREATED_TMP_TABLES > 0)
ORDER BY performance_schema.events_statements_summary_by_digest.SUM_CREATED_TMP_DISK_TABLES DESC, 
            performance_schema.events_statements_summary_by_digest.SUM_CREATED_TMP_TABLES DESC

1-6 of 6

Reply to this discussion

You cannot edit posts or make replies: You should be logged in before you can post.