背景
估计很多同学看过之前的月报PgSQL · 特性分析· JIT 在数据仓库中的应用价值，对JIT（just in time）和LLVM（Low Level Virtual Machine）有了一定的了解。概括地来说：
JIT 指的是即时编译，即程序在运行过程中即时进行编译，其中可以把编译的中间代码缓存或者优化。相对于静态编译代码，即时编译的代码可以处理延迟绑定并增强安全性。
LLVM 就提供了一种在程序运行时编译执行代码的程序框架，它对外提供API，使实现JIT 变得更加简单。
PostgreSQL 社区从2016年就开始对JIT 的实现进行了讨论，详见邮件列表。
该邮件中解释了PostgreSQL 需要JIT 技术的原因。因为PostgreSQL 代码中实现的都是通用的逻辑，这就导致在执行过程中可能造成大量不必要的跳转和代码分支执行，继而造成大量不必要的指令执行，造成CPU 的压力。而使用JIT 技术可以将代码扁平化（inline）执行，直接调用对应的函数，而且如果已经知道具体输入，可以直接删除掉很多间接代码的执行。
此外，邮件中也说明了在PostgreSQL 中实现JIT 选择LLVM 的理由，概括起来就是LLVM 成熟度更高，更稳定，license 更友好，支持C 语言。
在PostgreSQL 11 的版本中实现了基于LLVM 的JIT，本文主要是浅析JIT 在PostgreSQL 11 中的使用。
PostgreSQL 中JIT 的实现概述
PostgreSQL 中支持的JIT 功能
优化频繁调用的存取层 表达式计算 优化执行器流程
JIT accelerated operations
inlining
optimization
与JIT 相关的GUC 参数
jit，该参数为on 的时候代表打开JIT，反之则是不打开JIT。非常棒的一点是这个参数可以在session 中设置，这就给用户更大的主动权。目前PostgreSQL 11 中默认为off，对此社区也有相关的讨论（参见邮件列表），一方面希望jit 参数能够默认打开，让用户快速的使用起来，可以更多地发现问题，另一方面这样对直接升级上来的用户可能会带来一定的困扰，因为JIT 在某些场景下会带来额外的时间开销。所以最后讨论的结果是PostgreSQL 11 中该参数默认为off，而master 分支上该参数默认为on。 jit_provider，该参数表示提供JIT 的依赖库，默认为llvmjit。其实目前PostgreSQL 11 也只实现了llvmjit 一种方式。如果填写了不存在的依赖库，JIT 不会生效，也没有error 产生。 jit_above_cost，表示超过多少cost 的查询才会使用JIT 功能，其中不包含开销比较大的optimization。因为JIT 会增加一定的开销，所以这个参数可以使得满足要求的查询使用JIT，这样更大概率会起到加速的效果。默认为100000，如果设置为-1 则关闭JIT。 jit_inline_above_cost，表示超过多少cost 的查询使用JIT 的inline 功能。默认为500000，-1则关闭inline 功能。如果把这个值设置的比jit_above_cost 小，则达到了该cost，JIT 还是不会触发，没有意义。 jit_optimize_above_cost，表示超过多少cost 的查询使用JIT 的optimization 功能。默认为500000，-1则关闭优化功能。和jit_inline_above_cost 一样，如果把这个值设置的比jit_above_cost 小，没有意义。建议该值设置的比jit_inline_above_cost 大，这样可以在触发inline 功能后，开启optimization 功能。
如何启用JIT
./configure --with-llvm LLVM_CONFIG=/opt/rh/llvm-toolset-7/root/usr/bin/llvm-config CLANG=/opt/rh/llvm-toolset-7/root/usr/bin/clang简单的测试
postgres=# select version(); version----------------- PostgreSQL 11.4(1 row)
postgres=# create table t1(id integer primary key,c1 integer,c2 integer,c3 nteger,c4 integer,c5 integer,c6 integer,c7 integer,c8 integer,c9 integer);CREATE TABLEpostgres=# create table t2(id integer primary key,c1 integer,c2 integer,c3 integer,c4 integer,c5 integer,c6 integer,c7 integer,c8 integer,c9 integer);CREATE TABLEpostgres=# create table t3(id integer primary key,c1 integer not null,c2 integer not null,c3 integer not null,c4 integer not null,c5 integer not null,c6 integer not null,c7 integer not null,c8 integer not null,c9 integer not null);CREATE TABLEpostgres=# insert into t1 (id,c1,c2,c3,c4,c5,c6,c7,c8) values (generate_series(1,10000000),0,0,0,0,0,0,0,0);INSERT 10000000postgres=# insert into t2 (id,c2,c3,c4,c5,c6,c7,c8,c9) values (generate_series(1,10000000),0,0,0,0,0,0,0,0);INSERT 10000000postgres=# insert into t3 (id,c1,c2,c3,c4,c5,c6,c7,c8,c9) values (generate_series(1,10000000),0,0,0,0,0,0,0,0,0);INSERT 10000000postgres=# vacuum analyze t1;VACUUMpostgres=# vacuum analyze t2;VACUUMpostgres=# vacuum analyze t3;VACUUMpostgres=# set jit=off;SETpostgres=# explain analyze select sum(c8) from t1; QUERY PLAN---------------------------------------------------------------------------------------------------------------------- Aggregate (cost=218457.84..218457.85 rows=1 width=8) (actual time=1762.126..1762.126 rows=1 loops=1) -> Seq Scan on t1 (cost=0.00..193457.87 rows=9999987 width=4) (actual time=0.010..820.756 rows=10000000 loops=1) Planning Time: .242 ms Execution Time: 1762.159 ms(4 rows)
postgres=# explain analyze select sum(c8) from t2; QUERY PLAN----------------------------------------------------------------------------------------------------------------------- Aggregate (cost=218458.08..218458.09 rows=1 width=8) (actual time=1820.825..1820.825 rows=1 loops=1) -> Seq Scan on t2 (cost=0.00..193458.06 rows=10000006 width=4) (actual time=0.011..820.387 rows=10000000 loops=1) Planning Time: .102 ms Execution Time: 1820.855 ms(4 rows)
postgres=# explain analyze select sum(c8) from t3; QUERY PLAN---------------------------------------------------------------------------------------------------------------------- Aggregate (cost=208332.23..208332.24 rows=1 width=8) (actual time=1640.345..1640.345 rows=1 loops=1) -> Seq Scan on t3 (cost=0.00..183332.58 rows=9999858 width=4) (actual time=0.011..767.184 rows=10000000 loops=1) Planning Time: .101 ms Execution Time: 1640.374 ms(4 rows)
postgres=# explain analyze select sum(c2), sum(c3), sum(c4), sum(c5), sum(c6), sum(c7), sum(c8)from t1; QUERY PLAN----------------------------------------------------------------------------------------------------------------------- Aggregate (cost=368457.64..368457.65 rows=1 width=56) (actual time=2416.711..2416.711 rows=1 loops=1) -> Seq Scan on t1 (cost=0.00..193457.87 rows=9999987 width=28) (actual time=0.022..833.951 rows=10000000 loops=1) Planning Time: .069 ms Execution Time: 2416.755 ms(4 rows)
postgres=# explain analyze select sum(c2), sum(c3), sum(c4), sum(c5), sum(c6), sum(c7), sum(c8)from t2; QUERY PLAN------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=368458.17..368458.18 rows=1 width=56) (actual time=2451.844..2451.844 rows=1 loops=1) -> Seq Scan on t2 (cost=0.00..193458.06 rows=10000006 width=28) (actual time=0.019..842.359 rows=10000000 loops=1) Planning Time: .113 ms Execution Time: 2451.890 ms(4 rows)
postgres=# explain analyze select sum(c2), sum(c3), sum(c4), sum(c5), sum(c6), sum(c7), sum(c8)from t3; QUERY PLAN----------------------------------------------------------------------------------------------------------------------- Aggregate (cost=358330.10..358330.11 rows=1 width=56) (actual time=2273.825..2273.825 rows=1 loops=1) -> Seq Scan on t3 (cost=0.00..183332.58 rows=9999858 width=28) (actual time=0.017..792.839 rows=10000000 loops=1) Planning Time: .114 ms Execution Time: 2273.865 ms(4 rows)
postgres=# set jit=on;SETpostgres=# explain analyze select sum(c8) from t1; QUERY PLAN---------------------------------------------------------------------------------------------------------------------- Aggregate (cost=218457.84..218457.85 rows=1 width=8) (actual time=1421.869..1421.869 rows=1 loops=1) -> Seq Scan on t1 (cost=0.00..193457.87 rows=9999987 width=4) (actual time=0.024..820.463 rows=10000000 loops=1) Planning Time: .058 ms JIT: Functions: 3 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 0.551 ms, Inlining 2.166 ms, Optimization 20.364 ms, Emission 13.673 ms, Total 36.755 ms Execution Time: 1422.491 ms(8 rows)
postgres=# explain analyze select sum(c8) from t2; QUERY PLAN----------------------------------------------------------------------------------------------------------------------- Aggregate (cost=218458.08..218458.09 rows=1 width=8) (actual time=1414.109..1414.109 rows=1 loops=1) -> Seq Scan on t2 (cost=0.00..193458.06 rows=10000006 width=4) (actual time=0.022..818.406 rows=10000000 loops=1) Planning Time: .058 ms JIT: Functions: 3 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 0.557 ms, Inlining 2.231 ms, Optimization 20.261 ms, Emission 13.313 ms, Total 36.363 ms Execution Time: 1414.733 ms(8 rows)
postgres=# explain analyze select sum(c8) from t3; QUERY PLAN---------------------------------------------------------------------------------------------------------------------- Aggregate (cost=208332.23..208332.24 rows=1 width=8) (actual time=1388.406..1388.406 rows=1 loops=1) -> Seq Scan on t3 (cost=0.00..183332.58 rows=9999858 width=4) (actual time=0.023..768.711 rows=10000000 loops=1) Planning Time: .058 ms JIT: Functions: 3 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 0.549 ms, Inlining 2.177 ms, Optimization 20.383 ms, Emission 13.440 ms, Total 36.550 ms Execution Time: 1389.025 ms(8 rows)
postgres=# explain analyze select sum(c2), sum(c3), sum(c4), sum(c5), sum(c6), sum(c7), sum(c8) from t1; QUERY PLAN----------------------------------------------------------------------------------------------------------------------- Aggregate (cost=368457.64..368457.65 rows=1 width=56) (actual time=1687.375..1687.375 rows=1 loops=1) -> Seq Scan on t1 (cost=0.00..193457.87 rows=9999987 width=28) (actual time=0.025..830.483 rows=10000000 loops=1) Planning Time: .072 ms JIT: Functions: 3 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 0.756 ms, Inlining 2.180 ms, Optimization 26.391 ms, Emission 19.554 ms, Total 48.881 ms Execution Time: 1688.213 ms(8 rows)
postgres=# explain analyze select sum(c2), sum(c3), sum(c4), sum(c5), sum(c6), sum(c7), sum(c8) from t2; QUERY PLAN------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=368458.17..368458.18 rows=1 width=56) (actual time=1682.681..1682.681 rows=1 loops=1) -> Seq Scan on t2 (cost=0.00..193458.06 rows=10000006 width=28) (actual time=0.023..828.408 rows=10000000 loops=1) Planning Time: .071 ms JIT: Functions: 3 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 0.726 ms, Inlining 2.176 ms, Optimization 26.306 ms, Emission 19.807 ms, Total 49.015 ms Execution Time: 1683.482 ms(8 rows)
postgres=# explain analyze select sum(c2), sum(c3), sum(c4), sum(c5), sum(c6), sum(c7), sum(c8) from t3; QUERY PLAN----------------------------------------------------------------------------------------------------------------------- Aggregate (cost=358330.10..358330.11 rows=1 width=56) (actual time=1613.259..1613.259 rows=1 loops=1) -> Seq Scan on t3 (cost=0.00..183332.58 rows=9999858 width=28) (actual time=0.020..773.426 rows=10000000 loops=1) Planning Time: .069 ms JIT: Functions: 3 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 0.784 ms, Inlining 2.265 ms, Optimization 26.395 ms, Emission 19.780 ms, Total 49.224 ms Execution Time: 1614.121 ms(8 rows)select sum(c8) from t*; 在JIT 开启下大约有25% 左右的性能提升。 select sum(c2), sum(c3), sum(c4), sum(c5), sum(c6), sum(c7), sum(c8) from t*; 在JIT 开启下大约有29% 左右的性能提升。
postgres=# create table test (id serial);CREATE TABLEpostgres=# insert INTO test (id) select * from generate_series(1, 10000000);INSERT 10000000postgres=# set jit=off;SETpostgres=# explain select count(*) from test; QUERY PLAN----------------------------------------------------------------------------------------- Finalize Aggregate (cost=97331.43..97331.44 rows=1 width=8) -> Gather (cost=97331.21..97331.42 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=96331.21..96331.22 rows=1 width=8) -> Parallel Seq Scan on test (cost=0.00..85914.57 rows=4166657 width=0)(5 rows)
postgres=# set jit = 'on';SETpostgres=# show jit_above_cost; jit_above_cost---------------- 100000(1 row)
postgres=# show jit_inline_above_cost; jit_inline_above_cost----------------------- 500000(1 row)
postgres=# show jit_optimize_above_cost; jit_optimize_above_cost------------------------- 500000(1 row)
postgres=# explain select count(*) from test; QUERY PLAN----------------------------------------------------------------------------------------- Finalize Aggregate (cost=97331.43..97331.44 rows=1 width=8) -> Gather (cost=97331.21..97331.42 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=96331.21..96331.22 rows=1 width=8) -> Parallel Seq Scan on test (cost=0.00..85914.57 rows=4166657 width=0)(5 rows)
postgres=# explain analyze select count(*) from test; QUERY PLAN------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=97331.43..97331.44 rows=1 width=8) (actual time=415.747..415.748 rows=1 loops=1) -> Gather (cost=97331.21..97331.42 rows=2 width=8) (actual time=415.658..418.129 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=96331.21..96331.22 rows=1 width=8) (actual time=409.043..409.044 rows=1 loops=3) -> Parallel Seq Scan on test (cost=0.00..85914.57 rows=4166657 width=) (actual time=0.148..250.496 rows=3333333 loops=3) Planning Time: .054 ms Execution Time: 418.175 ms(8 rows)
postgres=# set jit_above_cost = 10; set jit_inline_above_cost = 10; set jit_optimize_above_cost = 10;SETSETSETpostgres=# show max_parallel_workers_per_gather; max_parallel_workers_per_gather--------------------------------- 2(1 row)
postgres=# explain analyze select count(*) from test; QUERY PLAN------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=97331.43..97331.44 rows=1 width=8) (actual time=441.672..441.672 rows=1 loops=1) -> Gather (cost=97331.21..97331.42 rows=2 width=8) (actual time=441.547..446.028 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=96331.21..96331.22 rows=1 width=8) (actual time=434.128..434.129 rows=1 loops=3) -> Parallel Seq Scan on test (cost=0.00..85914.57 rows=4166657 width=) (actual time=0.161..251.158 rows=3333333 loops=3) Planning Time: .057 ms JIT: Functions: 9 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 1.096 ms, Inlining 109.551 ms, Optimization 22.201 ms, Emission 19.127 ms, Total 151.974 ms Execution Time: 446.673 ms(12 rows)
postgres=# set max_parallel_workers_per_gather = 0;SETpostgres=# explain analyze select count(*) from test; QUERY PLAN------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=169247.71..169247.72 rows=1 width=8) (actual time=1172.932..1172.933 rows=1 loops=1) -> Seq Scan on test (cost=0.00..144247.77 rows=9999977 width=) (actual time=0.028..745.134 rows=10000000 loops=1) Planning Time: .046 ms JIT: Functions: 2 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 0.298 ms, Inlining 0.881 ms, Optimization 3.986 ms, Emission 3.788 ms, Total 8.952 ms Execution Time: 1173.292 ms(8 rows)
没有达到对应GUC 参数规定的cost，即使jit=on，JIT 也不会起作用。 JIT 对应功能的开启是有一定代价的，对于一些SQL 语句可能会有性能的下降，如上面的例子select count(*) from test。 JIT 会继承并行查询带来的性能提升。
总结
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69917105/viewspace-3162460/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
