PostgreSQL11編譯支持JIT功能

JIT? just-in-time 即時編譯功能

創新互聯建站專注為客戶提供全方位的互聯網綜合服務，包含不限于網站設計、成都做網站、黎城網絡推廣、成都小程序開發、黎城網絡營銷、黎城企業策劃、黎城品牌公關、搜索引擎seo、人物專訪、企業宣傳片、企業代運營等，從售前售中售后，我們都將竭誠為您服務，您的肯定，是我們最大的嘉獎；創新互聯建站為所有大學生創業者提供黎城建站搭建服務，24小時服務熱線：028-86922220，官方網址：m.kartarina.com

????JIT在大數據集的查詢條件下，可能迅速提升查詢速度的作用。但是它也不是任何情況下都能提效的，可以參考這篇??https://www.postgresql.org/docs/11/jit-decision.html

下面，我以編譯PG11開啟JIT為例演示下JIT的性能提升效果：

注意：JIT的功能需要在編譯的時候就開啟 jit的支持，PostgreSQL documentation 說明LLVM最低版本需要3.9

wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

yum localinstall epel-release-latest-7.noarch.rpm

yum install llvm5.0 llvm5.0-devel clang

cd /root/pg_sources/postgresql-11? ??# 切換到pg11的源碼的路徑下，執行編譯操作?

./configure --prefix=/usr/local/pgsql-11 \

--with-python --with-perl --with-tcl --with-pam \

--with-openssl --with-libxml --with-libxslt \

--with-llvm LLVM_CONFIG='/usr/lib64/llvm5.0/bin/llvm-config'

# 如果有缺少依賴包等報錯，可以參考網上的資料補充后，再次執行 configure 命令。

修改配置文件，開啟JIT的參數。修改后，重啟PG，查看到的參數設置值如下：

postgres=# select name,setting from pg_settings where name like 'jit%';

????????? name?????????? | setting

-------------------------+---------

?jit???????????????????? | on

?jit_above_cost????????? | 100000

?jit_debugging_support?? | off

?jit_dump_bitcode??????? | off

?jit_expressions???????? | on

?jit_inline_above_cost?? | 500000

?jit_optimize_above_cost | 500000

?jit_profiling_support?? | off

?jit_provider??????????? | llvmjit

?jit_tuple_deforming???? | on

(10 rows)

德哥給出的測試樣例? https://github.com/digoal/blog/blob/master/201910/20191017_01.md

下面是我自己實際測試的（CenOS7+PG11+普通SATA硬盤，PG就設置了shared_buffer=8GB?沒有做其它的參數優化，直接開搞）

造些測試數據：

create table a(id int, info text, crt_Time timestamp, c1 int);?

insert into a select generate_series(1,100000000),'test',now(),random()*100;? ?--也不加索引了，純靠PG自己來硬抗

analyze a;?

\dt+ a

??????????????????? List of relations

--------+------+-------+----------+---------+-------------

(1 row)

在開啟jit的PG11上的效果：

set jit=on;?

set max_parallel_workers_per_gather =32;?

alter table a set (parallel_workers =32);?

set min_parallel_table_scan_size =0;?

set min_parallel_index_scan_size =0;?

set parallel_setup_cost =0;?

set parallel_tuple_cost =0;?

postgres=# select t1.c1,count(*) from a t1 join a t2 using (id) group by t1.c1;?

Time: 31402.562 ms (00:31.403)

postgres=# explain select t1.c1,count(*) from a t1 join a t2 using (id) group by t1.c1;?

???????????????????????????????????????????????? QUERY PLAN

------------------------------------------------------------------------------------------------------------

?Finalize GroupAggregate? (cost=1657122.68..1657229.70 rows=101 width=12)

?? Group Key: t1.c1

?? ->? Gather Merge? (cost=1657122.68..1657212.53 rows=3232 width=12)

???????? Workers Planned: 32

???????? ->? Sort? (cost=1657121.85..1657122.10 rows=101 width=12)

?????????????? Sort Key: t1.c1

?????????????? ->? Partial HashAggregate? (cost=1657117.48..1657118.49 rows=101 width=12)

???????????????????? Group Key: t1.c1

???????????????????? ->? Parallel Hash Join? (cost=817815.59..1641492.46 rows=3125004 width=4)

?????????????????????????? Hash Cond: (t1.id = t2.id)

?????????????????????????? ->? Parallel Seq Scan on a t1? (cost=0.00..766545.04 rows=3125004 width=8)

?????????????????????????? ->? Parallel Hash? (cost=766545.04..766545.04 rows=3125004 width=4)

???????????????????????????????? ->? Parallel Seq Scan on a t2? (cost=0.00..766545.04 rows=3125004 width=4)

?JIT:

?? Functions: 23

?? Options: Inlining true, Optimization true, Expressions true, Deforming true

(16 rows)

