MySQL accounts for cpu100% processing a

A colleague from work today is responsible for a MySQL database with a CPU utilization of 100% and a server top view

Cpu (s): 95.9%us, 4.0%sy, 0.0%ni, 0.0%id, 20.0%wa, 0.0%hi, 0.0%si, 0.0%st

As the maintenance of Oracle has been done, not familiar with MySQL, first on the internet to check a few articles, but it seems to be written by a person, but from these articles also has a general approach. (because it is not my own operation, there is no detailed log, this is only the next process and the use of the command, the body of the code is only later for the description added.) )

First use the root user to mount MySQL and use the show Processlist command to see which threads are currently running. View down a total of more than 160

Mysql> Show processlist;+----+------+-----------+------+---------+------+-------+------------------+| Id | User | Host | db | Command | Time | State |  Info |+----+------+-----------+------+---------+------+-------+------------------+| 1 | Root | localhost | NULL |    Query | 0 | init | Show Processlist |+----+------+-----------+------+---------+------+-------+------------------+1 row in Set (0.00 sec)

Let's start by saying the meanings and uses of each column:

ID an identity that is useful when you want to kill a statement.

User displays the current users, if not root, this command displays only the SQL sentences within the scope of your permission.

Host shows which IP port this statement was issued from. A user that can be used to track a problem statement.

The DB shows which database the process is currently connected to.

command displays the execution commands for the current connection, typically sleep (sleep), query, connection (connect).

The time this state lasts, in seconds.

State displays the status of the SQL statement using the current connection, which is an important column. State is only a state in the execution of a statement, in order to query SQL for example, may need to go through the copying to TMP table,sorting result,sending data and other states to complete.

Info shows this SQL statement, because the length is limited, so the long SQL statement is not complete, but an important basis for judging the problem statement.

State column various status reference documents: http://blog.csdn.net/e421083458/article/details/38342051

The results from the show processlist command output see that there is a duplicate SQL statement, but the Info column does not display only information such as select A.col1,a.col2,a.col3 from table1 A. Then start with this table, select COUNT (*) from table1; Find out if this watch has 60w+ data. Select COUNT (*) was used for 6 seconds. But now I'm not sure if this statement executes with a where condition.

Continue to check the information, find out the results from the show processlist Command Information_schema Library under the Processlist table.

mysql> use information_schemadatabase changedmysql> desc processlist;+--------- +---------------------+------+-----+---------+-------+| field   | type                 | Null |  key | default | extra |+---------+---------------------+------+-----+---------+------ -+| id      | bigint (+)  unsigned | NO    |     | 0       |        | |  user    | varchar (         |)  NO   |     |          |       | |  host    | varchar (  )       | NO   |     |          |       | |  db      | varchar (        )  | YES  |     | NULL    |        | |  command | varchar (         | NO )   |     |         |        | |  time    | int (7)                | NO   |     | 0        |       | |  state &Nbsp; | varchar (         | yes  |)      | null    |       | |  INFO    | longtext             | YES  |     | NULL    |        |+---------+---------------------+------+-----+---------+-------+8  rows in set  (0.04 SEC)

You can see that the columns of the table Porcesslist table are consistent with the columns that show processlist output.

When querying the Processlist table, the info information is found to be complete, where the full version of the suspected SQL is found here for select A.col1,a.col2,a.col3 from table1 a where a.col4= ' 123 ' and A.COL5 = ' abc ';

View the execution plan for this statement (similar to the following)

mysql> explain select ename,hiredate,sal from  emp where sal=1000 \g;*************************** 1. row *****************            id: 1  select_type:  SIMPLE        table: emp          type: ALLpossible_keys: NULL           key: NULL      key_len: NULL           ref: NULL         rows:  3072        extra: using where1 row in set   (0.00 sec) error: no query specified 

You can see that the statement uses a full table scan instead of using an index. Col4= ' 123 ' and col5= ' abc ' were statistically found to have only one record of col4= ' 123 ', while Col5= ' abc ' records had 5w+ strips, and it was clear that creating an index on COL4 would be much more efficient. See if there is an index on the COL4 column on the table (similar to this).

Mysql> Show index from EMP \g;*************************** 1. Row *************************** table:emp non_unique:1 key_name:idx_emp_2 seq_in_index:1 column_name:de Ptno collation:a cardinality:6 sub_part:null packed:null null:yes index_type:btree Comm Ent:index_comment:1 row in Set (0.00 sec) Error:no query specified

The Col4 column does not have an index on it, the storage engine for the table is InnoDB, and the index is created on the COL4 column

mysql> show table status from test1 like  ' EMP ' \G;***********************  1. row ***************************            Name: emp         Engine: InnoDB         Version: 10     Row_format:  Compact           rows: 3072 avg_row_length:  53    data_length: 163840max_data_length: 0   index_length:  65536      Data_free: 0 Auto_increment: NULL     Create_time: 2016-11-15 21:54:49    Update_time: NULL      check_time: null      collation: gbk_chinese_ ci       checksum: null create_options:          comment: 1 row in set  (0.00 sec) error: no query specifiedmysql > create index idx_sal on emp (SAL); query ok, 0 rows affected  (0.15 sec) records: 0  duplicates:  0  warnings: 0

Review the execution plan again, and the discovery statement uses an index scan.

Mysql> explain select Ename,hiredate,sal from emp where sal=1000 \g;*************************** 1. Row *************************** id:1 select_type:simple table:emp type:refpossible_keys:idx _sal key:idx_sal key_len:6 ref:const rows:1 extra:null1 row in Set (0.00 sec) ER Ror:no query specified

The execution efficiency of the SQL statement increases immediately. CPU usage has also been lowered.

There is also a doubt that Oracle created the index in order to avoid the lock table introduced in the online creation index. Don't know how to create an index online in MySQL?

Reference Document: http://blog.csdn.net/wlzjsj/article/details/51537736

Reference Document: Http://www.jb51.net/article/75217.htm

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1873265

MySQL accounts for cpu100% processing a