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

【笔记】最佳实践—混合负载HTAP的实践和优化

2022-10-19 154
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB 分布式版，标准版 2核8GB
推荐场景：
使用PolarDB-X与Flink搭建实时数据大屏
阿里云百炼推荐规格 ADB PostgreSQL，4核16GB 100GB 1个月
推荐场景：
构建的企业专属Chatbot
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介： 背景信息 本文主要提供数据库上云后OLTP+OLAP一体化架构的最佳实践，既HTAP。主要面对的业务应用范围： 混合型HTAP数据库需求：如ORACLE数据库改造上云，云上数据库方案选型； OLTP系统查询慢，存在分析型场景和瓶颈的客户； 读写分离需求。

PolarDB-X HTAP架构更多信息，请参见混合负载HTAP。

HTAP集群

您购买的PolarDB-X主实例，主要面向在线通用业务场景。如果业务针对同一份数据有分析、专注离线拖数、跑批等场景，您可以在PolarDB-X主实例上购买多个只读实例。116.png业务如果有在线HTAP混合流量或者读写分离的需求，推荐使用集群地址。PolarDB-X内部会基于智能路由或者读写权重将部分流量转发给只读实例；业务上只有离线数据分析需求时，推荐使用只读地址，只读地址会直接访问只读实例，只读地址的流量会采用MPP加速。关于连接地址信息，请参见集群地址和只读地址。

路由

智能路由

PolarDB-X优化器会基于代价分析出查询物理扫描行数、CPU、内存、IO、网络等核心资源消耗量，将请求区分为TP与AP负载。当您在集群地址上开启了智能路由，会主动识别SQL的工作负载类型来做路由，比如将识别为AP负载的流量路由给只读实例。您可以通过explain cost指令查看SQL工作负载类型的识别情况。例如以下查询，该查询涉及到物理扫描行数rowcount很小，计算资源（CPU&Memory）也消耗比较少，所以这个查询被识别为TP负载。


mysql> explain cost  select a.k, count(*) cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 order by cnt limit 5, 10;                                                                                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TopN(sort="cnt ASC", offset=?2, fetch=?3): rowcount = 1.0, cumulative cost = value = 2.8765038E7, cpu = 37.0, memory = 64.0, io = 3.0, net = 5.75, id = 163602178                                                                                                                 |
|   Filter(condition="cnt > ?1"): rowcount = 1.0, cumulative cost = value = 2.8765026E7, cpu = 26.0, memory = 47.0, io = 3.0, net = 5.75, id = 163602177                                                                                                                            |
|     HashAgg(group="k", cnt="COUNT()"): rowcount = 1.0, cumulative cost = value = 2.8765025E7, cpu = 25.0, memory = 47.0, io = 3.0, net = 5.75, id = 163602171                                                                                                                     |
|       BKAJoin(condition="k = id", type="inner"): rowcount = 1.0, cumulative cost = value = 2.8765012E7, cpu = 12.0, memory = 18.0, io = 3.0, net = 5.75, id = 163602169                                                                                                           |
|         Gather(concurrent=true): rowcount = 1.0, cumulative cost = value = 2.3755003E7, cpu = 3.0, memory = 0.0, io = 1.0, net = 4.75, id = 163602164                                                                                                                             |
|           LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `id`, `k` FROM `sbtest1` AS `sbtest1` WHERE (`id` > ?)"): rowcount = 1.0, cumulative cost = value = 2.3755002E7, cpu = 2.0, memory = 0.0, io = 1.0, net = 4.75, id = 163601451         |
|         Gather(concurrent=true): rowcount = 1.0, cumulative cost = value = 5003.0, cpu = 3.0, memory = 0.0, io = 1.0, net = 0.0, id = 163602167                                                                                                                                   |
|           LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `k` FROM `sbtest1` AS `sbtest1` WHERE ((`k` > ?) AND (`k` IN (...)))"): rowcount = 1.0, cumulative cost = value = 5002.0, cpu = 2.0, memory = 0.0, io = 1.0, net = 0.0, id = 163601377 |                                                                                                                                                                                                                                                                    |
| WorkloadType: TP                                                                                                                                                                                                                                                                  |                                                                                                                                                                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

