AI 助理
备案控制台
开发者社区 数据库 文章 正文

auto_explain扩展分析

2023-11-14 150
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
日志服务 SLS，月写入数据量 50GB 1个月
简介： PostgreSQL官方提供的扩展auto_explain扩展可以将查询的执行计划记录到日志中，本文分析其实现原理。

PostgreSQL官方提供的扩展auto_explain扩展可以将查询的执行计划记录到日志中，便于查看、分析慢查询，找出瓶颈并优化。用到了executor模块、explain模块和guc（Grand Unified Configuration）模块。

一、实现原理

backend的executor模块提供了几个钩子可用于获取执行计划：

/* Hook for plugins to get control in ExecutorStart() */typedefvoid (*ExecutorStart_hook_type) (QueryDesc*queryDesc, inteflags);
externPGDLLIMPORTExecutorStart_hook_typeExecutorStart_hook;
/* Hook for plugins to get control in ExecutorRun() */typedefvoid (*ExecutorRun_hook_type) (QueryDesc*queryDesc,
ScanDirectiondirection,
uint64count,
boolexecute_once);
externPGDLLIMPORTExecutorRun_hook_typeExecutorRun_hook;
/* Hook for plugins to get control in ExecutorFinish() */typedefvoid (*ExecutorFinish_hook_type) (QueryDesc*queryDesc);
externPGDLLIMPORTExecutorFinish_hook_typeExecutorFinish_hook;
/* Hook for plugins to get control in ExecutorEnd() */typedefvoid (*ExecutorEnd_hook_type) (QueryDesc*queryDesc);
externPGDLLIMPORTExecutorEnd_hook_typeExecutorEnd_hook;
/* Hook for plugins to get control in ExecCheckRTPerms() */typedefbool (*ExecutorCheckPerms_hook_type) (List*, bool);
externPGDLLIMPORTExecutorCheckPerms_hook_typeExecutorCheckPerms_hook;

auto_explain hook了ExecutorStart_hook、ExecutorRun_hook、ExecutorFinish_hook、ExecutorEnd_hook 这四个钩子。

ExecutorStart_hook

每个顶层语句开始时，根据配置参数决定是否记录执行计划，并根据配置设置相关的参数。

ExecutorRun_hook

执行器运行时的hook，对于auto_explain，仅需要跟踪嵌套的层数。

ExecutorFinish_hook

执行器完成，对于auto_explain，也仅需要跟踪嵌套的层数。

ExecutorEnd_hook

执行器结束，在这里根据配置记录执行计划到日志中。

二、模块拆解

2.1 配置

auto_explain有如下配置选项：

auto_explain.log_min_duration - 最小执行时间，超过这个时间的查询才会记录，为0则记录所有的语句，-1则关闭，缺省-1
auto_explain.log_analyze - 为记录执行计划使用EXPLAIN ANALYZE，缺省为false
auto_explain.log_settings - 记录影响查询规划的已修改配置参数，缺省false
auto_explain.log_verbose - 为记录执行计划使用EXPLAIN VERBOSE，缺省false
auto_explain.log_buffers - 记录buffer使用情况，缺省false
auto_explain.log_wal - 记录wal使用，缺省false
auto_explain.log_triggers - 包含执行计划中的触发器统计，缺省false
auto_explain.log_format - 日志格式，缺省TEXT
auto_explain.log_level - 日志级别，缺省LOG
auto_explain.log_nested_statements - 是否记录嵌套语句，缺省false
auto_explain.log_timing - 是否记录时间指标，缺省false
auto_explain.sample_rate - 要处理的查询分值，大于此值才记录，缺省1.0

_PG_init函数加载配置并hook相应的钩子。