postgres=# select t1.c1,count(*) from a t1 join a t2 on (t1.id=t2.id and t1.c1=2 and t2.c1=2) group by t1.c1;

?c1 |? count?

----+---------

? 2 | 1000506

(1 row)

Time: 4780.824 ms (00:04.781)

postgres=# select * from a order by c1,id desc limit 10;

??? id??? | info |????????? crt_time????????? | c1

----------+------+----------------------------+----

?99999958 | test | 2019-10-18 09:22:32.391061 |? 0

?99999926 | test | 2019-10-18 09:22:32.391061 |? 0

?99999901 | test | 2019-10-18 09:22:32.391061 |? 0

?99999802 | test | 2019-10-18 09:22:32.391061 |? 0

?99999165 | test | 2019-10-18 09:22:32.391061 |? 0

?99999100 | test | 2019-10-18 09:22:32.391061 |? 0

?99998968 | test | 2019-10-18 09:22:32.391061 |? 0

?99998779 | test | 2019-10-18 09:22:32.391061 |? 0

?99998652 | test | 2019-10-18 09:22:32.391061 |? 0

?99998441 | test | 2019-10-18 09:22:32.391061 |? 0

(10 rows)

Time: 3317.480 ms (00:03.317)

postgres=# select c1,count(*) from a group by c1;?

Time: 5031.796 ms (00:05.032)

在未編譯jit的PG11上的效果：

postgres=# ?select t1.c1,count(*) from a t1 join a t2 using (id) group by t1.c1;?

Time: 71410.034 ms (01:11.410)

postgres=# explain? select t1.c1,count(*) from a t1 join a t2 using (id) group by t1.c1;

????????????????????????????????????????????????? QUERY PLAN

--------------------------------------------------------------------------------------------------------------

?Finalize GroupAggregate? (cost=6150282.43..6150308.02 rows=101 width=12)

?? Group Key: t1.c1

?? ->? Gather Merge? (cost=6150282.43..6150306.00 rows=202 width=12)

???????? Workers Planned: 2

???????? ->? Sort? (cost=6149282.41..6149282.66 rows=101 width=12)

?????????????? Sort Key: t1.c1

?????????????? ->? Partial HashAggregate? (cost=6149278.03..6149279.04 rows=101 width=12)

???????????????????? Group Key: t1.c1

???????????????????? ->? Parallel Hash Join? (cost=1835524.52..5940950.58 rows=41665490 width=4)

?????????????????????????? Hash Cond: (t1.id = t2.id)

?????????????????????????? ->? Parallel Seq Scan on a t1? (cost=0.00..1151949.90 rows=41665490 width=8)

?????????????????????????? ->? Parallel Hash? (cost=1151949.90..1151949.90 rows=41665490 width=4)

???????????????????????????????? ->? Parallel Seq Scan on a t2? (cost=0.00..1151949.90 rows=41665490 width=4)

(13 rows)

Time: 0.636 ms

postgres=# select t1.c1,count(*) from a t1 join a t2 on (t1.id=t2.id and t1.c1=2 and t2.c1=2) group by t1.c1;

?c1 |? count?

----+---------

? 2 | 1001209

(1 row)

Time: 9329.623 ms (00:09.330)

postgres=# select * from a order by c1,id desc limit 10;

??? id??? | info |????????? crt_time????????? | c1

----------+------+----------------------------+----

?99999518 | test | 2019-10-18 09:18:36.532469 |? 0

?99999088 | test | 2019-10-18 09:18:36.532469 |? 0

?99999016 | test | 2019-10-18 09:18:36.532469 |? 0

?99998987 | test | 2019-10-18 09:18:36.532469 |? 0

?99998899 | test | 2019-10-18 09:18:36.532469 |? 0

?99998507 | test | 2019-10-18 09:18:36.532469 |? 0

?99998142 | test | 2019-10-18 09:18:36.532469 |? 0

?99998107 | test | 2019-10-18 09:18:36.532469 |? 0

?99998050 | test | 2019-10-18 09:18:36.532469 |? 0

?99997437 | test | 2019-10-18 09:18:36.532469 |? 0

(10 rows)

Time: 6113.971 ms (00:06.114)

postgres=# select c1,count(*) from a group by c1;?

Time: 9868.117 ms (00:09.868)

?從上面的測試結果看，基本上，對于大數據集的JOIN之類的復雜?查詢，用了JIT后，查詢速度在原有的基礎上再縮短至少一半。

日常的OLTP+OLAP需求，一套PG11全搞定。

網頁名稱：PostgreSQL11編譯支持JIT功能
轉載來源：http://m.kartarina.com/article12/pgojgc.html

成都網站建設公司_創新互聯，為您提供網站設計公司、網站收錄、搜索引擎優化、商城網站、網站導航、外貿網站建設

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源：創新互聯

猜你還喜歡下面的內容