Mysql使用Profiling分析語句消耗

markzy5201190發表於2012-06-03
MySql

Profiling 分析具體的sql語句消耗
MySQL5.0.37版本以上支援了Profiling – 官方手冊。此工具可用來查詢 SQL 會執行多少時間，System lock和Table lock 花多少時間等等，對定位一條語句的 I/O消耗和CPU消耗 非常重要。
從啟動profile之後的所有查詢包括錯誤的語句都會記錄。
關閉會話或者set profiling=0 就關閉了。

# 開啟 profiling
mysql> set profiling=1;
mysql> select * from user limit 1;
mysql> select count(*) from user group by sexal;
# 檢視這些語句的profile
mysql> show profiles;
+--------------+----------------+--------------------------------------------------------------+
| Query_ID | Duration     | Query                                                                      |
+--------------+----------------+--------------------------------------------------------------+
|        1        | 0.00013200   | SELECT DATABASE()                                               |
|        2        | 0.00044100   | select * from user limit 2                                      |
|        3        | 1.95544100   | select nick,count(*) from user group by online|
+--------------+----------------+--------------------------------------------------------------+

檢視具體一條（Query_ID=3 這一條）語句的profiles，包括CPU和柱塞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 |
+----------------------+----------+----------+------------+--------------+---------------+

上面就獲得了一條語句的CPU和Block IO消耗，對定位瓶頸很方便，其餘的一些資訊，可以用語句：“Show profile *** for query 3”來獲取

http://www.cnblogs.com/wrmfw/archive/2011/09/05/2166927.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26855487/viewspace-731799/，如需轉載，請註明出處，否則將追究法律責任。

相關文章