MySQL Management long running queries

1. Reasons for a long-time executed query

Long-time queries due to poor SQL execution efficiency:

Long queries caused by SQL injection:

Because DDL statements cause table metadata lock waits:

2. Problems caused by long-time queries

In general, queries that are executed for long periods of time are not meaningful to the application unless they are bi/report class queries.

Consuming system resources, such as a large number of long queries, can cause problems such as excessive CPU, IOPS, and/or high usage of connections.

The potential for system instability (such as long-time queries on InnoDB engine tables may result in an increase in file size for ibdata1 systems)

3. How to avoid long-run queries

Applications should be aware of increased protection against SQL injection.

Before the new feature module goes live, stress tests are performed to avoid the execution of a poorly executed SQL.

Index creation deletes, table structure modifications, table maintenance, and table deletions are done as much as possible during low-peak business periods.

4. How to handle queries that are executed for a long time

A, through the order show processlist; View the current execution session and the kill session for a long time query.

B, create an event to automatically clean up queries that have been executed for a long time

create event my_long_running_query_monitoron schedule every 5 minutestarts  ' 2018-08-08 11:00:00 ' on completion preserve enable dobegin  declare v_ Sql varchar (+);  declare no_more_long_running_query integer default 0;   declare c_tid cursor for    select concat  (' Kill   ', id, '; ')  from     information_schema.processlist    where time  >= 3600    and user = substring (Current_User (), 1,instr ( Current_User (), ' @ ')-1)     and command not in  (' Sleep ')      and state not like  (' Waiting for table%lock ');   declare  continue handler for not found    set no_more_long_running_ Query=1;   open c_tid;  repeat    fetch c_tid into v_sql;     set  @v_sql =v_sql;    prepare stmt from  @v_sql;     execute stmt;    deallocate prepare stmt;   until no_more_long_running_query end repeat;  close c_tid;end;

MySQL Management long running queries