工作负载的识别，对于HTAP路由至关重要。这里也允许您通过HINT WORKLOAD_TYPE指定工作负载。同样以上述查询为例，可以将查询负载强制指定为AP。


mysql> explain cost /*+TDDL:WORKLOAD_TYPE=AP*/ select a.k, count(*) cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 order by cnt limit 5, 10;                                                                                                                                                                                                                                                           |

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | TopN(sort="cnt ASC", offset=?2, fetch=?3): rowcount = 1.0, cumulative cost = value = 2.8765038E7, cpu = 37.0, memory = 64.0, io = 3.0, net = 5.75, id = 163602178 |
 | Filter(condition="cnt > ?1"): rowcount = 1.0, cumulative cost = value = 2.8765026E7, cpu = 26.0, memory = 47.0, io = 3.0, net = 5.75, id = 163602177 |
 | HashAgg(group="k", cnt="COUNT()"): rowcount = 1.0, cumulative cost = value = 2.8765025E7, cpu = 25.0, memory = 47.0, io = 3.0, net = 5.75, id = 163602171 |
 | BKAJoin(condition="k = id", type="inner"): rowcount = 1.0, cumulative cost = value = 2.8765012E7, cpu = 12.0, memory = 18.0, io = 3.0, net = 5.75, id = 163602169 |
 | Gather(concurrent=true): rowcount = 1.0, cumulative cost = value = 2.3755003E7, cpu = 3.0, memory = 0.0, io = 1.0, net = 4.75, id = 163602164 |
 | LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `id`, `k` FROM `sbtest1` AS `sbtest1` WHERE (`id` > ?)"): rowcount = 1.0, cumulative cost = value = 2.3755002E7, cpu = 2.0, memory = 0.0, io = 1.0, net = 4.75, id = 163601451 |
 | Gather(concurrent=true): rowcount = 1.0, cumulative cost = value = 5003.0, cpu = 3.0, memory = 0.0, io = 1.0, net = 0.0, id = 163602167 |
 | LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `k` FROM `sbtest1` AS `sbtest1` WHERE ((`k` > ?) AND (`k` IN (...)))"): rowcount = 1.0, cumulative cost = value = 5002.0, cpu = 2.0, memory = 0.0, io = 1.0, net = 0.0, id = 163601377 | |
 | WorkloadType: AP | |
 +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

规则路由

除了基于代价的智能路由以外，我们也支持基于读写规则的路由。允许您在控制台参数管理上设置读写分离权重参数MASTER_READ_WEIGHT，默认值为100，可配置值区间[0, 100]。如果配置为Weight=60，意味着占60%的流量会继续在主实例执行，40%的剩余流量会路由到只读实例执行，如果只读实例有多个会进行自动分配。

智能路由和规则路由这两者关系是解耦的，具体关系请查看下表格。

智能路由规则 规则路由 (MASTER_READ_WEIGHT) 路由结果
开启 以代价的读写分离为主规则路由建议保持默认值为100
  • 事务和写操作流量，全部路由给主实例；
  • 识别为AP的查询流量，全部路由给只读实例；
  • 识别为TP的查询流量，按照（100-MASTER_READ_WEIGHT）路由给只读实例。
关闭 以规则的读写分离为主规则路由的可选范围：[0-100]
  • 事务和写操作流量，全部路由给主实例；
  • 识别为TP/AP的查询流量，一律按照（100-MASTER_READ_WEIGHT）路由给只读实例。

执行模式

目前PolarDB-X支持了三种执行模式：

  • 单机单线程（TP_LOCAL）：查询过程中，是单线程计算，TP负载的查询涉及到的扫描行数比较少，往往会采样这种执行模式，比如基于主键的点查。
  • 单机并行（AP_LOCAL）：查询过程中，会利用节点的多核资源做并行计算，您在没有购买只读实例的前提下，针对AP负载的查询，往往会采样这种执行模式，一般也称之为Parallel Query模式。
  • 多机并行（MPP）：您若购买了只读实例，针对AP负载的查询，可以协调只读实例上多个节点的多核做分布式多机并行加速。

为了让您可以准确知道执行模式，我们在原有EXPLAIN指令上，扩展出了EXPLAIN PHYSICAL。例如以下查询，通过指令可以查看到当前查询采样的是MPP模式，此外还可以获取到每个执行片段的并发数。


mysql> explain physical select a.k, count(*) cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 or
der by cnt limit 5, 10;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN                                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ExecutorType: MPP                                                                                                                                                 |
| The Query's MaxConcurrentParallelism: 2                                                                                                                           |
| Fragment 1                                                                                                                                                        |
|     Shuffle Output layout: [BIGINT, BIGINT] Output layout: [BIGINT, BIGINT]                                                                                       |
|     Output partitioning: SINGLE [] Parallelism: 1                                                                                                                 |
|     TopN(sort="cnt ASC", offset=?2, fetch=?3)                                                                                                                     |
|   Filter(condition="cnt > ?1")                                                                                                                                    |
|     HashAgg(group="k", cnt="COUNT()")                                                                                                                             |
|       BKAJoin(condition="k = id", type="inner")                                                                                                                   |
|         RemoteSource(sourceFragmentIds=[0], type=RecordType(INTEGER_UNSIGNED id, INTEGER_UNSIGNED k))                                                             |
|         Gather(concurrent=true)                                                                                                                                   |
|           LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `k` FROM `sbtest1` AS `sbtest1` WHERE ((`k` > ?) AND (`k` IN (...)))") |
| Fragment 0                                                                                                                                                        |
|     Shuffle Output layout: [BIGINT, BIGINT] Output layout: [BIGINT, BIGINT]                                                                                       |
|     Output partitioning: SINGLE [] Parallelism: 1 Splits: 16                                                                                                      |
|     LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `id`, `k` FROM `sbtest1` AS `sbtest1` WHERE (`id` > ?)")                     |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

工作负载和执行模式有一定的耦合关系，AP工作负载会采用多机并行计算。同样的也允许您通过HINT EXECUTOR_MODE指定执行模式。假如主实例空闲资源很多，可以考虑强制设置为单机或者多机并行模式来加速。


mysql> explain physical /+TDDL:EXECUTOR_MODE=AP_LOCAL/select a.k, count(*) cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 order by cnt limit 5, 10;                                                                                                                                                     |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ExecutorMode: AP_LOCAL                                                                                                                                      |
| Fragment 0 dependency: [] parallelism: 4                                                                                                                    |
| BKAJoin(condition="k = id", type="inner")                                                                                                                   |
|   Gather(concurrent=true)                                                                                                                                   |
|     LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `id`, `k` FROM `sbtest1` AS `sbtest1` WHERE (`id` > ?)")               |
|   Gather(concurrent=true)                                                                                                                                   |
|     LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `k` FROM `sbtest1` AS `sbtest1` WHERE ((`k` > ?) AND (`k` IN (...)))") |
| Fragment 1 dependency: [] parallelism: 8                                                                                                                    |
| LocalBuffer                                                                                                                                                 |
|   RemoteSource(sourceFragmentIds=[0], type=RecordType(INTEGER_UNSIGNED id, INTEGER_UNSIGNED k, INTEGER_UNSIGNED k0))                                        |
| Fragment 2 dependency: [0, 1] parallelism: 8                                                                                                                |
| Filter(condition="cnt > ?1")                                                                                                                                |
|   HashAgg(group="k", cnt="COUNT()")                                                                                                                         |
|     RemoteSource(sourceFragmentIds=[1], type=RecordType(INTEGER_UNSIGNED id, INTEGER_UNSIGNED k, INTEGER_UNSIGNED k0))                                      |
| Fragment 3 dependency: [0, 1] parallelism: 1                                                                                                                |
| LocalBuffer                                                                                                                                                 |
|   RemoteSource(sourceFragmentIds=[2], type=RecordType(INTEGER_UNSIGNED k, BIGINT cnt))                                                                      |
| Fragment 4 dependency: [2, 3] parallelism: 1                                                                                                                |
| TopN(sort="cnt ASC", offset=?2, fetch=?3)                                                                                                                   |
|   RemoteSource(sourceFragmentIds=[3], type=RecordType(INTEGER_UNSIGNED k, BIGINT cnt))                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

