Suggestions for querying MySQL processlist

query The MySQL process because the database has a lot of sleep connections (usually around thousands of), do not recommend direct show Processlist or show full

Processlist

try to use Select query

the thread being running

Select Count (*) from Information_schema.processlist where info was not null;

All Mysql Threads

Select Count (*) from information_schema.processlist;

Query the current running SQL for the longest execution time

Select * Frominformation_schema.processlist where info is not NULL for ORDER by time Desc limit10;

Query The number of connections to the IP executing SQL

Select Left (host,instr (host, ': ')-1) asip,count (*) as Num from information_schema.processlist GROUP by IP ORDER by num DESC ;

Query The number of user connections that Execute SQL

Select User,count (*) as Num from Information_schema.processlist GROUP by Userorder by num DESC;

Number of query execution SQL statements

Select COUNT (*) as Num,info from information_schema.processlist where info isnot null GROUP BY info ORDER by NUM;

query mysql server maximum connections, current database connections and running number show global variables like ' max_connections ';

Show global status like ' threads% ';

Query user maximum number of connections

Show grants for ' MYSQL_BI ';

Slave error problem, need to confirm data inconsistency reason

ADM and Click Libraries can skip 1 errors directly

the general processing methods include

kill slow query process, can be done for the user, execution time to operate

Select Concat (' KILL ', id, '; ') frominformation_schema.processlist where user= ' user name ' and Time>100into OutFile '/tmp/aa.txt ';

Source/tmp/aa.txt

This article from "Cloud Light Breeze Light" blog, declined reprint!

Suggestions for querying MySQL processlist