chenyongsuda
- 浏览: 74718 次
- 性别:
- 来自: 上海
-
文章分类
最新评论
MYSQL5.0家族提供的性能跟踪器确实很爽。
要注意两点。
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 |
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 |
| 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 |
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 |
要注意两点。
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 |
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 |
| 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 |
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 |
分享到：
发表评论
-
千万级测试数据生成方法【转】
2010-07-13 13:31 2262http://blog.csai.cn/user1/16350 ... -
数据库中使用自增量字段与Guid字段作主键的性能对比
2010-07-13 11:06 1081http://www.cnblogs.com/houleixx ... -
MySQL: MyISAM和InnoDB的区别
2010-07-12 15:58 841InnoDB和MyISAM是在使用MySQL最常用的两个表类型 ... -
利用frofile找新能瓶颈【转】
2010-07-12 15:54 980转自http://www.yqshare.com/mysql- ... -
查看mysql的一些小命令
2010-07-12 15:31 742mysql> \s查看版本信息 -
MySQL数据库优化脚本Tuning-primer.sh
2010-07-12 15:14 1717很好用的mysql数据库优化脚本，相对mysqltuner.p ... -
mysql在linux下的my.cnf文件在哪里！
2010-07-12 14:01 2206用rpm包安装的MySQL是不会安装/etc/my.cnf文件 ... -
MySQL密码丢失的找回方法(win/*inx)
2010-07-12 11:43 767破解本地密码： Windows: 1.用系统管理员登陆系统。 ... -
SELECT COUNT使用优化
2010-07-12 11:38 1503SQL 语句的COUNT有两种用途 1. 用来计算行数——Co ... -
MySQL数据的导出和导入工具
2010-07-12 11:11 617导出要用到MySQL的mysqldump工具，基本用法是： ... -
MySQL慢查询分析mysqldumpslow
2010-07-12 11:08 1705一直以来积累了很多对MySQL优化的心得和经验，计划通过写日志 ... -
打开MySQL的慢查询记录
2010-07-12 11:02 706转自http://www.ccvita.com/326.htm ... -
查看MySQL运行状况
2010-07-12 10:48 816列举了几种查看MySQL运行状况的方法，总结一下。 转自htt ... -
什么是mysql的慢查询
2010-07-12 10:31 1391MySQL有一个功能就是可以log下来运行的比较慢的sql语句 ... -
用MYSQLADMIN改用户密码
2010-07-12 09:55 994转自http://yueliangdao0608.blog.5 ... -
Mysql数据导入导出命令
2010-07-07 11:09 725many of us may only need to use ...
相关推荐
在分析测试结果时，我们需要关注几个关键点：是否随着查询范围扩大性能下降，不同时间类型和格式是否有显著性能差异，以及优化器是否能够有效地利用索引来提升查询速度。如果存在性能瓶颈，可能需要考虑调整索引策略...
- **GDB**：通用调试器，可用于线程堆栈跟踪，了解数据库内部状态。 - **tcpdump**：网络包抓取工具，帮助识别网络延迟或瓶颈。 - **oprofile**：性能分析工具，提供CPU使用率的详细信息。 #### MySQL监控：性能...
MySQL还提供了一个内置的查询分析器——`PROFILING`功能，它可以跟踪并记录查询的执行过程，包括每个阶段所花费的时间等详细信息。这有助于深入了解查询的实际执行情况，找出可能存在的性能瓶颈。 启用`PROFILING`...
2. 监控器与查询分析器的作用：监控器用于持续跟踪数据库性能和应用查询的问题，而查询分析器则是一个强大的工具，它帮助开发者和数据库管理员(DBA)识别和解决性能问题。这些工具尤其在生产环境中至关重要，因为在...
总的来说，MySQL SQL查询性能优化是一个涉及多方面的综合过程，包括但不限于合理配置、监控系统状态、理解业务需求以及持续跟踪和测试。通过运用这些技术和工具，我们可以显著提升MySQL数据库的性能，满足企业级应用...
MySQL查询分析器是一种强大的工具，专门用于帮助数据库管理员和开发人员高效地管理和操作MySQL数据库。SQLYog Enterprise绿色版是其中的一款，它提供了一个直观且功能丰富的界面，使得执行SQL查询、管理数据库对象...
MySQL监视器是数据库管理系统中一个至关重要的工具，它允许管理员实时跟踪和分析MySQL数据库的运行状态，以便优化性能、诊断问题以及确保数据的安全性。在本文中，我们将深入探讨MySQL监视器的功能、重要性以及如何...
MySQL 5.6提供了新的性能监视工具，如Performance Schema，用于跟踪数据库的运行状况和性能指标。文档会指导用户如何解读这些信息，进行性能调优。 7. **安全性和权限管理**： MySQL 5.6在安全方面有所加强，包括...
这个安装包通常包含了MySQL服务器、客户端工具、连接器和其他相关组件，如MySQL Workbench（数据库管理工具）和MySQL Shell（交互式脚本环境）。通过这个安装程序，用户可以方便地配置MySQL实例，设置用户账户，选择...
8. **查询优化器改进**：MySQL5.7的查询优化器有了重大改进，能更好地选择执行计划，提高了查询效率。 下载的“MySQL5.7精简版”压缩包通常包含了以下组件： - MySQL Server：数据库服务的核心部分，负责数据的存储...
7. **性能分析**：通过性能分析器，你可以监控和分析MySQL服务器的性能，找出潜在的瓶颈，优化数据库的运行效率。 8. **调试支持**：对于存储过程和触发器，插件提供了调试功能，你可以设置断点，逐步执行，查看...
MySQL 5.6.16还引入了性能改进的查询优化器。新的查询优化器采用了更智能的统计信息和成本模型，能够更准确地估算查询计划的成本，从而选择最优的执行路径。此外，支持更多复杂的SQL语句，包括窗口函数和高级联接...
3. **查询优化器改进**: MySQL 5.7.24的查询优化器有了显著升级，能更好地选择执行计划，提高查询速度。例如，它支持更多的统计信息和基于成本的优化。 4. **安全增强**: 这个版本提升了安全性，包括更好的身份验证...
在高性能MySQL中，第三版着重介绍了查询优化器是如何选择执行查询的最优路径，以及如何利用索引、表连接顺序和数据访问方法来最小化查询执行时间和资源消耗。此外，还讨论了如何分析慢查询日志，这是诊断性能问题和...
MySQL 5.7版本是MySQL发展中的一个重要里程碑，它引入了许多新特性、性能改进和安全性增强。以下是关于这个版本的一些关键知识点： 1. **InnoDB存储引擎优化**：MySQL 5.7对InnoDB存储引擎进行了大量优化，包括更快...
在提供的描述中，“mysql-8.0.33.x86_64.rpm-bundle”可能是一个包含所有必需组件的捆绑包，包括服务器、客户端工具、连接器以及其他相关软件。这使得用户能够一次性安装MySQL的完整环境。而“mysql80-community-...
在性能监控方面，虽然MySQL-Front V3.2版本可能没有现代版本那么全面，但用户仍然可以通过工具查看数据库的运行状态，比如查看当前连接数、执行的SQL语句等，从而对数据库性能有一定的了解。 在版本控制方面，MySQL...
在数据库管理和性能优化的过程中，跟踪执行的SQL语句是一个重要的步骤。在SQL Server中，我们可以使用事件探查器来追踪SQL语句的执行情况。但是在MySQL数据库中，实现这一功能的方法与SQL Server有所不同。本知识点...
与传统的glibc库中的malloc相比，TCMalloc在内存管理和分配方面表现出显著的优越性，特别是在处理高并发场景时，能够极大地提升MySQL服务器的性能，有效降低系统负载。 TCMalloc的核心设计理念是利用线程本地存储...