Forum Topic

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 4 years ago

-- 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;

-- 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;

-- 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

-- 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

-- 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

Memory usage

https://www.percona.com/blog/2014/01/24/mysql-server-memory-usage-2/

use performance_schema;
select event_name,current_alloc from sys.memory_global_by_current_bytes limit 10;

use information_schema;

use sys;
select * from memory_by_host_by_current_bytes;

Last updated 1 year ago

Memory Related

select * from sys.memory_global_by_current_bytes limit 10;
select thread_id,user,current_allocated from memory_by_thread_by_current_bytes limit 5;

IO Latency

 show variables where variable_name like '%innodb%';

 show variables where variable_name like '%io%';

use sys;
select * from host_summary_by_file_io;

Sampling Issues

https://www.percona.com/blog/correcting-mysql-inaccurate-table-statistics-for-better-execution-plan/

 show variables like '%persistent_sample%';

Query Store

https://docs.microsoft.com/en-us/azure/mysql/single-server/concepts-query-store#information-in-query-store

 select * from mysql.query_store_wait_stats;

 SELECT * FROM mysql.query_store
 where query_id=7253;

Kill Process in Azure System user

show processlist;
kill 2
13:25:25    kill 2  Error Code: 1095. You are not owner of thread 2 0.041 sec
CALL mysql.az_kill(2);