Record mysql Tuning

Symptom

Mysql CPU usage: 188% (multi-core)

Solution

1. Slow Query

Vi/etc/my. cnf Add the following content under mysqld:
 

Log-slow-queries = slow. log
Long_query_time = 5

It means to record the SQL to slow. log for more than 5 seconds.
Find the content of slow. log and find that there are a large number of SQL statements in it. Below are several SQL Optimization Principles
A) create an index on the fields in the where statement. Assume that the following SQL statement is the content of slow. log.
Select count (*) from table where table_column = 'test'

You need to create an index for the table_column (this is only a common rule. For details, please go to the Internet to check the rules)

After the index is created, modify the mysql parameters.

2. Adjust mysql Parameters

Vi/etc/my. cnf

Add the following under [mysqld ]:
Tmp_table_size = 1024 M

Restart mysql after adding it, And then observe it. If the load is still high, you can increase it accordingly. After I adjust this parameter to 2048, the load will be down.

This parameter value should be treated according to specific circumstances, and there is no specific value.

So what is the purpose of this parameter? Why is it so amazing? Haha, when executing a query, If SQL performs group by or orderby on the query, it will put the result set in this space. Therefore, if this parameter is small, the above problem will occur ~~