Optimize MySQL's memory

When MySQL consumes too much CPU, what should I do to optimize it?
High CPU consumption, can be considered as follows:
1) Generally, to eliminate the high concurrency factor, or to find out which of your CPU is too high in the execution of the SQL,Show Processlist Statement，find the most heavily loaded SQL statement, optimize the SQL,such as the appropriate establishment of a field index;
2) Open the slow query log, the execution time is too long and excessive resources of SQL to carry out explain analysis, resulting in high CPU, most of the groupby, order by sort problem caused, and then slowly optimize the improvement. such as optimizing INSERT statements, optimizing GROUP BY statements, optimizing order BY statements, optimizing join statements, and so on;
3) considertiming Optimization files and indexes；
4) Periodic analysis table, using optimize table;
5) Optimize database objects;
6) consider whetherIt's a lock problem.；
7) Adjust some MySQL server parameters, such askey_buffer_size, Table_cache, innodb_buffer_pool_size, Innodb_log_file_sizeAnd so on
8) If the amount of data is too large, consider using a MySQL cluster or building a highly available environment.
9) High database CPU may be caused by memory latch (leak)
10) In the case of multi-user high concurrency, any system will hold, so, useCacheis required, usememcached or Redis cacheAll can;
11) Looktmp_table_size SizeIs it too small, if allowed,a little more appropriate.；
12) ifmax_heap_table_sizeThe configuration is too small,increase a little；
MySQL SQL statement sleep connection Timeout time setting problem (Wait_timeout）
14) Use Show processlist to view the number of MySQL connections to see if the number of connections to MySQL settings is exceeded (http://www.cnblogs.com/kevingrace/p/6226324.html)

Here's a sample of a case you've encountered:
Site during peak hours access, click on the page a little card. Landing server, found that the machine load is a bit high, and MySQL occupies a high CPU resources, as follows:

MySQL load is high, if you open the slow query log function, the best way is to slow query log execution Slow SQL statement optimization, if the SQL statement with a large number of group by and other statements, Union union query will certainly increase MySQL occupancy rate. So we need to optimize the SQL statement

Apart fromOptimizing SQL statements, you can also do some optimizations on the configuration. Run show proceslist in MySQL; The following echo results appear:
1. EnquiryThere is a large number of copying to TMP table on disk status
is obviously due totemporary table too large causes MySQL to write temporary tables to the hard diskaffect the overall performance.

The default value for 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)

Workaround: Adjust the temp table size
1) into MySQL terminal command modification, plus global, the next time you enter MySQL 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
[Email protected] ~]#Vim my.cnf
.....
tmp_table_size = 32M

Restart MySQL
[Email protected] ~]#/etc/init.d/mysqld restart

2. Show Processlist;The output of the command shows which threads are running and can help identify problematic query statements. For example, the following results:
Id User Host db Command time State Info
207 Root 192.168.1.25:51718 mytest Sleep 5 NULL
First of all, the meaning and purpose of the columns,

The first column, ID, an identifier, is useful when you want to kill a statement.

The User column shows the single-user, if not root, this command displays only the SQL statements within the scope of your permission.

The host column that shows which IP port this statement was issued from. Oh, can be used to track the problem of the user statement.

The DB column that shows which database the process is currently connected to.

Command column , which displays the execution commands for the current connection, typically sleep (sleep), query, connection (connect). Time column, which is the duration of this state, in seconds.

The State column , which shows the status of the SQL statement using the current connection, the very important column, followed by a description of all States, note that State is just one of the states in the statement execution, an SQL statement, which has been queried as an example, may need to go through copying to TMP Table,sorting result,sending data and other states can be completed,

Info Column, the SQL statement is displayed because the length is limited, so the long SQL statement is incomplete, but an important basis for judging the problem statement.
Problems:
is generallyToo many sleep connections, severely consuming MySQL server resources (mainly CPU, memory),and may cause MySQL to crash.

Workaround:
In the MySQL configuration my.cnf file, there is an wait_timeout parameter setting. You can set the sleep connection timeout in seconds, and if a connection times out, it will be terminated by MySQL naturally.
Wait_timeout has a lot of drawbacks, its embodiment is that a large number of sleep in MySQL can not be released in a timely manner, drag down the system performance, but also can not be set this refers to too small, or you may encounter "MySQL has gone away" and so on.
Generally speaking, putWait_timeout set to 10 hours isA good choice, but in some cases may also be problematic, for example, there is a cron script, where two times the interval between SQL queries more than 10 seconds, then this setting is a problem (of course, this is not an unresolved problem, you can in the program occasionally mysql_ping a bit, So that the server knows you're still alive, recalculate wait_timeout time):

The default "Wait_timeout" for MySQL server is 28,800 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 considers the connection to be valid (because the validity of the connection is not verified), which causes the following error when the application requests the connection:
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, i.e. 8 hours.
If the database connection (java.sql.Connection) is in a waiting state for a period of wait_timeout seconds, MySQL shuts down the connection. At this point, your Java app's connection pool still legitimately holds a reference to that connection. This error is encountered when the connection is used for database operations.
The default value of the MySQL global variable wait_timeout can be changed to large.
View the MySQL manual and find that the maximum value for Wait_timeout is 24 days/365 days (Windows/linux), respectively.

Like changing it to 30 days.
mysql> set global wait_timeout=124800;
Query OK, 0 rows Affected (0.00 sec)

Optimize MySQL's memory