Optimization methods when Mysql occupies too high CPU (must read)

When Mysql occupies too much CPU, where should we start to optimize it?

If the CPU usage is too high, you can consider the following:

1) Generally speaking, excluding the high concurrency factor, you still need to find out which SQL statements are being executed that cause your CPU to be too high. Use the show processlist statement to find the SQL statement with the heaviest load and optimize it, such as creating an index for a certain field.

2) Open the slow query log and explain the SQL statements that take too long to execute and occupy too many resources. The high CPU usage is mostly caused by GroupBy and OrderBy sorting problems, and then slowly optimize and improve them. For example, optimize insert statements, optimize group by statements, optimize order by statements, optimize join statements, etc.

3) Consider optimizing files and indexes regularly;

4) Analyze the table regularly and use optimize table;

5) Optimize database objects;

6) Consider whether it is a lock problem;

7) Adjust some MySQL Server parameters, such as key_buffer_size, table_cache, innodb_buffer_pool_size, innodb_log_file_size, etc.

8) If the amount of data is too large, you can consider using a MySQL cluster or building a high-availability environment.

9) High database CPU usage may be caused by memory latch (leakage)

10) In the case of high concurrency of multiple users, any system will not be able to hold up, so it is necessary to use cache, either memcached or redis cache;

11) Check whether the size of tmp_table_size is too small. If possible, increase it appropriately.

12) If max_heap_table_size is configured too small, increase it a little;

13) MySQL SQL statement sleep connection timeout setting problem (wait_timeout)

14) Use show processlist to check the number of MySQL connections to see if it exceeds the number of connections set by MySQL.

Here is a case I have encountered:

The website is accessed during peak hours and there is a bit of lag when clicking pages. Log in to the server and find that the machine load is a bit high, and MySQL occupies a high CPU resource, as shown below:

The MySQL load remains high. If the slow query log function is turned on, the best way is to optimize the slow SQL statements in the slow query log. If the SQL statements use a large number of group by statements, union queries, etc., the MySQL usage rate will definitely increase. So you need to optimize the SQL statement

In addition to optimizing SQL statements, you can also do some configuration optimization. Run show procedureslist in mysql; the following results are displayed:

1. Query a large number of Copying to tmp table on disk status

Obviously, because the temporary table is too large, MySQL writes the temporary table to the hard disk, affecting the overall performance.

The default value of tmp_table_size in Mysql is only 16MB, which is obviously not enough in the current situation.
mysql> show variables like "%tmp%";
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
+-------------------+----------+
4 rows in set (0.00 sec)

Solution: Adjust the size of the temporary table

1) Enter the MySQL terminal command to modify it, add global, and it will take effect next time you enter MySQL

mysql> set global tmp_table_size=33554432;
Query OK, 0 rows affected (0.00 sec)

Log in to mysql again
mysql> show variables like "%tmp%";
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 33554432 |
| tmpdir | /tmp |
+-------------------+----------+
4 rows in set (0.01 sec)

2) my.cnf configuration file modification

[root@www ~]# vim my.cnf
.....
tmp_table_size = 32M

Restart mysql
[root@www ~]# /etc/init.d/mysqld restart

2. The output of the show processlist; command shows which threads are running, which can help identify problematic queries. For example, the following results:

Id User Host db Command Time State Info
207 root 192.168.1.25:51718 mytest Sleep 5 NULL
Let me first briefly explain the meaning and purpose of each column. The first column, id, is a flag that is very useful when you want to kill a statement. The user column shows the current user. If you are not the root user, this command will only show the SQL statements within your permission range. The host column shows the IP and port from which the statement was issued. Haha, it can be used to track down the user who made the problematic statement. The db column shows which database the process is currently connected to. The command column displays the command executed by the current connection, which is usually sleep, query, or connect. The time column is the duration of this state in seconds. The state column displays the status of the SQL statement using the current connection. It is a very important column. All the statuses will be described later. Please note that state is only a certain status in the statement execution. For example, a SQL statement, such as query, may need to go through the states of copying to tmp table, sorting result, sending data, etc. before it can be completed. The info column displays the SQL statement. Due to the limited length, long SQL statements are not fully displayed, but it is an important basis for judging problem statements.

Frequently Asked Questions:

Generally, there are too many sleeping connections, which seriously consume MySQL server resources (mainly CPU and memory) and may cause MySQL to crash.

Solution:

In the MySQL configuration my.cnf file, there is a wait_timeout parameter setting. You can set the sleep connection timeout in seconds. If a connection times out, it will be terminated naturally by MySQL.
There are disadvantages to setting wait_timeout too large, which means that a large number of SLEEP processes in MySQL cannot be released in time, dragging down system performance. However, this indicator cannot be set too small, otherwise you may encounter problems such as "MySQL has gone away".
Generally speaking, setting wait_timeout to 10 hours is a good choice, but it may cause problems in some cases. For example, if there is a CRON script and the interval between two SQL queries is greater than 10 seconds, then this setting will be problematic (of course, this is not an unsolvable problem. You can mysql_ping from time to time in the program so that the server knows you are still alive and recalculate the wait_timeout time):

The default "wait_timeout" of the MySQL server is 28800 seconds, or 8 hours, which means that if a connection is idle for more than 8 hours, MySQL will automatically disconnect the connection.

However, the connection pool believes that the connection is still valid (because the validity of the connection is not verified). When the application requests to use the connection, it will result in the following error:

The last packet successfully received from the server was 596,688 milliseconds ago.
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

28800seconds, or 8 hours.

If the database connection (java.sql.Connection) remains in a waiting state within wait_timeout seconds, MySQL will close the connection. At this point, your Java application's connection pool still legally holds a reference to the connection. When using this connection to perform database operations, the above error is encountered.
You can increase the default value of the MySQL global variable wait_timeout.

Checking the MySQL manual, I found that the maximum values ​​for wait_timeout are 24 days/365 days (windows/linux).

For example, change it to 30 days

mysql> set global wait_timeout=124800;
Query OK, 0 rows affected (0.00 sec)

The above article on optimization methods when Mysql occupies too high CPU (must read) is all the content that the editor shares with you. I hope it can give you a reference, and I also hope that you will support 123WORDPRESS.COM.