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

What this article shares to you is about how to understand the simple test of MySQL Profile in MySQL5.7. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

MySQL Profile is still helpful in analyzing the overhead of execution plans, at least with a lot of reference when analyzing some performance issues.

I tested it in version 5.6 and 5.7 and found no difference, so let's take 5.7 as an example.

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.7.10 |

+-+

1 row in set (0.00 sec)

The traditional way to use Profile is to use a command like show profile, which is turned off by default.

Mysql > show profiles

Empty set, 1 warning (0.00 sec)

You can see a warning in this place. Let's see what the warning is.

Mysql > show warnings

+-+

| | Level | Code | Message | |

+-+

| Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead |

+-+

1 row in set (0.00 sec)

It turns out that this approach is out of date, and the new feature is open in performance_schema. Of course, testing is still available in versions 5.6 and 5.7, so let's take a brief look at how to use performance_schema.

Several parameters related to Profile are as follows:

Mysql > show variables like'% profil%'

+-+ +

| | Variable_name | Value |

+-+ +

| | have_profiling | YES |

| | profiling | OFF |

| | profiling_history_size | 15 |

+-+ +

3 rows in set (0.00 sec)

You can see that Profileing is OFF, and the current default value is 0, which means the same thing.

Mysql > SELECT @ @ profiling

+-+

| | @ @ profiling |

+-+

| | 0 |

+-+

1 row in set, 1 warning (0.00 sec)

Have_profiling is used to control whether Profiling is enabled or disabled.

Profiling_history_size is the number of reserved Profiling

Of course, in essence, the content of Profile still comes from information_schema.profiling.

Mysql > select * from information_schema.profiling\ G

Empty set, 1 warning (0.00 sec)

There is still a warning and an expired hint in this place.

Mysql > show warnings

+- -- +

| | Level | Code | Message | |

+- -- +

| Warning | 1287 | 'INFORMATION_SCHEMA.PROFILING' is deprecated and will be removed in a future release. Please use Performance Schema instead |

+- -- +

1 row in set (0.00 sec)

Let's turn on profiling

Mysql > set profiling=1

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

View all profiles

Mysql > show profiles

+-+

| | Query_ID | Duration | Query | |

+-+

| | 1 | 0.00018200 | show warnings |

+-+

1 row in set, 1 warning (0.00 sec)

Let's run a SQL by the way.

Mysql > select count (*) from information_schema.columns

+-+

| | count (*) |

+-+

| | 3077 |

+-+

1 row in set (0.07 sec)

Then look at it again, and you'll see that query_ID gets the statement you just ran.

Mysql > show profiles

+-- +

| | Query_ID | Duration | Query | |

+-- +

| | 1 | 0.00018200 | show warnings |

| | 2 | 0.06627200 | select count (*) from information_schema.columns |

+-- +

2 rows in set, 1 warning (0.00 sec)

You can view profile information in the following ways, such as details involving CPU.

Mysql > show profile cpu for query 2

+-+

| | Status | Duration | CPU_user | CPU_system | |

+-+

| | checking permissions | 0.000004 | 0.000000 | 0.000000 | |

| | checking permissions | 0.000053 | 0.000999 | 0.000000 | |

| | checking permissions | 0.000014 | 0.000000 | 0.000000 | |

| | checking permissions | 0.000006 | 0.000000 | 0.000000 | |

.

| | closing tables | 0.000005 | 0.000000 | 0.000000 | |

| | freeing items | 0.000052 | 0.000000 | 0.000000 | |

| | cleaning up | 0.000023 | 0.000000 | 0.000000 | |

+-+

100 rows in set, 1 warning (0.00 sec)

Besides, what are the options that you can choose freely?

The above is somewhere between usage and expiration, so let's take a look at the recommended performace_schema in the new version.

First switch to performance_schema, which is a new performance optimization engine added by MySQL, which was turned off before 5.6.5. 6. 5. 7 is enabled by default, and there will be a prompt when switching between 5. 7.

