聊聊数据库的并行执行

并行执行一直是商用数据库的强大的武器库中的一个可以用来对开源数据库进行降维打击的重量级武器。不过随着PostgreSQL 10开始支持并行执行之后,开源数据库与商用数据库在这方面的差距再次被拉近了。不过在支持并行执行上面,PostgreSQL走的还是比Mysql走的快了很多。Mysql 8.0.14才支持了简单簇主键的并行扫描,从而实现简单的不带where条件的count(*)之类的查询的并行执行。不过既然有了开头,我想后续Mysql对并行查询的支持会越来越好。

并行执行是一个让DBA又爱又恨的功能,当一条SQL实在找不到什么可以优化的方案,无法通过索引来优化执行时间的时候,对全表的并行扫描可以十分简单的帮助我们解决这个问题。五、六年前,我们利用4路服务器上百线程的CPU资源和NVME SSD超高的IO吞吐能力,使用并行查询,让用户的一个无法优化的应用模块的执行时间从30分钟减少到不到1分钟,虽然那条SQL产生了瞬间2GB/秒以上的IO吞吐量,不过总算能够让用户把这个模块加入到即席查询功能里了。另外,数据库里早就支持的并行DDL更是让我们的维护时间得到了极大的节约。不过并行执行给DBA带来的不全是喜悦,也有十分痛苦的事情。

十多年前,我们公司的DBA在Oracle 数据库上创建索引的时候为了节约时间,设置了parallel 参数,建完索引忘记修改索引的parallel参数,第二天一上班,用户的系统就慢的一塌糊涂。经过分析发现,大量的SQL都在使用并行索引扫描,把CPU资源都耗尽了。在那个年代,CPU是稀缺资源,而并行查询就是洪水猛兽,在OLTP系统中是绝对要关掉的。这个习惯到现在还在深深的影响着当代的DBA,虽然很多DBA并不知道禁用并行查询的原理,但是他们从他们的前辈那里学到了,并行查询是洪水猛兽,绝对是要禁止的。在当今的硬件资源大发展的时代,平时我们的系统的资源使用率仅仅不到10%,此时再不分青红皂白的坚持禁用一切并行查询,就不合时宜了。

随着服务器硬件的快速升级,我们的系统中的CPU核数越来越多,服务器的内存也越来越大,IO的处理能力也越来愈强。如果在如此强大的硬件支持下,我们的SQL还只能够单进程串行执行,那么就无法发挥出现在的硬件的性能。并行执行是解决这个问题的很好的解决方法。通过多个并发会话分别处理SQL中的某个部分的工作,可以有效的利用系统资源,大大提升SQL执行的效率。目前支持并行执行的RDBMS 并不多,幸运的是,PostgreSQL是能够支持并行执行的。如果我们希望某些SQL的执行时间缩短一些,可以通过并行执行来进一步提高SQL的执行速度。

在PostgreSQL数据库上启用并行执行并不需要进行特殊的设置,不过需要我们的RDBMS的版本是10以上,因为在PostgreSQL 10之前,是不支持并行查询的。除此之外,我们还需要合理的设置两个参数:

l
max_parallel_workers_per_gather
必须设置为大于零的值,否则PostgreSQL的优化器可能不会生成并行执行的计划
l由于后台会话总数不能超过
max_worker_processes
的限制,因此必须设置合理的参数,否则可能会出现需要做并行执行时无法获取后台进程

当优化器确定并行查询是特定查询的最快执行策略时,它将创建一个包含Gather或Gather Merge节点的查询计划。Gather或者Gather Merge节点都将只有一个子计划,即计划中将并行执行的部分。如果Gather或者Gather Merge节点位于计划树的最顶部,则整个查询将并行执行。如果它在计划树的其他地方,那么只有它下面的计划部分会并行运行。

聊聊数据库的并行执行
图 并行执行的案例

在上面的例子中,查询只访问了一张表,当 max_parallel_workers_per_gather的时候,启动了4并发索引扫描,执行时间是599毫秒,当max_parallel_workers_per_gather设置为2的时候,执行时间是649毫秒,而当max_parallel_workers_per_gather设置为0的时候,并行执行的计划被关闭了,串行扫描的耗时为1533毫秒,比2并行时慢了一倍多。从这个例子看,并行为2的并行执行是性价比最好的执行方式。

通过并行执行来优化SQL的时候,需要注意以下几点:
l如果服务器的CPU/IO/内存等资源比较紧张时,则需要慎用并行执行。避免大量的并行执行消耗过多的系统资源,导致系统出现资源瓶颈;
l如果某条SQL并行执行后,系统资源消耗很大,但是执行时间改善不大,则要考虑是否使用并行执行;
l有些SQL无法通过其他方式进行优化,而且系统资源不存在瓶颈时,可以通过并行执行来优化SQL;
l当目前系统中执行并行执行的进程数量已经达到了max_worker_processes时,虽然执行计划生成了并行执行,但是并行执行会因为缺少执行进程资源而不会真正产生;
l可以通过调整max_worker_processes的数量来调整并行执行的SQL消耗的系统资源的数量。

