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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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; |
