MySQL DBA Script – Monitoring MySQL Application (SQL) efficiency: Latency and Throughput

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;
About Shiv Iyer 501 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.