Mysql performance debugging tool profile, mysqlprofile

Mysql performance debugging tool profile, mysqlprofile

I have been looking for mysql tools similar to Oracle 10046. I can see the time consumption of SQL Execution. I finally found a profile on the Internet. I think it is good to use it.

Mysql> select version ();
+ ----------- +
| Version () |
+ ----------- +
| 5.6.16 |
+ ----------- +
1 row in set (0.00 sec)


Mysql> SELECT @ profiling;
+ ------------- +
| @ Profiling |
+ ------------- +
| 0 |
+ ------------- +
1 row in set, 1 warning (0.00 sec)


Mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)


Mysql> select count (1) from test1 t1, test2 t2;
+ ---------- +
| Count (1) |
+ ---------- +
| 1, 7096832 |
+ ---------- +
1 row in set (0.38 sec)


Mysql> show profile cpu, block io for query 1; -- for query 1 is to view 1st SQL statements executed.
+ ------------------------------ + ---------- + ------------ + ---------------- + --------------- +
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+ ------------------------------ + ---------- + ------------ + ---------------- + --------------- +
| Starting | 0.000006 | 0.000000 | 0.000000 | NULL |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 | NULL |
| Init | 0.000002 | 0.000000 | 0.000000 | NULL |
| Checking query cache for query | 0.000023 | 0.000000 | 0.000000 | NULL |
| Checking permissions | 0.000003 | 0.000000 | 0.000000 | NULL |
| Checking permissions | 0.000002 | 0.000000 | 0.000000 | NULL |
| Opening tables | 0.000026 | 0.000000 | 0.000000 | NULL |
| Init | 0.000008 | 0.000000 | 0.000000 | NULL |
| System lock | 0.000007 | 0.000000 | 0.000000 | NULL |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 | NULL |
| System lock | 0.000022 | 0.000000 | 0.000000 | NULL |
| Optimizing | 0.000004 | 0.000000 | 0.000000 | NULL |
| Statistics | 0.000012 | 0.000000 | 0.000000 | NULL |
| Preparing | 0.000022 | 0.000000 | 0.000000 | NULL |
| Executing | 0.000002 | 0.000000 | 0.000000 | NULL |
| Sending data | 0.388263 | 0.375000 | 0.000000 | NULL |
| End | 0.000017 | 0.000000 | 0.000000 | NULL |
| Query end | 0.000008 | 0.000000 | 0.000000 | NULL |
| Closing tables | 0.000014 | 0.000000 | 0.000000 | NULL |
| Freeing items | 0.000006 | 0.000000 | 0.000000 | NULL |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 | NULL |
| Freeing items | 0.000045 | 0.000000 | 0.000000 | NULL |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 | NULL |
| Freeing items | 0.000002 | 0.000000 | 0.000000 | NULL |
| Storing result in query cache | 0.000004 | 0.000000 | 0.000000 | NULL |
| Cleaning up | 0.000015 | 0.000000 | 0.000000 | NULL |
+ ------------------------------ + ---------- + ------------ + ---------------- + --------------- +
26 rows in set, 1 warning (0.00 sec)


Mysql> select count (1) from test1 t1, test2 t2, test2 t3;
+ ------------ +
| Count (1) |
+ ------------ +
| 1, 1185170944 |
+ ------------ +
1 row in set (1 min 26.67 sec)


Mysql> show profile cpu, block io for query 2; -- for query 2 is to view 2nd SQL statements executed
+ ------------------------------ + ----------- + ------------ + -------------- + ----------------- +
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+ ------------------------------ + ----------- + ------------ + -------------- + ----------------- +
| Starting | 0.000005 | 0.000000 | 0.000000 | NULL |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 | NULL |
| Init | 0.000002 | 0.000000 | 0.000000 | NULL |
| Checking query cache for query | 0.000029 | 0.000000 | 0.000000 | NULL |
| Checking permissions | 0.000002 | 0.000000 | 0.000000 | NULL |
| Checking permissions | 0.000002 | 0.000000 | 0.000000 | NULL |
| Checking permissions | 0.000002 | 0.000000 | 0.000000 | NULL |
| Opening tables | 0.000071 | 0.000000 | 0.000000 | NULL |
| Init | 0.000008 | 0.000000 | 0.000000 | NULL |
| System lock | 0.000005 | 0.000000 | 0.000000 | NULL |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 | NULL |
| System lock | 0.000021 | 0.000000 | 0.000000 | NULL |
| Optimizing | 0.000004 | 0.000000 | 0.000000 | NULL |
| Statistics | 0.000011 | 0.000000 | 0.000000 | NULL |
| Preparing | 0.000019 | 0.000000 | 0.000000 | NULL |
| Executing | 0.000002 | 0.000000 | 0.000000 | NULL |
| Sending data | 86.679145 | 86.640625 | 0.000000 | NULL |
| End | 0.000024 | 0.000000 | 0.000000 | NULL |
| Query end | 0.000008 | 0.000000 | 0.000000 | NULL |
| Closing tables | 0.000014 | 0.000000 | 0.000000 | NULL |
| Freeing items | 0.000006 | 0.000000 | 0.000000 | NULL |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 | NULL |
| Freeing items | 0.000045 | 0.000000 | 0.000000 | NULL |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 | NULL |
| Freeing items | 0.000002 | 0.000000 | 0.000000 | NULL |
| Storing result in query cache | 0.000003 | 0.000000 | 0.000000 | NULL |
| Logging slow query | 0.000003 | 0.000000 | 0.000000 | NULL |
| Cleaning up | 0.000014 | 0.000000 | 0.000000 | NULL |
+ ------------------------------ + ----------- + ------------ + -------------- + ----------------- +
28 rows in set, 1 warning (0.00 sec)

Show profiles Syntax
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
Meaning of type:
ALL displays all information
Block io displays counts for block input and output operations
Context switches displays counts for voluntary and involuntary context switches
CPU displays user and system CPU usage times
IPC displays counts for messages sent and received ed
MEMORY is not currently implemented
Page faults displays counts for major and minor page faults
SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs
SWAPS displays swap counts

Why can't I perform one-step debugging when Mysql spam the stored procedure?

MySQL is a mature medium-and small-sized database with a wide range of users, and many of them are well-known large companies. Of course, every kind of things has its shortcomings, and mysql is no exception, but it is said to be rubbish, either technically or emotionally, what's more, your reason is that "phpmyadmin is difficult to use and the interface is ugly, and Chinese characters are garbled ", however, this also shows that you do not know mySQL (specifically, it should be "illiterate mySQL", because the above two problems are exactly what you are ignorant of mySQL, even if you are right-it looks ugly, can you change it? mySQL has many management tools). I want to learn from you further, you should like it.

[MYSQL] How to debug the stored procedure? Or debugging method

MYSQL 5.0 does not support stored procedures before. Of course, if you use Versions later than 5, you can use them.

Debugging is impossible. You can only view the results.