Optimization Methods when Mysql consumes too much CPU (mandatory). mysql is mandatory.

Optimization Methods when Mysql consumes too much CPU (mandatory). mysql is mandatory.

When the CPU usage of Mysql is too high, which aspects should we optimize?

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

1) In general, to eliminate high concurrency, you should find the SQL statements that cause your CPU to be too high and the show processlist statements that are being executed, and find the SQL statements with the heaviest load, optimize the SQL statement, such as creating an index for a field;

2) Open the slow query log and use the SQL statements that take too long execution time and occupy too many resources for explain analysis. As a result, the CPU usage is too high, most of which are caused by GroupBy and OrderBy sorting problems, then we will make improvements. For example, optimize insert statements, optimize group by statements, optimize order by statements, and optimize join statements;

3) Optimize files and indexes on a regular basis;

4) Regular analysis table, using optimize table;

5) optimize database objects;

6) check whether the lock problem exists;

7) adjust some MySQL Server parameters, such as key_buffer_size, table_cache, innodb_buffer_pool_size, and innodb_log_file_size;

8) if the data volume is too large, you can use a MySQL cluster or build a high-availability environment.

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

10) in the case of high concurrency among multiple users, any system will not be able to hold it. Therefore, it is necessary to use the cache, and memcached or redis can be used for caching;

11) check whether the size of tmp_table_size is too small. If allowed, increase the size by a bit;

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

13) mysql SQL statement sleep connection timeout setting (wait_timeout)

14) use show processlist to check the number of mysql connections and check whether the number of connections set by mysql has exceeded.

The following is a case study:

Visit the website during peak hours and click the page with a card. Log on to the server and find that the server load is a little high, and mysql occupies a high CPU resource, such:

MySQL has a high load. If the slow query log function is enabled, the best solution is to optimize the execution of slow SQL statements in slow query logs, if the SQL statement uses a large number of group by and other statements, the union Joint query will definitely increase the usage of mysql. Therefore, you need to optimize SQL statements.

In addition to optimizing SQL statements, you can also optimize some configurations. Run show proceslist in mysql. The following echo result is displayed:

1. query a large number of Copying to tmp table on disk statuses

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

In Mysql, the default value of tmp_table_size is only 16 MB, 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 temporary table size

1) Enter mysql terminal command modification, add global, the next mysql entry will take effect

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

Log on 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) modify the configuration file my. cnf

[Root @ www ~] # Vim my. cnf
.....
Tmp_table_size = 32 M

Restart mysql
[Root @ www ~] #/Etc/init. d/mysqld restart

2. show processlist; the command output shows which threads are running and can help identify problematic query statements. For example, the following result is returned:

Id User Host db Command Time State Info
207 root 192.168.1.25: 51718 mytest Sleep 5 NULL
Let's briefly describe the meaning and purpose of each column. The first column, id, needless to say, is an identifier. It is useful when you want to kill a statement. The user column displays the user before the ticket. If it is not the root user, this command only displays the SQL statements within your permission range. Host column, indicating the port from which the statement is sent. Well, it can be used to track users with problematic statements. Db column to display the database to which the process is currently connected. Command column, showing the commands executed by the current connection, which is generally sleep, query, and connect ). Time column, the duration of this state, in seconds. The state column displays the status of the SQL statement that uses the current connection. It is an important column and will be described in the future. Note that the state is only a certain state in the statement execution, for an SQL statement that has been queried as an example, it may need to pass through the copying to tmp table, Sorting result, Sending data and other statuses to complete the SQL statement. The info column displays this SQL statement because the length is limited, therefore, long SQL statements are not displayed completely, but an important basis for determining a problematic statement.

FAQs:

Generally, too many sleep connections 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 seconds. If a connection times out, it will be naturally terminated by mysql.
Wait_timeout has many drawbacks. It is reflected that a large number of SLEEP processes in MySQL cannot be released in time, dragging down system performance, but it cannot be set too small, otherwise, you may encounter problems such as "MySQL has gone away.
Generally, setting wait_timeout to 10 hours is a good option, but in some cases it may also cause problems. For example, there is a CRON script, if the interval between the two SQL queries is greater than 10 seconds, this setting will be problematic (of course, this is not an unsolved problem. You can ping mysql_ping from time to time in the program, so that the server knows that you are still alive and recalculates the wait_timeout Time ):

The default "wait_timeout" of the MySQL server is 28800 seconds, that is, 8 hours. This means that if the idle time of a connection exceeds 8 hours, MySQL will automatically disconnect the connection.

However, the connection pool considers the connection to be valid (because the connection is not verified). When the application applies to use the connection, the following error occurs:

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)

28800 seconds, that is, 8 hours.

If the database Connection (java. SQL. Connection) remains in the waiting state within the period of wait_timeout, mysql closes the Connection. At this time, the connection pool of your Java application still legally holds the reference of the connection. When you use this connection for database operations, you will encounter the above error.
You can increase the default value of the mysql global variable wait_timeout.

Check the mysql manual and find that the maximum value of wait_timeout is 24 days/365 days (windows/linux ).

For example, change it to 30 days.

Mysql & gt; set global wait_timeout = 124800;
Query OK, 0 rows affected (0.00 sec)

The above optimization method (mandatory) for Mysql CPU usage is all the content shared by Alibaba Cloud xiaobian. I hope you can give us a reference and support the customer's house.