Why overhead of SPI is so large?
Locked
12
12
Why overhead of SPI is so large?
 
|
Hi, hackers.
One of our customers complains about slow execution of PL/pgSQL functions comparing with Oracle. So he wants to compile PL/pgSQL functions (most likely just-in-time compilation). Certainly interpreter adds quite large overhead comparing with native code (~10 times) but most of PL/pgSQL functions are just running some SQL queues and iterating through results. I can not believe that JIT can significantly speed-up such functions. So I decided to make simple experiment: I created large enough table and implemented functions which calculates norm of one column in different languages. Results are frustrating (at least for me): PL/pgSQL: 29044.361 ms C/SPI: 22785.597 ms С/coreAPI: 2873.072 ms PL/Lua: 33445.520 ms SQL: 7397.639 ms (with parallel execution disabled) The fact that difference between PL/pgSQL and function implemented in C using SPI is not so large was expected by me. But why it is more than 3 time slower than correspondent SQL query? The answer seems to be in the third result: the same function in C implemented without SPI (usign table_beginscan/heap_getnext) Looks like SPI adds quite significant overhead. And as far as almost all PL languages are using SPI, them all suffer from it. Below is profile of SPI function execution: 9.47% postgres libc-2.23.so [.] __memcpy_avx_unaligned 9.19% postgres spitest.so [.] spi_norm 8.09% postgres postgres [.] AllocSetAlloc 4.50% postgres postgres [.] tts_buffer_heap_getsomeattrs 4.36% postgres postgres [.] heap_form_tuple 3.41% postgres postgres [.] standard_ExecutorRun 3.35% postgres postgres [.] ExecScan 3.31% postgres postgres [.] palloc0 2.41% postgres postgres [.] heapgettup_pagemode 2.40% postgres postgres [.] AllocSetReset 2.25% postgres postgres [.] PopActiveSnapshot 2.17% postgres postgres [.] PortalRunFetch 2.16% postgres postgres [.] HeapTupleSatisfiesVisibility 1.97% postgres libc-2.23.so [.] __sigsetjmp 1.90% postgres postgres [.] _SPI_cursor_operation 1.87% postgres postgres [.] AllocSetFree 1.86% postgres postgres [.] PortalRunSelect 1.79% postgres postgres [.] heap_getnextslot 1.75% postgres postgres [.] heap_fill_tuple 1.70% postgres postgres [.] spi_dest_startup 1.50% postgres postgres [.] spi_printtup 1.49% postgres postgres [.] nocachegetattr 1.45% postgres postgres [.] MemoryContextDelete 1.44% postgres postgres [.] ExecJustAssignScanVar 1.38% postgres postgres [.] CreateTupleDescCopy 1.33% postgres postgres [.] SPI_getbinval 1.30% postgres postgres [.] PushActiveSnapshot 1.30% postgres postgres [.] AllocSetContextCreateInternal 1.22% postgres postgres [.] heap_compute_data_size 1.22% postgres postgres [.] MemoryContextCreate 1.14% postgres postgres [.] heapgetpage 1.05% postgres postgres [.] palloc 1.03% postgres postgres [.] SeqNext As you can see, most of the time is spent in allocation and copying memory. I wonder if somebody tried to address this problem and are there some plans for improving speed of PL/pgSQL and other stored languages? I attached to this mail sources of spi_test extension with my experiments. Please build it and run norm.sql file. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company |
spi_test.tgz (2K)
RE: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
On 22.08.2019 18:56, Pavel Stehule
wrote:
I wonder if the following simple patch is correct? -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company |
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
On 23.08.2019 14:42, Pavel Stehule
wrote:
What about the following version of the patch? -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company |
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
Re: Why overhead of SPI is so large?
|
|
In reply to this post by konstantin knizhnik
Hi pá 23. 8. 2019 v 16:32 odesílatel Konstantin Knizhnik <[hidden email]> napsal:
I am sending review of this small patch. This small patch reduce a overhead of usage buildin immutable functions in volatile functions with simple trick. Starts snapshot only when it is necessary. In decrease runtime time about 25 % on this small example. do $$ declare i int; begin i := 0; while i < 10000000 loop i := i + 1; end loop; end; $$; If there are more expressions, then speedup can be more interesting. If there are other bottlenecks, then the speedup will be less. 25% is not bad, so we want to this feature. I believe so similar method can be used more aggressively with more significant performance benefit, but this is low hanging fruit and isn't reason to wait for future. This patch doesn't introduce any new feature, so new tests and new doc is not necessary. The patch is readable, well formatted, only comments are too long. I fixed it. All tests passed I fixed few warnings, and I reformated little bit function expr_needs_snapshot to use if instead case, what is more usual in these cases. I think so this code can be marked as ready for commit Regards Pavel
|
| Free forum by Nabble | Edit this page |