分享人：digoal

正文：

本篇内容将从2个部分为读者介绍PG+MySQL联合解决方案，希望可以让大家对PG+MySQL有更深入的了解，并可以将这些特性应用到项目中，达到降本提效的目的。

l 并行计算

l JIT

PG的并行计算跟JIT，分别应用在分析型的业务场景，比如复杂的TB级别的实时分析场景。其中加速分为两块内容，1、内置的多核并行计算，在PG里面支持CPU的多核并行计算，通过扩展插件能够支持GPU的并行计算。在阿里的RPG里，我们10的基础版有输出过GPU加速的针对于时空的业务场景。在特别复杂的业务场景，可以去使用GPU加速的规格；2、还有一块功能是JIT，即动态编译功能，在分析型的业务场景里面是一种非常常见的加速技术。

一、并行计算

1）并行计算的含义

从这两幅图来看，左边这幅图很显然不是一个并行计算的模型，比如给你派了一个很大的活，你有很多空闲的cpu，有很多的员工，但他们只能干等着，只有一个人在干活，别人怎么都使不着力；右边这张图显示了你有多少个活，我就可以上多少人，然后多个人一起完成一个重大的任务，或者是单人基本上在一个很长的时间周期才可能完成任务，这个就是并行计算。

2）并行计算相关参数

那么在PG里相关的参数有哪些？包括它的并行计算最大的并行度或自动计计算的并行是如何算出来的？

我们首先来看几个参数：

l max work process：这个参数你可以理解为你的一个PG实例最多有多少功能？ max parallel works：它指的是在同一个时间窗口，比如今早10:00，同一个时间窗口最多有多少个功能可以分配到并行计算的工作当中，即你的功能可能干很多事情。并行计算是一种工种，可能还有比如后台的一些垃圾回收，主从复制等等其他一些任务。那么我们这里max parallel works指的就是在同一时间窗口可以被分配到做并行计算最多能有多少个功能，比如我有一个实例，这个实例有64个cpu核，我把max parallel works设置成32，那意味着什么呢？意味着在任何时候，任何的时间窗口，它最多有32个并行计算的work process在工作，就不可能有33个，即通过这个来限制。那通过它来限制有什么好处？好处就是你可以让更多的工人去干别的事情。或者你的cpu只有64个，分配的功能数有64个，那么也有可能一个SQL就把它消耗光了，就可能会影响到别的紧急需要cpu计算能力的小事物。所以这是一个设置同一窗口有多少功能可以分配到并行计算任务当中的一个参数。

l max_parallel_workers_per_gather：这个指的是并行计算的子任务最多可以分多少功能。比如一条SQL，我们去读它的执行计划，它会分好多个阶段。比如先做一次全面扫描，一次过滤；下一个阶段是拿到这些结果之后做聚合；聚合之后做排序。它分成了好多个阶段，那每一个阶段最多可以使用多少个功能？因为这个阶段是串行的，就说要先做扫描，然后做聚合，做排序等。这个指的就是有一些任务是串行的，那么在每一个串行的子任务里，它一次可以分多少个功能。所以通常来说，per_gather也是我们会用来比如设置一个SQL，在其中的并行过程当中，每一次并行的子任务,开多少个并行的工作量的一个参数。

l parallel_leader_participation：并行计算有一个领导节点，指要follow进程，然后做安排任务这样一些事情。leader participation这个参数的是什么意思？就是指领导要不要跟工人一起干分配好的任务，比如一个全面扫描的任务，我把它分成了12块，就12个人一起来领任务，然后分完之后领导就什么也不干了，这种情况就把它设成off；如果你把它设成on，那么领导也会参与到这12份子任务里面去，其实就变成了13个子任务，即分任务的时候他会把自己这份也算上，变成13个子任务。这个就是leader要不要参加运行，跟工人一起干活的这样一个参数设置。

l 紧接着的四个参数是跟自动计算并行度有关的参数，其中一个是parallel_setup_cost，下面的是parallel_tuple_cost。那么parallel_setup_cost指的是什么？指的是如果我们要启动一次并行计算任务，首先要唤醒功能，然后叫多少个人等，包括follow进程，告诉工人一些状态信息，怎么派活，怎么处理等等，这都是一些启动的成本，即使你叫醒了十个人，每个人干的活都很少,也要算入启动成本，因为这是一个必须加载的成本。

