MySQL CPU usage 100%

Windows 2003 + IIS + PHP + MySQL) Recently, MySQL service process (MySQLd-nt.exe) has a high CPU usage of 100%. 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%

The server solves the problem of MySQL CPU usage of 100%. I hope it will be helpful to you.

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. The database used by website A currently has 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: webMySQL> 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: webMySQL> notepad c: windowsmy. 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% ~ Ups and downs between 100%. 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. In the first left join on clause of this 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%. When the problem is found, check another SQL statement that appears repeatedly in show processlist:
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 only the primary key is created for the pid, and no index is created for the keywords. _ Mydata_key currently has 0.33 million records. Text Retrieval and matching of 0.33 million records without indexing does not take a lot of CPU time. 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 Forum programs, and several tables of the Discuz Forum also have this problem. 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 MB. If a temporary table exceeds this size, MySQL generates an error in The table tbl_name is full format. If you do 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 the processlist for queries with temporary tables that take too long to 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. Without an index, MySQL has to start with the first record and then read the entire table until it finds the relevant rows. The larger the table, the more time it takes. If the table has an index on the queried columns, MySQL can quickly find the data file at a location without considering all the data. If a table has 1000 rows, this is at least 100 times faster than sequential reading. 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 values can be retrieved from the index tree for faster speed.

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 on 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.