MySQL optimization ideas

Through the script, refresh the status of the watch MySQL to see if there are periodic fault live fluctuations,
Usually caused by peak access or cache invalidation, home cache and change cache invalidation policy, is the expiration time dispersion or page timing loss,

Show Processlist shows which threads are running.

You can also use the Mysqladmin processlist statement to get this information. If you have super privileges, you can see all the threads. Otherwise, you can only see your own threads
MySQL turn on slow query log

Slow_query_log This parameter is set to on to capture an SQL statement that executes longer than a certain number. Long_query_time when the SQL statement execution time exceeds this value, it is recorded in the log and is recommended to be set to 1 or less slow_query_log_file the file name of the logging. Log_queries_not_using_indexes This parameter is set to on to capture all SQL statements that do not use the index, although this SQL statement is likely to execute very quickly.

Use Profiler to analyze the execution time and performance bottlenecks of a query,
Open profiling;

Set profiling=1;

Arbitrarily executes a statement select COUNT (*) from user where id>2;

Show Profiles;

Get

+----------+------------+--------------------------------------+| query_id | Duration   | Query                                |+----------+------------+--------------------------------------+|         0.00009200 | set profiling=1                      | |        id>2 |+----------+------------+--------------------------------------+   

Contains a query_id and execution time and a query statement
More detailed information can be viewed through the query_id;

Show profile CPU, block IO  5;

+----------------------+----------+----------+------------+--------------+---------------+| Status | Duration | Cpu_user | Cpu_system | block_ops_in | Block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| Starting |0.000170 |0.000000 |0.000000 |0 |0 | | Checking Permissions |0.000019 |0.000000 |0.000000 |0 |0 | | Opening Tables |0.000033 |0.000000 |0.000000 |0 |0 | | init |0.000061 |0.000000 |0.000000 |0 |0 | | System Lock |0.000021 |0.000000 |0.000000 |0 |0 | | Optimizing |0.000021 |0.000000 |0.000000 |0 |0 | | Statistics |0.010826 |0.000000 |0.000000 |184 |0 | | Preparing |0.000041 |0.000000 |0.000000 |0 |0 | | Executing |0.000005 |0.000000 |0.000000 |0 |0 | | Sending Data |0.008731 |0.008000 |0.000000 |0 |0 | | End |0.000020 |0.000000 |0.000000 |0 |0 | | Query End |0.000018 |0.000000 | 0.000000 | 0 | 0 | | closing Tables | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000032 | 0.000000 | 0.000000 | 0 | 0 | | cleaning Up | 0.000027 | 0.000000 | 0.000000 | 0 | 0 |+----------------------+----------+----------+------------+--------------+------ ---------+ 

Optimization of MySQL server do not go to optimize the SQL statement, you should first observe the global situation, at least first to understand
The problem is that you should use a script to observe the health of the server for a period of time (one day or longer), such as Cpu,io, number of process connections, etc.
The final analysis is where the specific cause lies;

MySQL optimization ideas