Solve the Problem of 100% CPU usage in MySQL

Experience in solving 100% of MySQL CPU usage

Recently, the CPU usage of MySQL service process (mysqld-nt.exe) on a friend host (Windows 2003 + IIS + PHP + MySQL) is 100% high. This host has about 10 databases, which are called for 10 Websites respectively. According to a Friend test, lead to MySQLd-nt.exe CPU usage is very high is the site a, once in IIS will stop this site service, CPU usage fell down. Once enabled, it will immediately rise.

MySQL CPU usage 100%

I checked it carefully this morning. Currently, the average daily IP address of the website on a 7-day basis is 2000, and The pageview value is about 30 thousand. Database used by website
Currently, there are 39 tables with 0.601 million records, accounting for 45 MB. Based on this data, MySQL cannot occupy such high resources.

Then run the command on the server to output the current MySQL environment variable to the file output.txt:
D:/web/mysql> mysqld.exe -- help> output.txt

We found that the value of tmp_table_size is the default 32 m, So we modified my. ini and assigned the value of tmp_table_size to 200 m:
D:/web/mysql> notepad C:/Windows/My. ini
[Mysqld]
Tmp_table_size = 200 m

Then restart the MySQL service. CPU usage is slightly reduced. The previously used CPU usage waveform is 100% in a straight line, and now it is 97% ~ 100%
Between ups and downs. This indicates that adjusting the tmp_table_size parameter can improve MySQL performance. However, the problem has not been completely solved.

Enter the MySQL shell command line and call show processlist to view the SQL statements frequently used by MySQL:
Mysql> show processlist;

After calling this command repeatedly (twice per second), we found that the two SQL statements of website a often appear in the process list. The syntax is as follows:
Select t1.pid, t2.userid, t3.count, t1.date
From _ mydata as T1
Left join _ myuser as T3 on t1.userid = t3.userid
Left join _ mydata_body as T2 on t1.pid = t3.pid
Order by t1.pid
Limit 0, 15

Call show columns to check the structure of the three tables:
Mysql> show columns from _ myuser;
Mysql> show columns from _ mydata;
Mysql> show columns from _ mydata_body;

Finally, the problem is found: _ mydata table, only a primary key is created based on the PID, but no index is created for the userid. And in this
In the first left join on Clause of an SQL statement:
Left join _ myuser as T3 on t1.userid = t3.userid

_ Mydata userid is involved in the condition comparison operation. So I created an index for the _ mydata table based on the field userid:
Mysql> alter table '_ mydata' add index ('userid ')

After this index is created, the CPU usage immediately drops to about 80%. The problem is found, so check another one repeatedly appears in show processlist.
SQL statement:
Select count (*)
From _ mydata as T1, _ mydata_key as T2
Where t1.pid = t2.pid and t2.keywords = 'peacock'

After checking the structure of the _ mydata_key table, it is found that it only creates the primary key for the PID, not for the keywords
Index. _ Mydata_key currently has 0.33 million records. Text Retrieval and matching of 0.33 million records without indexing does not consume a large amount of CPU
Time is strange. It seems that the search for this table has a problem. Therefore, add an index to the _ mydata_key table based on the field keywords:
Mysql> alter table '_ mydata_key' add index ('keyword ')

After this index is created, the CPU immediately drops down, at 50% ~ Between 70%.

Call show prosslist again, and the SQL call of website a rarely appears in the result list. However, the host runs several discuz
ForumProgramThis problem also exists in several tables on the discuz forum. As a result, the CPU usage was reduced again.

Now, the problem is solved.

Experience in solving 100% of MySQL CPU usage

1. Increase the tmp_table_size value. In the MySQL configuration file, the default size of tmp_table_size is
32 m. If a temporary table exceeds this size, MySQL generates an error in the table tbl_name is full format. If you perform many advanced
group by queries, increase the tmp_table_size value. This is an official MySQL explanation of this option:

Tmp_table_size
This variable determines the maximum size for a temporary table in
Memory. If the table becomes too large, a MyISAM table is created on
Disk. Try to avoid temporary tables by optimizing the queries where
Possible, but where this is not possible,
Try to ensure temporary tables are always stored in memory. Watching
Processlist for queries with temporary tables that take too long
Resolve can give you an early warning that tmp_table_size needs to be
Upped. Be aware that memory is also allocated per-thread.
An example where upping this worked for more was a server where I upped
This from 32 MB (the default) to 64 MB with immediate effect. The quicker
Resolution of queries resulted in less threads being active at any one
Time, with all-round benefits for the server, and available memory.

2. Index indexes should be created based on the fields used in condition judgment in clauses such as where, join, max (), min (), and order.

The index is used to quickly find rows with a specific value in a column. No index, MySQL
You have to start with the first record and read the entire table until it finds the relevant row. The larger the table, the more time it takes. If the table has an index on the queried column, MySQL
You can quickly find the data file in the middle of a location without having to consider all the data. If a table has 1000 rows, this is at least 100 faster than sequential reading.
Times. All MySQL indexes (primary, unique, and index) are stored in the B-tree.

According to the MySQL development documentation:

Index is used:

O quickly finds the rows matching a where clause.

O when join is executed, rows are retrieved from other tables.

O find the max () or min () value for a specific index column.

O if the sorting or grouping is performed on the leftmost prefix of an available key (for example, order by key_part_1,
Key_part_2), sort or group a table. If all key values follow DESC, the key is read in reverse order.

O
In some cases, a query can be optimized to retrieve values without consulting data files. If all columns used for some tables are numeric and constitute the leftmost prefix of some keys, the value can be indexed
Tree retrieved.

Assume that you have issued the following SELECT statement:
Mysql> select * From tbl_name where col1 = val1 and col2 = val2;

If a multi-column index exists on col1 and col2, appropriate rows can be taken out directly. If the separate single row and column indexes exist in col1 and col2
The optimizer tries to determine which index will find fewer rows and find more restrictive indexes and use this index to retrieve rows.

When designing SQL data tables, developers must make it clear.