Machine code caching in PostgreSQL query JIT-compiler

As the efficiency of main and external memory grows, alongside with decreasing hardware costs, the performance of database management systems (DBMS) on certain kinds of queries is more determined by CPU characteristics and the way it is utilized. Relational DBMS utilize diverse execution models to run SQL queries. Those models have different properties, but in either way suffer from substantial overhead during query plan interpretation. The overhead comes from indirect calls to handler functions, runtime checks and large number of branch instructions. One way to solve this problem is dynamic query compilation that is reasonable only in those cases when query interpretation time is larger than the time of compilation and optimized machine code execution. This requirement can be satisfied only when the amount of data to be processed is large enough. If query interpretation takes milliseconds to finish, then the cost of dynamic compilation can be hundreds of times more than the execution time of generated machine code. To pay off the cost of dynamic compilation, the generated machine code has to be reused in subsequent executions, thus saving the cost of code compilation and optimization. In this paper, we examine the method of machine code caching in our query JIT-compiler for DBMS PostgreSQL. The proposed method allows us to eliminate compilation overhead. The results show that dynamic compilation of queries with machine code caching feature gives a significant speedup on OLTP queries.

1. Graefe G. Volcano – an extensible and parallel query evaluation system. IEEE Transactions on Knowledge and Data Engineering, vol. 6, issue 1, 1994, pp. 120–135.

2. Stefan Manegold, Martin L. Kersten, and Peter Boncz. Database architecture evolution: mammals flourished long before dinosaurs became extinct. Proceedings of the VLDB Endowment, vol. 2, 2009, pp. 1648-1653.

3. S. Padmanabhan, T. Malkemus, A. Jhingran and R. Agarwal. Block oriented processing of relational database operations in modern computer architectures. In Proc. of the 17th International Conference on Data Engineering, 2001, pp. 567-574.

4. Thomas Neumann. Efficiently compiling efficient query plans for modern hardware. Proceedings of the VLDB Endowment, vol. 4, no. 9, 2011, pp. 539-550.

5. А.Н.Андреев. Классификация OLAP-систем вида xOLAP / A.N. Andreev. OLAP systems of XOLAP type classification. Available at: http://citforum.ru/consulting/BI/xolap_classification/, accessed: 25.07.2019 (in Russian).

6. TPC-H benchmark for testing OLAP workload. Available at: http://www.tpc.org/tpch/, accessed 25.07.2019.

7. What is an OLTP System? Available at: https://docs.oracle.com/database/121/VLDBG/GUID-0BC75680-5BD4-43A9-826F-CD8837D30EB2.htm#VLDBG1367, accessed: 25.07.2019.

8. TPC-B benchark for testing OLTP workload. Available at: http://www.tpc.org/tpcb/, accessed: 25.07.2019.

9. The LLVM Compiler Infrastructure. Available at: http://llvm.org/, accessed: 25.07.2019.

10. Шарыгин Е.Ю., Бучацкий Р.А., Скворцов Л.В., Жуйков Р.А., Мельник Д.М. Динамическая компиляция выражений в SQL-запросах для СУБД PostgreSQL. Труды ИСП РАН, том 28, вып. 4, 2016 г., стр. 217-240 / Sharygin E.Y., Buchatskiy R.A., Skvortsov L.V., Zhuykov R.A., Melnik D.M. Dynamic compilation of expressions in SQL queries for PostgreSQL. Trudy ISP RAN/Proc. ISP RAS, vol. 28, issue 4, 2016. pp. 217-240 (in Russian). DOI: 10.15514/ISPRAS-2016-28(4)-13

11. Бучацкий Р.А., Шарыгин Е.Ю., Скворцов Л.В., Жуйков Р.А., Мельник Д.М., Баев Р.В. Динамическая компиляция SQL-запросов для СУБД PostgreSQL. Труды ИСП РАН, том 28, вып. 6, 2016, стр. 37-48 / Buchatskiy R.A., Sharygin E.Y., Skvortsov L.V., Zhuykov R.A., Melnik D.M., Baev R.V. Dynamic compilation of SQL queries for PostgreSQL. Trudy ISP RAN/Proc. ISP RAS, vol. 28, issue 6, 2016, pp. 37-48 (in Russian). DOI: 10.15514/ISPRAS-2016-28(6)-3

12. E. Sharygin, R. Buchatskiy, R. Zhuykov, and A. Sher. Runtime Specialization of PostgreSQL Query Executor. Lecture Notes in Computer Science, vol. 10742, pp. 375–386, 2018.

13. PostgreSQL official site. Available at: https://www.postgresql.org/, accessed: 25.07.2019.

14. ISP RAS website. Available at: https://www.ispras.ru/, accessed: 25.07.2019.

15. PREPARE command, PostgreSQL. Available at: https://www.postgresql.org/docs/9.6/sql-prepare.html, accessed: 25.07.2019.

16. EXECUTE command, PostgreSQL. Available at: https://www.postgresql.org/docs/9.6/sql-execute.html, accessed: 25.07.2019.

17. MCJIT Design and Implementation. Available at: https://releases.llvm.org/4.0.0/docs/MCJITDesignAndImplementation.html, accessed: 25.07.2019.

18. Stack maps and patch points in LLVM. Available at: https://llvm.org/docs/StackMaps.html, accessed: 25.07.2019.

19. pgbench utility. Available at: https://www.postgresql.org/docs/9.6/pgbench.html, accessed: 25.07.2019.