"Reprint" resolves Mysqld-nt.exe cpu-intensive problems

Server Host (Windows 2003 + IIS + PHP + MySQL) The recent MYSQL service process (Mysqld-nt.exe) CPU occupancy rate is 100% high. This host has 10 or so database, which is called to 10 websites respectively. According to a friend test, causing Mysqld-nt.exe CPU consumption is the site A, once the site in IIS to stop the service, CPU consumption down. Once enabled, it rises immediately. MYSQL CPU consumes 100% of the resolution process

I checked it carefully this morning. At present, the 7th Average daily IP of this website is 2000,pageview of about 30,000. Web Site A database currently has 39 tables, the number of records 601,000, accounting for space 45MB. According to this data, MySQL can not occupy such a high resource.

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

D:\web\mysql>mysqld.exe--help>output.txt

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

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

Then restart the MySQL service. CPU usage has dropped slightly, the previous CPU occupancy waveform is 100% a straight line, and now it's undulating between 97%~100%. This shows that adjusting the Tmp_table_size parameter improves the performance of MYSQL. But the problem is not yet fully resolved.

Then go to the MySQL shell command line, call show Processlist, and view the current MySQL usage frequentlySQL statements:

Mysql>show processlist;


Call this command repeatedly (brush two times per second) and discover two of site A'sSQL statements often appear in the process list with the following syntax:

SELECT t1.pid, T2.userid, T3.count, t1.date from _mydata as T1left joins _myuser as T3 on T1.userid=t3.useridleft join _myd Ata_body as T2 on T1.pid=t3.pid ORDER by T1.pid limit0,15


Call Show columns to check the structure of these three tables:

Mysql>show columns from _myuser;mysql>show columns to _mydata;mysql>show columns from _mydata_body;


Finally found the problem: _mydata table, only based on the PID established a primary key, but did not index the UserID. And in thisThe first left JOIN of an SQL statement in the ON clause:

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


_mydata's UserID is involved in the conditional 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 established, the CPU drops to about 80% immediately. See the problem is found, and then check another recurring in show ProcesslistSQL statements:

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 was found that it only built primary key for PID, and did not establish index for keywords. _mydata_key currently has 330,000 records, in the case of no index to the 330,000 records for text retrieval matching, not a lot of CPU time to blame. There seems to be a problem with the search for this table. The same _mydata_key table is indexed according to the field keywords:

Mysql>alter TABLE ' _mydata_key ' ADD INDEX (' keywords ')


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

Call show Prosslist again, site A'sSQL calls rarely appear in the results list. However, it was found that this host ran several Discuz forum programs, and there were several tables in the Discuz forum. As a result, the CPU consumption is lowered again.

At this point, the problem is resolved.
1. Increase the tmp_table_size value. In the MySQL configuration file, the default size of Tmp_table_size is 32M. If a temporary table exceeds that size, MySQL produces an error in the form of the table tbl_name is, and if you do many advanced GROUP by queries, increase the tmp_table_size value. This is the official MySQL explanation for 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, and where this is not possible, try to ensure temp Orary tables is always stored in memory. Watching the processlist for queries with temporary tables so take too long to resolve can give you an early warning tha T tmp_table_size needs to be upped. Be aware this memory is also allocated per-thread. An example where upping this worked for 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 being active at any one time, with all-round benefits for the S Erver, and available memory.

2. The field used in the conditions in the WHERE, JOIN, MAX (), MIN (), ORDER BY clauses should be indexed by index.

The index is used to quickly find a row with 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 line. The larger the table, the more time it takes. If the table has an index to the column of the query, MySQL can quickly reach a location to find 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 the MySQL development documentation:

Indexed index is used to:

O Quickly find the row that matches a WHERE clause
o When a junction (join) is executed, rows are retrieved from other tables.
o Find Max () or min () values for a specific index column
o If sorting or grouping is done on the leftmost front of an available key (for example, order by key_part_1,key_part_2), sort or group a table. If all key values are partially followed by DESC, the key is read in reverse order.
O 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, the values can be retrieved from the index tree for faster.

Assume that you issue 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, the appropriate row can be fetched directly. If separate single-row column indexes exist on col1 and col2, the optimizer tries to find a more restrictive index by deciding which index will find fewer rows and using that index to fetch rows.

Developers to dowhen designing SQL data tables, be sure to consider them all.

"Reprint" resolves Mysqld-nt.exe cpu-intensive problems