PostgreSQL 10.0 preview 多核并行增强 - tuplesort 多核并行创建索引

标签

PostgreSQL , 10.0 , 并行增强 , 多核并行创建索引

---

背景

PostgreSQL 10.0的多核并行有了诸多增强，比如多核并行排序，可以用于提升创建btree索引的速度。

As some of you know, I've been working on parallel sort. I think I've  
gone as long as I can without feedback on the design (and I see that  
we're accepting stuff for September CF now), so I'd like to share what  
I came up with. This project is something that I've worked on  
inconsistently since late last year. It can be thought of as the  
Postgres 10 follow-up to the 9.6 work on external sorting.  

Attached WIP patch series:  

* Adds a parallel sorting capability to tuplesort.c.  

* Adds a new client of this capability: btbuild()/nbtsort.c can now  
create B-Trees in parallel.  

Most of the complexity here relates to the first item; the tuplesort  
module has been extended to support sorting in parallel. This is  
usable in principle by every existing tuplesort caller, without any  
restriction imposed by the newly expanded tuplesort.h interface. So,  
for example, randomAccess MinimalTuple support has been added,  
although it goes unused for now.  

I went with CREATE INDEX as the first client of parallel sort in part  
because the cost model and so on can be relatively straightforward.  
Even CLUSTER uses the optimizer to determine if a sort strategy is  
appropriate, and that would need to be taught about parallelism if its  
tuplesort is to be parallelized. I suppose that I'll probably try to  
get CLUSTER (with a tuplesort) done in the Postgres 10 development  
cycle too, but not just yet.  

For now, I would prefer to focus discussion on tuplesort itself. If  
you can only look at one part of this patch, please look at the  
high-level description of the interface/caller contract that was added  
to tuplesort.h.  

Performance  
===========  

Without further ado, I'll demonstrate how the patch series improves  
performance in one case. This benchmark was run on an AWS server with  
many disks. A d2.4xlarge instance was used, with 16 vCPUs, 122 GiB  
RAM, 12 x 2 TB HDDs, running Amazon Linux. Apparently, this AWS  
instance type can sustain 1,750 MB/second of I/O, which I was able to  
verify during testing (when a parallel sequential scan ran, iotop  
reported read throughput slightly above that for multi-second bursts).  
Disks were configured in software RAID0. These instances have disks  
that are optimized for sequential performance, which suits the patch  
quite well. I don't usually trust AWS EC2 for performance testing, but  
it seemed to work well here (results were pretty consistent).  

Setup:  

CREATE TABLE parallel_sort_test AS  
    SELECT hashint8(i) randint,  
    md5(i::text) collate "C" padding1,  
    md5(i::text || '2') collate "C" padding2  
    FROM generate_series(0, 1e9::bigint) i;  

CHECKPOINT;  

This leaves us with a parallel_sort_test table that is 94 GB in size.  

SET maintenance_work_mem = '8GB';  

-- Serial case (external sort, should closely match master branch):  
CREATE INDEX serial_idx ON parallel_sort_test (randint) WITH  
(parallel_workers = 0);  

Total time: 00:15:42.15  

-- Patch with 8 tuplesort "sort-and-scan" workers (leader process  
participates as a worker here):  
CREATE INDEX patch_8_idx ON parallel_sort_test (randint) WITH  
(parallel_workers = 7);  

Total time: 00:06:03.86  

这个patch的讨论，详见邮件组，本文末尾URL。

PostgreSQL社区的作风非常严谨，一个patch可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，patch合并到master已经非常成熟，所以PostgreSQL的稳定性也是远近闻名的。

参考

https://commitfest.postgresql.org/13/690/

https://www.postgresql.org/message-id/flat/CAM3SWZQKM=Pzc=CAHzRixKjp2eO5Q0Jg1SoFQqeXFQ647JiwqQ@mail.gmail.com#CAM3SWZQKM=Pzc=CAHzRixKjp2eO5Q0Jg1SoFQqeXFQ647JiwqQ@mail.gmail.com