JIT overhead slowdown

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

JIT overhead slowdown

Luis Carril

Hello,

    we noticed that in the presence of a schema with many partitions the jitting overhead penalizes the total query execution time so much that the planner should have decided not to jit at all. For example without jitting we go a 8.3 s execution time and with jitting enabled 13.8 s.

 

   Attached you can find the TPC-h schema, a query to trigger it and the plans that we obtained.


Setup:

   Current master from PSQL git repo, only compiled with llvm

   TPC-h schema attached, plus a single index per table, and a scale factor of 10. Tables where analyzed.

   The query is variation of query 12 to make the effect more relevant.

   Max_workers_per_gather is 8

   And we only vary the jit flag, we do not modify the costs.



Is this behavior expected? Is the cost function for jitting missing some circumstances?


Cheers
Luis


Dr. Luis M. Carril Rodríguez
Senior Software Engineer
[hidden email] 

Swarm64 AS

Parkveien 41 B | 0258 Oslo | Norway

Registered at Brønnøysundregistrene in Norway under Org.-Number 911 662 787

CEO/Geschäftsführer (Daglig Leder): Dr. Karsten Rönner; Chairman/Vorsitzender (Styrets Leder): Dr. Sverre Munck 


Swarm64 AS Zweigstelle Hive

Ullsteinstr. 120 | 12109 Berlin | Germany

Registered at Amtsgericht Charlottenburg - HRB 154382 B 




tpch-schema-part.sql (3K) Download Attachment
12-jit-slow.sql (1K) Download Attachment
plan-jit.txt (66K) Download Attachment
plan-no-jit.txt (64K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: JIT overhead slowdown

Andres Freund
Hi,

On 2019-01-18 14:12:23 +0000, Luis Carril wrote:
> Is this behavior expected? Is the cost function for jitting missing some circumstances?

The costing doesn't take the effect of overhead of repeated JITing in
each worker into account.  I could give you a test patch that does, if
you want to play around with it?

Greetings,

Andres Freund

Reply | Threaded
Open this post in threaded view
|

Re: JIT overhead slowdown

Luis Carril

Hi Andres,

   yes please it would be much apreciated. Would it not be possible to share the jitted program across the workers?


Cheers,
Luis


Dr. Luis M. Carril Rodríguez
Senior Software Engineer
[hidden email] 

Swarm64 AS

Parkveien 41 B | 0258 Oslo | Norway

Registered at Brønnøysundregistrene in Norway under Org.-Number 911 662 787

CEO/Geschäftsführer (Daglig Leder): Dr. Karsten Rönner; Chairman/Vorsitzender (Styrets Leder): Dr. Sverre Munck 


Swarm64 AS Zweigstelle Hive

Ullsteinstr. 120 | 12109 Berlin | Germany

Registered at Amtsgericht Charlottenburg - HRB 154382 B 




From: Andres Freund <[hidden email]>
Sent: Friday, January 18, 2019 5:42:54 PM
To: Luis Carril
Cc: [hidden email]
Subject: Re: JIT overhead slowdown
 
Hi,

On 2019-01-18 14:12:23 +0000, Luis Carril wrote:
> Is this behavior expected? Is the cost function for jitting missing some circumstances?

The costing doesn't take the effect of overhead of repeated JITing in
each worker into account.  I could give you a test patch that does, if
you want to play around with it?

Greetings,

Andres Freund
Reply | Threaded
Open this post in threaded view
|

Re: JIT overhead slowdown

Justin Pryzby
Hi,

On Fri, Jan 18, 2019 at 02:12:23PM +0000, Luis Carril wrote:
>     we noticed that in the presence of a schema with many partitions the jitting overhead penalizes the total query execution time so much that the planner should have decided not to jit at all. For example without jitting we go a 8.3 s execution time and with jitting enabled 13.8 s.
...
> Is this behavior expected? Is the cost function for jitting missing some circumstances?

On Fri, Jan 18, 2019 at 08:42:54AM -0800, Andres Freund wrote:
> The costing doesn't take the effect of overhead of repeated JITing in
> each worker into account.  I could give you a test patch that does, if
> you want to play around with it?

On Fri, Jan 18, 2019 at 06:02:43PM +0000, Luis Carril wrote:
>  yes please it would be much apreciated.

I'm also interested to try that ; on re-enabling JIT in 11.2, I see that JITed
queries seem to be universally slower than non-JIT.

I found that was discussed here:
https://www.postgresql.org/message-id/20180822161241.je6nghzjsktbb57b%40alap3.anarazel.de
https://www.postgresql.org/message-id/20180624203633.uxirvmigzdhcyjsd%40alap3.anarazel.de

Multiplying JIT cost by nworkers seems like an obvious thing to try, but I
wondered whether it's really correct?  Certainly repeated JITing takes N times
more CPU time, but doesn't make the query slower...unless the CPU resources are
starved and limiting ?

Justin