[MySQL] using Profiles to view the execution time consumption of the create statement, profilescreate

[MySQL] using Profiles to view the execution time consumption of the create statement, profilescreate
 

1. view the status value of profiles
1. Check whether profiles is enabled. By default, profiles is disabled.
Mysql> show profiles;
Empty set (0.02 sec)

Mysql> show variables like '% pro % ';
+ --------------------------- + ------- +
| Variable_name | Value |
+ --------------------------- + ------- +
| Have_profiling | YES |
| Profiling | OFF |
| Profiling_history_size | 15 |
| Protocol_version | 10 |
| Proxy_user |
| Slave_compressed_protocol | OFF |
+ --------------------------- + ------- +
6 rows in set (0.00 sec)

I checked that there was nothing in profiles, so the profile in the company server was not opened. I checked the mysql variable and it was OFF.

2. Open profiles and Test
1. Enable profiles
Mysql> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)

2. check the status value, which is ON
Mysql> show variables like '% pro % ';
+ --------------------------- + ------- +
| Variable_name | Value |
+ --------------------------- + ------- +
| Have_profiling | YES |
| Profiling | ON |
| Profiling_history_size | 15 |
| Protocol_version | 10 |
| Proxy_user |
| Slave_compressed_protocol | OFF |
+ --------------------------- + ------- +
6 rows in set (0.00 sec)

3. Test
Mysql> drop table if exists test_cpny.listed_cpny_fin_rpt_prd;
Query OK, 0 rows affected (0.08 sec)

Mysql> create table test_cpny.listed_cpny_fin_rpt_prd
-> SELECT dat .*
-> FROM test. qa_cpny es
-> Inner join cdp. listed_cpny_fin_rpt_prd dat
-> On (es. excel_id = dat. excel_id)
->;
Query OK, 60960 rows affected (30.00 sec)
Records: 60960 Duplicates: 0 Warnings: 0

Mysql>

4. view the result value:
Mysql> show profiles;
+ ---------- + ------------ + Response -----------------------------------------------------------------------------------------------------
| Query_ID | Duration | Query
+ ---------- + ------------ + Response -----------------------------------------------------------------------------------------------------
| 1 | 0.00044300 | show variables like '% pro %'
| 2 | 0.05818800 | drop table if exists test_cpny.listed_cpny_fin_rpt_prd
| 3 | 30.0030300 | create table test_cpny.listed_cpny_fin_rpt_prd
SELECT dat .*
FROM test. qa_cpny es
Inner join cdp. listed_cpny_fin_rpt_prd dat
On (es. excel_id = dat. excel_id) |
+ ---------- + ------------ + Response -----------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)

Mysql>

5. check again
Mysql> show profile for query 3;
+ ------------------------------ + ---------- +
| Status | Duration |
+ ------------------------------ + ---------- +
| Startling | 0.000072 |
| Check permissions | 0.000007 |
| Check permissions | 0.000003 |
| Check permissions | 0.000007 |
| Opening tables | 0.000161 |
| System lock | 0.000013 |
| Init | 1, 0.000006 |
| Check permissions | 0.000065 |
| Creating table | 0.003520 |
| After create| 0.000089 |
| System lock | 0.000011 |
| Optimizing | 0.000011 |
| Statistics | 0.000016 |
| Preparing | 0.000010 |
| Executing | 0.000003 |
| Sendingdata | 29.96599 |
| Waiting for query cache lock | 0.000014 |
| Sendingdata | 0.010215 |
| End| 0.000016 |
| Query end | 0.000004 |
| Closing tables | 0.000016 |
| Freeing items | 0.000042 |
| Logging slow query | 0.000004 |
| Cleaningup | 0.000004 |
+ ------------------------------ + ---------- +
24 rows in set (0.00 sec)

It seems that the time is spent | Sending data | 29.96599 | above!

Supplement:

•  profiling_history_size

  The number of statements for which to maintain profiling information ifprofilingIs enabled. The default value is 15. The maximum value is 100. Setting the value to 0 when tively disables profiling. See Section 13.7.5.32,"SHOW PROFILESSyntax ".

How does one record the execution time of a statement in Mysql?

1. Enable and disable
Mysql> set profiling = 1;
Mysql> set profiling = 0;
The information_schema database creates a PROFILING table record.
2. Execute some statements
Mysql> show databases;
Mysql> use information_schema;
3. query the statement execution time
Mysql> show profiles;
Mysql> help show profiles for more tips

The SQL statement is automatically executed at intervals in mysql.

Event is required for mysql to implement regular SQL statement execution

The procedure is as follows:

Check whether the event is enabled.
Show variables like '% sche % ';

If it is not enabled, it is enabled. Requires database super Permissions
Set global event_scheduler = 1;

Create the Stored Procedure update_a (Note: it is the SQL statement you want to execute)
Mysql> create procedure update_a () update a set a. y_avg = (select avg (B. youhao) from B where a. a_id = B. a_id );

Create a scheduled task: event e_updateA
Mysql> create event if not exists e_updateA
-> On schedule every 60 second --- set the execution time to 60 seconds.

-> On schedule at date_add (now (), interval 1 minute) --- execute in one minute

-> On completion preserve
-> Do call update_a (); --- execute the update_a () Stored Procedure

After an Event is created, the SQL statement is periodically executed once.

Close event task
Mysql> alter event e_updateA ON
-> Completion preserve disable;

Enable event tasks
Mysql> alter event e_updateA ON
-> Completion preserve enable;

Contact me if you have any questions.