Performance Analysis of Profiling in Mysql

MySQL 5.0.37 and later versions support Profiling-official manual. This tool can be used to query how much time the SQL statement executes, how much time the System lock and Table lock take, and so on. It is very important to locate the I/O consumption and CPU consumption of a statement.
All queries after profile is started, including statements with errors, are recorded.
Close the session or set profiling = 0.
# Enable profiling
Mysql & gt; set profiling = 1;
Mysql & gt; select * from user limit 1;
Mysql & gt; select count (*) from user group by sexal;
# View the profile of these statements
Mysql & gt; show profiles;
+ -------------- + ---------------- + ---------------------------------------------------------------- +
| Query_ID | Duration | Query |
+ -------------- + ---------------- + ---------------------------------------------------------------- +
| 1 | 1 | 0.00013200 | select database () |
| 2 | 0.00044100 | select * from user limit 2 |
| 3 | 1.95544100 | select nick, count (*) from user group by online |
+ -------------- + ---------------- + ---------------------------------------------------------------- +
View the profiles of a specific (Query_ID = 3) statement, including CPU and plunger I/O
Mysql> show profile cpu, block io for query 3;
+ ---------------------- + ---------- + ------------ + -------------- + --------------- +
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+ ---------------------- + ---------- + ------------ + -------------- + --------------- +
| Starting | 0.000057 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| Init | 0.000021 | 0.000000 | 0.000000 | 0 | 0 |
| Optimizing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| Statistics | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| Preparing | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| Creating tmp table | 0.000039 | 0.000000 | 0.000000 | 0 | 0 |
| Executing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Copying to tmp table | 1.900619 | 1.030785 | 0.197970 | 127 | 127 |
| Sorting result | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| End | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Removing tmp table | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| End | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| Freeing items | 0.000025 | 0.000000 | 0.000000 | 0 | 0 |
| Logging slow query | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| Cleaning up | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
+ ---------------------- + ---------- + ------------ + -------------- + --------------- +
The preceding figure shows the CPU and Block I/O consumption of a statement. It is convenient to locate the bottleneck. You can use the statement "Show profile *** for query 3" to obtain the remaining information.
 
Additionally, the learning material of profiling is attached.
Basics of profiling
Http://www.xaprb.com/blog/2006/10/12/how-to-profile-a-query-in-mysql/
Http://www.xaprb.com/blog/2006/10/15/a-case-study-in-profiling-queries-in-mysql/
Http://www.xaprb.com/blog/2006/10/17/mysql-profiling-case-study-part-2/
Advanced profiling
Http://www.mysqlperformanceblog.com/2009/01/19/profiling-mysql-stored-routines/
Http://www.mysqlperformanceblog.com/2008/05/18/wanted-better-memory-profiling-for-mysql/
Maatkit-perldoc mk-query-profiler
Profiling advanced
Http://dev.mysql.com/tech-resources/articles/pro-mysql-ch6.html
Http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html
Profiling hardcore
Http://www.scribd.com/doc/2669413/DTrace-and-MySQL-Presentation
Http://forge.mysql.com/wiki/Using_DTrace_with_MySQL
Http://wikis.sun.com/display/BluePrints/Optimizing+MySQL+Database+Application+Performan

From Performance Test Column