在多机并行MPP执行模式的并发度是根据物理扫描行数、实例规格和计算所涉及到表的分表数来计算出来的，整体的并行度要考虑高并发场景，所以并行度的计算会偏保守，您可以通过上述EXPLAIN PHYSICAL指令查看并行度。当然也同样支持HINT MPP_PARALLELISM强制指定并行度，


/+TDDL:EXECUTOR_MODE=MPP MPP_PARALLELISM=8/select a.k, count() cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 order by cnt limit 5, 10;

调度策略

假设您购买了多个只读实例并加入到集群地址中，您通过集群地址的查询SQL路由到只读实例的流量，会被均匀调度到只读实例多个节点上执行，调度会考虑各个节点的资源负载，确保各个节点的负载压力差不多。比如PolarDB-X会将只读实例延迟作为调度参考指标，避免将流量调度到延迟较大的只读实例上执行。115.png

业务如果有在线HTAP混合流量或者读写分离的需求，推荐使用集群地址。PolarDB-X内部会基于智能路由或者读写权重将部分流量转发给只读实例；业务上只有离线数据分析需求时，推荐使用只读地址，只读地址会直接访问只读实例，只读地址的流量会采用MPP加速。关于连接地址信息，请参见集群地址和只读地址。

路由

智能路由

PolarDB-X优化器会基于代价分析出查询物理扫描行数、CPU、内存、IO、网络等核心资源消耗量，将请求区分为TP与AP负载。当您在集群地址上开启了智能路由，会主动识别SQL的工作负载类型来做路由，比如将识别为AP负载的流量路由给只读实例。您可以通过explain cost指令查看SQL工作负载类型的识别情况。例如以下查询，该查询涉及到物理扫描行数rowcount很小，计算资源（CPU&Memory）也消耗比较少，所以这个查询被识别为TP负载。


mysql> explain cost  select a.k, count() cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 order by cnt limit 5, 10;                                                                                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TopN(sort="cnt ASC", offset=?2, fetch=?3): rowcount = 1.0, cumulative cost = value = 2.8765038E7, cpu = 37.0, memory = 64.0, io = 3.0, net = 5.75, id = 163602178                                                                                                                 |
|   Filter(condition="cnt > ?1"): rowcount = 1.0, cumulative cost = value = 2.8765026E7, cpu = 26.0, memory = 47.0, io = 3.0, net = 5.75, id = 163602177                                                                                                                            |
|     HashAgg(group="k", cnt="COUNT()"): rowcount = 1.0, cumulative cost = value = 2.8765025E7, cpu = 25.0, memory = 47.0, io = 3.0, net = 5.75, id = 163602171                                                                                                                     |
|       BKAJoin(condition="k = id", type="inner"): rowcount = 1.0, cumulative cost = value = 2.8765012E7, cpu = 12.0, memory = 18.0, io = 3.0, net = 5.75, id = 163602169                                                                                                           |
|         Gather(concurrent=true): rowcount = 1.0, cumulative cost = value = 2.3755003E7, cpu = 3.0, memory = 0.0, io = 1.0, net = 4.75, id = 163602164                                                                                                                             |
|           LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `id`, `k` FROM `sbtest1` AS `sbtest1` WHERE (`id` > ?)"): rowcount = 1.0, cumulative cost = value = 2.3755002E7, cpu = 2.0, memory = 0.0, io = 1.0, net = 4.75, id = 163601451         |
|         Gather(concurrent=true): rowcount = 1.0, cumulative cost = value = 5003.0, cpu = 3.0, memory = 0.0, io = 1.0, net = 0.0, id = 163602167                                                                                                                                   |
|           LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `k` FROM `sbtest1` AS `sbtest1` WHERE ((`k` > ?) AND (`k` IN (...)))"): rowcount = 1.0, cumulative cost = value = 5002.0, cpu = 2.0, memory = 0.0, io = 1.0, net = 0.0, id = 163601377 |                                                                                                                                                                                                                                                                    |
| WorkloadType: TP                                                                                                                                                                                                                                                                  |                                                                                                                                                                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

工作负载的识别，对于HTAP路由至关重要。这里也允许您通过HINT WORKLOAD_TYPE指定工作负载。同样以上述查询为例，可以将查询负载强制指定为AP。


