Mysql server Process CPU Usage 100% Solution _mysql

Friend Host (Windows 2003 + IIS + PHP + MySQL) recently the MYSQL service process (Mysqld-nt.exe) CPU occupancy rate of total 100% high. This host has 10 or so database, respectively, to 10 web site calls. According to a friend test, resulting in Mysqld-nt.exe CPU consumption is a very high site A, once the site in IIS to stop the service, CPU footprint down. Once enabled, it immediately rises.

MYSQL CPU takes up 100% of the resolution process
A careful examination this morning. At present this website 7th average day IP for 2000,pageview is about 30,000. Website a uses the database currently has 39 tables, records number 601,000, occupies the space 45MB. By this data, MySQL cannot occupy such a high resource.

Then run the command on the server, the MySQL current environment variable output to file Output.txt:

d:\web\mysql> Mysqld.exe--help >output.txt

found that the value of the tmp_table_size is the default 32M, so modify the My.ini and assign the tmp_table_size to 200M:

D:\web\mysql> notepad C:\windows\my.ini
[mysqld]
tmp_table_size=200m

Then restart the MySQL service. CPU consumption has a slight drop, the previous CPU consumption waveform is 100% a line, now 97%~100% between the ups and downs. This indicates that adjusting the tmp_table_size parameters can improve the performance of MYSQL . But the problem has not been completely resolved.

So go into the MySQL shell command line, call show processlist, view the current MySQL use frequent SQL statements:

Mysql> show Processlist;

Repeatedly calling this command, found that two SQL statements for site A often appear in the process list with the following syntax:

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 found the problem lies: _mydata table, only based on the PID set up a primary key, but did not establish an index for userid. And in the first left JOIN in clause of this SQL statement:

Left JOIN _myuser as T3 on T1.userid=t3.userid

The userid of _mydata is involved in the conditional comparison operation. So I built an index for the _mydata table based on the field UserID:

mysql> ALTER TABLE ' _mydata ' ADD INDEX (' userid ')

After this index was established, the CPU was immediately reduced to about 80%. Seeing that the problem was found, I checked another SQL statement that repeatedly appeared 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, found that it only for PID built primary key, did not establish index for keywords. _mydata_key currently has 330,000 records, without the index of 330,000 records for text retrieval matching, do not consume a lot of CPU time to blame. It seems to be a problem with the search for this table. The _mydata_key table is also indexed according to the field keywords:

mysql> ALTER TABLE ' _mydata_key ' ADD INDEX (' keywords ')

After the index is established, the CPU immediately drops down and swings between the 50%~70%.

Call show Prosslist again, and site A's SQL calls rarely appear in the results list. However, it was found that the host ran several Discuz forum programs, and several tables of the Discuz forum also had this problem. So smoothly together to solve, CPU occupancy down again. (2007.07.09 Note : About the Discuz forum specific optimization process, I later wrote another article, see: Tens recorded discuz! forum led to MySQL CPU 100% optimization notes http://www.xiaohui.co m/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm)
The experience of solving MYSQL CPU occupancy 100%

1. Increase tmp_table_size value。 MySQL in the configuration file,tmp_table_sizeThe default size is 32M. If a temporary table exceeds that size, MySQL generates aThe table Tbl_name is fullForm the error if you do many advanced GROUP by queries,Increase tmp_table_size value。 This is the MySQL official explanation for this option:

   tmp_table_size

   This variable determines the maximum size to 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, isn't possible, try to ensure temp Orary tables are always stored in memory. Watching the processlist for queries with temporary tables, take too long to resolve can give your an early tha T tmp_table_size needs to be upped. Be aware this memory is also allocated per-thread. An example where upping this worked as more is a server where I upped this from 32MB (the default) to 64MB with Immediat E effect. The quicker resolution of queries resulted in less threads active at any one time, with being all-round for the s Erver, and available memory.

3. for where, JOIN, MAX (), MIN (), the conditions in the clause inthe order by, and so on, the fields used in the judgment should be indexed index. Indexes are used to quickly find rows that use a specific value on a column. Without an index, MySQL had to start with the first record and then read through the entire table until it found the relevant rows. The bigger the table, the more time it takes. If the table has an index to the query's column, MySQL can quickly reach a location to search the middle of the data file, and there is no need to consider all the data. If a table has 1000 rows, this is at least 100 times times faster than sequential reads. All MySQL indexes (PRIMARY, unique, and index) are stored in the B-tree.

   According to MySQL's development documentation:

   indexed index is used to :

   • Quickly find a row that matches a WHERE clause
   • When a join is performed, rows are retrieved from other tables.
   • Find the Max () or min () value for a specific index column
   • Sort or Group A table if you sort or group on the leftmost prefix of an available key (for example, order by key_part_1,key_part_2). If all key values partially 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 the columns used for some tables are numeric and form the leftmost prefix of some keys, the value can be retrieved from the index tree for faster.
     
     Suppose you issue the following SELECT statement:
     

     Mysql> SELECT * from Tbl_name WHERE col1=val1 and Col2=val2;

     If a multiple-column index exists on col1 and col2, the appropriate rows can be taken out directly. If separate row-column indexes exist on col1 and col2, the optimizer attempts to determine which index will find fewer rows and find more restrictive indexes and use the index to fetch rows.

When developers do SQL datasheet design, be sure to think about it in a holistic sense.