你的位置:首页 > 软件开发 > 数据库 > Profiling MySQL queries from Performance Schema

Profiling MySQL queries from Performance Schema

发布时间:2015-05-01 00:00:22
转自:http://www.percona.com/blog/2015/04/16/profiling-mysql-queries-from-performance-schema/?utm_source=tuicoolWhen optimizing queries and inv ...

转自:http://www.percona.com/blog/2015/04/16/profiling-mysql-queries-from-performance-schema/?utm_source=tuicool

When optimizing queries and investigating performance issues, MySQL comes with built in support for profiling queries aka SET profiling = 1; . This is already awesome and simple to use, but why the PERFORMANCE_SCHEMA alternative?

Because profiling will be removed soon (already deprecated on MySQL 5.6 ad 5.7); the built-in profiling capability can only be enabled per session. This means that you cannot capture profiling information for queries running from other connections. If you are using Percona Server, the profiling option for log_slow_verbosity is a nice alternative, unfortunately, not everyone is using Percona Server.

Now, for a quick demo: I execute a simple query and profile it below. Note that all of these commands are executed from a single session to my test instance.

mysql> SHOW PROFILES;+----------+------------+----------------------------------------+| Query_ID | Duration  | Query                 |+----------+------------+----------------------------------------+|    1 | 0.00011150 | SELECT * FROM sysbench.sbtest1 LIMIT 1 |+----------+------------+----------------------------------------+1 row in set, 1 warning (0.00 sec)mysql> SHOW PROFILE SOURCE FOR QUERY 1;+----------------------+----------+-----------------------+------------------+-------------+| Status        | Duration | Source_function    | Source_file   | Source_line |+----------------------+----------+-----------------------+------------------+-------------+| starting       | 0.000017 | NULL         | NULL       |    NULL || checking permissions | 0.000003 | check_access     | sql_parse.cc   |    5797 || Opening tables    | 0.000021 | open_tables      | sql_base.cc   |    5156 || init         | 0.000009 | mysql_prepare_select | sql_select.cc  |    1050 || System lock     | 0.000005 | mysql_lock_tables   | lock.cc     |     306 || optimizing      | 0.000002 | optimize       | sql_optimizer.cc |     138 || statistics      | 0.000006 | optimize       | sql_optimizer.cc |     381 || preparing      | 0.000005 | optimize       | sql_optimizer.cc |     504 || executing      | 0.000001 | exec         | sql_executor.cc |     110 || Sending data     | 0.000025 | exec         | sql_executor.cc |     190 || end         | 0.000002 | mysql_execute_select | sql_select.cc  |    1105 || query end      | 0.000003 | mysql_execute_command | sql_parse.cc   |    5465 || closing tables    | 0.000004 | mysql_execute_command | sql_parse.cc   |    5544 || freeing items    | 0.000005 | mysql_parse      | sql_parse.cc   |    6969 || cleaning up     | 0.000006 | dispatch_command   | sql_parse.cc   |    1874 |+----------------------+----------+-----------------------+------------------+-------------+15 rows in set, 1 warning (0.00 sec)

原标题:Profiling MySQL queries from Performance Schema

关键词:MYSQL

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。