Re: Stampede of the JIT compilers

James Coleman <jtc331@gmail.com> writes:
> On Sat, Jun 24, 2023 at 7:40 AM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>> On 6/24/23 02:33, David Rowley wrote:
>>> On Sat, 24 Jun 2023 at 02:28, James Coleman <jtc331@gmail.com> wrote:
>>>> There are a couple of issues here. I'm sure it's been discussed
>>>> before, and it's not the point of my thread, but I can't help but note
>>>> that the default value of jit_above_cost of 100000 seems absurdly low.
>>>> On good hardware like we have even well-planned queries with costs
>>>> well above that won't be taking as long as JIT compilation does.

>>> It would be good to know your evidence for thinking it's too low.

> It's definitely possible that I stated this much more emphatically
> than I should have -- it was coming out of my frustration with this
> situation after all.

I think there is *plenty* of evidence that it is too low, or at least
that for some reason we are too willing to invoke JIT when the result
is to make the overall cost of a query far higher than it is without.
Just see all the complaints on the mailing lists that have been
resolved by advice to turn off JIT.  You do not even have to look
further than our own regression tests: on my machine with current
HEAD, "time make installcheck-parallel" reports

real    0m8.544s
user    0m0.906s
sys     0m0.863s

for a build without --with-llvm, and

real    0m13.211s
user    0m0.917s
sys     0m0.811s

for a build with it (and all JIT settings left at defaults).  If you
do non-parallel "installcheck" the ratio is similar.  I don't see how
anyone can claim that 50% slowdown is just fine.

I don't know whether raising the default would be enough to fix that
in a nice way, and I certainly don't pretend to have a specific value
to offer.  But it's undeniable that we have a serious problem here,
to the point where JIT is a net negative for quite a few people.


> In that context capping the number of backends compiling, particularly
> where plans (and JIT?) might be cached, could well save us (depending
> on workload).

TBH I do not find this proposal attractive in the least.  We have
a problem here even when you consider a single backend.  If we fixed
that, so that we don't invoke JIT unless it really helps, then it's
not going to help less just because you have a lot of backends.
Plus, the overhead of managing a system-wide limit is daunting.

            regards, tom lane