Add SQL diagnosis rule: SQL plan changed and execution is slowed down notably

Introduce

If the SQL plan was changed, and the execution is slowed down notably, It may be caused by the statistics was not accurate or something else( such as a bug of TiDB 😢 ).

Task1

First, we can find the SQL of the plan changed and execution is slowed down notably, Then append a warning in the INSPECTION_RESULT table.

How to find the SQL of the plan changed?

1. Find from cluster_statements_summary and cluster_statements_summary_history.

select count(distinct plan_digest) as count,digest,min(QUERY_SAMPLE_TEXT) 
from information_schema.cluster_statements_summary 
where SUMMARY_BEGIN_TIME <= '2020-06-10 20:26:00' 
    and SUMMARY_END_TIME >= '2020-06-10 20:26:06' 
group by digest having count > 1 limit 3

select count(distinct plan_digest) as count,digest,min(QUERY_SAMPLE_TEXT) 
from information_schema.cluster_statements_summary_history 
where SUMMARY_BEGIN_TIME <= '2020-06-10 20:26:00' 
    and SUMMARY_END_TIME >= '2020-06-10 20:26:06' 
group by digest having count > 1 limit 3

2. Find from cluster_slow_query:

select count(distinct plan_digest) as count,digest,min(query) 
from information_schema.cluster_slow_query 
where time >= '2020-06-10 20:26:00' and time < '2020-06-10 20:26:06' 
group by digest having count > 1 limit 3

Should query different table according to the diagnosis time range:

• cluster_statements_summary and cluster_statements_summary_history only contain the recent data, so if the diagnosis time range is in the cluster_statements_summary or cluster_statements_summary_history, we can only query the table, otherwise, should query from the cluster_slow_query.

Attention

Need specify the time range according to the INSPECTION_RESULT diagnose time range.

After find out the SQL of the plan changed, we also need to make sure the execution is slowed down notably, such as t1/t2 >= 2.

If the result was too much, I think we should also need to add a limit to this.

Task2

After task1 find out the SQL of the plan changed, maybe we can give more advice such as:

• If this was caused by the statistics not accurate, we can advise running analyze table ... timely or use Plan binding

Refer

• doc of Statement Summary Tables
• Docs of CLUSTER_SLOW_QUERY table
• You can refer the configInspection implementation in TiDB.

If you have any questions or want to discuss something, you can chat in the sig-dashboard slack channel and @ chenshuang.