mysql diagnose problems

Step 1. Run Use Case (Sample Query)

select * from output_trade_log;

Step 2. Analyze the Root Casue (Show current user and connection)

select CURRENT_USER(), CONNECTION_ID();

Step 3. Rule Out Benign Issues (Show all the processes)
SHOW FULL PROCESSLIST;

Step 4. Isolate Malign Issues

     4.1 show all thread_id of current connection

      select THREAD_ID, PROCESSLIST _ID from performance_schema.threads where processlist_id = connection_id();

     4.2 Select all the waits for current events

      select * from performance_scheam.events_waits_current;

      4.3 Select only the event waits for current threads

      select event_name,(timer_end-timer_start)/1000000000 as 'Duration (ms)', object_name,object_type,index_name, operation from  performance_schema.event_waits_current e inner join performance_schema.threads t on e.thread_id = t.thread_id where processlist_id = connection_id();

Step 5. Take Appropriate Correcive Actions (turn off the logging)

  set global general_log = 'OFF';

6. repeat the process (go to step 1)

e.g.

select a.DIGEST_TEXT as query, a.COUNT_STAR as exec_count, sec_to_time(a.SUM_TIMER_WAIT/1000000000000) as exec_time_total, sec_to_time(a.MAX_TIMER_WAIT/1000000000000) as exec_time_max, (a.AVG_TIMER_WAIT/1000000000) as exec_time_avg_ms, a.SUM_ROWS_SENT as rows_sent, a.SUM_ROWS_EXAMINED as rows_scanned from performance_schema.events_statements_summary_by_digest a order by a.SUM_TIMER_WAIT desc;