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

Editor to share with you how to use Performance Schema to view Profiling, I hope you will learn something after reading this article, let's discuss it together!

SHOW PROFILE can be used for the resources used by MySQL when executing statements (such as IO, context switching, CPU,Memory, etc.), but this feature will be removed from MySQL 5.6.7 and Performance Schema will be used instead, as follows:

Setup_actors configuration

MySQL 5.7.8. You can configure host, user, and or account through the setup_actors table. The information is collected as follows:

# by default, setup_actors is configured to allow monitoring and collection of historical event information for all foreground threads

> SELECT * FROM performance_schema.setup_actors

+-+

| | HOST | USER | ROLE | ENABLED | HISTORY | |

+-+

| |% | YES | YES |

+-+

1 row in set (0.03 sec)

# modify the default configuration to turn off the monitoring and collection of all foreground threads. And insert the user specified by the new behavior to start monitoring and collecting information.

Mysql > UPDATE performance_schema.setup_actors SET ENABLED = 'NO', HISTORY =' NO'

-> WHERE HOST ='% 'AND USER ='%'

Mysql > INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY)

-> VALUES ('localhost','test_user','%','YES','YES')

# the modified configuration is as follows:

Mysql > SELECT * FROM performance_schema.setup_actors

+-+

| | HOST | USER | ROLE | ENABLED | HISTORY | |

+-+

| |% | NO | NO |

| | localhost | test_user |% | YES | YES | |

+-+

# only monitor and collect event information related to test_user@localhost users

Query Profiling Using Performance Schema below simply try to use Performance Schema to query profile related information, using the following methods: 1. Enable the configuration of statement and stage instrumentation in the setup_instruments table, some of which may be enabled by default

Mysql > UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED =' YES'

-> WHERE NAME LIKE'% statement/%'

Mysql > UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED =' YES'

-> WHERE NAME LIKE'% stage/%'

two。 Enable events_statements_* and events_stages_* related consumption (consumers). Some projects are enabled by default.

Mysql > UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'

-> WHERE NAME LIKE'% events_statements_%'

Mysql > UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'

-> WHERE NAME LIKE'% events_stages_%'

3. To compare with the results of show profile, turn on the profiling function

Mysql > set profiling=1

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

Mysql > show warnings; # here, you can also see the warning that this feature will be removed

+-- +

| | Level | Code | Message | |

+-- +

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

+-- +

1 row in set (0.00 sec)

4. Execute SQL statement

Mysql > select * from t

+-+ +

| | 9 | 15 |

| | 10 | 15 |

| | 2 | 20 |

| | 3 | 20 |

| | 8 | 25 |

+-+ +

5 rows in set (0.00 sec) 5 rows in set (0.00 sec)

5. View profiling results

Mysql > show profiles

+-+

| | Query_ID | Duration | Query | |

+-+

| | 1 | 0.00010150 | show warnings |

| | 2 | 0.00032075 | select * from t |

+-+

2 rows in set, 1 warning (0.00 sec)

Mysql > show profile for query 2

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000038 | |

| | checking permissions | 0.000009 | |

| | Opening tables | 0.000048 | |

| | init | 0.000022 | |

| | System lock | 0.000012 | |

| | optimizing | 0.000007 | |

| | statistics | 0.000016 | |

| | preparing | 0.000015 | |

| | executing | 0.000005 | |

| | Sending data | 0.000063 | |

| | end | 0.000008 | |

| | query end | 0.000009 | |

| | closing tables | 0.000013 | |

| | freeing items | 0.000012 | |

| | cleaning up | 0.000050 | |

+-+ +

15 rows in set, 1 warning (0.00 sec)

6. Find the EVENT_ID that just executed the SQL, which is similar to show profiles viewing the query id. Obtain the corresponding EVENT_ID by querying the table events_statements_history_long

Note: for illustrative purposes only, many other SQL may be found, but we know which SQL we are implementing, and all other SQL are omitted here.

7. Get the final result by querying the events_stages_history_ long table (NESTING_EVENT_ID=EVENT_ID)

Mysql > SELECT event_name AS Stage, TRUNCATE (TIMER_WAIT/1000000000000,6) AS Duration

> FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=79

+-+ +

| | Stage | Duration |

+-+ +

| | stage/sql/init | 0.000048 | |

| | stage/sql/checking permissions | 0.000008 | |

| | stage/sql/Opening tables | 0.000051 | |

| | stage/sql/init | 0.000019 | |

| | stage/sql/System lock | 0.000012 | |

| | stage/sql/optimizing | 0.000006 | |

| | stage/sql/statistics | 0.000016 | |

| | stage/sql/preparing | 0.000015 | |

| | stage/sql/executing | 0.000004 | |

| | stage/sql/Sending data | 0.000066 | |

| | stage/sql/end | 0.000005 | |

| | stage/sql/query end | 0.000008 | |

| | stage/sql/closing tables | 0.000013 | |

| | stage/sql/freeing items | 0.000011 | |

| | stage/sql/cleaning up | 0.000001 | |

+-+ +

15 rows in set (0.01 sec)

As above, it is implemented to query profileing-related information through Performance Schema, and the final options you can see are almost the same as those displayed by show profile, but the values of each item do not seem to be quite the same.

After reading this article, I believe you have some understanding of "how to use Performance Schema to check Profiling". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.