Summary of high occupancy problem solutions for Mysql CPU
- 2020-05-13 03:33:44
- OfStack
Through the previous mysql operation experience, the configuration problem of mysql has been eliminated. To check whether msyql is running normally, you can check the *.err file (change the extension to.txt) in the mysql data directory. If it is not recommended to use notepad, easy to die, you can use editplus and other tools
Here are a few simple steps to solve this problem:
1. mysql is running normally, or it may be caused by synchronization Settings
2. If mysql is running normally, it is the sql statements of php that lead to the problem discovery, and the root user enters mysql management
mysql -u root -p
Enter the password
mysql:show processlist statement, find the most heavily loaded SQL statement, and optimize the SQL statement, such as properly indexing a field.
From this command I can see that it was a malicious search, because the dedecms search followed by the call to search for the highest word, which caused many people to use the tool to brush this, and there are regular intervals, so rename the php program jump method is solved.
Of course, if your sql statement does use a lot of group by and so on, union joint queries and so on will definitely increase the occupancy of mysql. Therefore, we need to optimize the sql statement, and try to generate a static website like 4W ip, and the occupancy rate of mysql is almost zero. So this is a consideration for the programmer's experience. Maximize mysql performance (top 20 tips for MySQL performance optimization)
The following is a collection of articles from this site that you can refer to
MYSQL CPU accounts for 100% of the phenomenon described
In the morning, I helped a friend with a server to solve the problem that Mysql cpu occupied 100%. Sort out a little 1, the experience will be recorded in this article
Recently, the MySQL service process (mysqld-nt.exe) CPU has a high occupancy rate of 100%. This host has about 10 database, which are called to 10 websites respectively. According to a friend's test, the site that causes mysqld-nt.exe cpu to take up a huge amount of space is A. Once the site is taken out of service in IIS, the CPU usage drops. When 1 is enabled, it goes up immediately.
MYSQL CPU occupies 100% of the resolution process
I had a double check this morning. At present, the average daily IP of this website is 2000 and PageView is about 30,000. The website database used by A currently has 39 tables with 601,000 records, occupying 45MB. At this figure, MySQL would not be able to consume such a high level of resources.
So run the command on the server and output mysql's current environment variables to the file output.txt:
d:\web\mysql > mysqld.exe --help > output.txt
tmp_table_size is found to be the default value of 32M, so My.ini is modified to assign tmp_table_size to 200M:
d:\web\mysql > notepad c:\windows\my.ini
[mysqld]
tmp_table_size=200M
Then restart the MySQL service. There was a slight decrease in the CPU occupancy. The previous CPU occupancy waveform was 100% 1 straight line, but now it fluctuates between 97% and 100%. This indicates that the adjustment of tmp_table_size parameter can improve the performance of MYSQL. But the problem is not completely solved.
Go to the shell command line of mysql, call show processlist, and see the sql statement that mysql USES frequently:
mysql > show processlist;
Repeated calls to this command revealed that the two SQL statements of the website A were frequently found in process list, with the syntax 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, I found the problem: the _mydata table, which only builds an primary key based on pid, but does not index userid. And in the first LEFT JOIN ON 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 created an index for the _mydata table based on the field userid:
mysql > ALTER TABLE `_mydata` ADD INDEX ( `userid` )
Immediately after this index was created, the CPU dropped to about 80%. Seeing that the problem has been identified, I examine 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, not index for keywords. _mydata_key currently has 330,000 records, and matching 330,000 records by text retrieval without an index would not take a lot of cpu time. It seems that there is something wrong with the retrieval of this table. So again, index the _mydata_key table according to the field keywords:
mysql > ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )
Immediately after this index was created, CPU came down, oscillating between 50% and 70%.
Call show prosslist again, and the sql call to website A rarely shows up in the results list. However, it was found that this host was running several Discuz forum programs, and several tables of the Discuz forum also had this problem. So the cpu occupancy went down again. (note: about the specific optimization process of discuz forum, I wrote another article later. For details, please refer to: Discuz! BBS in MySQL CPU http: 100% of the optimized note. / / www xiaohui. com/dev/server / 20070701 - discuz - mysql cpu - 100 - optimize. htm)
Resolve MYSQL CPU 100% experience summary
Increase the tmp_table_size value. In the configuration file for mysql, the default size for tmp_table_size is 32M. If a temporary table is larger than this size, MySQL generates an The table tbl_name is full error. If you do a lot of advanced GROUP BY queries, increase the tmp_table_size value. This is the 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 32MB (the default) to 64MB 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.
For fields used in conditional judgments in clauses such as WHERE, JOIN, MAX(), MIN(), ORDER BY, INDEX should be indexed according to them. Indexes are used to quickly find rows that have a particular value of 1 on a column. Without an index, MySQL has to start with the first record and then read through the table until it finds the relevant row. The bigger the watch, the more time it takes. If the table has one index for the columns of the query, MySQL can quickly go to one location to search the middle of the data file. There is no need to consider 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 the B tree.
According to the development documentation of mysql:
The index index is used for:
Quickly find the line that matches an WHERE clause
When a join (JOIN) is performed, rows are retrieved from other tables.
Find the MAX() or MIN() values for a particular indexed column
If sorting or grouping takes place on the leftmost prefix of one of the available keys (for example, ORDER BY key_part_1,key_part_2), sort or group one table. If all key value parts follow DESC, the key is read in reverse order.
In some cases, a query can be optimized to retrieve a value without consulting a data file. If all the columns used for some tables are numeric and form the leftmost prefix for some keys, the value can be retrieved from the index tree for faster results.
Suppose 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 indexes exist on col1 and col2, the optimizer tries to find the more restrictive index by deciding which index will find fewer rows and use that index to fetch rows.
When developers are designing the SQL data table, they must think through it all.
Here are a few simple steps to solve this problem:
1. mysql is running normally, or it may be caused by synchronization Settings
2. If mysql is running normally, it is the sql statements of php that lead to the problem discovery, and the root user enters mysql management
mysql -u root -p
Enter the password
mysql:show processlist statement, find the most heavily loaded SQL statement, and optimize the SQL statement, such as properly indexing a field.
From this command I can see that it was a malicious search, because the dedecms search followed by the call to search for the highest word, which caused many people to use the tool to brush this, and there are regular intervals, so rename the php program jump method is solved.
Of course, if your sql statement does use a lot of group by and so on, union joint queries and so on will definitely increase the occupancy of mysql. Therefore, we need to optimize the sql statement, and try to generate a static website like 4W ip, and the occupancy rate of mysql is almost zero. So this is a consideration for the programmer's experience. Maximize mysql performance (top 20 tips for MySQL performance optimization)
The following is a collection of articles from this site that you can refer to
MYSQL CPU accounts for 100% of the phenomenon described
In the morning, I helped a friend with a server to solve the problem that Mysql cpu occupied 100%. Sort out a little 1, the experience will be recorded in this article
Recently, the MySQL service process (mysqld-nt.exe) CPU has a high occupancy rate of 100%. This host has about 10 database, which are called to 10 websites respectively. According to a friend's test, the site that causes mysqld-nt.exe cpu to take up a huge amount of space is A. Once the site is taken out of service in IIS, the CPU usage drops. When 1 is enabled, it goes up immediately.
MYSQL CPU occupies 100% of the resolution process
I had a double check this morning. At present, the average daily IP of this website is 2000 and PageView is about 30,000. The website database used by A currently has 39 tables with 601,000 records, occupying 45MB. At this figure, MySQL would not be able to consume such a high level of resources.
So run the command on the server and output mysql's current environment variables to the file output.txt:
d:\web\mysql > mysqld.exe --help > output.txt
tmp_table_size is found to be the default value of 32M, so My.ini is modified to assign tmp_table_size to 200M:
d:\web\mysql > notepad c:\windows\my.ini
[mysqld]
tmp_table_size=200M
Then restart the MySQL service. There was a slight decrease in the CPU occupancy. The previous CPU occupancy waveform was 100% 1 straight line, but now it fluctuates between 97% and 100%. This indicates that the adjustment of tmp_table_size parameter can improve the performance of MYSQL. But the problem is not completely solved.
Go to the shell command line of mysql, call show processlist, and see the sql statement that mysql USES frequently:
mysql > show processlist;
Repeated calls to this command revealed that the two SQL statements of the website A were frequently found in process list, with the syntax 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, I found the problem: the _mydata table, which only builds an primary key based on pid, but does not index userid. And in the first LEFT JOIN ON 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 created an index for the _mydata table based on the field userid:
mysql > ALTER TABLE `_mydata` ADD INDEX ( `userid` )
Immediately after this index was created, the CPU dropped to about 80%. Seeing that the problem has been identified, I examine 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, not index for keywords. _mydata_key currently has 330,000 records, and matching 330,000 records by text retrieval without an index would not take a lot of cpu time. It seems that there is something wrong with the retrieval of this table. So again, index the _mydata_key table according to the field keywords:
mysql > ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )
Immediately after this index was created, CPU came down, oscillating between 50% and 70%.
Call show prosslist again, and the sql call to website A rarely shows up in the results list. However, it was found that this host was running several Discuz forum programs, and several tables of the Discuz forum also had this problem. So the cpu occupancy went down again. (note: about the specific optimization process of discuz forum, I wrote another article later. For details, please refer to: Discuz! BBS in MySQL CPU http: 100% of the optimized note. / / www xiaohui. com/dev/server / 20070701 - discuz - mysql cpu - 100 - optimize. htm)
Resolve MYSQL CPU 100% experience summary
Increase the tmp_table_size value. In the configuration file for mysql, the default size for tmp_table_size is 32M. If a temporary table is larger than this size, MySQL generates an The table tbl_name is full error. If you do a lot of advanced GROUP BY queries, increase the tmp_table_size value. This is the 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 32MB (the default) to 64MB 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.
For fields used in conditional judgments in clauses such as WHERE, JOIN, MAX(), MIN(), ORDER BY, INDEX should be indexed according to them. Indexes are used to quickly find rows that have a particular value of 1 on a column. Without an index, MySQL has to start with the first record and then read through the table until it finds the relevant row. The bigger the watch, the more time it takes. If the table has one index for the columns of the query, MySQL can quickly go to one location to search the middle of the data file. There is no need to consider 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 the B tree.
According to the development documentation of mysql:
The index index is used for:
Quickly find the line that matches an WHERE clause
When a join (JOIN) is performed, rows are retrieved from other tables.
Find the MAX() or MIN() values for a particular indexed column
If sorting or grouping takes place on the leftmost prefix of one of the available keys (for example, ORDER BY key_part_1,key_part_2), sort or group one table. If all key value parts follow DESC, the key is read in reverse order.
In some cases, a query can be optimized to retrieve a value without consulting a data file. If all the columns used for some tables are numeric and form the leftmost prefix for some keys, the value can be retrieved from the index tree for faster results.
Suppose 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 indexes exist on col1 and col2, the optimizer tries to find the more restrictive index by deciding which index will find fewer rows and use that index to fetch rows.
When developers are designing the SQL data table, they must think through it all.