Mysql CPU High-occupancy problem solving method Summary _mysql

Through the previous experience of MySQL operation, the MySQL configuration problem is excluded, to see if the MSYQL is running normally, by looking at the MySQL data directory inside the *.err file (change the extension to. txt) Notepad view. If too big does not suggest to use Notepad, easy to die, can use EditPlus and other tools

There are several steps to solve this problem:

1, MySQL running normally, it is possible that the synchronization setup problem caused

2, if the MySQL operation is normal, that is PHP, some SQL statements caused problems found, 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 index of a field appropriately.

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

Of course, if you are a SQL statement with a large number of group by and other statements, union query, etc. will certainly increase the occupancy rate of MySQL. So you need to optimize the SQL statements, the site as far as possible to generate static, general 4W IP static Web site, 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)

The following is a collection of articles by the cloud Habitat community, we can refer to the following

Description of MYSQL CPU occupancy 100%

Help a friend in the morning a server solves the problem of 100% of Mysql CPU usage. Just sort it out and record the experience in this article.
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.com/dev/ser ver/20070701-discuz-mysql-cpu-100-optimize.htm)

The experience of solving MYSQL CPU occupancy 100%

Increase the tmp_table_size value. In MySQL's configuration file, the default size of the Tmp_table_size is 32M. If a temporary table exceeds that size, MySQL produces a full form of the table Tbl_name error, and if you do many advanced GROUP by queries, increase the tmp_table_size value. This is the MySQL official 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, 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.

For where, JOIN, MAX (), MIN (), the conditions in the clause in the 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.