Dangers of slow query in MySQL Databases

Dangers of slow query in MySQL Databases

1. When a slow query occurs in a MySQL database, it is dangerous. Other DDL operations may cause the entire database to wait.

It can be divided into the following situations:

When a table is a MyiSAM table, the table has slow queries and does not block the Select statement. Other DML and DDL operations on the table are blocked, such as the appearance of Wating for table level lock, the MyiSAM table cannot exist in the database.

When the table is an Innodb table and the table has slow queries without blocking Select and DML, other DDL operations will be blocked, such as waiting for table metadata lock

In summary, when there is a slow query in the database, it is quite dangerous. When performing backup, create index, alter table, flush table, and other operations, it will cause the database to wait.

Solution:

1. Monitor Select statements that have been executed for a long time in the database, and trigger alarms in a timely manner.

2. if allowed, write the script and find a long select statement, kill it directly, and record the log

-B, -- batch Don't use history file. Disable interactive behavior.

-S, -- silent Be more silent. Print results with a tab as separator, each row on new line.

-E, -- execute = name Execute command and quit. (Disables -- force and historyfile .)

# If the database currently has a large number of select statements, you can filter them out and only kill the waiting

Cat killWaitSession. sh

#! /Bin/bash
For I in 'mysql-BSE' show full processlist '| grep-I select | grep-I "Waiting | awk' {print $1 }''
Do
Mysql-Bse "kill $ I"
Done

The show processlist command has many statuses, and Query indicates the command being executed.

Query: The thread is executing a statement.

Cat killLongQuerySession. sh

#! /Bin/bash
Executetime = ('mysql-BSE' show processlist' | grep 'query' | awk '{print $6 "" $1}' | sort-rn | head-1 ') # The first column is the run time and the first column is the session id.
Time =$ {executetime [0]}
Id =$ {executetime [1]}
While:
Do
Maxtime= 300
If [$ time-gt $ maxtime]; then
Echo $ time $ id>/tmp/killqueryid. log
Mysql-Bse "kill $ id"
# Else
# Echo $ time $ id
Fi
Sleep 10 # sleep for 10 s
Done

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151304.htm