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

1. When there is a slow query in MySQL database, it is dangerous. Once there are other DDL operations, it may cause the whole database to wait.

It can be divided into the following situations:

When the table is a MyiSAM table, if there is a slow query on the table and Select is not blocked, other DML,DDL operations on the table will be blocked. For example, if Wating for table level lock occurs, there must be no MyiSAM table in the database.

When the table is an Innodb table, when there is a slow query on the table and does not block Select and DML, other DDL operations will be blocked, such as waiting for table metadata lock

To sum up, it is dangerous when there is a slow query in the database, and it will cause the database to wait when performing backup, create index, alter table, flush table and other operations.

Solution:

1. Monitor the Select with long execution time in the database and give an alarm in time.

2. If allowed, write a script, find longer select statements, kill directly, and record in 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 there are a large number of select in the database, you can filter them out, only those of kill waiting

Cat killWaitSession.sh

#! / bin/bashfor I in `print-Bse 'show full processlist' | grep-I select | grep-I "Waiting | awk' {print $1} '`do mysql-Bse" kill $I "done

There are many states of show processlist's command, where Query represents the command being executed

Query: The thread is executing a statement.

Cat killLongQuerySession.sh

#! / bin/bashexecutetime= (`print-Bse 'show processlist' | grep' Query' | awk'{print $6 "" $1}'| sort-rn | head-1`) # column 6 is the run time, the first column is session idtime=$ {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 10sdone

Reverse sort by execution time in MySQL

Mysqladmin processlist-- verbose | grep 'Query' | awk-F "|' {print $7 $2 $9}'| sort-rn-K1

Reference:

Https://blog.51cto.com/jim123/1836712

Https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

Https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html

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.