Shulou(Shulou.com)06/01 Report--

The following content mainly brings you MySQL management for a long time to implement the reason analysis, the knowledge here is slightly different from books, are professional and technical personnel in the process of contact with users, summed up, has a certain experience sharing value, hope to bring help to the majority of readers.

1. Reasons for long-running queries

Long-time queries due to inefficient execution of SQL:

A long query caused by SQL injection:

Table metadata lock wait due to DDL statement:

two。 Problems caused by long-executed queries

In general, long-executed queries are meaningless for applications unless they are BI/ report-like queries.

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

Bring the hidden danger of system instability (for example, long queries on the InnoDB engine table may lead to an increase in file size in the ibdata1 system)

3. How to avoid long-running queries

In the aspect of application, attention should be paid to increasing the protection against SQL injection.

Stress testing should be carried out before the new functional module is launched to avoid a large number of inefficient SQL execution.

Try to carry out index creation deletion, table structure modification, table maintenance and table deletion operations during the business trough.

4. How to handle long-executed queries

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

B. create events to automatically clean up long-executed queries

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 (500); 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 sleep' 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 cantidsql; repeat fetch c_tid into vSecretsql; set @ vSecretsql; prepare stmt from @ v_sql Execute stmt; deallocate prepare stmt; until no_more_long_running_query end repeat; close cantilever

For the above reasons for the long-term implementation of MySQL management, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.