MySQL profile)

MySQL uses explain for SQL statement optimization, but this does not know the detailed memory/CPU usage.
MySQL query profiler, You can query the number of SQL statements executed, and see the CPU/memory usage

Amount, the execution process system lock, the amount of time the table lock takes, and so on.
Mysql> show variables like 'profiling % ';
+ ------------------------ + ------- +
| Variable_name | value |
+ ------------------------ + ------- +
| Profiling | off |
| Profiling_history_size | 15 |
+ ------------------------ + ------- +
Enable this function
Mysql> set profiling = 1;
Mysql> show variables like 'profiling % ';
# This command will allow MySQL to create a profiling

Table.
+ ------------------------ + ------- +
| Variable_name | value |
+ ------------------------ + ------- +
| Profiling | on |
| Profiling_history_size | 15 |
+ ------------------------ + ------- +
Profiling_history_size number of queries

Mysql> show profiles;
+ ---------- + ------------ + -------------------------------------- +
| Query_id | duration | query |
+ ---------- + ------------ + -------------------------------------- +
| 1 | 0.00018100 | show variables like 'profiling % '|
| 2 | 0.00020400 | show variables like 'profiling % '|
| 1 | 0.00007800 | set profiling = 1 |
| 4 | 0.00011000 | show variables like 'profiling % '|
| 5 | 0.00002400 | select count (1) From 'mrhao _ stats' |
| 6 | 1.52181400 | select count (*) from 'mrhao _ stats' |
| 7 | 0.00026900 | show variables like 'profiling % '|

Mysql> show profile for query 6;
+ -------------------------------- + ---------- +
| Status | duration |
+ -------------------------------- + ---------- +
| (Initialization) | 0.000003 |
| Checking query cache for query | 0.000042 |
| Opening tables | 0.00001 |
| System lock | 0.000004 |
| Table lock | 0.000025 |
| Init | 1, 0.000009 |
| Optimizing | 0.000003 |
| Statistics | 0.000007 |
| Preparing | 0.000007 |
| Executing | 0.000004 |
| Sendingdata | 1.521676 |
| End| 0.000007 |
| Query end | 0.000003 |
| Storing result in query cache | 0.000002 |
| Freeing items | 0.000006 |
| Closing tables | 0.000004 |
| Logging slow query | 0.000002 |
+ -------------------------------- + ---------- +
17 rows in SET (0.00 Sec)

Mysql> show profile CPU for query 6;
+ -------------------------------- + ---------- + ------------ +
| Status | duration | cpu_user | cpu_system |
+ -------------------------------- + ---------- + ------------ +
| (Initialization) | 0.000003 | 0 | 0 |
| Checking query cache for query | 0.000042 | 0.001 | 0 |
| Opening tables | 0.00001 | 0 | 0 |
| System lock | 0.000004 | 0 | 0 |
| Table lock | 0.000025 | 0 | 0 |
| Init | 0.000009 | 0 | 0 |
| Optimizing | 0.000003 | 0 | 0 |
| Statistics | 0.000007 | 0 | 0 |
| Preparing | 0.000007 | 0 | 0 |
| Executing | 0.000004 | 0 | 0 |
| Sending data | 1.521676 | 1.631752 | 0.036995 |
| End| 0.000007 | 0 | 0 |
| Query end | 0.000003 | 0 | 0 |
| Storing result in query cache | 0.000002 | 0 | 0 |
| Freeing items | 0.000006 | 0 | 0 |
| Closing tables | 0.000004 | 0 | 0 |
| Logging slow query | 0.000002 | 0 | 0 |
+ -------------------------------- + ---------- + ------------ +
17 rows in SET (0.00 Sec)
* All-displays all information
* Block io-displays counts for block input and output operations
* Context switches-displays counts for voluntary and involuntary

Context switches
* 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

Query time and CPU usage
Mysql> select Min (SEQ) seq, state, count (*) numb_ops,
Round (sum (duration), 5) sum_dur, round (AVG (duration), 5) avg_dur,
Round (sum (cpu_user), 5) sum_cpu, round (AVG (cpu_user), 5) avg_cpu
From information_schema.profiling
Where query_id = 7
Group by state
Order by seq;
Disable this function

Mysql> set profiling = 0;
Mysql> show variables like 'profiling % ';
+ ------------------------ + ------- +
| Variable_name | value |
+ ------------------------ + ------- +
| Profiling | off |
| Profiling_history_size | 15 |
+ ------------------------ + ------- +