1.MySQL Performance Tuning Ideas
What if a server has a long time load too high/ periodic load is too large , or occasionally stuck how to handle ?
is it a cyclical change or an occasional problem ? is a problem with overall server performance , or the question of a single statement? ?
specific to a single statement, this statement is waiting on the time spent , or the time spent on the query ?
1.1.
Monitor and observe the status of the server .
observe the server status , generally with the following 2 commands
Show status; Show processlist;
Example : mysql> show status;
#mysqladmin ext
1.2.
MySQL Requests per second periodic changes , with cache failure , There is a short period of peak
Workaround :
1: reduce irrelevant requests ( business logic level , temporarily not discussed , But in fact is the most effective means )
2: If the number of requests is certain , it cannot be reduced . we'll try to keep the number of requests steady . , do not have drastic fluctuations .
most of the time, not the server can't hold the total query volume , but in a certain period of time cannot hold the peak request .
1.3.
observation of irregular delay phenomena
Irregular delays are often caused by inefficient statements, and how to catch these inefficient statements .
You can use the show processlist command for long-term observation , or slow query .
Show processlist;
This command displays the working status of all current connections .
#!/bin/bashwhile truedomysql-uroot-e ' show processlist\g ' |grep state:|uniq-c|sort-rnecho '---' Sleep 1Done
If you observe the following States , you need to be aware
converting HEAP to MyISAM When query results are too large , Put the results on disk ( statement not well written , fetching too much data )
Create TMP table creates temporary tables ( such as group when storing intermediate results , indicating that the cable Poor introduction of the building )
Copying to TMP table on disk copies the memory temp table to the disks ( The index is not good , the table field selection is not good )
Locked is locked by other queries ( typically occurs when using transactions , and Internet applications do not occur frequently )
Logging slow query records slow queries
1.4.
after MySQL 5.5 adds a profile setting , You can observe the execution steps of the specific statement .
See If profile is turned on
Show variables like ' profiling '
Open profile
Set Profiling=on;
View profiles;
Show Profiles;
View profile;
Show profile for query 1;
2. How do I navigate to a problematic statement ?
- Turn on server slow query
- Understanding the usage rules for temporary tables
2.1.
how MySQL uses internal temporary tables
In some scenarios where requests are processed , the server creates an internal temporary table . that is, the table MEMORY the engine is processed in memory , or by MyISAM the engine is stored on disk for processing . If the table is too large , The server may turn the temporary table in memory onto the disk .
Users cannot directly control internal memory or disk storage of temporary tables in the server
2.2.
Temporary tables are created in the following situations :
if the group by column does not have an index , an internal temporary table will be generated .
if order by is different from Group by, or if multiple tables are in order by, the group by A column that contains a column that is not the first table will produce a temporary table
Distinct with order by may produce temporary tables
If you use Sql_small_result,mysql uses a temporary memory table , unless some of the queries must have temporary tables on disk .
A temporary table is used when the union merges queries
Some views use temporary tables , such as using the temptable method , or using the Union or aggregate query views
to determine whether a query requires a temporary table , you can use the EXPLAIN query plan and view the Extra column , See if there is Using temporary.
if the temporary table that was created in memory at first becomes larger , it is automatically converted to a disk temp table . the maximum value of a temporary table in memory is tmp_table_size and the max_heap_size medium and small values .
This is not the same as displaying the specified memory table when you create TABLE : These tables are only affected by max_heap_table_size System parameters are affected .
when the server creates an internal temporary table ( whether in memory or on disk ), Create_tmp_tables variables will increase .
If you created an internal temporary table on disk ( Whether it was initially created or converted by in-memory ),
The Create_tmp_disk_tables variable will increase .
in some cases, the use of temporary memory tables is limited , and disk staging tables are used :
( with the use of an internal temporary table ) statement exists in BLOB or TEXT column
in the string column with greater than bytes in GROUP by or DISTINCT clause
in the when Union or Union all , a SELECT statement has a string greater than column .
build table : , If the core field is used int,char,enum non-core field , text, extra long varchar, split one table .
indexed: A reasonable index can reduce internal temporal tables ( detailed in the index optimization strategy )
Write statements : Unreasonable statements will result in large amounts of data transfer and the use of internal temporary tables .
MySQL Performance tuning ideas