My friend's host (Windows 2003 + IIS + PHP + MYSQL) Recently, the MySQL service process (mysqld-nt.exe) has a CPU usage of 100%. This host has about 10 databases, which are called for ten websites. According to a friend test, the mysqld-nt.exe cpu takes up a high degree of website A. Once the website is stopped in IIS, the CPU usage is reduced. Once enabled, it immediately rises. The MYSQLCPU takes up 100% of the resolution process. I checked it carefully this morning. At present, the average daily IP of this website is 2000, and the PageView is about 30,000. The database used by website A currently has 39 tables, with a record number of 601,000, occupying 45MB. According to this data, MySQL can't take up such a high resource. So run the command on the server and output the current mysql environment variable to the file output.txt:d:\\web\\mysql>mysqld.exe--help >output.txt and find tmp_table_size The value is the default 32M, so modify My.ini, assign tmp_table_size to 200M:d:\\web\\mysql>notepad c:\\windows\\my.ini[mysqld]tmp_table_size=200M and restart MySQL service. CPU usage has a slight drop , the previous CPU occupied wave The graph is 100% straight, and now it fluctuates between 97% and 100%. This shows that adjusting the tmp_table_size parameter improves the performance of MYSQL. But the problem has not been completely solved. So enter the mysql shell command line, call show Processlist, view the current sql statement frequently used by mysql: mysql> showprocesslist; repeatedly call this command (two times per second), found that 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.dateFROM _mydata AS t1LEFT JOIN _myuser AS t3 ON t1.userid=t3.useridLEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pidORDER BY t1.pidLIMIT 0,15 call show columns check The structure of these three tables: mysql> showcolumns from _myuser; mysql> showcolumns from _mydata; mysql> showcolumns from _mydata_body; finally found the problem: _mydata table, only a primary key is established according to pid, but no index is created for userid And in the first LEFT JOIN ON clause of this SQL statement: LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid_mydata userid is Compared with the conditional comparison operation, I created an index for the _mydata table based on the field userid: mysql> ALTERTABLE `_mydata` ADD INDEX ( `userid` ) After creating this index, the CPU immediately dropped to about 80%. See the found The problem is, so check another sql statement that appears repeatedly in the show processlist: SELECT COUNT(*)FROM _mydata AS t1, _mydata_key AS t2WHERE t1.pid=t2.pid and t2.keywords = 'Peacock' checked _mydata_key The structure of the table, found that it only built a primary key for pid, no index._mydata_key for keywords. There are currently 330,000 records, and there is no index to perform text retrieval matching on 330,000 records, without spending a lot of cpu. Time is strange. It seems that the search for this table is a problem. So the same as the _mydata_key table according to the field keywords plus index: mysql> ALTERTABLE `_mydata_key` ADD INDEX ( `keywords`) after the establishment of this index, the CPU immediately drops Down, oscillate between 50% and 70%. Call show prosslist again, the sql call of website A rarely appears in the result list. But found that this host is running Several Discuz forum programs, several of the Discuz forums also have this problem. So they are solved together, the CPU usage is reduced again. Solve the MYSQLCPU occupation of 100% of the experience to increase the tmp_table_size value. mysql configuration file, The default size of tmp_table_size is 32M. If a temporary table exceeds this size, MySQL generates an error of the form tbl_nameis full. If you do a lot of advanced GROUP BY queries, increase the tmp_table_size value. This is the mysql official explanation for this option: Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensuretemporary tables are always Stored in memory. Watching theprocesslist for queries with temporary tables that take too long toresolve can give you an early warning that tmp_table_size needs tobe upped. Be aware that memory is also allocated per-thread. Anexa Mple where upping this worked for more was a server where Iupped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less bytes being active at any one time, with all-round benefits for the server, andavailable Memory. Fields used in conditional judgments in WHERE, JOIN, MAX(), MIN(), ORDER BY clauses should be indexed according to their index INDEX. The index is used to quickly find a column on a column. Rows with specific values. Without an index, MySQL has to start with the first record and then 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 column of the query MySQL can quickly reach a location to search for the middle of a data file. There is no need to consider all the data. If a table has 1000 rows, this is at least 100 times faster than sequential reads. All MySQL indexes (PRIMARY, UNIQUE, and INDEX) are in Stored in the B-tree. According to the mysql development documentation: index index is used to: quickly find the row that matches a WHERE clause. When performing a join (JOIN), retrieve rows from other tables. The index column finds the MAX() or MIN() value if sorting or grouping is done on the leftmost prefix of an available key (for example, ORDER BYkey_part_1, key_part_2), sorting or grouping a table. If all key-value parts follow DESC, The keys are read in reverse order. In some cases, a query can be optimized to retrieve values without consulting the data file. If all columns used for some tables are numeric and form the leftmost prefix of some keys, For faster, the value can be retrieved from the index tree. Suppose you issue the following SELECT statement: mysql> SELECT* FROM tbl_name WHERE col1=val1 AND col2=val2; if a multicolumn index exists on col1 and col2, the appropriate line Can be fetched directly. If separate single-row column indexes exist on col1 and col2, the optimizer tries to find a more restrictive index and decide to use the index to fetch rows by deciding which index will find fewer rows. Developer When designing SQL data tables, be sure to consider them all.