在实际工作中,并行执行并不一定一定能够改善你的SQL的执行效率,而且针对较为复杂的SQL语句,因为并行执行节点所处的位置的不同,并行执行的效果也会有较大的不同。在使用并行执行来优化SQL的时候,一定要充分评估并行执行的消费比。针对大幅度增加了系统开销,而执行时间改善不大的SQL,要慎用并行执行。

另外一点要注意的是,进行并行执行的时候,并不是设置更多的并行WORKER就会获得更快的执行效率,并且目前也没有一个十分准确的公式可以计算一个SQL最佳的并行度,有时候,在优化一个并行执行的时候,需要你反复测试,才能获得较好的并行度参数。

此外,某些扫描类型并不能很好地并行执行。其中之一是 BITMAP HEAP SCAN,当并行度过大时,它的性能往往会降低。

最后但并非最不重要的一点是,一些查询结构是并行执行的,但它们的并行度分布不均匀。因此,您可能会看到 CPU 利用率在整个查询执行过程中变化很大。如果你遇到了这种情况,优化的最好方法是重写查询,使 PostgreSQL 可以创建一个分布得更好的执行计划。

前面我们通过PostgreSQL数据库的并行查询查询的例子来了解了并行查询,并且知道了并行查询带来的好处。不过在实际的数据库优化工作中,并行查询还是不能滥用,否则可能回导致文中前面所提到的那种后果。特别在OLTP系统上,你更需要慎重使用并行查询。在本文的最后,我们再次以PostgreSQL为例,讨论一些关于使用并行查询的限制性的注意事项。针对其他数据库,比如Oracle,这些问题也是类似的。

首先,如果你的系统是OLTP系统,并且CPU资源十分有限,并且r队列经常超出CPU线程数的2倍,那么所有 CPU 内核都已饱和,则不要启用并行执行,否则可能会因为CPU资源瓶颈而使你的大多数应用模块变得更慢。同时,大多数低延迟 OLTP 查询无法通过并行执行获得更低的执行时间,因为多个进程之间的调度协同可能会完全抵消掉并行执行带来的性能提升,很可能你的SQL消耗了大量的CPU资源,而并没有给你的应用带来执行时间的提升。

其次,如果让你在并行表扫描和索引范围扫描之间做选择,可能大多数情况下,正确的索引扫描会优于并行顺序表扫描。针对OLTP场景,大多数情况下,并行索引扫描的效果并不见得会快很多。

第三,并行执行会显着增加 WORK_MEM 值的内存使用量,因为每个哈希连接或排序操作都会分配 work_mem 内存量。在OLTP系统中,过度使用并行执行,会消耗光你有限的内存资源。

第四,如果你的IO子系统的能力有限,那么并行执行可能会耗光IO资源,让你的系统的IO延时增加数倍甚至数十倍,你的系统的整体性能可能因为IO延时的增加而急剧下降。

为了更好的控制并行查询,既能够达到对某些SQL的优化作用,又可以避免一些没有必要的OLTP应用错误使用并行查询。我们可以通过下面的参数来进行控制:

lmax_parallel_workers:限制可以做并行查询的WORKER的总数,从而控制并行查询可以消耗的CPU资源的上限,设置合理的参数,一方面可以确保需要并行查询的SQL可以有效的利用并行执行计划来缩短执行时间,又可以从总量上控制最大的资源开销;
lmin_parallel_table_scan_size/ min_parallel_index_scan_size:可以控制一个并行执行WORKER最小扫描的大小,这个参数不要设置的太小,从而使用过多的WORKER来进行扫描。比如如果我们不希望小于16MB的表采用并行扫描,那么min_parallel_table_scan_size可以设置为大于等于16MB的值;
lparallel_setup_cost:这个参数是评估执行计划时,用于计算启动并行进程的开销,默认是1000,这个参数设置的越大,执行计划使用并行执行的概率就越小。通过加大这个参数我们可以避免一些执行时间很短的,不需要通过并行执行来优化的SQL使用并行执行;
lparallel_tuple_cost:这个参数被优化器用来评估WORKER和LEADER之间传输tuple的开销,在有些PostgreSQL的版本中,这个参数默认值是0.1,加大这个参数,也会让并行执行在执行计划评估中,成本变高。

聊聊数据库的并行执行》来自互联网,仅为收藏学习,如侵权请联系删除。本文URL:https://www.hashtobe.com/549.html