Mysql > use performance_schema

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Using profile involves several tables, setup_actors,setup_instruments,setup_consumers

To put it bluntly, it's all configuration, it's all tricks.

The default table setup_actors contains the following:

Mysql > SELECT * FROM setup_actors

+-+

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

+-+

| |% | YES | YES |

+-+

1 row in set (0.00 sec)

According to the official recommendation, it is enabled by default and can be disabled according to the demand.

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

WHERE HOST ='% 'AND USER ='%'

The disabled content is as follows:

Mysql > select * from setup_actors

+-+

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

+-+

| |% | NO | NO |

+-+

1 row in set (0.00 sec)

Then join the specified user

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

VALUES ('localhost','root','%','YES','YES')

The data after joining successfully are as follows:

Mysql > select * from setup_actors

+-+

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

+-+

| |% | NO | NO |

| | localhost | root |% | YES | YES | |

+-+

2 rows in set (0.00 sec)

Well, this is the configuration of setup_actors, and the contents of the other two tables are more or less the same.

Table setup_consumers describes various events, and setup_instruments describes the table name under this database and whether monitoring is enabled.

I counted that there is still a lot of default data for the two tables.

Setup_instruments 1006 rows

Setup_consumers 15 rows

When we modify it according to the official suggestion, we can see that it is not a single line, but a lot of related lines.

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

-> WHERE NAME LIKE'% statement/%'

Query OK, 0 rows affected (0.00 sec)

Rows matched: 192 Changed: 0 Warnings: 0

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

-> WHERE NAME LIKE'% stage/%'

Query OK, 119 rows affected (0.00 sec)

Rows matched: 128 Changed: 119 Warnings: 0

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

-> WHERE NAME LIKE'% events_statements_%'

Query OK, 1 row affected (0.01sec)

Rows matched: 3 Changed: 1 Warnings: 0

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

-> WHERE NAME LIKE'% events_stages_%'

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3 Changed: 3 Warnings: 0

All right, the configuration is complete, let's briefly test how to use it.

Create a test database.

Mysql > create database test

Query OK, 1 row affected (0.00 sec)

Switch to test database

Mysql > use test

Database changed

Create a test table, test_profile, and insert several rows of data.

Mysql > create table test_profile as select * from information_schema.columns limit 1pr 5

Query OK, 5 rows affected (0.10 sec)

Records: 5 Duplicates: 0 Warnings: 0

Run it, and we'll get some detailed statistics based on this statement.

Mysql > select * from test.test_profile limit 1 and 2

According to the following statement to query a history table, you can see from the table name is related to the event, feeling more and more like Oracle.

Mysql > SELECT EVENT_ID, TRUNCATE (TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT

-> FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like'% limit 1BI 2%'

+-- +

| | EVENT_ID | Duration | SQL_TEXT | |

+-- +

| | 4187 | 0.000424 | select * from test.test_profile limit 1 Magne2 |

+-- +

1 row in set (0.00 sec)

From the above statement, we can get an overview, the corresponding event and execution time.

Then go to the stage-related history table to see the details of the event, which is the performance data we expect. Then you should understand the meaning of the work to be done in the configuration table above.

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

-> FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=4187

+-+ +

| | Stage | Duration |

+-+ +

| | stage/sql/starting | 0.000113 | |

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

| | stage/sql/Opening tables | 0.000025 | |

| | stage/sql/init | 0.000062 | |

| | stage/sql/System lock | 0.000013 | |

. . .

| | stage/sql/freeing items | 0.000031 | |

| | stage/sql/cleaning up | 0.000002 | |

+-+ +

15 rows in set (0.01 sec)

Overall, seeing the output of this feature reminds me of Datapump in Oracle, because the output is so similar and organized.

The above is how to understand the simple test of MySQL Profile in MySQL5.7. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow 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.