On Thu, May 4, 2017 at 10:36 PM, David Rowley
<david.rowley@2ndquadrant.com> wrote:
> On 3 May 2017 at 07:13, Robert Haas <robertmhaas@gmail.com> wrote:
>> Multiple people (including David Rowley
>> as well as folks here at EnterpriseDB) have demonstrated that for
>> certain queries, we can actually use a lot more workers and everything
>> works great. The problem is that for other queries, using a lot of
>> workers works terribly. The planner doesn't know how to figure out
>> which it'll be - and honestly, I don't either.
>
> For me, it seems pretty much related to the number of tuples processed
> on a worker, vs how many they return. As a general rule, I'd say the
> higher this ratio, the higher the efficiency ratio will be for the
> worker. Although that's not taking into account contention points
> where workers must wait for fellow workers to complete some operation.
> I think parallel_tuple_cost is a good GUC to have, perhaps we can be
> smarter about the use of it when deciding on how many workers should
> be used.
It does seem pretty clear that Gather is horrifyingly slow and
therefore often the gating factor, but I think it's also clear that
even if you removed the overhead of Gather completely, there will
always be *something* that limits scaling -- and I bet we're quite a
long way from the point where that thing is typically the amount of
hardware that you have.
One idea that crossed my mind is to just have workers write all of
their output tuples to a temp file and have the leader read them back
in. At some cost in I/O, this would completely eliminate the overhead
of workers waiting for the leader. In some cases, it might be worth
it. At the least, it could be interesting to try a prototype
implementation of this with different queries (TPC-H, maybe) and see
what happens. It would give us some idea how much of a problem
stalling on the leader is in practice. Wait event monitoring could
possibly also be used to figure out an answer to that question.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