#include "postgres.h"#include <limits.h>#include "access/parallel.h"#include "commands/explain.h"#include "common/pg_prng.h"#include "executor/instrument.h"#include "jit/jit.h"#include "utils/guc.h"PG_MODULE_MAGIC;
/* GUC variables */staticintauto_explain_log_min_duration=-1; /* msec or -1 */staticboolauto_explain_log_analyze=false;
staticboolauto_explain_log_verbose=false;
staticboolauto_explain_log_buffers=false;
staticboolauto_explain_log_wal=false;
staticboolauto_explain_log_triggers=false;
staticboolauto_explain_log_timing=true;
staticboolauto_explain_log_settings=false;
staticintauto_explain_log_format=EXPLAIN_FORMAT_TEXT;
staticintauto_explain_log_level=LOG;
staticboolauto_explain_log_nested_statements=false;
staticdoubleauto_explain_sample_rate=1;
staticconststructconfig_enum_entryformat_options[] = {
    {"text", EXPLAIN_FORMAT_TEXT, false},
    {"xml", EXPLAIN_FORMAT_XML, false},
    {"json", EXPLAIN_FORMAT_JSON, false},
    {"yaml", EXPLAIN_FORMAT_YAML, false},
    {NULL, 0, false}
};
staticconststructconfig_enum_entryloglevel_options[] = {
    {"debug5", DEBUG5, false},
    {"debug4", DEBUG4, false},
    {"debug3", DEBUG3, false},
    {"debug2", DEBUG2, false},
    {"debug1", DEBUG1, false},
    {"debug", DEBUG2, true},
    {"info", INFO, false},
    {"notice", NOTICE, false},
    {"warning", WARNING, false},
    {"log", LOG, false},
    {NULL, 0, false}
};
/* Current nesting depth of ExecutorRun calls */staticintnesting_level=0;
/* Is the current top-level query to be sampled? */staticboolcurrent_query_sampled=false;
#define auto_explain_enabled() \(auto_explain_log_min_duration >= 0 && \(nesting_level == 0 || auto_explain_log_nested_statements) && \current_query_sampled)/* Saved hook values in case of unload */staticExecutorStart_hook_typeprev_ExecutorStart=NULL;
staticExecutorRun_hook_typeprev_ExecutorRun=NULL;
staticExecutorFinish_hook_typeprev_ExecutorFinish=NULL;
staticExecutorEnd_hook_typeprev_ExecutorEnd=NULL;
void_PG_init(void);
staticvoidexplain_ExecutorStart(QueryDesc*queryDesc, inteflags);
staticvoidexplain_ExecutorRun(QueryDesc*queryDesc,
ScanDirectiondirection,
uint64count, boolexecute_once);
staticvoidexplain_ExecutorFinish(QueryDesc*queryDesc);
staticvoidexplain_ExecutorEnd(QueryDesc*queryDesc);
/** Module load callback*/void_PG_init(void)
{
/* Define custom GUC variables. */DefineCustomIntVariable("auto_explain.log_min_duration",
"Sets the minimum execution time above which plans will be logged.",
"Zero prints all plans. -1 turns this feature off.",
&auto_explain_log_min_duration,
-1,
-1, INT_MAX,
PGC_SUSET,
GUC_UNIT_MS,
NULL,
NULL,
NULL);
DefineCustomBoolVariable("auto_explain.log_analyze",
"Use EXPLAIN ANALYZE for plan logging.",
NULL,
&auto_explain_log_analyze,
false,
PGC_SUSET,
0,
NULL,
NULL,
NULL);
DefineCustomBoolVariable("auto_explain.log_settings",
"Log modified configuration parameters affecting query planning.",
NULL,
&auto_explain_log_settings,
false,
PGC_SUSET,
0,
NULL,
NULL,
NULL);
DefineCustomBoolVariable("auto_explain.log_verbose",
"Use EXPLAIN VERBOSE for plan logging.",
NULL,
&auto_explain_log_verbose,
false,
PGC_SUSET,
0,
NULL,
NULL,
NULL);
DefineCustomBoolVariable("auto_explain.log_buffers",
"Log buffers usage.",
NULL,
&auto_explain_log_buffers,
false,
PGC_SUSET,
0,
NULL,
NULL,
NULL);
DefineCustomBoolVariable("auto_explain.log_wal",
"Log WAL usage.",
NULL,
&auto_explain_log_wal,
false,
PGC_SUSET,
0,
NULL,
NULL,
NULL);
DefineCustomBoolVariable("auto_explain.log_triggers",
"Include trigger statistics in plans.",
"This has no effect unless log_analyze is also set.",
&auto_explain_log_triggers,
false,
PGC_SUSET,
0,
NULL,
NULL,
NULL);
DefineCustomEnumVariable("auto_explain.log_format",
"EXPLAIN format to be used for plan logging.",
NULL,
&auto_explain_log_format,
EXPLAIN_FORMAT_TEXT,
format_options,
PGC_SUSET,
0,
NULL,
NULL,
NULL);
DefineCustomEnumVariable("auto_explain.log_level",
"Log level for the plan.",
NULL,
&auto_explain_log_level,
LOG,
loglevel_options,
PGC_SUSET,
0,
NULL,
NULL,
NULL);
DefineCustomBoolVariable("auto_explain.log_nested_statements",
"Log nested statements.",
NULL,
&auto_explain_log_nested_statements,
false,
PGC_SUSET,
0,
NULL,
NULL,
NULL);
DefineCustomBoolVariable("auto_explain.log_timing",
"Collect timing data, not just row counts.",
NULL,
&auto_explain_log_timing,
true,
PGC_SUSET,
0,
NULL,
NULL,
NULL);
DefineCustomRealVariable("auto_explain.sample_rate",
"Fraction of queries to process.",
NULL,
&auto_explain_sample_rate,
1.0,
0.0,
1.0,
PGC_SUSET,
0,
NULL,
NULL,
NULL);
MarkGUCPrefixReserved("auto_explain");
/* Install hooks. */prev_ExecutorStart=ExecutorStart_hook;
ExecutorStart_hook=explain_ExecutorStart;
prev_ExecutorRun=ExecutorRun_hook;
ExecutorRun_hook=explain_ExecutorRun;
prev_ExecutorFinish=ExecutorFinish_hook;
ExecutorFinish_hook=explain_ExecutorFinish;
prev_ExecutorEnd=ExecutorEnd_hook;
ExecutorEnd_hook=explain_ExecutorEnd;
}