l parallel_tuple_cost：指的就是我们工人在处理记录的时候，比如一次全面扫描，然后每个人都分了一些任务，每个人在处理这一条记录的时候需要额外去加载的成本。为什么会有这个成本呢？并行计算的进程中，一个任务可能会分很多个阶段，有扫描、聚合、排序等。不同的阶段之间在衔接的时候，功能之间就要交接任务，比如我已经扫描完了，要交给下一个流程的时候还需要交接任务，就产生交接的成本，即我们可以理解为每处理一条记录，你需要给多少代价或者提成。所以这使得优化器在评估使用并行计算的时候，会把这两部分代价算上。假如算出来一套不开启并行计算的代价，然后又算出来一套要开并行计算的代价，最后会选出一个代价更低的，很显然开并行计算，计算代价的时候是跟这两部分有关的。

l min_parallel_index_scan_size & min_parallel_table_scan_size：这两个参数指如果有一张表很小，就几十兆几百兆；另外一张表是一个索引，如果走索引扫描，索引也很小就几十兆几百兆，通常来说我们可以选择当你的表或者你的索引小于设置的参数的时，如小于一个G，就不要去启动并行计算，因为可能直接用单个进程去工作就可以获得很好的效率。min_parallel_table_scan_size是设置启动并行的最小的表或者最小的索引。

l alter table xx set (parallel_workers =32)：每一张表上面我们可以设置的表的并行度。

l max_parallel_workers_per_gather：当扫描这张表的时候，最多可以开启的并行度。

一条SQL到底能开多少个并行，是否启用并行取决什么？

第一跟代价有关，代价不能高于不开并行的。

第二就是参数开关，比如关闭了并行，设置了works_per_gather=0, 或表级 workers=0 ,或者把max_parallel_workers_per_gather设成0，那就没有功能了，没有功能怎么开工？就不能用并行。

第三就是max_parallel_workers：当前比如在同一窗口其他人也在执行SQL，其他人把你的功能全部用完，那你当前执行的这条SQL就没有功能可以分配了，你也不能启动并行，就是跟剩余的功能多少有关。如果剩余功能变成零了，你就不可能开并形。

并行度到底怎么算出来的呢？跟四个参数有关：

第一个是表级别的alter table xx set (parallel_workers =32)。

第二个是max_parallel_workers_per_gather。

第三个就是根据表的大小或者索引的大小。根据索引大小推算一个并行度出是如何计算的呢？因为PG是开源的，我们可以在它的代码里面看到，它是一个R的n次方，取的是那个n或者叫log n，跟那个表的大小有关，表越大，可开启的密度越高，那么具体到底是怎么算的，我们可以去看PG的内核里面关于并行计算这一块的优化器代码。也有可能我们设初始参数时，一个表示1g，另外一个表示100g， 100g的表它可能就会开16个并行，然后1g的表可能最多开3个并行或者几个并行。这个是取决于表的大小。

最后一个就是当前你在执行这条SQL的时候还有没有可以分配的功能，如果没有也不会启动并行。比如min( coalesce (table_parallel_workers是24，max_parallel_workers_per_gather是32，compute_by(rel_size)), max_parallel_workers是16， max_parallel_workers当前窗口已分配工人数已经没有了，是0，那么最后也不会启用并行。因为它是四个参数里面选择了一个最小的。

3）强制并行度

如何强制开并行的？

1、我们要保证我们有多少功能，比如我有128个功max_worker_processes=128；

2、可以用作并行工作的有128个工人set max_parallel_workers=128；

3、在一次SQL的一个子任务当中最多给过24个工人set max_parallel_workers_per_gather=24；

4、领导不参加工作，我们把它设成off，set parallel_leader_participation=off，即领导不干工人干的活，领导只干派任务、调度等等一些活。、

5、set parallel_setup_cost=0; set parallel_tuple_cost=0;

我们把它设成零的意思是说并行计算不会给你带来额外的开销，启动的成本即我们分配任务的成本也等这些代价都不算，保证可以去使用并行计算。

6、set min_parallel_index_scan_size=0; set min_parallel_table_scan_size=0，我们都把它设成设成零，意味着索引或者表不管多大，都可以走并行计算。

7、把这个表设成24set alter table xx set (parallel_workers =24); 这个时候我们就会就会去启用并强制使用24这样一个并行度。coalesce的意思就是当这个是空的时候，会去取后面这个compute_by(rel_size)),当这个表的这个层面设置了，他就不会取后面这个。那么这时你去看一个SQL的执行计划，你就会发现它的强制并行度变成24了。然后build的索引的时候它也是可以启动并行的。

