• notice
  • Congratulations on the launch of the Sought Tech site

Mysql takes up an optimization method when using high CPU (must see)

When Mysql occupies too much CPU, which aspects should be optimized?

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

1) Generally speaking, to exclude the factors of high concurrency, you still have to find which SQL is being executed that causes your CPU to be too high, show processlist statement, find the SQL statement with the heaviest load, and optimize the SQL, such as appropriate Create an index of a field;

2) Open the slow query log, and use the SQL that takes too long to execute and takes up too much resources for explain analysis, which leads to high CPU, mostly caused by GroupBy and OrderBy sorting problems, and then slowly optimize and improve.Such as optimizing insert statement, optimizing group by statement, optimizing order by statement, optimizing join statement, etc.;

3) Consider timing optimization of files and indexes;

4) Analyze the table regularly and use optimize table;

5) Optimize database objects;

6) Consider whether it is a lock issue;

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 highly available environment.

9) The database CPU may be high due to a memory latch (leak)

10) In the case of multi-user high concurrency, any system will not be able to hold it.Therefore, the use of cache is a must.Memcached or redis cache can be used;

11) Check if the size of tmp_table_size is too small, if allowed, increase it appropriately;

12) If the max_heap_table_size configuration is too small, increase it a bit;

13) mysql sql statement sleep connection timeout setting problem (wait_timeout)

14) Use show processlist to view the number of mysql connections to see if it exceeds the number of connections set by mysql

The following is an example of a case encountered:

When the website is visited during peak hours, clicking on the page is a bit stuck.Log in to the server and found that the load of the machine is a bit high, and mysql occupies high CPU resources, as shown in the following figure:

The MySQL load remains high.If the slow query log function is turned on, the best way is to optimize the slow SQL statement in the slow query log.If the SQL statement uses a lot of group by and other statements , Union joint query, etc.will definitely increase the occupancy rate of mysql.So you need to optimize the sql statement

In addition to optimizing SQL statements, some configuration optimizations can also be done.Run show proceslist in mysql; the following echo result appears:

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

It is obvious that mysql writes the temporary table to the hard disk because the temporary table is too large, which affects the overall performance.

The default value of tmp_table_size in Mysql is only 16MB, which is obviously not enough under current circumstances.
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, add global, the next time you enter mysql, it will take effect

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.show processlist; The output of the command shows which threads are running, which can help identify problematic queries.For example, the following result:

Id User Host db Command Time State Info
207 root 192.168.1.25:51718 mytest Sleep 5 NULL
Let me briefly talk about the meaning and purpose of each column.The first column, id, needless to say, an identifier, which is very useful when you want to kill a statement.The user column displays the previous user.If it is not root, this command will only display the SQL statements within your authority.The host column shows which port of which ip this statement was issued from.Hehe, it can be used to track the user who has the problem statement.The db column shows which database the process is currently connected to.The command column displays the currently connected commands, usually sleep, query, and connect.The time column, the duration of this state, in seconds.The state column shows the state of the SQL statement using the current connection.It is a very important column.There will be descriptions of all the states in the follow-up.Please note that the state is only a certain state in the execution of the statement.An SQL statement is queried as an example.It can be completed after copying to tmp table, Sorting result, Sending data, etc.The info column shows this sql statement.Because of the limited length, the long sql statement is not displayed completely, but it is an important basis for judging the problem statement.

Frequently asked questions:

Generally, there are too many sleep connections, which seriously consume mysql server resources (mainly cpu, 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 number of seconds for the sleep connection timeout.If a connection times out, it will be naturally terminated by mysql.
If wait_timeout is too large, it has its drawbacks.The manifestation is that a large number of SLEEP processes in MySQL cannot be released in time, which slows down system performance.However, this should not 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 there may be problems in some cases.For example, if there is a CRON script where the interval between two SQL queries is greater than 10 seconds, then this setting is There is a problem (of course, this is not an unsolvable problem, you can mysql_ping from time to time in the program, so that the server knows that you are still alive, 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 applies for the connection, it will cause 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, which is 8 hours.

If during wait_timeout seconds, the database connection (java.sql.Connection) has been in a waiting state, mysql will close the connection.At this time, the connection pool of your Java application still legally holds the reference to the connection.When using this connection to perform database operations, the above error was encountered.
You can increase the default value of the mysql global variable wait_timeout.

Check the mysql manual and found that the maximum value of wait_timeout is 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 optimization method (must see) when Mysql consumes too much CPU is all the content shared by the editor.I hope I can give you a reference and I hope you can support it.

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+