Use the PROFILING debugging function of MySQL

MySQL 5.0.37 and later versions support PROFILING debugging, allowing you to learn more about resource consumption of SQL statements. Because it needs to call the system's getrusage () function

MySQL 5.0.37 and later versions support PROFILING debugging, allowing you to learn more about resource consumption of SQL statements. Because it needs to call the system's getrusage () function

MySQL 5.0.37 and later versions support PROFILING debugging, allowing you to learn more about resource consumption of SQL statements. Because it needs to call the system's getrusage () function, it can only be used on Linux/Unix platforms, rather than Windows platforms. In addition, PROFILING is for processing processes rather than threads. Other applications on the server may affect your debugging results. Therefore, this tool is suitable for debugging during development. If you want to debug and use it in the production environment, pay attention to its limitations.

References:

The format of PROFILING and PROFILE and PROFILES is as follows:

Mysql>? Show profiles;
--------------------------------------------------------------------------------
Show profile [type [, type]… ]
[For query n]
[LIMIT row_count [OFFSET offset]

Type:
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
--------------------------------------------------------------------------------

By default, this debugging function is disabled. You can view the variable parameters of the MySQL database:

Mysql> SELECT @ PROFILING;
+ ------------- +
| @ PROFILING |
+ ------------- +
| 0 |
+ ------------- +

Mysql> show variables like '% profil % ';
+ ------------------------ + ------- +
| Variable_name | Value |
+ ------------------------ + ------- +
| Have_profiling | YES |
| Profiling | OFF |
| Profiling_history_size | 15 |
+ ------------------------ + ------- +

You can use the SQL command to enable the debugging function:

Mysql> set profiling = 1;

Then, start executing the SQL statement to be tested. The MySQL database will record the debugging information you want to close, for example:

Mysql> SELECT * FROM mysql. user;
Mysql> show profile;
+ -------------------------------- + ---------- +
| Status | Duration |
+ -------------------------------- + ---------- +
| Startling | 0.000041 |
| Waiting for query cache lock | 0.000007 |
| Checking query cache for query | 0.000044 |
| Check permissions | 0.000011 |
| Opening tables | 0.000024 |
| System lock | 0.000015 |
| Init | 1, 0.000038 |
| Optimizing | 0.000009 |
| Statistics | 0.000014 |
| Preparing | 0.000012 |
| Executing | 0.000006 |
| Sendingdata | 0.000087 |
| End| 0.000008 |
| Query end | 0.000006 |
| Closing tables | 0.000010 |
| Freeing items | 0.000017 |
| Logging slow query | 0.000007 |
| Cleaningup | 0.000007 |
+ -------------------------------- + ---------- +
18 rows in set (0.00 sec)

You can also display all the currently recorded PROFILES, for example:

Mysql> show profiles;
+ ---------- + ------------ + -------------------- +
| Query_ID | Duration | Query |
+ ---------- + ------------ + -------------------- +
| 1 | 0.07194300 | show databases |
| 1 | 0.01126250 | select database () |
| 3 | 0.00059125 | show tables |
| 4 | 0.96143900 | SELECT * FROM |
+ ---------- + ------------ + -------------------- +
4 rows in set (0.00 sec)

Note: The length of this List is determined by the MySQL database Variable Parameter profiling_history_size. The default value is 15.
(Command query database variable parameters: mysql> show variables like 'profiling _ history_size ';)