Troubleshooting of high usage of MySQL CPU _ MySQL

Summary of problems with high usage of mysql CPU bitsCN.com through previous operations on mysql, I first excluded the mysql configuration problem and checked whether msyql is running normally, view the *. errfile (change the extension name to .txt) in the mysql data directory. If it is too large, it is not recommended to use notepad, it is easy to die, you can use editplus and other tools

To solve this problem, follow these steps:

1. mysql runs normally and may also be caused by synchronization settings problems.

2. if mysql runs normally, some SQL statements in php may cause problems. use the root user to go to mysql management.
Mysql-u root-p
Enter password
Mysql: show processlist statement: Find the SQL statement with the heaviest load and optimize the SQL statement. for example, create an index for a field.

Through this command, I can see that some people maliciously click to search. because dedecms searches are followed by the most frequently used words, many people use tools to click this one, and there is a regular interval, so we can solve the problem by renaming and redirecting the php program.

Of course, if you actually use a large number of group by statements in SQL statements, union queries will definitely increase the usage of mysql. Therefore, you need to optimize the SQL statement. The website should generate static websites as much as possible. generally, for static websites with 4 W ip addresses, the mysql usage is almost 0. Therefore, this is a consideration for the programmer's experience. Improve mysql performance as much as possible (more than 20 best experiences on MySQL Performance optimization)

The following are collected articles. for details, refer

Description of mysql cpu usage by 100%

In the morning, I helped a friend solve the Mysql cpu usage problem of 100%. I sorted it out and recorded the experience in this article.
Recently, the CPU usage of MYSQL service process (mysqld-nt.exe) on a friend host (Windows 2003 + IIS + PHP + MySQL) is 100% high. This host has about 10 databases, which are called for 10 websites respectively. According to A friend Test, lead to mysqld-nt.exe cpu usage is very high is the site A, once in IIS will stop this site service, CPU usage fell down. Once enabled, it will immediately rise.

Mysql cpu usage 100%

I checked it carefully this morning. Currently, the average daily IP address of the website on a 7-day basis is 2000, and the PageView value is about 30 thousand. The database used by website A currently has 39 tables with 0.601 million records, accounting for 45 MB. Based on this data, MySQL cannot occupy such high resources.

Then run the command on the server to output the current mysql environment variable to the file output.txt:

D:/web/mysql> mysqld.exe -- help> output.txt
We found that the value of tmp_table_size is the default 32 M, so we modified My. ini and assigned the value of tmp_table_size to 200 M:

D:/web/mysql> notepad c:/windows/my. ini
[Mysqld]
Tmp_table_size = 200 M

Then restart the MySQL service. CPU usage is slightly reduced. the previously used CPU usage waveform is 100% in a straight line, and now it is 97% ~ Ups and downs between 100%. This indicates that adjusting the tmp_table_size parameter can improve MYSQL Performance. However, the problem has not been completely solved.

Enter the mysql shell command line and call show processlist to view the SQL statements frequently used by mysql:

Mysql> show processlist;
By calling this command repeatedly, we find that the two SQL statements of website A are often displayed in the process list. The syntax is as follows:

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, the problem is found: _ mydata table, only a primary key is created based on the pid, but no index is created for the userid. In the first left join on clause of this SQL statement:

Left join _ myuser AS t3 ON t1.userid = t3.userid
_ Mydata userid is involved in the condition 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 created, the CPU usage immediately drops to about 80%. When the problem is 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 is found that only the primary key is created for the pid, and no index is created for the keywords. _ Mydata_key currently has 0.33 million records. text retrieval and matching of 0.33 million records without indexing does not take a lot of cpu time. It seems that the search for this table has a problem. Therefore, add an index to the _ mydata_key table based on the field keywords:

Mysql> alter table '_ mydata_key' add index ('keyword ')
After this index is created, the CPU immediately drops down, at 50% ~ Between 70%.

Call show prosslist again, and the SQL call of website A rarely appears in the result list. However, the host runs several Discuz Forum programs, and several tables of the Discuz Forum also have this problem. As a result, the cpu usage was reduced again. (Note: I wrote another article about the optimization process of the discuz forum later. for details, refer to the Discuz with tens of millions of records! Forum Lead to optimization notes http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm for MySQL CPU 100%)

Experience in solving 100% of mysql cpu usage

Increase the tmp_table_size value. In the mysql configuration file, the default size of tmp_table_size is 32 MB. If a temporary table exceeds this size, MySQL generates an error in The table tbl_name is full format. if you do many advanced group by queries, increase The tmp_table_size value. This is an official mysql explanation of 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 this is not possible, try to ensure temporary tables are always stored in memory. watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. be aware that memory is also allocated per-thread. an example where upping this worked for more was a server where I upped this from 32 MB (the default) to 64 MB with immediate effect. the quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory.

INDEX indexes should be created based on the fields used in condition judgment in clauses such as WHERE, JOIN, MAX (), MIN (), and order. The index is used to quickly find rows with a specific value in a column. Without an index, MySQL has to start with the first record and then read the entire table until it finds the relevant rows. The larger the table, the more time it takes. If the table has an index on the queried columns, MySQL can quickly find the data file at a location without considering all the data. If a table has 1000 rows, this is at least 100 times faster than sequential reading. All MySQL indexes (PRIMARY, UNIQUE, and INDEX) are stored in tree B.

According to the mysql development documentation:

Index is used:

Quickly finds the rows matching a WHERE clause
When JOIN is executed, rows are retrieved from other tables.
Find the MAX () or MIN () value for a specific index column
If sorting or grouping is performed on the leftmost prefix of an available key (for example, order by key_part_1, key_part_2), sort or group a table. If all key values follow DESC, the key is read in reverse order.

In some cases, a query can be optimized to retrieve values without consulting data files. If all columns used for some tables are numeric and constitute the leftmost prefix of some keys, the values can be retrieved from the index tree for faster speed.

Assume that you have issued 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, appropriate rows can be taken out directly. If the separate single-row and column indexes exist on col1 and col2, The Optimizer tries to determine which index will find fewer rows and find more restrictive indexes and use this index to retrieve rows.

When designing SQL data tables, developers must make it clear. BitsCN.com