Thread: JIT compiler for expressions
Hello hackers,
We'd like to present our work on adding LLVM JIT compilation of expressions in SQL queries for PostgreSQL. The source code (based on 9.6.1) along with brief manual is available at our github: https://github.com/ispras/
Currently, our JIT is used to compile expressions in every query, so for short-running queries JIT compilation may take longer than query execution itself. We plan to address it by using planner estimate to decide whether it worth JIT compiling, also we may try parallel JIT compilation. But for now we recommend testing it on a large workload in order to pay off the compilation (we've tested on 40GB database for Q1).
The changes in PostgreSQL code itself are rather small, while the biggest part of new code in our repository is autogenerated (it's LLVM IR generators for PostgreSQL backend functions). The only real reason for shipping prebuild_llvm_
JIT compilation was tested on Linux, and currently we have 5 actual tests failing (which results in 24 errors in a regtest). It requires LLVM 3.7 (3.7.1) as build dependency (you can specify path to proper llvm-config with --with-llvm-config= configure option, e.g. it could be named llvm-config-3.7 on your system). Mac support is highly experimental, and wasn't tested much, but if you like to give it a try, you can do it with LLVM 3.7 from MacPorts or Homebrew.
This work is a part of our greater effort on implementing full JIT compiler in PostgreSQL, where along with JITting expressions we've changed the iteration model from Volcano-style to push-model and reimplemented code generation with LLVM for most of Scan/Aggregation/Join methods. That approach gives much better speedup (x4-5 times on Q1), but it takes many code changes, so we're developing it as PostgreSQL extension. It's not ready for release yet, but we're now working on performance, compatibility, as well as how to make it easier to maintain by making it possible to build both JIT compiler and the interpreter from the same source code. More information about our full JIT compiler and related work is available in presentation at LLVM Cauldron (http://llvm.org/devmtg/2016-
Dmitry Melnik
ISP RAS (www.ispras.ru/en/)
<p dir="ltr">This sounds amazing.<p dir="ltr">My only comment is that LLVM 3.7 is kind of old in the accelerated world ofLLVM. If you have patches to LLVM you need you won't have much success submitting them as patches on 3.7. <p dir="ltr">Thecurrent stable release is 3.9 and the development snapshots are 4.0. I know LLVM moves quickly and that makesit hard to try to track the development. If you worked with 4.0 you might find the apis you're using getting deprecatedand rewritten several times while your project is under development.
Hi , On 2016-10-28 14:47:35 +0300, Dmitry Melnik wrote: > We'd like to present our work on adding LLVM JIT compilation of expressions > in SQL queries for PostgreSQL. Great! I'm also working in the area, albeit with a, I think, a bit different approach[1]. Is your goal to integrate this work into postgres proper, or is this more an academic research project? If the former, lets try to collaborate. If the latter, lets talk, so we're not integrating something dumb ;) [1]. So far I've basically converted expression evaluation, and tuple deforming, into small interpreted (switch/computed goto opcode dispatch) mini-languages, which then can be JITed. Adding a small handwritten x86-64 JIT (out of fun, not because I think that's a good approach) also resulted in quite noticeable speedups. Did you experiment with JITing tuple deforming as well? The reason I was thinking of going in this direction, is that it's a lot faster to compile such mini pieces of code, and it already gives significant speedups. There still are function calls to postgres functions, but they're all direct function calls, instead of indirect ones. > Currently, our JIT is used to compile expressions in every query, so for > short-running queries JIT compilation may take longer than query execution > itself. We plan to address it by using planner estimate to decide whether > it worth JIT compiling, also we may try parallel JIT compilation. But for > now we recommend testing it on a large workload in order to pay off the > compilation (we've tested on 40GB database for Q1). Could you give some estimates about how long JITing takes for you, say for Q1? Different approaches here obviously have very different tradeoffs. > This work is a part of our greater effort on implementing full JIT compiler > in PostgreSQL, where along with JITting expressions we've changed the > iteration model from Volcano-style to push-model and reimplemented code > generation with LLVM for most of Scan/Aggregation/Join methods. That > approach gives much better speedup (x4-5 times on Q1), but it takes many > code changes, so we're developing it as PostgreSQL extension. FWIW, I think long term, we're going to want something like that in core. I'm currently working on a "gradual" transformation towards that, by *optionally* dealing with "batches" of tuples which get passed around. Noticeable speedups, but not in the 4-5x range. > Also we're going to give a lightning talk at upcoming PGConf.EU in Tallinn, > and discuss the further development with PostgreSQL community. We'd > appreciate any feedback! Cool, lets chat a bit, I'm also here. Greetings, Andres Freund
On 10/28/16 6:47 AM, Dmitry Melnik wrote: > We'd like to present our work on adding LLVM JIT compilation of > expressions in SQL queries for PostgreSQL. The source code (based on > 9.6.1) along with brief manual is available at our > github: https://github.com/ispras/postgres > <https://github.com/ispras/postgres> . Сurrent speedup for TPC-H Q1 is > 20% (on 40GB workload). Please feel free to test it and tell us what you > think. For anyone looking to experiment with some of this stuff, it's possible to get LLVM-based JIT via plpython and numba as well. https://github.com/AustinPUG/PGDay2016/blob/master/Numba%20inside%20PostgreSQL.ipynb -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532)