8、build的索引跟并行度及上面这些没什么关系，它只跟一个参数有关，就是max_parallel_maintenance_workers，比如把它设成6，那么它就开6个并行去建这个索引。

4)例子

接下来就讲一些例子，我们的数据是10亿条记录：

l 没有索引的情况下我们做条件过滤。

l 做哈希聚合以及分组聚合。

l 自定义的函数的计算，就是比如我们自己创建了一个函数，然后要做大量的运算，个函数要经过很多就作为一个条件，然后在每一条记录上面都筛选一下。

l 通常分析业务场景，我们很可能会把一个任务分很多个阶段，比如运算一些表产生一些中间结果，然后这个中间结果又跟后面的去做继续的运算，那么也就意味着有可能会create table as后面跟一个大的查询。所以会产生分析中间表。

l 没有索引的情况下怎么做大量的排序，比如10亿条我没有所有的情况下我排序要排多久？

l 两张10亿的表做Join包括nestloop，merge,hash。

l 创建索引

l 索引的扫描

l 子查询

l CTE语法，如果思路复杂，逻辑复杂，你可以用CTE来写 common table express。

l 当分区表跟分区表放在一起用的时候，比如两个分区表，它们的分区字段分区类型都一样，那么当两个表要做join的时候，在pg里面它会采用智能join，也就意味着它每一个分区跟分区之间会自己去做join，就类似于NPP技术。

5）测试表

接下来我们要去完成这个测试，需要两张测试表t1&t2。

我们往t1里面去写入10亿条记录，t2这张表也写入10亿条记录，那么它的结构是八个字段，其中1个是int，6个是int2，其中有1个是int4，1个是文本类型c6 text，1个是时间戳timestamp。

第一个字段是一个唯一的值 1~10亿，第二个字段取值范围是0~10，第三个字段是0~100，第四个字段0~1000，然后0~1万， 0~10万这样的一些随机值。第6个字段是一个文本，这个文本是取自一个随机值，所以它也是随机的。那么也就意味着每一条记录其实都是非常随机的。最后这个时间戳是一个比较连续的值，取的是clock time out，所以每一条记录其实也是不一样的。然后我们设置这个表的parallel_workers为32，也就意味着我们把覆盖自动计算的并行度强制设成了32。

完成之后我们这个表已经创建好，每一个表都是87个g，10亿条记录。然后我们先看执行计划，当前并没有设置并行，

我们设置一下这几个值，让它强制开启24的并行度。

这里可以看到用了全面扫描，并行度是24。然后我们再来执行一下，

下面已经执行完了，那么87g的数据10亿条花了5.7秒，就扫描完了10亿条记录。

我们把并行关掉，看一下如果不开并行，它要花多少时间？

如果按照24×5的话应该是要花掉100一百二十几秒，对吧？一百二十几秒，我们可以先接着后面讲，我们可以可以来去等他是不是要花一百二十几秒，很显然它的这个性能提升应该基本上是线性的。

6）并行例子示例

l 并行无索引条件过滤、分组聚合、哈希聚合

下图中这条就用了HashAgg，分成了两个阶段来做（红色方框）。

那么我们可以来看一下它需要花多少时间，它需要花110秒的时间来完成这一次分组聚合，开并行是5.7秒。我们可以看一下时间的倍数关系大概是19.2倍。我们开的是24个并行，哈希的分组聚合，即C1字段花的时间是8.3秒，就完成了10亿条记录，87个GP数据的运算。

除了哈希聚合以外，实际上还支持一种聚合GroupAgg。GroupAgg实际上要用到排序，所以你会发现排序要用到中间结果，即要存临时的结果集，你会发现消耗了大量的临时文件，那么GroupAgg花了多少时间呢？只花了20秒。

很显然HashAgg要更快，所以一般建议选择HashAgg。

l 并行自定义函数计算

接下来我们去创建一个自定义的函数。这个函数做了传入文本的事，
然后把这个文本转换成一个Hash值，也就是int8的这样一个数值。完成之后判断它是不是小于1万select hashtext($1) < 10000，如果小于1万就返回true，否则就返回false。