2.2 explain_ExecutorStart

/** ExecutorStart hook: start up logging if needed*/staticvoidexplain_ExecutorStart(QueryDesc*queryDesc, inteflags)
{
/** At the beginning of each top-level statement, decide whether we'll* sample this statement.  If nested-statement explaining is enabled,* either all nested statements will be explained or none will.** When in a parallel worker, we should do nothing, which we can implement* cheaply by pretending we decided not to sample the current statement.* If EXPLAIN is active in the parent session, data will be collected and* reported back to the parent, and it's no business of ours to interfere.*/if (nesting_level==0)
    {
if (auto_explain_log_min_duration>=0&&!IsParallelWorker())
current_query_sampled= (pg_prng_double(&pg_global_prng_state) <auto_explain_sample_rate);
elsecurrent_query_sampled=false;
    }
if (auto_explain_enabled())
    {
/* Enable per-node instrumentation iff log_analyze is required. *//* 根据配置设置相应的选项 */if (auto_explain_log_analyze&& (eflags&EXEC_FLAG_EXPLAIN_ONLY) ==0)
        {
if (auto_explain_log_timing)
queryDesc->instrument_options|=INSTRUMENT_TIMER;
elsequeryDesc->instrument_options|=INSTRUMENT_ROWS;
if (auto_explain_log_buffers)
queryDesc->instrument_options|=INSTRUMENT_BUFFERS;
if (auto_explain_log_wal)
queryDesc->instrument_options|=INSTRUMENT_WAL;
        }
    }
/* 调用钩子链上的其他钩子 */if (prev_ExecutorStart)
prev_ExecutorStart(queryDesc, eflags);
elsestandard_ExecutorStart(queryDesc, eflags);
if (auto_explain_enabled())
    {
/** Set up to track total elapsed time in ExecutorRun.  Make sure the* space is allocated in the per-query context so it will go away at* ExecutorEnd.*/if (queryDesc->totaltime==NULL)
        {
MemoryContextoldcxt;
oldcxt=MemoryContextSwitchTo(queryDesc->estate->es_query_cxt);
queryDesc->totaltime=InstrAlloc(1, INSTRUMENT_ALL, false);
MemoryContextSwitchTo(oldcxt);
        }
    }
}

2.3 explain_ExecutorRun

