Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you how to use MySQL performance analysis tool PROFILE, I believe most people still do not know how to use, so share this article for your reference, I hope you have a lot of harvest after reading this article, let's go to understand it together!

Analyzing the overhead of SQL execution is a common way to optimize SQL, and in MySQL databases, SQL profiling can be enabled by configuring the profiling parameter.

It can only be set at the session level, after setting affects the current session; when it is enabled, subsequent SQL statements will record their resource overhead, such as IO, context, CPU, MEMORY, etc.

Experimental environment:

mysql> select version();

+------------+

| version() |

+------------+

| 5.7.17-log |

+------------+

1 row in set (0.00 sec)

There are three parameters associated with profile:

mysql> show variables like '%profil%';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| have_profiling | YES |---Used to control whether profiling is enabled or disabled by system variables

| profiling | OFF |---Enable SQL parsing

| profiling_history_size | 15 |---Sets the number of profiling reserved, default is 15, range is 0 to 100, 0 disables profiling

+------------------------+-------+

3 rows in set (0.01 sec)

Enable profiling with a warning that this parameter will be removed later and replaced with information_scheam.PROFILING.

mysql> set profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;

+---------+------+----------------------------------------------------------------------+

| Level | Code | Message |

+---------+------+----------------------------------------------------------------------+

| Warning | 1287 | '@@profiling' is deprecated and will be removed in a future release. |

+---------+------+----------------------------------------------------------------------+

Run a sql test:

select * from oms3.customers where `type` = 1 AND `status`

< 7 AND `isarea` = 6 AND `into_time`>

='2016-12-01'AND `into_time` show profiles;

+----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 7 | 0.00022275 | SELECT * FROM setup_actors |

| 8 | 0.00016050 | SELECT DATABASE() |

| 9 | 0.00032350 | show databases |

| 10 | 0.00024050 | show tables |

| 11 | 0.00019250 | SELECT * FROM setup_actors |

| 12 | 0.00183950 | show variables like "profiling_hist%" |

| 13 | 0.00192500 | show variables like '%profil%' |

| 14 | 0.00011550 | show warnings |

| 15 | 0.00044725 | help 'show profile' |

| 16 | 0.00013875 | set profiling=1 |

| 17 | 0.00011550 | show warnings |

| 18 | 0.00025075 | select * from customers where `type` = 1 AND `status`

< 7 AND `isarea` = 6 AND `into_time`>

='2016-12-01'AND `into_time`='2016-12-01'AND `into_time` set @query_id=19; ---query_id is 19 as shown above

Query OK, 0 rows affected (0.00 sec)

mysql> select STATE,sum(duration) as Total_R, ---Use this sql to query exactly what went wrong. This sql is quoted in High Performance MySQL,Third Edition.

-> ROUND(

-> 100*SUM(DURATION)/

-> (SELECT SUM(DURATION) FROM INFORMATiON_SCHEMA.PROFILING WHERE QUERY_ID=@query_id),2) as Pct_R,

-> count(*) as calls,

-> sum(duration)/count(*) as "R/Call"

-> from information_schema.profiling

-> where query_id=@query_id

-> group by state

-> order by Total_R desc;

+----------------------+------------+-------+-------+----------------+

| STATE | Total_R | Pct_R | calls | R/Call |

+----------------------+------------+-------+-------+----------------+

| Sending data | 332.162424 | 99.69 | 1 | 332.1624240000 |---This state basically takes up all the resources, so the optimization of this sql focuses on reducing io.

| statistics | 1.027729 | 0.31 | 1 | 1.0277290000 |

| Opening tables | 0.000519 | 0.00 | 1 | 0.0005190000 |

| freeing items | 0.000157 | 0.00 | 1 | 0.0001570000 |

| starting | 0.000147 | 0.00 | 1 | 0.0001470000 |

| init | 0.000123 | 0.00 | 1 | 0.0001230000 |

| logging slow query | 0.000096 | 0.00 | 1 | 0.0000960000 |

| preparing | 0.000035 | 0.00 | 1 | 0.0000350000 |

| cleaning up | 0.000019 | 0.00 | 1 | 0.0000190000 |

| optimizing | 0.000016 | 0.00 | 1 | 0.0000160000 |

| end | 0.000014 | 0.00 | 1 | 0.0000140000 |

| System lock | 0.000014 | 0.00 | 1 | 0.0000140000 |

| closing tables | 0.000013 | 0.00 | 1 | 0.0000130000 |

| query end | 0.000013 | 0.00 | 1 | 0.0000130000 |

| Sorting result | 0.000010 | 0.00 | 1 | 0.0000100000 |

| checking permissions | 0.000009 | 0.00 | 1 | 0.0000090000 |

| executing | 0.000003 | 0.00 | 1 | 0.0000030000 |

+----------------------+------------+-------+-------+----------------+

17 rows in set, 18 warnings (0.00 sec)

Other uses:

mysql> SHOW PROFILE CPU FOR QUERY 2;

+----------------------+----------+----------+------------+

| Status | Duration | CPU_user | CPU_system |

+----------------------+----------+----------+------------+

| checking permissions | 0.000040 | 0.000038 | 0.000002 |

| creating table | 0.000056 | 0.000028 | 0.000028 |

| After create | 0.011363 | 0.000217 | 0.001571 |

| query end | 0.000375 | 0.000013 | 0.000028 |

| freeing items | 0.000089 | 0.000010 | 0.000014 |

| logging slow query | 0.000019 | 0.000009 | 0.000010 |

| cleaning up | 0.000005 | 0.000003 | 0.000002 |

+----------------------+----------+----------+------------+

For more information, see:

help show profiles;

The above is "MySQL performance analysis tool PROFILE how to use" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.