MySQL find SQL time-consuming bottleneck SHOW profiles

http://blog.csdn.net/k_scott/article/details/8804384

1. First check whether the profiling function is turned on [SQL]View PlainCopy

1. SHOW VARIABLES like '%pro% ';

Or

[SQL]View PlainCopy

1. SELECT @ @profiling;

2. Open Profiling

[SQL]View PlainCopy

1. SET profiling=1;

3. Execute SQL statements

For example:

[SQL]View PlainCopy

1. select   
2.   table_schema as  ' Db name ',  
3.   round ( sum ( data_length +  index_length )  / 1024 / 1024, 3 )  as  ' db size  (MB) ',   
4.   round ( sum ( data_free )  / 1024 / 1024,  3 )  as  ' free space  (MB) '   
5. from information_schema.tables   
6. group by table_schema ;   

4. View Results

[SQL]View PlainCopy

1. SHOW profiles;

[SQL]View PlainCopy

1. SHOW profile all for QUERY 94;

94 is the query ID number.

SHOW Profiles Syntax:

[SQL]View PlainCopy

1. SHOW profile [Type [, type] ...]
2. [ForQUERY N]
3. [LIMIT row_count [offset offset]]
5. Type
6. All
7. | BLOCK IO
8. | CONTEXT Switches
9. | Cpu
10. | Ipc
11. | MEMORY
12. | PAGE faults
13. | SOURCE
14. | SWAPS

MySQL find SQL time-consuming bottleneck SHOW profiles