/** ExecutorRun hook: all we need do is track nesting depth*/staticvoidexplain_ExecutorRun(QueryDesc*queryDesc, ScanDirectiondirection,
uint64count, boolexecute_once)
{
nesting_level++;
PG_TRY();
    {
if (prev_ExecutorRun)
prev_ExecutorRun(queryDesc, direction, count, execute_once);
elsestandard_ExecutorRun(queryDesc, direction, count, execute_once);
    }
PG_FINALLY();
    {
nesting_level--;
    }
PG_END_TRY();
}

2.4 explain_ExecutorFinish

/** ExecutorFinish hook: all we need do is track nesting depth*/staticvoidexplain_ExecutorFinish(QueryDesc*queryDesc)
{
nesting_level++;
PG_TRY();
    {
if (prev_ExecutorFinish)
prev_ExecutorFinish(queryDesc);
elsestandard_ExecutorFinish(queryDesc);
    }
PG_FINALLY();
    {
nesting_level--;
    }
PG_END_TRY();
}

2.5 explain_ExecutorEnd

/** ExecutorEnd hook: log results if needed*/staticvoidexplain_ExecutorEnd(QueryDesc*queryDesc)
{
if (queryDesc->totaltime&&auto_explain_enabled())
    {
MemoryContextoldcxt;
doublemsec;
/** Make sure we operate in the per-query context, so any cruft will be* discarded later during ExecutorEnd.*/oldcxt=MemoryContextSwitchTo(queryDesc->estate->es_query_cxt);
/** Make sure stats accumulation is done.  (Note: it's okay if several* levels of hook all do this.)*/InstrEndLoop(queryDesc->totaltime);
/* Log plan if duration is exceeded. */msec=queryDesc->totaltime->total*1000.0;
if (msec>=auto_explain_log_min_duration)
        {
ExplainState*es=NewExplainState();
es->analyze= (queryDesc->instrument_options&&auto_explain_log_analyze);
es->verbose=auto_explain_log_verbose;
es->buffers= (es->analyze&&auto_explain_log_buffers);
es->wal= (es->analyze&&auto_explain_log_wal);
es->timing= (es->analyze&&auto_explain_log_timing);
es->summary=es->analyze;
es->format=auto_explain_log_format;
es->settings=auto_explain_log_settings;
ExplainBeginOutput(es);
ExplainQueryText(es, queryDesc);
ExplainPrintPlan(es, queryDesc);
if (es->analyze&&auto_explain_log_triggers)
ExplainPrintTriggers(es, queryDesc);
if (es->costs)
ExplainPrintJITSummary(es, queryDesc);
ExplainEndOutput(es);
/* Remove last line break */if (es->str->len>0&&es->str->data[es->str->len-1] =='\n')
es->str->data[--es->str->len] ='\0';
/* Fix JSON to output an object */if (auto_explain_log_format==EXPLAIN_FORMAT_JSON)
            {
es->str->data[0] ='{';
es->str->data[es->str->len-1] ='}';
            }
/** Note: we rely on the existing logging of context or* debug_query_string to identify just which statement is being* reported.  This isn't ideal but trying to do it here would* often result in duplication.*/ereport(auto_explain_log_level,
                    (errmsg("duration: %.3f ms  plan:\n%s",
msec, es->str->data),
errhidestmt(true)));
        }
MemoryContextSwitchTo(oldcxt);
    }
if (prev_ExecutorEnd)
prev_ExecutorEnd(queryDesc);
elsestandard_ExecutorEnd(queryDesc);
}
文章标签：
日志服务
关系型数据库
PostgreSQL
相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
t-25772328213-6-45193
目录
相关文章
wljslmz
|
3月前
|
SQL 数据库管理
深入理解SQL中的自动增量（AUTO_INCREMENT）
【8月更文挑战第31天】
wljslmz
266 0
sky_qiyi
|
5月前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引（4）-SQL性能分析-profile详情、explain（profile查看指令，explain执行计划中各个字段的含义）
MySQL数据库——索引（4）-SQL性能分析-profile详情、explain（profile查看指令，explain执行计划中各个字段的含义）
sky_qiyi
68 2
sky_qiyi
|
5月前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL优化（2/3）-order by 优化、group by 优化
MySQL数据库——SQL优化（2/3）-order by 优化、group by 优化
sky_qiyi
55 0
sky_qiyi
|
5月前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL优化（3/3）-limit 优化、count 优化、update 优化、SQL优化 小结
MySQL数据库——SQL优化（3/3）-limit 优化、count 优化、update 优化、SQL优化 小结
sky_qiyi
303 0
kobo_骷堡
|
SQL JSON 监控
24_mysql数据库优化之Explain扩展、SHOW WARNINGS、trace及sys表
学习笔记
kobo_骷堡
888 0
24_mysql数据库优化之Explain扩展、SHOW WARNINGS、trace及sys表
德哥
|
SQL 弹性计算 关系型数据库
PostgreSQL 12 preview - CTE 增强，支持用户语法层控制 materialized 优化
标签 PostgreSQL , CTE , materialized , not materialized , push down 背景 PostgreSQL with 语法，能跑非常复杂的SQL逻辑，包括递归，多语句物化计算等。 在12以前的版本中，WITH中的每一个CTE（common table express），都是直接进行物化的，也就是说外层的条件不会推到CTE（物化节点）里
德哥
1001 0
工程师甲
|
SQL 固态存储 架构师
Elasticsearch 如何实现 SQL 语句中 Group By 和 Limit 的功能
给大家讲一条常见的 SQL 语句如何用 Elasticsearch 的查询语言实现。
工程师甲
3243 0
Elasticsearch 如何实现 SQL 语句中 Group By 和 Limit 的功能
优惠码发放
|
SQL 关系型数据库 MySQL
【MySQL 原理分析】之 Explain & Trace 深入分析全模糊查询走索引的原理
优惠码发放
1179 0
杰克.陈
|
SQL 索引 存储
SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句
原文:SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句 原文出处：http://www.cnblogs.com/wy123/p/5933734.html     先看常用的一种表结构设计方式：   那么可能会遇到一种典型的查询方式，主子表关联，查询子表中的某些（或者全部）Key点对应的Value，横向显示（也即以行的方式显示）   这种查询方式很明显的一个却显示多次对字表查询（暂时抛开索引）   相比这种查询方式很多人都遇到过，如果子表是配置信息之类的小表的话，问题不大，如果字表数据量较大，可能就会有影响了。
杰克.陈
1253 0
德哥
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 11 preview - pg_stat_statements 增强，支持执行计划翻转记录
德哥
1937 0

