- Rongsen.Com.Cn 版权所有 2008-2010 京ICP备08007000号 京公海网安备11010802026356号 朝阳网安编号:110105199号
- 北京黑客防线网安工作室-黑客防线网安服务器维护基地为您提供专业的
服务器维护
,企业网站维护
,网站维护
服务 - （建议采用1024×768分辨率，以达到最佳视觉效果） Powered by 黑客防线网安 ©2009-2010 www.rongsen.com.cn
MYSQL5.0家族提供的性能跟踪器.
作者：黑客防线网安MYSQL维护基地 来源：黑客防线网安MYSQL维护基地 浏览次数：0 |
黑客防线网安网讯： 要注意两点。1、不过版本要在5.0.37之后手册上介绍：(SHOW PROFILES and SHOW PROFILE were added in MySQL 5.0.37. )ImportantPlease note that the SHOW PROFILE and SHOW PROFILES f...
要注意两点。
1、不过版本要在5.0.37之后
手册上介绍：(SHOW PROFILES and SHOW PROFILE were added in MySQL 5.0.37. )
Important
Please note that the SHOW PROFILE and SHOW PROFILES functionality is part of the MySQL 5.0 Community Server only.
2、变量profiling是用户变量，每次都得重新启用。
以下是我做的一些实验。数据很明显，就不多解释了。
mysql> use test
Database changed
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| bag_item |
| bag_user |
| score |
| t |
+----------------+
4 rows in set (0.03 sec)
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 2097152 |
+----------+
1 row in set (0.74 sec)
mysql> show profiles;
+----------+------------+------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------+
| 1 | 0.02717000 | show tables |
| 2 | 0.74770100 | select count(*) from t |
+----------+------------+------------------------+
2 rows in set (0.00 sec)
mysql> show profile for query 2;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| (initialization) | 0.000004 |
| checking query cache for query | 0.000044 |
| Opening tables | 0.000012 |
| System lock | 0.000017 |
| Table lock | 0.00003 |
| init | 0.000013 |
| optimizing | 0.000008 |
| statistics | 0.000013 |
| preparing | 0.000011 |
| executing | 0.000006 |
| Sending data | 0.747313 |
| end | 0.000014 |
| query end | 0.000006 |
| storing result in query cache | 0.000006 |
| freeing items | 0.000012 |
| closing tables | 0.000009 |
| logging slow query | 0.000183 |
+--------------------------------+----------+
17 rows in set (0.00 sec)
mysql> show profile block io,cpu for query 2;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| (initialization) | 0.000004 | 0 | 0 | 0 | 0 |
| checking query cache for query | 0.000044 | 0 | 0 | 0 | 0 |
| Opening tables | 0.000012 | 0 | 0 | 0 | 0 |
| System lock | 0.000017 | 0 | 0 | 0 | 0 |
| Table lock | 0.00003 | 0 | 0 | 0 | 0 |
| init | 0.000013 | 0 | 0 | 0 | 0 |
| optimizing | 0.000008 | 0 | 0 | 0 | 0 |
| statistics | 0.000013 | 0 | 0 | 0 | 0 |
| preparing | 0.000011 | 0 | 0 | 0 | 0 |
| executing | 0.000006 | 0 | 0 | 0 | 0 |
| Sending data | 0.747313 | 0.746887 | 0 | 0 | 0 |
| end | 0.000014 | 0 | 0 | 0 | 0 |
| query end | 0.000006 | 0 | 0 | 0 | 0 |
| storing result in query cache | 0.000006 | 0 | 0 | 0 | 0 |
| freeing items | 0.000012 | 0 | 0 | 0 | 0 |
| closing tables | 0.000009 | 0 | 0 | 0 | 0 |
| logging slow query | 0.000183 | 0 | 0 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set (0.00 sec)
mysql> insert into t(username) select username from t;
Query OK, 2097152 rows affected (34.17 sec)
Records: 2097152 Duplicates: 0 Warnings: 0
mysql> show profiles;
+----------+-------------+------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+------------------------------------------------+
| 1 | 0.02717000 | show tables |
| 2 | 0.74770100 | select count(*) from t |
| 3 | 0.00004200 | show prifile for query 2 |
| 4 | 34.30410100 | insert into t(username) select username from t |
+----------+-------------+------------------------------------------------+
4 rows in set (0.00 sec)
mysql> show profile cpu,block io,memory,swaps for query 4;
+------------------------------+-----------+-----------+------------+--------------+---------------+-------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+------------------------------+-----------+-----------+------------+--------------+---------------+-------+
| (initialization) | 0.000038 | 0 | 0 | 0 | 0 | 0 |
| checking permissions | 0.000016 | 0 | 0 | 0 | 0 | 0 |
| Opening tables | 0.000014 | 0 | 0 | 0 | 0 | 0 |
| System lock | 0.000007 | 0 | 0 | 0 | 0 | 0 |
| Table lock | 0.000013 | 0 | 0 | 0 | 0 | 0 |
| init | 0.000015 | 0 | 0 | 0 | 0 | 0 |
| optimizing | 0.000006 | 0 | 0 | 0 | 0 | 0 |
| statistics | 0.000012 | 0 | 0 | 0 | 0 | 0 |
| preparing | 0.000011 | 0 | 0 | 0 | 0 | 0 |
| Creating tmp table | 0.000029 | 0 | 0 | 0 | 0 | 0 |
| executing | 0.000005 | 0 | 0 | 0 | 0 | 0 |
| Copying to tmp table | 1.262877 | 1.24981 | 0.012998 | 0 | 0 | 0 |
| converting HEAP to MyISAM | 0.384814 | 0.187971 | 0.19797 | 0 | 0 | 0 |
| Copying to tmp table on disk | 1.417069 | 1.203817 | 0.191971 | 0 | 0 | 0 |
| Sending data | 31.104185 | 13.965877 | 0.843872 | 0 | 0 | 0 |
| end | 0.000017 | 0 | 0 | 0 | 0 | 0 |
| removing tmp table | 0.134872 | 0 | 0.029995 | 0 | 0 | 0 |
| end | 0.000026 | 0 | 0 | 0 | 0 | 0 |
| query end | 0.000006 | 0 | 0 | 0 | 0 | 0 |
| freeing items | 0.000011 | 0 | 0 | 0 | 0 | 0 |
| closing tables | 0.000009 | 0 | 0 | 0 | 0 | 0 |
| logging slow query | 0.000049 | 0 | 0 | 0 | 0 | 0 |
+------------------------------+-----------+-----------+------------+--------------+---------------+-------+
22 rows in set (0.00 sec)
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 4194304 |
+----------+
1 row in set (1.51 sec)
mysql> show profiles;
+----------+-------------+------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+------------------------------------------------+
| 1 | 0.02717000 | show tables |
| 2 | 0.74770100 | select count(*) from t |
| 3 | 0.00004200 | show prifile for query 2 |
| 4 | 34.30410100 | insert into t(username) select username from t |
| 5 | 1.50563800 | select count(*) from t |
+----------+-------------+------------------------------------------------+
5 rows in set (0.00 sec)
mysql> show profile cpu,block io,memory,swaps,context switches,source for query 5;
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+-------+---------------------------+---------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Swaps | Source_function | Source_file | Source_line |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+-------+---------------------------+---------------+-------------+
| (initialization) | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | send_result_to_client | sql_cache.cc | 1099 |
| checking query cache for query | 0.000048 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 2629 |
| Opening tables | 0.000011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 153 |
| System lock | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 162 |
| Table lock | 0.000032 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 2242 |
| init | 0.000013 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_select.cc | 745 |
| optimizing | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_select.cc | 904 |
| statistics | 0.000013 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_select.cc | 914 |
| preparing | 0.000011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_select.cc | 1565 |
| executing | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_select.cc | 2083 |
| Sending data | 1.505401 | 1.504771 | 0 | 3 | 5 | 0 | 0 | 0 | mysql_select | sql_select.cc | 2287 |
| end | 0.000015 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5122 |
| query end | 0.000005 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | query_cache_end_of_result | sql_cache.cc | 729 |
| storing result in query cache | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 6116 |
| freeing items | 0.00001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2146 |
| closing tables | 0.000009 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | log_slow_statement | sql_parse.cc | 2204 |
| logging slow query | 0.000038 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2169 |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+-------+---------------------------+---------------+-------------+
17 rows in set (0.00 sec)
mysql> update t set username = 'waill';
Query OK, 4194304 rows affected (44.82 sec)
Rows matched: 4194304 Changed: 4194304 Warnings: 0
mysql> show profiles;
+----------+-------------+------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+------------------------------------------------+
| 1 | 0.02717000 | show tables |
| 2 | 0.74770100 | select count(*) from t |
| 3 | 0.00004200 | show prifile for query 2 |
| 4 | 34.30410100 | insert into t(username) select username from t |
| 5 | 1.50563800 | select count(*) from t |
| 6 | 44.82054700 | update t set username = 'waill' |
+----------+-------------+------------------------------------------------+
6 rows in set (0.00 sec)
mysql> show profile cpu,block io,memory,swaps,context switches,source for query 6;
+----------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+-------+-----------------------+---------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Swaps | Source_function | Source_file | Source_line |
+----------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+-------+-----------------------+---------------+-------------+
| (initialization) | 0.000035 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 5306 |
| checking permissions | 0.000011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 2629 |
| Opening tables | 0.000011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 153 |
| System lock | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 162 |
| Table lock | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_update | sql_update.cc | 167 |
| init | 0.000021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_update | sql_update.cc | 429 |
| Updating | 44.514679 | 27.976747 | 2.818571 | 9949 | 1584 | 0 | 0 | 0 | mysql_update | sql_update.cc | 560 |
| end | 0.305671 | 0.028996 | 0.023997 | 43 | 18 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5122 |
| query end | 0.000026 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 6116 |
| freeing items | 0.000016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2146 |
| closing tables | 0.000012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | log_slow_statement | sql_parse.cc | 2204 |
| logging slow query | 0.00005 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2169 |
+----------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+-------+-----------------------+---------------+-------------+
黑客防线网安服务器维护方案本篇连接：http://www.rongsen.com.cn/show-8764-1.html
新闻栏目
| 我要申请本站：N点 | 黑客防线官网 | |
| 专业服务器维护及网站维护手工安全搭建环境，网站安全加固服务。黑客防线网安服务器维护基地招商进行中！QQ:29769479 |