mysql> explain cost /+TDDL:WORKLOAD_TYPE=AP/ select a.k, count(*) cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 order by cnt limit 5, 10;                                                                                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TopN(sort="cnt ASC", offset=?2, fetch=?3): rowcount = 1.0, cumulative cost = value = 2.8765038E7, cpu = 37.0, memory = 64.0, io = 3.0, net = 5.75, id = 163602178                                                                                                                 |
|   Filter(condition="cnt > ?1"): rowcount = 1.0, cumulative cost = value = 2.8765026E7, cpu = 26.0, memory = 47.0, io = 3.0, net = 5.75, id = 163602177                                                                                                                            |
|     HashAgg(group="k", cnt="COUNT()"): rowcount = 1.0, cumulative cost = value = 2.8765025E7, cpu = 25.0, memory = 47.0, io = 3.0, net = 5.75, id = 163602171                                                                                                                     |
|       BKAJoin(condition="k = id", type="inner"): rowcount = 1.0, cumulative cost = value = 2.8765012E7, cpu = 12.0, memory = 18.0, io = 3.0, net = 5.75, id = 163602169                                                                                                           |
|         Gather(concurrent=true): rowcount = 1.0, cumulative cost = value = 2.3755003E7, cpu = 3.0, memory = 0.0, io = 1.0, net = 4.75, id = 163602164                                                                                                                             |
|           LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `id`, `k` FROM `sbtest1` AS `sbtest1` WHERE (`id` > ?)"): rowcount = 1.0, cumulative cost = value = 2.3755002E7, cpu = 2.0, memory = 0.0, io = 1.0, net = 4.75, id = 163601451         |
|         Gather(concurrent=true): rowcount = 1.0, cumulative cost = value = 5003.0, cpu = 3.0, memory = 0.0, io = 1.0, net = 0.0, id = 163602167                                                                                                                                   |
|           LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `k` FROM `sbtest1` AS `sbtest1` WHERE ((`k` > ?) AND (`k` IN (...)))"): rowcount = 1.0, cumulative cost = value = 5002.0, cpu = 2.0, memory = 0.0, io = 1.0, net = 0.0, id = 163601377 |                                                                                                                                                                                                                                                                    |
| WorkloadType: AP                                                                                                                                                                                                                                                                  |                                                                                                                                                                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

规则路由

除了基于代价的智能路由以外，我们也支持基于读写规则的路由。允许您在控制台参数管理上设置读写分离权重参数MASTER_READ_WEIGHT，默认值为100，可配置值区间[0, 100]。如果配置为Weight=60，意味着占60%的流量会继续在主实例执行，40%的剩余流量会路由到只读实例执行，如果只读实例有多个会进行自动分配。

智能路由和规则路由这两者关系是解耦的，具体关系请查看下表格。

智能路由规则 规则路由 (MASTER_READ_WEIGHT) 路由结果
开启 以代价的读写分离为主规则路由建议保持默认值为100
  • 事务和写操作流量，全部路由给主实例；
  • 识别为AP的查询流量，全部路由给只读实例；
  • 识别为TP的查询流量，按照（100-MASTER_READ_WEIGHT）路由给只读实例。
关闭 以规则的读写分离为主规则路由的可选范围：[0-100]
  • 事务和写操作流量，全部路由给主实例；
  • 识别为TP/AP的查询流量，一律按照（100-MASTER_READ_WEIGHT）路由给只读实例。

执行模式

目前PolarDB-X支持了三种执行模式：

  • 单机单线程（TP_LOCAL）：查询过程中，是单线程计算，TP负载的查询涉及到的扫描行数比较少，往往会采样这种执行模式，比如基于主键的点查。
  • 单机并行（AP_LOCAL）：查询过程中，会利用节点的多核资源做并行计算，您在没有购买只读实例的前提下，针对AP负载的查询，往往会采样这种执行模式，一般也称之为Parallel Query模式。
  • 多机并行（MPP）：您若购买了只读实例，针对AP负载的查询，可以协调只读实例上多个节点的多核做分布式多机并行加速。

为了让您可以准确知道执行模式，我们在原有EXPLAIN指令上，扩展出了EXPLAIN PHYSICAL。例如以下查询，通过指令可以查看到当前查询采样的是MPP模式，此外还可以获取到每个执行片段的并发数。


