Detailed introduction to optimization methods when Mysql takes up too much CPU

The editor below will bring you an articleMysqlOptimization methods when occupying too much CPU (must read). The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

When Mysql takes up too much CPU, what aspects should be optimized?

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

1) Generally speaking, to eliminate high concurrency factors, you still need to find the cause Which SQL statements are being executed because your CPU is too high, use the show processlist statement to find the SQL statement with the heaviest load, and optimize the SQL, such as appropriately establishing an index on a certain field;

2) Open the slow query log, Use the SQL statements that take too long to execute and occupy too many resources to explain and analyze, resulting in excessive CPU usage. Most of them are caused by GroupBy and OrderBy sorting issues, and then slowly optimize and improve them. For example, optimize the insert statement, optimize the group by statement, optimize the order by statement, optimize the join statement, 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) The database CPU may be high due to memory latch (leakage)

10) In the case of multi-user high concurrency, any system will not be able to hold it, so the use of cache is necessary Yes, you can use memcached or redis cache;

11) Check whether the tmp_table_size size is too small. If allowed, 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 connection set by mysql Number

The following is a case I have encountered:

The website is accessed during peak hours, and the page clicks are a bit stuck. Log in to the server and find that the machine load is a bit high, and mysql takes up a lot of CPU resources, as shown below:

MySQL load remains high. If it is slow to open For the query log function, the best way is to optimize the slow execution of SQL statements in the slow query log. If the SQL statement uses a large number of group by statements, unionUnion query, etc. It will definitely increase the occupancy rate of mysql. Therefore, you need to optimize the sql statement

In addition to optimizing the sql statement, you can also do some configuration optimization. Run show proceslist in mysql; the following echo results appear:

1. The query has a large number of Copying to tmp table on disk status

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

The default value of tmp_table_size in Mysql is only 16MB, which is obviously not enough under the current circumstances.

mysql> show variables like "%tmp%";
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| max_tmp_tables | 32 |
| slave_load_tmp
dir
 | /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 Go to 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.cnfConfiguration fileModify

[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 query statements. For example, the following result:

Id User Host db Command Time State Info 
207 root 192.168.1.25:51718 mytest Sleep 5 
NULL

先简单说一下各列的含义和用途，第一列，id，不用说了吧，一个标识，你要kill一个语句的时候很有用。user列，显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。host列，显示这个语句是从哪个ip的哪个端口上发出的。呵呵，可以用来追踪出问题语句的用户。db列，显示这个进程目前连接的是哪个数据库 。command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。time列，此这个状态持续的时间，单位是秒。state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成，info列，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

常见问题：

一般是睡眠连接过多，严重消耗mysql服务器资源(主要是cpu, 内存)，并可能导致mysql崩溃。

解决办法 ：

在mysql的配置my.cnf文件中，有一项wait_timeout参数设置.即可设置睡眠连接超时秒数，如果某个连接超时，会被mysql自然终止。
wait_timeout过大有弊端，其体现就是MySQL里大量的SLEEP进程无法及时释放，拖累系统性能，不过也不能把这个指设置的过小，否则你可能会遭遇到“MySQL has gone away”之类的问题。
通常来说，把wait_timeout设置为10小时是个不错的选择，但某些情况下可能也会出问题，比如说有一个CRON脚本，其中两次SQL查询的间隔时间大于10秒的话，那么这个设置就有问题了（当然，这也不是不能解决的问题，你可以在程序里时不时mysql_ping一下，以便服务器知道你还活着，重新计算wait_timeout时间）：

MySQL服务器默认的“wait_timeout”是28800秒即8小时，意味着如果一个连接的空闲时间超过8个小时，MySQL将自动断开该连接。

然而连接池却认为该连接还是有效的(因为并未校验连接的有效性)，当应用申请使用该连接时，就会导致下面的报错：

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，也就是8小时。

如果在wait_timeout秒期间内，数据库连接(java.sql.Connection)一直处于等待状态，mysql就将该连接关闭。这时，你的Java应用的连接池仍然合法地持有该连接的引用。当用该连接来进行数据库操作时，就碰到上述错误。
可以将mysql全局变量wait_timeout的缺省值改大。

查看mysql手册，发现对wait_timeout的最大值分别是24天/365天(windows/linux)。

比如将其改成30天

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

The above is the detailed content of Detailed introduction to optimization methods when Mysql takes up too much CPU. For more information, please follow other related articles on the PHP Chinese website!