Subject: Re: Rename max_parallel_degree?
To: "David G. Johnston" <david.g.johnston@gmail.com>
References: <20160426152217.GA548975@alvherre.pgsql>
 <7570.1464816591@sss.pgh.pa.us>
 <CA+TgmoZehxRoiby6Nj9mFO5pz6Ufdd9-bqa9e0zrKsgOZas7TQ@mail.gmail.com>
 <WM!805040707366f5cdb3300e025b7980c02089ce1331b645738e0eed164506a414e003d0d5027fbf21b435d5f3062c9bcf!@mailstronghold-1.zmailcloud.com>
 <575051AF.2090101@agliodbs.com> <21660.1464882811@sss.pgh.pa.us>
 <WM!724b24b17b93e28a639b08a6942b6a6157bfdd1279efab51af9a99d9a9c64a20e4b0cbbd1a06bbecfc8587389a6f6b67!@mailstronghold-1.zmailcloud.com>
 <57508E69.9000505@agliodbs.com>
 <CAKFQuwbJwf26X0JFOWMLZYpaUobmDVEsp3YDvNhHtM8YDNbaKQ@mail.gmail.com>
 <WM!de247c8715b277e7427f9aaacd540544aa13b6024b86cc6bd18c6481f34478ca83e1c40296c1e8a66a23a078bb07191d!@mailstronghold-3.zmailcloud.com>
 <57509894.9080507@agliodbs.com>
 <CAKFQuwa6K0AqxZv-NYdBLQvUeZHVKtOgd+BJgpan5SwnfuSiBg@mail.gmail.com>
 <WM!015b29b5a6838ea765569b67f6e620b1640146731a8f2745b578a26a0dca3e74d409165933a07d085ea7274f8c7a1f43!@mailstronghold-3.zmailcloud.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>,
 Amit Kapila <amit.kapila16@gmail.com>, Peter Geoghegan <pg@heroku.com>,
 Noah Misch <noah@leadboat.com>, David Rowley <david.rowley@2ndquadrant.com>,
 Alvaro Herrera <alvherre@2ndquadrant.com>,
 Magnus Hagander <magnus@hagander.net>, Bruce Momjian <bruce@momjian.us>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
From: Josh berkus <josh@agliodbs.com>
Message-ID: <5750D73D.8080203@agliodbs.com>
Date: Thu, 2 Jun 2016 18:02:53 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.6.0
MIME-Version: 1.0
In-Reply-To: 
 <WM!015b29b5a6838ea765569b67f6e620b1640146731a8f2745b578a26a0dca3e74d409165933a07d085ea7274f8c7a1f43!@mailstronghold-3.zmailcloud.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 06/02/2016 01:42 PM, David G. Johnston wrote:
>     =E2=80=8BAre you referring to right now or if we move the goal posts =
to making
>     > this a per-statement reservation?=E2=80=8B
>=20
>     I was assuming that we would have *both* per-operation and per-statem=
ent
>     limits.  I can see reasons for having both, I can see why power users
>     would want both, but it's going to be overwhelming to casual users.
>=20
>=20
> =E2=80=8B Got that.  The only problem on that front with the current setu=
p is
> that right now we are saying: "at most use 3 of the 8 available
> processes": i.e., we tie ourselves to a fixed number when I think a
> better algorithm would be: "on/off/auto - default auto" and we detect at
> runtime whatever values we feel are most appropriate based upon the
> machine we are running on.  If the user doesn't like our choices they
> can specify their own values.  But the only specified values in the
> configurations would be those placed there automatically by the user.=20
> If value isn't specified but is required it gets set at startup and can
> be seen in pg_settings.
>=20

Well, the hard part here is that the appropriate value is based on the
level of concurrency in the database as a whole.  For example, if it's a
website database with 200 active connections on a 32-core machine
already, you want zero parallelism.  Whereas if it's the only current
statement on a 16-core VM, you want like 8.

That sounds like a heuristic, except that the number of concurrent
statements can change in milleseconds.  So we'd really want to base this
on some sort of moving average, set conservatively.  This will be some
interesting code, and will probably need to be revised several times
before we get it right.  Particularly since this would involve scanning
some of the global catalogs we've been trying to move activity off of.

--=20
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)


--=20
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers