mysql初始化三部曲_MySQL调优三部曲（三）PROFILE

分析SQL执行带来的开销是优化SQL的重要手段，MySQL可以通过设置 profiling

参数，将SQL语句的资源开销，如IO、上下文切换、CPU、Memory等记录下来

查看 profiling

系统变量

mysql> show variables like '%profil%';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| have_profiling | YES |

| profiling | OFF |

| profiling_history_size | 15 |

+------------------------+-------+

have_profiling：

当前版本是否支持 profiling

功能

profiling：

是否开启 profiling

功能

profiling_history_size：

保留profiling的数目，默认是15，范围为0~100，为0时代表禁用profiling

开启 profiling

启用 session

级别的 profile

mysql> set profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

验证修改后的结果

mysql> show variables like '%profil%';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| have_profiling | YES |

| profiling | ON |

| profiling_history_size | 15 |

+------------------------+-------+

进行 profile

分析

1. 进行Query操作

mysql> select count(*) from ttkk_user;

+----------+

| count(*) |

+----------+

| 55 |

+----------+

2. 查看当前 session

产生的 profile

mysql> show profiles;

+----------+------------+--------------------------------+

| Query_ID | Duration | Query |

+----------+------------+--------------------------------+

| 1 | 0.00051550 | show variables like '%profil%' |

| 2 | 0.00016350 | select count(*) from ttkk_user |

+----------+------------+--------------------------------+

3. 获取指定查询的开销

mysql> show profile for query 2;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000053 |

| checking permissions | 0.000007 |

| Opening tables | 0.000019 |

| init | 0.000013 |

| System lock | 0.000008 |

| optimizing | 0.000008 |

| executing | 0.000009 |

| end | 0.000004 |

| query end | 0.000003 |

| closing tables | 0.000008 |

| freeing items | 0.000020 |

| cleaning up | 0.000013 |

+----------------------+----------+

12 rows in set, 1 warning (0.00 sec)

当查到最耗时的线程状态时，可以进一步选择all或者cpu、block io等明细类型来查看mysql在每个线程状态中使用什么资源上耗费了过高的时间

mysql> show profile block io,cpu for query 2;

+----------------------+----------+----------+------------+--------------+---------------+

| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

+----------------------+----------+----------+------------+--------------+---------------+

| starting | 0.000053 | 0.000022 | 0.000026 | 0 | 0 |

| checking permissions | 0.000007 | 0.000003 | 0.000004 | 0 | 0 |

| Opening tables | 0.000019 | 0.000008 | 0.000010 | 0 | 0 |

| init | 0.000013 | 0.000006 | 0.000008 | 0 | 0 |

| System lock | 0.000008 | 0.000004 | 0.000004 | 0 | 0 |

| optimizing | 0.000008 | 0.000003 | 0.000004 | 0 | 0 |

| executing | 0.000009 | 0.000004 | 0.000005 | 0 | 0 |

| end | 0.000004 | 0.000002 | 0.000002 | 0 | 0 |

| query end | 0.000003 | 0.000001 | 0.000001 | 0 | 0 |

| closing tables | 0.000008 | 0.000003 | 0.000005 | 0 | 0 |

| freeing items | 0.000020 | 0.000010 | 0.000011 | 0 | 0 |

| cleaning up | 0.000013 | 0.000005 | 0.000007 | 0 | 0 |

+----------------------+----------+----------+------------+--------------+---------------+

12 rows in set, 1 warning (0.00 sec)

一条query每个阶段的资源开销可以从 information_schema.profiling

表查询

mysql> select * from profiling where query_id = 2;

+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+

| QUERY_ID | SEQ | STATE | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION | SOURCE_FILE | SOURCE_LINE |

+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+

| 2 | 2 | starting | 0.000053 | 0.000022 | 0.000026 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |

| 2 | 3 | checking permissions | 0.000007 | 0.000003 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 5350 |

| 2 | 4 | Opening tables | 0.000019 | 0.000008 | 0.000010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5095 |

| 2 | 5 | init | 0.000013 | 0.000006 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_prepare_select | sql_select.cc | 1051 |

| 2 | 6 | System lock | 0.000008 | 0.000004 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 304 |

| 2 | 7 | optimizing | 0.000008 | 0.000003 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 139 |

| 2 | 8 | executing | 0.000009 | 0.000004 | 0.000005 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 110 |

| 2 | 9 | end | 0.000004 | 0.000002 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_select | sql_select.cc | 1106 |

| 2 | 10 | query end | 0.000003 | 0.000001 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5049 |

| 2 | 11 | closing tables | 0.000008 | 0.000003 | 0.000005 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5097 |

| 2 | 12 | freeing items | 0.000020 | 0.000010 | 0.000011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 6486 |

| 2 | 13 | cleaning up | 0.000013 | 0.000005 | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1815 |

+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+

information_schema.profiling

表主要字段含义

state :

当前 query

所在的阶段

CPU_user :

CPU用户

CPU_system :

CPU系统

Context_voluntary :

上下文主动切换

Context_involuntary :

上下文被动切换

Block_ops_in :

阻塞的输入操作

Block_ops_out :

阻塞的输出操作

Messages_sent :

消息发出

Messages_received :

消息接受

Page_faults_major :

主分页错误

Page_faults_minor :

次分页错误

Swaps :

交换次数

Source_function :

源功能

Source_file :

源文件

Source_line :

源代码行

注意：本文来自简书。本站无法对本文内容的真实性、完整性、及时性、原创性提供任何保证，请您自行验证核实并承担相关的风险与后果！

CoLaBug.com遵循[CC BY-SA 4.0]分享并保持客观立场，本站不承担此类作品侵权行为的直接责任及连带责任。您有版权、意见、投诉等问题，请通过[eMail]联系我们处理，如需商业授权请联系原作者/原网站。