Troubleshooting MySQL performance for both Latency and Throughput (MySQL DBA Scripts)

Overview

Being a full-time MySQL Performance Engineer, the most important part of my job is troubleshooting MySQL performance using the relevant matrix for Root Cause Analysis of performance bottlenecks. In MySQL, The Performance_Schema and SYS are a plethora of data useful for the performance health checks, forensics and diagnostics of Database Infrastructure Operations. In this post, I wanted to identify the most expensive SQLs in MySQL by Memory (RAM) consumption. I also wanted to understand how MySQL locks and wait events influence Response Time/Latency and Throughput, so I included details like LOCK TYPE, LOCK MODE, LOCKED DATA, etc.

MySQL DBA Script for troubleshooting SQL performance (latency & throughput), Also understanding how MySQL Locks and Wait Events are influencing performance:

select
    sys.format_time(esh.TIMER_START) AS TIMER_START,
    sys.format_time(esh.TIMER_END) AS TIMER_END,
    substring(h.SQL_TEXT,1,60) AS SQL_TEXT,
    eshl.SOURCE AS SQL_SOURCE,
    e.CPU_TIME AS CPU_TIME,
    essbd.MAX_TOTAL_MEMORY AS TOTAL_MEMORY,
    essbd.SUM_ROWS_EXAMINED AS SUM_OF_ROWS_EXAMINED,
    essbd.SUM_ROWS_SENT AS SUM_OF_ROWS_SENT,
    essbtben.SUM_SORT_ROWS AS TOTAL_NUMBER_OF_SORT_OPERATIONS,
    essbtben.SUM_SORT_SCAN AS TOTAL_NUMBER_OF_SORT_SCANS,
    date_format(essbd.FIRST_SEEN, '%d/%m/%Y') AS FIRST_SEEN,
    date_format(essbd.LAST_SEEN,'%d%m%y') AS LAST_SEEN,
    dl.LOCK_TYPE AS LOCK_TYPE,
    dl.LOCK_MODE AS LOCK_MODE,
    dl.LOCK_DATA AS DATA_LOCKED,
    dl.LOCK_STATUS AS LOCK_STATUS,
    essbtben.SUM_LOCK_TIME AS SUM_LOCK_TIME,
    h.EVENT_NAME AS EVENT_NAME,
    esc.TIMER_WAIT AS WAIT_TIME,
    sys.format_time(essbp.SUM_TIMER_WAIT) AS TIMER_WAIT,
    essbp.MAX_STATEMENTS_WAIT AS MAXIMUM_STATEMENTS_WAIT
from performance_schema.events_stages_history esh
join performance_schema.data_locks dl on esh.THREAD_ID = dl.THREAD_ID and esh.EVENT_ID = dl.EVENT_ID
join performance_schema.events_stages_history_long eshl on dl.THREAD_ID = eshl.THREAD_ID
join performance_schema.events_statements_history e on eshl.EVENT_ID = e.EVENT_ID
join performance_schema.events_stages_current esc on dl.EVENT_ID = esc.EVENT_ID
join performance_schema.events_statements_history h on esc.EVENT_ID = h.EVENT_ID
join performance_schema.events_statements_summary_by_thread_by_event_name essbtben on esh.THREAD_ID = essbtben.THREAD_ID
join performance_schema.events_statements_summary_by_digest essbd on e.MAX_CONTROLLED_MEMORY = essbd.MAX_CONTROLLED_MEMORY
JOIN performance_schema.events_statements_summary_by_program essbp on essbd.SUM_CPU_TIME = essbp.SUM_CPU_TIME
join performance_schema.events_stages_history_long l on esc.END_EVENT_ID = l.END_EVENT_ID
order by TOTAL_MEMORY desc;