Troubleshooting MySQL Thread Performance – MySQL DBA Scripts Series(Tricks and Techniques)

Overview

This post is about how we can troubleshoot MySQL Thread performance intuitively.  Several folks have asked me by now about how I do a detailed Performance Audit of MySQL Thread Operations, so I thought would share the details and script I use. Technically, Threads in MySQL are independent functionally as MySQL doesn’t have its Thread operations mechanism of the underlying Operating System. Whenever a user connects with MySQL, a user thread is created within mysqld for the user to execute the queries, and this connection remains till the user disconnects with MySQL Server. The connection management from MySQL client to Server happens via a simple TCP-IP connect message sent to port 3306 on the MySQL server host machine. But, How can we proactively address performance bottlenecks before you hit the thread concurrency bottleneck?  In this blog, I have shared the matrices recommended to troubleshoot MySQL Thread performance more effectively.

What you should know to troubleshoot MySQL Thread performance?

• How expensive are MySQL Threads?
• SQL efficiency 
• System Resource Distribution for Threads 
• How do MySQL Locks and Wait Events influence Query Performance? 
• MySQL Transaction Isolation Level Metadata
• Cost of MySQL Application Performance 
• MySQL Capacity Utilization 
• MySQL DBA Checklist for monitoring concurrency level and throughput
• Trending Data Load Distribution from MySQL Threads

MySQL DBA Script for Troubleshooting Thread Performance 

select
    t.THREAD_ID as Thread_ID,
    t.PARENT_THREAD_ID as Parent_Thread_ID,
    t.PROCESSLIST_DB as Default_Database,
    SUBSTRING(esc.SQL_TEXT,1,65) as SQL_Text,
    sys.format_time(esc.TIMER_START) as Timer_Start,
    sys.format_time(esc.TIMER_END) as Timer_End,
    t.TOTAL_MEMORY as Total_Memory,
    esc.CPU_TIME as Time_Spent_on_CPU_for_current_thread,
    esc.ROWS_EXAMINED as Total_Rows_Examined,
    esc.ROWS_SENT as Total_Rows_Sent,
    (esc.ROWS_EXAMINED/esc.ROWS_SENT) as Cost_To_Data_Access,
    etc.STATE as Curent_State,
    etc.ISOLATION_LEVEL as Transaction_Isolation_Level,
    etc.ACCESS_MODE as Access_Mode,
    sys.format_time(esc.LOCK_TIME) as Time_Spent_Waiting_for_Table_Locks,
    ewc.SPINS as number_of_spin_round_for_a_Mutex,
    dl.LOCK_STATUS as Lock_Status,
    dl.LOCK_DATA as Data_Locked,
    dl.LOCK_MODE as Lock_Mode,
from performance_schema.threads t
join events_statements_current esc on t.THREAD_ID = esc.THREAD_ID
join events_stages_current e on t.THREAD_ID = e.THREAD_ID
join events_transactions_current etc on e.THREAD_ID = etc.THREAD_ID
join events_waits_current ewc on e.THREAD_ID = ewc.THREAD_ID
left join data_locks dl on esc.THREAD_ID = dl.THREAD_ID
left outer join status_by_thread sbt on dl.THREAD_ID = sbt.THREAD_ID
where t.PROCESSLIST_STATE!=null;