Solution to 100% CPU usage of MySQL Server Processes

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.

Therefore, run the command on the serverEnvironment VariableOutput to 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=200M

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 indicatesAdjust the tmp_table_size ParameterPairMYSQL performance improvement can improve. However, the problem has not been completely solved.

Enter the mysql shell command line and callShow processlistTo view SQL statements frequently used by mysql:

mysql> show processlist;

By calling this command repeatedly, we find that the two SQL statements of website A are often displayed in the process list. The syntax is as follows:

SELECT t1.pid, t2.userid, t3.count, t1.dateFROM _mydata AS t1 LEFT JOIN _myuser AS t3 ON t1.userid=t3.useridLEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pidORDER BY t1.pidLIMIT 0,15

CallShow columnsCheck 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. The first SQL statementLEFT JOIN ONClause:

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 t2WHERE 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 ( `keywords` )

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. (Notes for 2007.07.09: I wrote another article about the specific optimization process of the discuz Forum. For details, see Discuz, a record of tens of millions! Forum lead to optimization notes http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm for MySQL CPU 100%)
Experience in solving 100% of mysql cpu usage

1. Increase the tmp_table_size value.. In the mysql configuration file,Tmp_table_sizeThe default size is 32 MB. If a temporary table exceeds this size, MySQL generatesThe table tbl_name is fullFormat error. If you do a lot of 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. For the fields used in condition judgment in the WHERE, JOIN, MAX (), MIN (), order by, and Other clauses, INDEX indexes should be created based on them.. 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 Tree B.

   According to the mysql development documentation:

   Index is used:

   • Quickly finds the rows matching a WHERE clause
   • When JOIN is executed, rows are retrieved from other tables.
   • Find the MAX () or MIN () value for a specific index Column
   • If 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.
   • 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.