public inbox for [email protected]
help / color / mirror / Atom feedFrom: Daniil Davydov <[email protected]>
To: Jingtang Zhang <[email protected]>
Cc: Bharath Rupireddy <[email protected]>
Cc: [email protected]
Cc: PostgreSQL-development <[email protected]>
Subject: Re: Introduce new multi insert Table AM and improve performance of various SQL commands with it for Heap AM
Date: Sun, 9 Mar 2025 18:27:58 +0700
Message-ID: <CAJDiXggcx+v7eKruvvBK-mpyf3Y3e8vgBJhcZwhkm4p6907edw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CALj2ACXdrOmB6Na9amHWZHKvRT3Z0nwTRsCwoMT-npOBtmXLXg@mail.gmail.com>
<[email protected]>
<CALj2ACX5UMWVFdrRNUE0KDrg54WV1cumBXwcETXhrPc1ibKAQA@mail.gmail.com>
<CAAWbhmj5Pio3nOUakObzLGCSS9dwFfgsNVDhwTGzXNwZc00uCQ@mail.gmail.com>
<CALj2ACVHC=c6eC9SRxhcTUrnXvNDNkEBgedi2WkVJYRb=0sWYw@mail.gmail.com>
<CALj2ACVE2h=LnFnpr3rh+6SZzdwzW5EZOYG2Z0t=p28Fn75eag@mail.gmail.com>
<CALj2ACWT0Rz8oybWBm5W4CeS0DvFkwaw-pEvGArhDLyPbZnW_g@mail.gmail.com>
<CALj2ACWxO3HPtpYZb765LZk-uKVuAvZPO1HDeZ8=mzMgVPgaww@mail.gmail.com>
<CALj2ACXJA4QQ_6zAHez0Uy-9t-ebmpox2y1QBja+mF4QP+h8WQ@mail.gmail.com>
<CAD21AoD97mhzF8cqsd2v1jg9z8xfvAJrPx6Wvi+Ev0Hmu96LJA@mail.gmail.com>
<CALj2ACUcv5pZoB0=gRrz54M9+YT9JCmo6FYyo5WUS6wnS+em=A@mail.gmail.com>
<CALj2ACWm77YofBMs9x3Zmp3ctNAhcS4TvPVuXKdfwCr22FqOHg@mail.gmail.com>
<[email protected]>
<CALj2ACWqVzhxDuWNTWAH-LuADvsyX0r-wpwgeJ+Q1FnAKjY5Yw@mail.gmail.com>
<[email protected]>
<CALj2ACU70HZm+0QRJdkGA5RdJUo4zPYnV2hzkiV-wH5QS2PAEQ@mail.gmail.com>
<[email protected]>
<CALj2ACVMV=gMROte2=0LBFnSCRvzL4D9WK6oQ9ZHr4Qj2S8xWA@mail.gmail.com>
<[email protected]>
<CALj2ACX9vVYHYkX8e6w058EuAs8JL5EsnzadTxGhpiE_Ep_ByA@mail.gmail.com>
<[email protected]>
<CALj2ACWTrx1zxWvq8Uj2rEwCsDgQHeJ53WdvzZUw3kW+_VPG6A@mail.gmail.com>
<CALj2ACUz5+_YNEa4ZY-XG960_oXefM50MjD71VgSCAVDkF3bzQ@mail.gmail.com>
<[email protected]>
<CALj2ACX90L5Mb5Vv=jsvhOdZ8BVsfpZf-CdCGhtm2N+bGUCSjg@mail.gmail.com>
<CALj2ACXnCvcYvaz4aDH_ezUBJanz_Boi8W=76fOwrxiwSnUFOQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<CALj2ACUBwVu_1LOWF07Acv+dWx5NO7uTqAPMNwqi5WpJRw69_Q@mail.gmail.com>
<[email protected]>
<CALj2ACWUUTFrTF0L_xdj64i9xsjf_+4nt_HPEBiM17cxEk4CWg@mail.gmail.com>
<CAPsk3_AuOsLcOrgTX4-QDP0Qv+AHBuuH3fTK6MwVacH6gvR1Nw@mail.gmail.com>
<CALj2ACUVE8CYvYrudem4bR7W3sNRE-akC4B996K65_7C6xTBJQ@mail.gmail.com>
<[email protected]>
Hi,
A few days ago I came up with an idea to implement multi insert
optimization wherever possible. I prepared a raw patch
and it showed a great performance gain (up to 4 times during INSERT
... INTO ... in the best case).
Then I was very happy to find this thread. You did a great job and I
want to help you to bring the matter to an end.
On Thu, Oct 31, 2024 at 11:17 AM Jingtang Zhang <[email protected]> wrote:
> I did some performance test these days, and I have some findings.
> HEAD:
> 12.29% postgres [.] pg_checksum_block
> 6.33% postgres [.] GetPrivateRefCountEntry
> 5.40% postgres [.] pg_comp_crc32c_sse42
> 4.54% [kernel] [k] copy_user_enhanced_fast_string
> 2.69% postgres [.] BufferIsValid
> 1.52% postgres [.] XLogRecordAssemble
>
> Patched:
> 11.75% postgres [.] tts_virtual_materialize
> 8.87% postgres [.] pg_checksum_block
> 8.17% postgres [.] slot_deform_heap_tuple
> 8.09% postgres [.] heap_compute_data_size
> 6.17% postgres [.] fill_val
> 3.81% postgres [.] heap_fill_tuple
> 3.37% postgres [.] tts_virtual_copyslot
> 2.62% [kernel] [k] copy_user_enhanced_fast_string
I applied v25 patches on the master branch and made some measurements
to find out what is the bottleneck in this case. The 'time' utility
showed that without a patch, this query will run 1.5 times slower. I
also made a few flamegraphs for this test. Most of the time is spent
calling
these two functions : tts_virtual_copyslot and heap_form_tuple.
All tests were run in virtual machine with these CPU characteristics:
Architecture: x86_64
CPU(s): 2
On-line CPU(s) list: 0,1
Virtualization features:
Virtualization: AMD-V
Hypervisor vendor: KVM
Virtualization type: full
Caches (sum of all):
L1d: 128 KiB (2 instances)
L1i: 128 KiB (2 instances)
L2: 1 MiB (2 instances)
L3: 32 MiB (2 instances)
NUMA:
NUMA node(s): 1
NUMA node0 CPU(s): 0,1
In my implementation, I used Tuplestore functionality to store tuples.
In order to get rid of getting stuck in the above mentioned functions,
I crossed it with the current implementation (v25 patches) and got a
10% increase in performance (for the test above). I also set up v22
patches to
compare performance (with/without tuplestore) for INSERT ... INTO ...
queries (with -j 4 -c 10 parameters for pgbech), and there also was an
increase in TPS (about 3-4%).
I attach a patch that adds Tuplestore to v25. What do you think about this idea?
--
Best regards,
Daniil Davydov
Attachments:
[text/x-patch] 0001-Replace-holding-tuples-in-virtual-slots-with-tuplest.patch (5.9K, 2-0001-Replace-holding-tuples-in-virtual-slots-with-tuplest.patch)
download | inline diff:
From a59cfcbb05bb07c94a4c0ad6531baa5e531629ae Mon Sep 17 00:00:00 2001
From: Daniil Davidov <[email protected]>
Date: Sun, 9 Mar 2025 16:37:44 +0700
Subject: [PATCH] Replace holding tuples in virtual slots with tuplestorage
During performance testing, it was found out that in the current
implementation a lot of the program's time is spent calling two functions :
tts_virtual_copyslot and heap_fill_tuple. Calls to these functions are related
to the fact that tuples are stored in virtual_tts, so I propose to replace this
logic with Tuplestore functionality.
Discussion: https://www.postgresql.org/message-id/9F9326B4-8AD9-4858-B1C1-559FC64E6E93%40gmail.com
---
src/backend/access/heap/heapam.c | 67 +++++++++++++++-----------------
src/include/access/heapam.h | 9 ++++-
2 files changed, 38 insertions(+), 38 deletions(-)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index acdce1a4b4..276480213a 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -2665,7 +2665,6 @@ void
heap_modify_buffer_insert(TableModifyState *state,
TupleTableSlot *slot)
{
- TupleTableSlot *dstslot;
HeapInsertState *istate;
HeapMultiInsertState *mistate;
MemoryContext oldcontext;
@@ -2682,8 +2681,10 @@ heap_modify_buffer_insert(TableModifyState *state,
mistate =
(HeapMultiInsertState *) palloc(sizeof(HeapMultiInsertState));
mistate->slots =
- (TupleTableSlot **) palloc0(sizeof(TupleTableSlot *) * HEAP_MAX_BUFFERED_SLOTS);
- mistate->cur_slots = 0;
+ (TupleTableSlot **) palloc0(sizeof(void *) * HEAP_MAX_BUFFERED_SLOTS);
+ mistate->tstore = tuplestore_begin_heap(false, false, work_mem);
+ mistate->nused = 0;
+
istate->mistate = mistate;
/*
@@ -2702,36 +2703,11 @@ heap_modify_buffer_insert(TableModifyState *state,
istate = (HeapInsertState *) state->data;
Assert(istate->mistate != NULL);
mistate = istate->mistate;
- dstslot = mistate->slots[mistate->cur_slots];
-
- if (dstslot == NULL)
- {
- /*
- * We use virtual tuple slots buffered slots for leveraging the
- * optimization it provides to minimize physical data copying. The
- * virtual slot gets materialized when we copy (via below
- * ExecCopySlot) the tuples from the source slot which can be of any
- * type. This way, it is ensured that the tuple storage doesn't depend
- * on external memory, because all the datums that aren't passed by
- * value are copied into the slot's memory context.
- */
- dstslot = MakeTupleTableSlot(RelationGetDescr(state->rel),
- &TTSOpsVirtual);
-
- mistate->slots[mistate->cur_slots] = dstslot;
- }
-
- Assert(TTS_IS_VIRTUAL(dstslot));
-
- /*
- * Note that the copy clears the previous destination slot contents, so no
- * need to explicitly ExecClearTuple() here.
- */
- ExecCopySlot(dstslot, slot);
- mistate->cur_slots++;
+ tuplestore_puttupleslot(mistate->tstore, slot);
+ mistate->nused += 1;
- if (mistate->cur_slots >= HEAP_MAX_BUFFERED_SLOTS)
+ if (mistate->nused >= HEAP_MAX_BUFFERED_SLOTS)
heap_modify_buffer_flush(state);
MemoryContextSwitchTo(oldcontext);
@@ -2746,19 +2722,35 @@ heap_modify_buffer_flush(TableModifyState *state)
HeapInsertState *istate;
HeapMultiInsertState *mistate;
MemoryContext oldcontext;
+ TupleDesc tupdesc;
/* Quick exit if we haven't inserted anything yet */
if (state->data == NULL)
return;
+ tupdesc = RelationGetDescr(state->rel);
istate = (HeapInsertState *) state->data;
Assert(istate->mistate != NULL);
mistate = istate->mistate;
/* Quick exit if we have flushed already */
- if (mistate->cur_slots == 0)
+ if (mistate->nused == 0)
return;
+ for (int i = 0; i < mistate->nused; i++)
+ {
+ bool ok;
+
+ if (istate->mistate->slots[i] == NULL)
+ {
+ istate->mistate->slots[i] =
+ MakeSingleTupleTableSlot(tupdesc, &TTSOpsMinimalTuple);
+ }
+ ok = tuplestore_gettupleslot(mistate->tstore, true, false,
+ istate->mistate->slots[i]);
+ Assert(ok);
+ }
+
/*
* heap_multi_insert() can leak memory, so switch to short-lived memory
* context before calling it.
@@ -2766,7 +2758,7 @@ heap_modify_buffer_flush(TableModifyState *state)
oldcontext = MemoryContextSwitchTo(mistate->mem_ctx);
heap_multi_insert(state->rel,
mistate->slots,
- mistate->cur_slots,
+ mistate->nused,
state->cid,
state->options,
istate->bistate);
@@ -2779,14 +2771,15 @@ heap_modify_buffer_flush(TableModifyState *state)
*/
if (state->buffer_flush_cb != NULL)
{
- for (int i = 0; i < mistate->cur_slots; i++)
+ for (int i = 0; i < mistate->nused; i++)
{
state->buffer_flush_cb(state->buffer_flush_ctx,
mistate->slots[i]);
}
}
- mistate->cur_slots = 0;
+ tuplestore_clear(mistate->tstore);
+ mistate->nused = 0;
}
/*
@@ -2811,11 +2804,13 @@ heap_modify_insert_end(TableModifyState *state)
heap_modify_buffer_flush(state);
- Assert(mistate->cur_slots == 0);
+ Assert(mistate->nused== 0);
for (int i = 0; i < HEAP_MAX_BUFFERED_SLOTS && mistate->slots[i] != NULL; i++)
ExecDropSingleTupleTableSlot(mistate->slots[i]);
+ tuplestore_end(mistate->tstore);
+
MemoryContextDelete(mistate->mem_ctx);
}
diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h
index fdbbf9b8e8..5d8e672059 100644
--- a/src/include/access/heapam.h
+++ b/src/include/access/heapam.h
@@ -27,8 +27,10 @@
#include "storage/lockdefs.h"
#include "storage/read_stream.h"
#include "storage/shm_toc.h"
+#include "tcop/dest.h"
#include "utils/relcache.h"
#include "utils/snapshot.h"
+#include "utils/tuplestore.h"
/* "options" flag bits for heap_insert */
@@ -285,8 +287,11 @@ typedef struct HeapMultiInsertState
/* Array of buffered slots */
TupleTableSlot **slots;
- /* Number of buffered slots currently held */
- int cur_slots;
+ /* Holds the tuple set */
+ Tuplestorestate *tstore;
+
+ /* Number of buffered tuples currently held */
+ int nused;
/* Memory context for dealing with multi inserts */
MemoryContext mem_ctx;
--
2.43.0
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Introduce new multi insert Table AM and improve performance of various SQL commands with it for Heap AM
In-Reply-To: <CAJDiXggcx+v7eKruvvBK-mpyf3Y3e8vgBJhcZwhkm4p6907edw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox