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

Select from information_schema.processlist where INFO is not null and INFO not like 'select from information_schema.processlist%' order by time,db\ G

It is used to view some sql with long execution time, and sort them incrementally according to the execution time.

Show engine innodb status\ G

It can be used to view deadlock, transaction execution status, lock waiting and other information.

Userstat related monitoring commands:

Flush USER_STATISTICS

Flush TABLE_STATISTICS

Set global long_query_time=0

Flush logs

Show processlist

Select from information_schema.USER_STATISTICS where user not in ('admin','monitor','#mysql_system#')\ G

Select from information_schema.TABLE_STATISTICS where table_name not in ('mysql','db')\ G

Database delay processing:

1. Database latency may be as follows: a. The main library performs a large modification of the data caused by SQL; b. The amount of concurrent writes in the main library has increased suddenly; c. Queries or other operations from the library cause disk io or cpu bottlenecks or locks; d. Poor performance due to hardware problems in slave libraries

two。 Deal with:

a. Let R & D know the situation, and recommend breaking large operations into small ones to avoid recurrence

b. If it occurs frequently, because the master can never expand and write, then communicate with RD to confirm that if the business cannot control the writing, then prepare to dismantle the library.

c. Inform RD of the relevant situation. If necessary sql, you can put these sql on a special statistical database and raise the alarm threshold to solve the problem, otherwise let R & D split or offline sql. If it is a lock such as MDL, this needs to be solved by kill dropping the corresponding query and releasing the lock.

d. If there is a slave library, you can consider downtime to repair the slave library. If not, you need to urgently redeploy a new slave library for repair.

Kill the connection that matches a sql

Pt-kill-match-command Query-busy-time 5-host dbxx-port 5002-interval-print-kill-victims all-match-info= "select distinct (Issue_label)"

Kill the sql in progress of filesort.

Pt-kill-match-command Query-match-state "Sorting result"-busy-time 5-host *. -- port 5002-- interval 2-- print-- kill-- victims all-uduzq-p

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.