mysql> explain physical select a.k, count(*) cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 or
der by cnt limit 5, 10;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN                                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ExecutorType: MPP                                                                                                                                                 |
| The Query's MaxConcurrentParallelism: 2                                                                                                                           |
| Fragment 1                                                                                                                                                        |
|     Shuffle Output layout: [BIGINT, BIGINT] Output layout: [BIGINT, BIGINT]                                                                                       |
|     Output partitioning: SINGLE [] Parallelism: 1                                                                                                                 |
|     TopN(sort="cnt ASC", offset=?2, fetch=?3)                                                                                                                     |
|   Filter(condition="cnt > ?1")                                                                                                                                    |
|     HashAgg(group="k", cnt="COUNT()")                                                                                                                             |
|       BKAJoin(condition="k = id", type="inner")                                                                                                                   |
|         RemoteSource(sourceFragmentIds=[0], type=RecordType(INTEGER_UNSIGNED id, INTEGER_UNSIGNED k))                                                             |
|         Gather(concurrent=true)                                                                                                                                   |
|           LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `k` FROM `sbtest1` AS `sbtest1` WHERE ((`k` > ?) AND (`k` IN (...)))") |
| Fragment 0                                                                                                                                                        |
|     Shuffle Output layout: [BIGINT, BIGINT] Output layout: [BIGINT, BIGINT]                                                                                       |
|     Output partitioning: SINGLE [] Parallelism: 1 Splits: 16                                                                                                      |
|     LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `id`, `k` FROM `sbtest1` AS `sbtest1` WHERE (`id` > ?)")                     |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

工作负载和执行模式有一定的耦合关系，AP工作负载会采用多机并行计算。同样的也允许您通过HINT EXECUTOR_MODE指定执行模式。假如主实例空闲资源很多，可以考虑强制设置为单机或者多机并行模式来加速。


mysql> explain physical /+TDDL:EXECUTOR_MODE=AP_LOCAL/select a.k, count(*) cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 order by cnt limit 5, 10;                                                                                                                                                     |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ExecutorMode: AP_LOCAL                                                                                                                                      |
| Fragment 0 dependency: [] parallelism: 4                                                                                                                    |
| BKAJoin(condition="k = id", type="inner")                                                                                                                   |
|   Gather(concurrent=true)                                                                                                                                   |
|     LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `id`, `k` FROM `sbtest1` AS `sbtest1` WHERE (`id` > ?)")               |
|   Gather(concurrent=true)                                                                                                                                   |
|     LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `k` FROM `sbtest1` AS `sbtest1` WHERE ((`k` > ?) AND (`k` IN (...)))") |
| Fragment 1 dependency: [] parallelism: 8                                                                                                                    |
| LocalBuffer                                                                                                                                                 |
|   RemoteSource(sourceFragmentIds=[0], type=RecordType(INTEGER_UNSIGNED id, INTEGER_UNSIGNED k, INTEGER_UNSIGNED k0))                                        |
| Fragment 2 dependency: [0, 1] parallelism: 8                                                                                                                |
| Filter(condition="cnt > ?1")                                                                                                                                |
|   HashAgg(group="k", cnt="COUNT()")                                                                                                                         |
|     RemoteSource(sourceFragmentIds=[1], type=RecordType(INTEGER_UNSIGNED id, INTEGER_UNSIGNED k, INTEGER_UNSIGNED k0))                                      |
| Fragment 3 dependency: [0, 1] parallelism: 1                                                                                                                |
| LocalBuffer                                                                                                                                                 |
|   RemoteSource(sourceFragmentIds=[2], type=RecordType(INTEGER_UNSIGNED k, BIGINT cnt))                                                                      |
| Fragment 4 dependency: [2, 3] parallelism: 1                                                                                                                |
| TopN(sort="cnt ASC", offset=?2, fetch=?3)                                                                                                                   |
|   RemoteSource(sourceFragmentIds=[3], type=RecordType(INTEGER_UNSIGNED k, BIGINT cnt))                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

在多机并行MPP执行模式的并发度是根据物理扫描行数、实例规格和计算所涉及到表的分表数来计算出来的，整体的并行度要考虑高并发场景，所以并行度的计算会偏保守，您可以通过上述EXPLAIN PHYSICAL指令查看并行度。当然也同样支持HINT MPP_PARALLELISM强制指定并行度，


