Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to solve the problem of slow query in MySQL database". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to solve the problem of slow query in MySQL database".

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/bash

For i in mysql-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/bash

Executetime= (mysql-Bse 'show processlist' | grep' Query' | awk'{print $6 "" $1}'| sort-rn | head-1) # column 6 is the run time, and the first column is 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 $idfisleep 10 # Sleep 10s

Done

Reverse sort by execution time in MySQL

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

Thank you for your reading, the above is the content of "how to solve the problem of slow query in MySQL database". After the study of this article, I believe you have a deeper understanding of how to solve the problem of slow query in MySQL database. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.