Mysql built-in optimization tools show profiles and mysqlprofiles

Source: Internet
Author: User

Mysql built-in optimization tools show profiles and mysqlprofiles

I. Overview:

Currently, the Mysql explain tool is not as powerful as the explain plan tool of Oracle, but the show profiles tool can achieve similar results. The show profiles statement is used for resource usage of the statements executed in the current session. The specific syntax is:

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

2. Enable profiling

show variables like '%profil%';

Set @ session. profiling = 1; -- enable session level

After Mysql5.6.7, show profiles is not officially recommended for continued use (deprecated). Instead, it supports performance_schema (which is easy to use). For the usage of performance_schema, see the following link:

Https://dev.mysql.com/doc/refman/5.6/en/performance-schema-query-profiling.html

Iii. Use of show profiles

If you don't talk much about it, You can directly explain it in three pictures. (Note: show profile is not show profiles for a single query)

As for the explanations of each column in the figure, the official website will describe here: (that is, the description of the infomation_schema.PROFILING table)

QUERY_ID is a numeric statement identifier.SEQ is a sequence number indicating the display order for rows with the same QUERY_ID value.STATE is the profiling state to which the row measurements apply.DURATION indicates how long statement execution remained in the given state, in seconds.CPU_USER and CPU_SYSTEM indicate user and system CPU use, in seconds.CONTEXT_VOLUNTARY and CONTEXT_INVOLUNTARY indicate how many voluntary and involuntary context switches occurred.BLOCK_OPS_IN and BLOCK_OPS_OUT indicate the number of block input and output operations.MESSAGES_SENT and MESSAGES_RECEIVED indicate the number of communication messages sent and received.PAGE_FAULTS_MAJOR and PAGE_FAULTS_MINOR indicate the number of major and minor page faults.SWAPS indicates how many swaps occurred.SOURCE_FUNCTION, SOURCE_FILE, and SOURCE_LINE provide information indicating where in the source code the profiled state executes.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.