热门文章

最新文章

  • 1
    SpringBoot开发案例之整合Dubbo提供者(一)
  • 2
    阿里AI上岗双11服装车间，能识别头发丝直径十分之一的瑕疵
  • 3
    Webpack构建library时的踩坑经历
  • 4
    安装nginx：src/os/unix/ngx_user.c:26:7: error: ‘struct crypt_data’ has no member named ‘curren
  • 5
    字节流套接口的读写
  • 6
    【转】类别catagory的用法
  • 7
    lsof 命令
  • 8
    TeamCity : 自动触发 Build
  • 9
    建站篇-用户认证系统-管理员登陆后台
  • 10
    asp.net学习之ado.net(无连接模式中的DataAdapter)
  • 1
    VideoChat：高效学习新神器！一键解读音视频内容，结合 AI 生成总结内容、思维导图和智能问答
    30
  • 2
    《C++巧筑智能框架根基：开启 AI 开发新航道》
    26
  • 3
    《跨越语言壁垒：Python 人工智能原型到 C++可执行程序的转型之路》
    20
  • 4
    Go语言中json序列化的一个小坑，建议多留意一下
    24
  • 5
    JVM常见面试题（四）：垃圾回收
    24
  • 6
    C语言物联网开发之设备安全与代码可靠性隐患
    21
  • 7
    C语言物联网开发之设备安全与代码可靠性隐患
    18
  • 8
    C 语言物联网开发之网络通信与数据传输难题
    17
  • 9
    Linux服务器安全
    15
  • 10
    Web应用防火墙（WAF）与数据库应用防火墙有什么区别？
    15

    • 相关课程

    更多
  • SQL调优与架构优化
  • SQL进阶及查询

    • 相关电子书

    更多
  • PolarDB NL2SQL: 帮助您写出准确、优化的SQL
  • Phoenix Search Index 功能与应用场景介绍
  • Blink SQL关键技术及实现原理

    • 相关实验场景

    更多
  • 使用SQL语句管理索引
  • SQL进阶之约束、索引
    • 下一篇
    阿里云OSS设置跨域访问