preface

We have covered MySQL indexes, explained whether SQL statements use indexes, and optimized indexes. Today we will cover Show Profiles to see where SQL time actually occurs.

Show profiles

What is the

MySQL provides resources that can be used to analyze the resource consumption of SQL statement execution in the current session. Can be used for tuning measurements of SQL. By default, the parameter is turned off and the results of the last 15 runs are saved

Open the profiles

  • Check whether the function is enabled.
show variables like "%profiling%";
Copy the code
  • open
set profiling = 1;
Copy the code

To analyze

  • Perform the analysis firstSQLstatements
  • performshow profiles;The following results occur

  • Analyze one of the above screenshotsSQLgrammar
show profile type1,type2.. for query Query_ID
Copy the code
  • For example, let’s analyze the number one in the screenshot5articleSQLstatements
show profile cpu,block io for query 5
Copy the code

Show profile Field meaning of the returned result

  • Status: indicates the execution Status of the SQL statement
  • Duration: Indicates the Duration of each step during SQL execution
  • CPU_user: indicates the CPU occupied by the current user
  • CPU_system: indicates the CPU occupied by the system
  • Block_ops_in: I/O input
  • Block_ops_out: I/O output

Show profile Type option

  • All: displays all performance cost information
  • Block IO: displays the block I/O costs
  • Context switches: Costs related to context switches
  • CPU: displays CPU information
  • Ipc: Displays the cost information about sending and receiving
  • Memory: Displays memory-related costs
  • Page faults: Displays overhead information related to page errors
  • Source: displays cost information related to Source_function, Source_file, and Source_line
  • Swaps: Displays information about the number of swaps

Status Indicates the suggestion when the following situations occur

  • System lock

Confirm which lock is caused by, usually by MySQL or InnoDB kernel-level locks. Suggestion: If it takes a long time to pay attention to it, it is generally ok

  • Sending data

Sending data from the server to the client, or possibly receiving data from the storage engine layer and sending it back to the client, especially when there is a large amount of data. Note: Sending Data is not sent over the network. It is read from a hard disk. Writing to NET is sent to the network. Suggestion: Reduce the amount of data that needs to be scanned and sent to the client by index or LIMIT

  • Sorting result

Sorting the results, similar to Creating Sort Index, but in a normal table rather than in an in-memory table. Suggestion: Create an appropriate index

  • Table lock

Table level lock, nothing to say, either because MyISAM engine table level lock, or some other case explicit table lock

  • create sort index

Create a temporary table in ORDER BY

  • Creating tmp table

Create temporary tables. Copy data to temporary tables and delete temporary tables when all temporary tables are used up. Memory consumption and data copy and deletion consume time. Suggestion: Optimize the index

  • converting HEAP to MyISAM

The query result is too large, the memory is not enough, and the data is moved to disk. Suggestion: Optimize the index. You can adjust max_HEAP_table_size

  • Copying to tmp table on disk

Copy temporary table in memory to disk, danger!! Suggestion: Optimize the index by adjusting the tmp_table_size parameter to increase the size of temporary tables in memory

conclusion

Optimization is endless, specific scene specific analysis, no optimization method can be applicable to all scenes, brother can only tell you what optimization means, performance problems encountered in specific work, we have to learn these knowledge to analyze where the problem in the end, I hope we can become a performance master.

IT brother

A big factory to do advanced Java development program ape

Follow wechat public account: IT elder brother

Java foundation, Java Web, JavaEE all tutorials, including Spring Boot, etc

Reply: Resume template, you can get 100 beautiful resumes

Reply: Java learning route, you can get the latest and most complete a learning roadmap

Re: Java ebooks, get 13 must-read books for top programmers