Shulou(Shulou.com)06/01 Report--

The following mainly brings you the method of tuning Mysql using profile. I hope this content can bring you practical use, which is also the main purpose of this article that I edit the method of tuning Mysql using profile. All right, don't talk too much nonsense, let's just read the following.

When we do mysql performance analysis, there are three most commonly used ways:

(1) slow query (analyze the sql with problems)

(2) Explain (shows how mysql uses indexes to process select statements and join tables. Can help select better indexes and write more optimized query statements)

(3) Profile (query how long it will take to execute SQL, and see the amount of CPU/Memory used, how much time Systemlock and Table lock will take during execution, etc.)

This chapter mainly gives a brief overview of profile and is used to analyze the performance of a sql statement.

Profiling is not available until after the mysql5.0.3 version. However, after mysql5.7, profile information will gradually become obsolete, and mysql recommends using performance schema.

Profile this tool can be used to query the execution status of SQL, how much time System lock and Table lock spend, and so on. It is very important to locate the CPU O consumption and CPU consumption of a statement. (the two largest resources consumed by SQL statement execution are IO and CPU.)

Use of profile tool

Check your own version of mysql:

Mysql > select version (); +-+ | version () | 5.6.35-log | +-+

Check whether the profile function is enabled (profiling=on means enabled):

Mysql > show variables like'% profil%' +-+-+ | Variable_name | Value | +-+-+ | have_profiling | YES | | profiling | OFF | | profiling_history_size | 15 | +- -+

Enable profile:

Mysql > set profile=1

After opening profile, execute the sql statement you want to analyze:

Mysql > select t1.Action from pre_forum_thread as t1left join (select a.* from pre_forum_threadmod as a, (select tid,max (dateline) as dateline from pre_forum_threadmod group by tid) as bwhere a.tid=b.tid and a.dateline=b.dateline) as t2on t1.tid=t2.tidwhere t1.displayorder > = 0 and t1.fid in (47 ppm 49) and t1.tid > 100318 and (t1.authorid = 7683017 or t2.actionmakers DWN'or t2.action is null) order by t1.dateline desc limit 20

View the generated profile information:

Mysql > show profiles +- -+ | Query_ID | Duration | Query | +- -+ | 1 | 1.37183777 | select t1.* T2.action from pre_forum_thread as T1 | | 2 | 0.00078796 | show columns from `bbs`.`t2` | | 3 | 0.00150425 | show columns from `bbs`.`pre _ forum_ thread` | +-- -- +

Gets the cost of the specified query statement:

Mysql > show profile for query 2 +-- +-+ | Status | Duration | +-+-+ | starting | 0.000147 | | checking permissions | 0.000023 | Opening tables | 0.000047 | | init | 0.000081 | | System Lock | 0.000031 | optimizing | 0.000034 | statistics | 0.001650 | preparing | 0.000046 | executing | 0.000018 | Sending data | 2.460588 | end | 0.000041 | query end | 0.000019 | closing tables | 0.000022 | freeing items | 0.000055 | cleaning up | 0.000085 | +- -+

Turn off profile:

Mysql > set profiling=0

Relevant specific parameters:

Type: ALL-display all cost information | BLOCK IO-display block IO related cost | CONTEXT SWITCHES-context switch related cost | CPU-display CPU related cost information | IPC-display send and receive related cost information | MEMORY-display Memory related overhead information | PAGE FAULTS-display page error related overhead information | SOURCE-display and Source_function Source_file,Source_line-related cost information | SWAPS-displays information about the cost related to the number of exchanges, for example, to view cpu and io costs, execute the command: mysql > SHOW profile CPU,BLOCK IO FOR query 2 Summary

General and simple process:

(1) set profiling=1; / / turn on profile analysis (2) run your sql1; (3) run your sql2; (4) show profiles; / / View sentence analysis of sql1,sql2 (5) SHOW profile CPU,BLOCK IO io FOR query 1; / / View CPU, IO consumption (6) set profiling=0; / / turn off profile analysis

Do you think it is very helpful to use profile to tune Mysql above. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.