MySQL Performance tuning ideas

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