Database CPU load High __ Database

CPU utilization up to 100% processing process

first, the search process, mainly to find the locked table of the process ID

Show Processlist

From the above table, you can clearly see the slow query statement (just show some of the data), general MySQL additions and deletions to the operation will lock the table

Second, Id,kill these processes off
First, all the process numbers are taken out. If the process is more numerous, you can use a for loop to do this:

Mysql-uroot-p password-e "show processlist" | awk ' {print '} ' |grep-iv ' ID '

Then, use the For loop to kill these processes

For i in ' mysql-uroot-p password-e ' show Processlist | awk ' {print '} ' |grep-iv ' id '; \
do mysql-uroot-p password-e ' kill $i ';d one

#备注: Kill process inside MySQL, can't use "kill-9 process number", only use "Ki LL process Number "

third, view the CPU situation of the database again

Four, query the database of some common instructions
1. See which threads are running

Show Processlist only list the first 100, if you want to list all please use show full processlist;

2. View the currently available tables are open.

Show open tables [from database (library name)];

Note: The In_use column indicates how many threads are using a table, name_locked whether the table name is locked, which typically occurs when the drop or rename command operates on this table. So this command does not help answer our common question: whether a table has a deadlock, who owns the lock on the table, and so on.

3. View server Status

Show status like '%lock% ';

4. View the Run-time information of the InnoDB engine

Show Engine InnoDB status\g;

5. View server configuration Parameters

Show variables like '%timeout% ';

Five, the database commonly used graphical tool
1, Mysql-workbench
2, phpMyAdmin (recommended, mainly its installation is simple)
3, Navicat (recommended by friends, has not been used)