Profiling performance using the show Profile command in 006-mysql

I. Overview

1, Version support

Show profiles is added after 5.0.37, to use this feature, make sure the version is after 5.0.37.

To view the database version:

Select version ();

2. View turn on off and default settings observation count

 like ' profiling% ';
Or
SELECT @ @profiling;

　　

3. Open and Close parameters (parameters are session-level parameters, only valid for current session)

To open the operation:

Set Profiling=1; or set profiling=on;

To close the operation:

Set Profiling=0; or set profiling=off;

Second, the Operation procedure

1. Open

SET Profiling=on;

2, run the corresponding SQL statement;

3. View the overall results:

Show Profiles;

4, see the detailed results:

 for QUERY N;

Here n is the query_id that corresponds to the output of show PROFILES that is the third step;

5, can view the CPU, IO and other specific details;

Show Profie syntax "help profile command View"

syntax:show Profile[type [, type] ... ] [For QUERY N]    [LIMIT row_count [offset offset]]type: All  |BLOCK IO|CONTEXT Switches|CPU|IPC|MEMORY|PAGE faults|SOURCE|SWAPS

The type is optional, and the range of values can be as follows:

All: Show all performance information
Block IO: Shows the number of block IO operations
Context switches: Shows the number of contextual switches, whether active or passive
CPU: Displays user CPU time, System CPU time
IPC: Displays the number of messages sent and received
Memory:[temporarily not implemented]
Page faults: Number of page errors displayed
Source: Displays the name and location of the function in the source code
SWAPS: Shows the number of swaps

Profiling performance using the show Profile command in 006-mysql