Re: Stampede of the JIT compilers

On 6/24/23 02:33, David Rowley wrote:
> On Sat, 24 Jun 2023 at 02:28, James Coleman <jtc331(at)gmail(dot)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.
>
> The main problem I see with it is that the costing does not account
> for how many expressions will be compiled. It's quite different to
> compile JIT expressions for a query to a single table with a simple
> WHERE clause vs some query with many joins which scans a partitioned
> table with 1000 partitions, for example.
>

I think it's both - as explained by James, there are queries with much
higher cost, but the JIT compilation takes much more than just running
the query without JIT. So the idea that 100k difference is clearly not
sufficient to make up for the extra JIT compilation cost.

But it's true that's because the JIT costing is very crude, and there's
little effort to account for how expensive the compilation will be (say,
how many expressions, ...).

IMHO there's no "good" default that wouldn't hurt an awful lot of cases.

There's also a lot of bias - people are unlikely to notice/report cases
when the JIT (including costing) works fine. But they sure are annoyed
when it makes the wrong choice.

>> But on the topic of the thread: I'd like to know if anyone has ever
>> considered implemented a GUC/feature like
>> "max_concurrent_jit_compilations" to cap the number of backends that
>> may be compiling a query at any given point so that we avoid an
>> optimization from running amok and consuming all of a servers
>> resources?
>
> Why do the number of backends matter? JIT compilation consumes the
> same CPU resources that the JIT compilation is meant to save. If the
> JIT compilation in your query happened to be a net win rather than a
> net loss in terms of CPU usage, then why would
> max_concurrent_jit_compilations be useful? It would just restrict us
> on what we could save. This idea just covers up the fact that the JIT
> costing is disconnected from reality. It's a bit like trying to tune
> your radio with the volume control.
>

Yeah, I don't quite get this point either. If JIT for a given query
helps (i.e. makes execution shorter), it'd be harmful to restrict the
maximum number of concurrent compilations. It we just disable JIT after
some threshold is reached, that'd make queries longer and just made the
pileup worse.

If it doesn't help for a given query, we shouldn't be doing it at all.
But that should be based on better costing, not some threshold.

In practice there'll be a mix of queries where JIT does/doesn't help,
and this threshold would just arbitrarily (and quite unpredictably)
enable/disable costing, making it yet harder to investigate slow queries
(as if we didn't have enough trouble with that already).

> I think the JIT costs would be better taking into account how useful
> each expression will be to JIT compile. There were some ideas thrown
> around in [1].
>

+1 to that

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company