比如传入一个字符串叫abc，它返回的是true，abcd、abcc返回的是false。那么我们把这个作为word条件传到这个t1表，就把c6字段作为一个传参，select count(*) from t1 where udf(c6)，然后我们去看一下它的执行效率怎么样？这个udf函数会不会影响我们的并行计算？很显然没有影响，因为它也启用了并行，我们在这里设置了safe，所以并行是安全的。有一些函数可能并行是不安全的，所以我们一定要根据实际条件去定义是否安全。

l 并行产生分析中间表

再往下就是create unlogged table t_1 as select c1,count(*) from t1 group by c1查询，这个查询就是用来存储我们的中间结果的。假使我们执行了一条这样的命令，然后我要把它的中间结果存起来，这个操作花了9.6秒。

l 并行无索引排序

再往下我们来看一下在没有索引的字段上，它的排序能不能并行？需要花多少时间？

我们这里选择了几种字段，一种是唯一id即这个字段它是唯一值select * from t1 order by id desc limit 10;select * from t1 order by id limit 10;一种是里面有大量的重复值select * from t1 order by c1 desc limit 10，C1这个字段有大量的重复值；还有一种是基于udf做排序的select * from t1 order by hashtext(c6) limit 10;select * from t1 order by hashtext(c6) desc limit 10;

1、基于id的倒排，同样它也是用了24个并行，花了8.7秒的时间完成10亿条记录的排序。

再往下同样还是基于id的正排，花的时间理稍微会快一点，只花了6.3秒。为什么会有快慢呢？因为这里用了quick sort，因为扫描值它会优先扫到最小值，所以没有memory的copy，没有换进换出，比如先存了一波，然后扫描的值倒排的时候，后面的那个值的又大，再扫一波，然后后面值又比它大，这样内存就会换进换出，所以我们可以看到倒排花了8.7秒，顺排只花了6.3秒，原因就在这里。

2、接下来是针对C1这个字段有大量的重复值，基于它来做一个正向的排序，和倒排的排序花了多少时间呢？

只花了6秒，因为比如你可能limit1000的时候，这个字段（上图红色方框）就已经出现了很多的重复，

所以它排序时不会有大量的memory copy，时间和基于id的顺排差不多的原因就在这里，少了memory。

3、再往下是基于udf做的排序，select * from t1 order by hashtext(c6) limit 10,这个udf输入一个c6字段，然后把它转换成int8的类型，然后基于这个in8的类型做排序，所以会多一点计算在里面。同时应该有大量的copy，所以花了16秒,因为这里面多了一些运算。

4、再往下是并行的哈希join，我们执行了两条，一条是带了where条件过滤的，一条是不带where条件过滤，我们知道t1跟t2这两张表都是10亿万条记录，

这两张10亿的记录表对id这个字段我们都没有建索引。它是一个唯一值，因为它是我们用generate series函数去生成的，它是从1一直生成到了10亿，那么这个表可能没有在内存里面，所它比我们t1这张表多了很多io的时间在里面，花了31秒才扫描出来。

当你再次执行时应该已经在内存里，会变快，5秒就扫出来了。

我们来看一下它的执行计划。

执行计划用了哈希join，图中哈希表是带了过滤的，因为这个表更小，结果集更小，c2＜5。

我们来看一下执行时间。观察下图，你发现会产生一些写操作。这些写操作可能是用来存哈希table的，因为在内存里已经塞不下了。那么会先执行写操作，然后再去跟t1那张表去做join，那么它到底需要花多少时间呢？1分15秒。

第二条是我们不带where条件。不带where条件，哈希表就更大了。我们刚刚看到之前哈希表是c2＜5，其实是5%的记录，大概就是50亿%×5%也就是5000万条记录的哈希表以及另外一个是10亿条记录的表去做join。针对这两张10亿的表做一个join花了1分15秒，然后select t1.c1,count(*) from t1 join t2 using (id) group by t1.c1就是我们两张表一对一的join，我们不做过滤，那么哈希表会更大，写操作的时间会更长，花了275.8秒。

5、并行创建索引&扫描

我们设置了两个参数，一个是用8个并行度去创建索引set max_parallel_maintenance_workers=8，然后创建索引时候用maintenance_work_memory。创建的是btree这个索引。

我们简单讲一下这两个参数的意思，max_parallel_maintenance_workers=8是创建索引的时候有多少个功能；set maintenance_work_mem='2GB就是创建索引的时候有多少临时的内存空间可以用来存储排序的时候分配的空间；创建索引这个操作create index idx_t1_1 on t1 using btree (c1)并行索引扫描。

最后索引创建完之后，我们再去做一个并行索引select count(*) from t1 wherec1=1扫描的这样一个case。

先set max_parallel_maintenance_workers=8，setmaintenance_work_mem='2GB';

workers=8也就开了8个并行，那么我们知道并行的创建索引过程是一个分阶段的，首先它会开多个work进程去扫描表的数据；接着针对我们要创建索引的字段在内部做排序；然后当要落盘落到文件里时会去做merge。

在一个10亿的表上去创建一个索引，花了5分钟的时间，如果不开并行计算，我们这个花的时间会更长，基本上起码三倍以上。

二、JIT

1）拉取式模型

我们为什么会有GIT。

我们来看一下执行计划，在数据库里面我们做一次聚合查询，然后带where条件Filter时，我们看一下这个数据库是怎么操作的呢？

数据库的操作过程实际是一个拉取式的模型。所谓拉取式模型就是要的结果是count。基于这个结果get tuple也就是记录， get tuple的过程当中又会出现Filter，然后又会再去调用filter去匹配是不是满足这个条件。最后拿到这个结果再返回到aggregation这样的一个处理函数里去做聚合，所以它是一个推的过程，也就意味着所有的这些调用，这些操作,其实有较多的函数跳进跳出，特别是一些带传参的函数还会涉及到memory的copy。所以这样一条查询的交互带来的开销占整个的运行的开销大概是56%。

2）推式模型

基于下面这种模型，左边我们可以使用动态编译的方法来规避每处理一条记录会有这么多的调用，那怎么做呢？实际上就是反过来，变成一个推的模型。所谓的模型就是说我们先Scan，Scan里面有Filter，然后在HashAgg里面处理这个结果。所以我们是动态的生成这样的一个处理过程，但是需要避免get tuple大量的函数调用这样一个动作。

3）优化的好处

这样的一个优化之后，能够使得我们函数调用交互带来的开销从56%降到6%，特别是调用越多，资源占比，整个的执行时间的占比会更高。用JIT方法，它所带来的性能提升会更加的明显。

4）JIT参数

下面是跟JIT相关的一些参数：

l jit：要不要开启JIT

l jit_provider：因为JIT是一个动态编译去生成generate code，就是谁提供这个编译器。业界用的比较多就是llvm。当然如果你有更好的也可以替换它，那么默认用的就是jit_provider，配的就是llvm。

l above cost：因为产生generate code的本身也是有开销的，就好像我们刚刚提到的并计算要唤醒那么多的功能，然后派活一样，它有启动成本。那么jit也是一样，jit的启动成本就是一条SQL要生成in编码，这个过程的开销要把你的执行执行计划里面涉及到的那些函数一次打包成一个大段的机器执行码，打包的过程是有开销的。所以如果这条SQL本身就很简单，那就没有必要启用它。jit的above cost指的就是如果你这个SQL超过我指定的代价，比如你设定了一个代价，要100万或者200万，那超过这个值的时候你才会去启用jit。

l inline above cost：这个指的是当你SQL的代价超过inline above cost值的时候，我们在git里面的哪些内容要做动态编译呢？inline的代码做动态编译。

l optimize：优化器相关的那些代码做动态编译。

l expressions & tuple_deforming：expressions就是说我们执行SQL的时候有一些表达式如加减乘除，函数调用，where条件里面可能也会有一些大于小于等这样一些表达式，这个表达式要不要启动动态编译，我们是true or false的，它不是通过cos控制的，是直接设为true或者false的。包括tuple_deforming，比如把一个tuple里面的某一些字段内容解析出来，它其实要经过函数的解析，要不要也把这个函数本身也塞到解动态编译的过程当中去。如果把这个四个inline_above_cost；optimize_above_cost；expressions；tuple_deforming都包了，大量的函数调用，全部都会把它转成硬代码，那它的启动成本就是转成硬代码的机器码的成本也会随之增加。

下面这几个参数是跟开发者相关的，比如jit_profiling_support是用于诊断jit的优化效果，打印profile的，然后dump是机器码，最后debugging_support是打印debug的信息。