PostgreSQL · 实现分析 · PostgreSQL 10.0 并行查询和外部表的结合

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

title: PostgreSQL · 实现分析 · PostgreSQL 10.0 并行查询和外部表的结合

author: 义从


前言

大家都知道,PostgreSQL 近几大版本中加入了很多 OLAP 相关特性。9.6 的并行扫描应该算最大的相关特性。在今年发布的 10.0 中,并行扫描也在不断加强,新增了并行的索引扫描。

我们知道并行扫描是支持外部数据源的。在云上,有很多存储存储产品可以以外部数据源的形式做数据库的外部存储。例如,阿里云的 OSS 和 AWS 的 S3 都是绝佳的外部数据源。云上的 PostgreSQL 和他们的结合可以给用户提供既廉价又高性能数存储的方案。

另人欣喜的是,PostgreSQL 的外部表对外提供了可编程接口,并且支持并行扫描框架。利用它可以使 PostgreSQL 的外部数据源访问效率得到质的提升。

技术铺垫

并行查询

并行查询是 PostgreSQL 引入的一个大特性,它可以优化 SQL 语句的执行方式,从传统的单一进程,最多使用单个 CPU 运算的模式,提升到多进程,协同完成工作的模式。

并行查询消耗更多的硬件资源,大大提高了任务的执行效率。
在 PostgreSQL 中,一个 SQL 任务是否可以被并行化,可以通过查看 SQL 的执行计划(Plan)的方式看到。

例如:

EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Gather  (cost=1000.00..217018.43 rows=1 width=97)
   Workers Planned: 2
   ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..216018.33 rows=1 width=97)
         Filter: (filler ~~ '%x%'::text)
(4 rows)

可以看到,上面的 SQL 采用了并行的方式执行,它使用了2个额外的并行工作进程(共3个进程)完成工作。

  1. 并行 worker 主要完成顺序扫描数据的和过滤数据的工作,符合条件的数据被发送给主进程。
  2. 主进程的 Gather 节点接受来自子进程的数据,再发给客户端。

并行查询的参数配置

合理的配置下列参数能让 PostgreSQL 成功开启并行查询特性。

  1. max_worker_processes 整个实例允许的最大并行工作进程,它的值建议和实例所在主机的逻辑 CPU 相关
  2. max_parallel_workers_per_gather 单个 Gather 节点的并行度,让单个 SQL 更快的执行,可以增大该参数的设置。
  3. force_parallel_mode 是否让查询优化器尽可能的选择并行的执行方式。

[详细的参数描述在这] [1]

外部表

外部表是 PostgreSQL 引入外部数据的入口,任何的外部数据源都可以使用该接口把数据引入到数据库中。用户可以像访问表一样读写外部数据源上的数据。
目前 PostgreSQL 支持的常见外部数据源有 MySQL Oracle PostgreSQL OSS S3 等。
PostgreSQL 在引入并行查询时也支持了外部表的并行查询,并扩展了之前的编程接口。

并行的外部表扫描实现分析

SQL 语句执行一般过程

一条 SQL 语句的执行通常经历下面的过程:

1) 语法分析和语意分析
2) 查询优化
3) 查询执行

外部表的扫描在阶段 2 和 3 都有相应的操作

  1. 查询优化阶段,需要提供对应外部数据源的数据大小(行数和行宽度)等信息,用于优化器计算最优的查询路径
  2. 查询执行阶段,需要实现几个回调函数,用于向执行器(executor)输送以行(slot)为单位的数据,直到外部数据读取完成。

并行查询在传统模式上的变化

并行查询模式的引入,是 PostgreSQL 在传统的 Pipeline 模式上的较大改动。

大致的改进点如下,这部分也是外部表的并行查询模式实现所要注意的

一 查询优化阶段

提供给优化器并行模型的各类代价信息,参与优化器进行整体的代价评估。
当并行模式最终被确认为最优方案后,优化器会给出并行模式的执行计划。

二 查询执行器阶段

执行器得到一个带有并行执行节点的计划,还要进行如下工作

  1. 启动并行工作进程。
  2. 开辟相关共享内存结构,准备交换数据。
  3. 构造并行协作相关内存结构。
  4. 给工作进程下发并行相关的执行任务。
  5. 并行执行,并行工作进程拿到数据做相应的处理后发送给主进程的 Gather 节点,主进程的 Gather 节点拿到数据后返回给上层节点。直到所有数据处理完毕。
  6. 释放资源,处理事物信息。

上诉工作中框架相关的通用工作 PostgreSQL 已经完成,我们需要在并行框架下实现各阶段的部分逻辑。下面将会重点说明这部分细节。

外表的并行查询的实现分析

实现外部表的并行扫描需要注意上述环节中每个环节,下面描述概要设计

一: 查询优化阶段

  1. 实现 IsForeignScanParallelSafe ,返回 true。 标志该数据源可以并行化。
  2. 补充函数 GetForeignPaths 根据外数据的规模和可提供的并行工作进程数等信息提供给优化器可以行并行 Path。

    a) 调用 create_foreignscan_path 创建可并行的外部表扫描节点 Path。
    
    b) 调用 add_partial_path 把生成好的 Path 加入优化器 Path 备选队列。
    
  3. 补充函数 GetForeignPlan 创建可并行的外部表扫描节点 Plan。

    
    a) 函数内部调用 make_foreignscan 根据输入的 Path 生成 Plan, 并向上返回。
    

二: 查询执行阶段

并行任务关键当然是把一个大的任务拆分成多个尽可能不相关的子任务,让这些子任务被并行的完成。

