Mysql CPU High-occupancy problem solving method Summary

Through the previous experience with MySQL, the MySQL configuration problem is excluded, to see if the MSYQL is working properly, by viewing the MySQL data directory in the *.err file (change the extension to. txt) to view Notepad. If too big do not recommend using Notepad, easy to die, you can use EditPlus and other tools

Here are some simple steps to solve this problem:

1, MySQL operation is normal, there may be synchronization settings problems caused

2, if MySQL is running normally, it is PHP some SQL statements cause problem discovery, with the root user into the MySQL management
Mysql-u root-p
Enter password
Mysql:Show ProcesslistStatement to find the most heavily loaded SQL statement, optimizing the SQL, such as setting the appropriate index for a field.

Through this command I see the original is someone malicious brush search, because Dedecms search after the call to search the highest word, resulting in a lot of people with tools to brush this, and is timed interval, so the PHP program renamed Jump Method solved.

Of course, if you do SQL statements with a large number of group by and other statements, Union union query will certainly increase the occupancy rate of MySQL. So you need to optimize the SQL statement, the site as far as possible to generate static, general 4W IP static website, mysql occupancy rate of almost 0. So this is a consideration for the programmer's experience. Maximize MySQL Performance (best 20 + experience sharing for MySQL performance optimization)

Here is the script House collection of articles, everyone can refer to the following

MYSQL CPU consumes 100% of the phenomenon description

Help a friend in the morning a server solves the problem of Mysql CPU consumption 100%. A little bit of tidying up, the experience is recorded in this article
Friends Host (Windows 2003 + IIS + PHP + MySQL) recently the 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 and call show Processlist to see the current MySQL frequently used SQL statements:

Mysql> show Processlist;
Call this command repeatedly to discover that site A's two SQL statements 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 these three tables:

Mysql> show columns from _myuser;
Mysql> show columns from _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. In the first left JOIN of this 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. Seeing that the problem was 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 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, and the SQL call to site a rarely appears 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. (2007.07.09 Note: For the specific optimization process of the Discuz forum, I later wrote another article, see: Tens recorded discuz! forum led to MySQL CPU 100% optimization Note http://www.xiaohui.com/dev/ser ver/20070701-discuz-mysql-cpu-100-optimize.htm)

100% Summary of the experience of solving MYSQL CPU consumption

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.

The field that is 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:

Quickly find the row that matches a WHERE clause
When a junction (join) is executed, rows are retrieved from other tables.
Find Max () or min () values for a specific index column
Sort or Group A table if sorting or grouping is done on the leftmost prefix of an available key (for example, order by key_part_1,key_part_2). If all key values are partially followed by DESC, the key is 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, 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.

When developers do SQL data sheet design, they must be considered clearly.

Mysql CPU High-occupancy problem solving method Summary