/+TDDL:EXECUTOR_MODE=MPP MPP_PARALLELISM=8/select a.k, count(*) cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 order by cnt limit 5, 10;

调度策略

假设您购买了多个只读实例并加入到集群地址中，您通过集群地址的查询SQL路由到只读实例的流量，会被均匀调度到只读实例多个节点上执行，调度会考虑各个节点的资源负载，确保各个节点的负载压力差不多。比如PolarDB-X会将只读实例延迟作为调度参考指标，避免将流量调度到延迟较大的只读实例上执行。

文章标签：
云原生分布式数据库 PolarDB-X
云原生数据仓库 AnalyticDB PostgreSQL版
云数据库 RDS MySQL 版
数据挖掘
并行计算
SQL
OLTP
关系型数据库
Oracle
数据库
调度
xaubllxwtvaqiu
目录
相关文章
姚华
|
测试技术
iozone如何进行分布式性能测试
iozone的一个特性就是可以进行分布式测试，对分布式存储系统进行测试
姚华
613 0
王老邪
|
Java 数据安全/隐私保护
JavaWeb-Springboot图片裁剪
Springboot下，通过HTTP方式对图片进行裁剪
王老邪
310 0
JavaWeb-Springboot图片裁剪
qb656s3kpfkgu
|
存储 数据可视化 前端开发
地图作业平台低代码实战（搭建能力提升）
文章主要介绍了地图作业平台借助阿里低代码引擎，通过自定义插件，设置器，组件标准化等一系列方式，降低了作业平台内组件间联动，数据处理的难度，从而实现地图作业平台的可视化搭建
qb656s3kpfkgu
1393 9
云课程笔记
|
架构师 Java 关系型数据库
电商网站需求分析和架构设计（一）|学习笔记
快速学习电商网站需求分析和架构设计（一）
云课程笔记
402 0
电商网站需求分析和架构设计（一）|学习笔记
战旗向前
|
存储 自然语言处理 数据库
Elasticsearch的完整读写流程
Elasticsearch的完整读写流程
战旗向前
638 0
彭旭锐
|
存储 缓存 安全
NDK 系列（5）：JNI 从入门到实践，万字爆肝详解！(下）
NDK 系列（5）：JNI 从入门到实践，万字爆肝详解！(下）
彭旭锐
438 0
NDK 系列（5）：JNI 从入门到实践，万字爆肝详解！(下）
rancunjiejie32
|
存储 弹性计算 缓存
阿里云服务器4核16G配置租用价格及公网带宽和系统盘最新收费标准整理
本文介绍了阿里云服务器4核16G配置最新的租用费用，包含收费标准及最新活动价格和公网带宽和系统盘收费情况以及后续升级续费最新优惠政策等。
rancunjiejie32
1343 0
阿里云服务器4核16G配置租用价格及公网带宽和系统盘最新收费标准整理
云小帮
|
弹性计算 CDN
阿里云服务器香港节点和北京深圳上海杭州地域的区别对比
阿里云中国香港地域服务器和中国大陆地域有什么区别？阿里云百科分别从备案、网络延迟速度及价格三方面来详细对比
云小帮
1723 0
阿里云服务器香港节点和北京深圳上海杭州地域的区别对比
6ybclawazwxz6
MATLAB-直方图均衡化
直方图均衡化(Histogram Equalization) 又称直方图平坦化,实质上是对图像进行非线性拉伸,重新分配图像象元值,使一定灰度范围内象元值的数量大致相等。这样,原来直方图中间的峰顶部分对比度得到增强,而两侧的谷底部分对比度降低,输出图像的直方图是一个较平的分段直方图:如果输出数据分段值较小的话,会产生粗略分类的视觉效果。
6ybclawazwxz6
425 0
MATLAB-直方图均衡化
bigsai
|
关系型数据库 MySQL Java
mysql 表名和和数据库函数名称冲突的解决方法
好久没写blog了，今天刚考完网络后面还有一大段时间没考试可以学点技术了。但是，今天晚上被mysql卡了一晚上，，，因为我的表有一个叫show，因为我很少使用show这个函数。
bigsai
555 0
mysql 表名和和数据库函数名称冲突的解决方法