例如:

  • 1 对外部 MySQL 一张表 t 的读取,可以按照表 t 主键的值域把数据拆分成 N 部分,让并行 worker 分别读取其中一部分。
  • 2 对外部数据源 oss 一个目录 dir1 中多个文件中数据的读取,可以把这批文件均匀的分成 N 份,让并行 worker 分别完成其中的一部分。

如何合理的切分子任务,往往决定了最终的并行效果。合理的切分数据会使并行任务间尽量少的交互,最终任务完成耗时和并行工作进程数线性相关。

执行器的具体工作:

  1. 实现 EstimateDSMForeignScan 计算需求的共享内存大小。这部分内存将用户存放整个并行任务的相关信息。

    这部分流程主进程完成,即 Gather 节点完成。
    
  2. 实现 InitializeDSMForeignScan 分配共享内存,放入相关信息。

    我们把整个大任务拆分成一个子任务队列,并存入到共享内存中,初始化锁等信息。
    这部分流程也主进程完成,即 Gather 节点完成。
    
  3. 实现 InitializeWorkerForeignScan 并行 Worker 读取共享内存上的信息,获取子任务,准备正式开始工作。
  4. 数据的读写操作。

    这部分的实现尽量兼容传统模式的数据读取,或小幅调整。
    
  5. 实现 ShutdownForeignScan 数据扫描完成的后清理工作。

[详细的 Foreign Data Wrapper 接口实现说明在这] [2]

并行外部表查询的应用

并行查询能大大提高数据的访问效率,他把外部数据源深度整合到 PostgreSQL 中。可以轻松的和本地数据一起做复杂的运算。同时,我们也能利用这套机制,实现高效的外部数据导入工作。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
2月前
|
SQL 人工智能 关系型数据库
如何实现MySQL百万级数据的查询?
本文探讨了在MySQL中对百万级数据进行排序分页查询的优化策略。面对五百万条数据,传统的浅分页和深分页查询效率较低,尤其深分页因偏移量大导致性能显著下降。通过为排序字段添加索引、使用联合索引、手动回表等方法,有效提升了查询速度。最终建议根据业务需求选择合适方案:浅分页可加单列索引,深分页推荐联合索引或子查询优化,同时结合前端传递最后一条数据ID的方式实现高效翻页。
133 0
|
3月前
|
SQL 存储 关系型数据库
PostgreSQL窗口函数避坑指南:如何让复杂分析查询提速300%?
本文基于真实企业级案例,深入剖析PostgreSQL窗口函数的执行原理与性能陷阱,提供8大优化策略。通过定制索引、分区裁剪、内存调优及并行处理等手段,将分钟级查询压缩至秒级响应。结合CTE分阶段计算与物化视图技术,解决海量数据分析中的瓶颈问题。某金融客户实践表明,风险分析查询从47秒降至0.8秒,效率提升5800%。文章附带代码均在PostgreSQL 15中验证,助您高效优化SQL性能。
171 0
|
28天前
|
存储 关系型数据库 MySQL
使用命令行cmd查询MySQL表结构信息技巧分享。
掌握了这些命令和技巧,您就能快速并有效地从命令行中查询MySQL表的结构信息,进而支持数据库维护、架构审查和优化等工作。
165 9
|
10月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
566 66
|
2月前
|
关系型数据库 PostgreSQL
【赵渝强老师】PostgreSQL的并行查询
PostgreSQL的并行查询功能通过多CPU提升查询速度,尤其适用于处理大量数据但返回少量结果的场景。它利用Leader进程、Gather节点和Worker线程协作完成查询任务,显著提高性能。本文详细解析其工作原理及适用场景,并通过实例展示开启与关闭并行查询的性能差异。
108 2
|
3月前
|
关系型数据库 MySQL 数据库
MySQL报错:未知系统变量'tx_isolation'及隔离级别查询
记住,选择合适的隔离级别,就像是在风平浪静的湖面上找到适合的划船速度——既要快到能赶上午饭(性能),又不至于翻船(数据一致性问题)。
191 3
|
3月前
|
SQL 关系型数据库 MySQL
MySQL 进行 select 查询时 where 条件中 in 的value数过多却导致无记录返回
MySQL 进行 select 查询时 where 条件中 in 的value数过多返回不符合预期怎么办?会不会遇到bug了?
208 0
|
4月前
|
缓存 JSON 关系型数据库
MySQL 查询优化分析 - 常用分析方法
本文介绍了MySQL查询优化分析的常用方法EXPLAIN、Optimizer Trace、Profiling和常用监控指标。
|
7月前
|
SQL 关系型数据库 OLAP
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
179 2
|
7月前
|
算法 关系型数据库 MySQL
join查询可以⽆限叠加吗?MySQL对join查询有什么限制吗?
大家好,我是 V 哥。本文详细探讨了 MySQL 中 `JOIN` 查询的限制及其优化方法。首先,`JOIN` 查询不能无限叠加,存在资源(CPU、内存、磁盘 I/O)、性能和语法等方面的限制。过多的 `JOIN` 操作会导致数据库性能急剧下降。其次,介绍了三种常见的 `JOIN` 查询算法:嵌套循环连接(NLJ)、索引嵌套连接(INL)和基于块的嵌套循环连接(BNL),并分析了它们的触发条件和性能特点。最后,分享了优化 `JOIN` 查询的方法,包括 SQL 语句优化、索引优化、数据库配置调整等。关注 V 哥,了解更多技术干货,点赞👍支持,一起进步!
154 3

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版
  • 推荐镜像

    更多