Horton

Re: Parallel tuplesort (for parallel B-Tree index creation)

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Michael Paquier <michael.paquier@gmail.com>, Robert Haas <robertmhaas@gmail.com>, Heikki Linnakangas <hlinnaka@iki.fi>, Pg Hackers <pgsql-hackers@postgresql.org>, Corey Huinker <corey.huinker@gmail.com>
Date: 2017-02-04T01:45:10Z
Lists: pgsql-hackers 
On Fri, Feb 3, 2017 at 4:15 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
>> I suspect that this system isn't particularly well balanced for the
>> task of benchmarking the patch. You would probably see notably better
>> scalability than any you've shown in any test if you could add
>> additional sequential I/O bandwidth, which is probably an economical,
>> practical choice for many users. I suspect that you aren't actually
>> saturating available CPUs to the greatest extent that the
>> implementations makes possible.
>
> I will look into what IO options I can access before running larger
> tests.  Also I will look into running the test with both cold and warm
> caches (ie "echo 1 > /proc/sys/vm/drop_caches") so that read bandwidth
> enters the picture.

It might just have been that the table was too small to be an
effective target for parallel sequential scan with so many workers,
and so a presorted best case CREATE INDEX, which isn't that different,
also fails to see much benefit (compared to what you'd see with a
similar case involving a larger table). In other words, I might have
jumped the gun in emphasizing issues with hardware and I/O bandwidth
over issues around data volume (that I/O parallelism is inherently not
very helpful with these relatively small tables).

As I've pointed out a couple of times before, bigger sorts will be
more CPU bound because sorting itself has costs that grow
linearithmically, whereas writing out runs has costs that grow
linearly. The relative cost of the I/O can be expected to go down as
input goes up for this reason. At the same time, a larger input might
make better use of I/O parallelism, which reduces the cost paid in
latency to write out runs in absolute terms.

-- 
Peter Geoghegan

Commits

  1. Support parallel btree index builds.

  2. Report an ERROR if a parallel worker fails to start properly.

  3. Transfer state pertaining to pending REINDEX operations to workers.

  4. Add a barrier primitive for synchronizing backends.

  5. Allow DML commands that create tables to use parallel query.

  6. Refactor GetOldestXmin() to use flags

  7. Fix regression in parallel planning against inheritance tables.

  8. Don't create "holes" in BufFiles, in the new logtape code.

  9. Simplify the code for logical tape read buffers.

  10. Fix excessive memory consumption in the new sort pre-reading code.

  11. Implement binary heap replace-top operation in a smarter way.

  12. Cosmetic code cleanup in commands/extension.c.

  13. Speed up planner's scanning for parallel-query hazards.

  14. Read from the same worker repeatedly until it returns no tuple.

  15. Improve tuplesort.c to support variable merge order. The original coding