Shulou(Shulou.com)06/02 Report--

JIT just-in-time real-time compilation function

Under the query condition of large data set, JIT may rapidly improve the query speed. But it can not be effective in any case, you can refer to this https://www.postgresql.org/docs/11/jit-decision.html

Next, I'll take compiling PG11 to enable JIT as an example to demonstrate the performance improvement of JIT:

Note: the function of JIT needs to be supported by jit when compiling. PostgreSQL documentation states that the minimum version of LLVM needs 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 # switch to the path of the source code of pg11 to perform the compilation operation

. / 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'

# if there are errors such as lack of dependency packages, you can refer to the information on the network and execute the configure command again.

Modify the configuration file to enable the parameters of JIT. After modification, restart PG, and check the parameter setting values as follows:

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)

The test sample https://github.com/digoal/blog/blob/master/201910/20191017_01.md given by Dege

The following is my own actual test (CenOS7+PG11+ ordinary SATA hard disk, PG set shared_buffer=8GB without other parameter optimization, just start)

Create some test data:

Create table a (id int, info text, crt_Time timestamp, C1 int)

Insert into a select generate_series (1JM 100000000), 'test',now (), random () * 100;-- No indexing either. It depends solely on PG to fight hard.

Analyze a

\ dt+ a

List of relations

Schema | Name | Type | Owner | Size | Description

-+-

Public | a | table | postgres | 5746 MB |

(1 row)

Effect on PG11 with jit enabled:

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 (00RV 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 (00RU 04.781)

Postgres=# select * from an order by C1 desc limit 10

Id | info | crt_time | C1

-+-

99999958 | test | 2019-10-18 09 2232.391061 | 0

99999926 | test | 2019-10-18 09 2232.391061 | 0

99999901 | test | 2019-10-18 09 2232.391061 | 0

99999802 | test | 2019-10-18 09 2232.391061 | 0

99999165 | test | 2019-10-18 09 2232.391061 | 0

99999100 | test | 2019-10-18 09 2232.391061 | 0

99998968 | test | 2019-10-18 09 2232.391061 | 0

99998779 | test | 2019-10-18 09 2232.391061 | 0

99998652 | test | 2019-10-18 09 2232.391061 | 0

99998441 | test | 2019-10-18 09 2232.391061 | 0

(10 rows)

Time: 3317.480 ms (00VOR 03.317)

Postgres=# select C1 count (*) from a group by C1

Time: 5031.796 ms (00VOR 05.032)

Effect on PG11 with uncompiled jit:

Postgres=# select T1 C1 count (*) from a T1 join a T2 using (id) group by t1.c1

Time: 71410.034 ms (01purl 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 (00RV 09.330)

Postgres=# select * from an order by C1 desc limit 10

Id | info | crt_time | C1

-+-

99999518 | test | 2019-10-18 09VOV 1815. 36.532469 | 0

99999088 | test | 2019-10-18 09VOV 1815. 36.532469 | 0

99999016 | test | 2019-10-18 09VOV 1815. 36.532469 | 0

99998987 | test | 2019-10-18 09VOV 1815. 36.532469 | 0

99998899 | test | 2019-10-18 09VOV 1815. 36.532469 | 0

99998507 | test | 2019-10-18 09VOV 1815. 36.532469 | 0

99998142 | test | 2019-10-18 09VOV 1815. 36.532469 | 0

99998107 | test | 2019-10-18 09VOV 1815. 36.532469 | 0

99998050 | test | 2019-10-18 09VOV 1815. 36.532469 | 0

99997437 | test | 2019-10-18 09VOV 1815. 36.532469 | 0

(10 rows)

Time: 6113.971 ms (00VOR 06.114)

Postgres=# select C1 count (*) from a group by C1

Time: 9868.117 ms (09.868)

From the above test results, basically, for complex queries such as JOIN of big data set, after using JIT, the query speed is reduced by at least half.

Day-to-day OLTP+OLAP requirements, a set of PG11 all done.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.