Optimize commit performance with a large number of 'on commit delete rows' temp tables
Hi hackers,
# Background
PostgreSQL maintains a list of temporary tables for 'on commit
drop/delete rows' via an on_commits list in the session. Once a
transaction accesses a temp table or namespace, the
XACT_FLAGS_ACCESSEDTEMPNAMESPACE flag is set. Before committing, the
PreCommit_on_commit_actions function truncates all 'commit delete
rows' temp tables, even those not accessed in the current transaction.
Commit performance can degrade if there are many such temp tables.
In practice, users created many 'commit delete rows' temp tables in a
session, but each transaction only accessed a few. With varied access
frequency, users were reluctant to change to 'on commit drop'.
Below is an example showing the effect of the number of temp tables
on commit performance:
```
-- 100
DO $$
DECLARE
begin
FOR i IN 1..100 LOOP
EXECUTE format('CREATE TEMP TABLE temp_table_%s (id int) on commit delete ROWS', i) ;
END LOOP;
END;
$$;
postgres=# insert into temp_table_1 select 1;
INSERT 0 1
Time: 1.325 ms
postgres=# insert into temp_table_1 select 1;
INSERT 0 1
Time: 1.330 ms
```
```
-- 1000
DO $$
DECLARE
begin
FOR i IN 1..1000 LOOP
EXECUTE format('CREATE TEMP TABLE temp_table_%s (id int) on commit delete ROWS', i) ;
END LOOP;
END;
$$;
postgres=# insert into temp_table_1 select 1;
INSERT 0 1
Time: 10.939 ms
postgres=# insert into temp_table_1 select 1;
INSERT 0 1
Time: 10.955 ms
```
```
-- 10000
DO $$
DECLARE
begin
FOR i IN 1..10000 LOOP
EXECUTE format('CREATE TEMP TABLE temp_table_%s (id int) on commit delete ROWS', i) ;
END LOOP;
END;
$$;
postgres=# insert into temp_table_1 select 1;
INSERT 0 1
Time: 110.253 ms
postgres=# insert into temp_table_1 select 1;
INSERT 0 1
Time: 175.875 ms
```
# Solution
An intuitive solution is to truncate only the temp tables that
the current process has accessed upon transaction commit.
In the attached patch (based on HEAD):
- A Bloom filter (can also be a list or hash table) maintains
the temp tables accessed by the current transaction.
- Only temp tables filtered through the Bloom filter need
truncation. False positives may occur, but they are
acceptable.
- The Bloom filter is reset at the start of the transaction,
indicating no temp tables have been accessed by the
current transaction yet.
After optimization, the performance for the same case is as
follows:
```
-- 100
DO $$
DECLARE
begin
FOR i IN 1..100 LOOP
EXECUTE format('CREATE TEMP TABLE temp_table_%s (id int) on commit delete ROWS', i) ;
END LOOP;
END;
$$;
postgres=# insert into temp_table_1 select 1;
INSERT 0 1
Time: 0.447 ms
postgres=# insert into temp_table_1 select 1;
INSERT 0 1
Time: 0.453 ms
```
```
-- 1000
DO $$
DECLARE
begin
FOR i IN 1..1000 LOOP
EXECUTE format('CREATE TEMP TABLE temp_table_%s (id int) on commit delete ROWS', i) ;
END LOOP;
END;
$$;
postgres=# insert into temp_table_1 select 1;
INSERT 0 1
Time: 0.531 ms
postgres=# insert into temp_table_1 select 1;
INSERT 0 1
Time: 0.567 ms
```
```
-- 10000
DO $$
DECLARE
begin
FOR i IN 1..10000 LOOP
EXECUTE format('CREATE TEMP TABLE temp_table_%s (id int) on commit delete ROWS', i) ;
END LOOP;
END;
$$;
postgres=# insert into temp_table_1 select 1;
INSERT 0 1
Time: 1.370 ms
postgres=# insert into temp_table_1 select 1;
INSERT 0 1
Time: 1.362 ms
```
Hoping for some suggestions from hackers.
Best Regards,
Fei Changhong
Attachments:
v1-0000-Optimize-commit-with-temp-tables.patchapplication/octet-stream; charset=ISO-8859-1; name=v1-0000-Optimize-commit-with-temp-tables.patchDownload
diff --git a/src/backend/access/common/relation.c b/src/backend/access/common/relation.c
index d8a313a2c9..74b1415ad5 100644
--- a/src/backend/access/common/relation.c
+++ b/src/backend/access/common/relation.c
@@ -70,7 +70,10 @@ relation_open(Oid relationId, LOCKMODE lockmode)
/* Make note that we've accessed a temporary relation */
if (RelationUsesLocalBuffers(r))
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relationId);
+ }
pgstat_init_relation(r);
@@ -120,7 +123,10 @@ try_relation_open(Oid relationId, LOCKMODE lockmode)
/* Make note that we've accessed a temporary relation */
if (RelationUsesLocalBuffers(r))
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relationId);
+ }
pgstat_init_relation(r);
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index d119ab909d..7f6c6b67cf 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -2122,6 +2122,9 @@ StartTransaction(void)
forceSyncCommit = false;
MyXactFlags = 0;
+ /* When starting a new transaction, reset the oncommit Bloom filter. */
+ on_commits_filter_reset();
+
/*
* reinitialize within-transaction counters
*/
diff --git a/src/backend/commands/lockcmds.c b/src/backend/commands/lockcmds.c
index cd20ae76ba..77ad85576d 100644
--- a/src/backend/commands/lockcmds.c
+++ b/src/backend/commands/lockcmds.c
@@ -98,7 +98,10 @@ RangeVarCallbackForLockTable(const RangeVar *rv, Oid relid, Oid oldrelid,
*/
relpersistence = get_rel_persistence(relid);
if (relpersistence == RELPERSISTENCE_TEMP)
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relid);
+ }
/* Check permissions. */
aclresult = LockTableAclCheck(relid, lockmode, GetUserId());
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index dbfe0d6b1c..662d163976 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -68,6 +68,7 @@
#include "executor/executor.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
+#include "lib/bloomfilter.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -128,6 +129,14 @@ typedef struct OnCommitItem
static List *on_commits = NIL;
+/*
+ * Filter out the temporary tables accessed in the current transaction.
+ * For a Bloom filter with 128 items and a size of 1KB, the false positive
+ * rate is far less than 1%.
+ */
+#define ON_COMMITS_FILTER_ITEMS 128
+#define ON_COMMITS_FILTER_BYTES (1 * 1024)
+static bloom_filter* on_commits_filter = NULL;
/*
* State information for ALTER TABLE
@@ -17429,7 +17438,23 @@ PreCommit_on_commit_actions(void)
* tables, as they must still be empty.
*/
if ((MyXactFlags & XACT_FLAGS_ACCESSEDTEMPNAMESPACE))
- oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+ {
+ if (on_commits_filter_contains(oc->relid))
+ oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+#ifdef USE_ASSERT_CHECKING
+ else
+ {
+ /*
+ * All temporary tables not in the filter should either
+ * have no physical files or a file size of zero.
+ */
+ Relation rel;
+ rel = table_open(oc->relid, AccessShareLock);
+ Assert(!rel->rd_smgr || !smgrexists(rel->rd_smgr, MAIN_FORKNUM) || smgrnblocks(rel->rd_smgr, MAIN_FORKNUM) == 0);
+ table_close(rel, AccessShareLock);
+ }
+#endif
+ }
break;
case ONCOMMIT_DROP:
oids_to_drop = lappend_oid(oids_to_drop, oc->relid);
@@ -20766,3 +20791,56 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Keep the lock until commit. */
table_close(newPartRel, NoLock);
}
+
+/*
+ * on_commit_filter_init: Allocate memory in CacheMemoryContext to initialize
+ * the oncommit filter. It is called during process initialization.
+ */
+void on_commits_filter_init(void)
+{
+ MemoryContext oldcxt;
+
+ Assert(on_commits_filter == NULL);
+
+ oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
+ on_commits_filter =
+ bloom_create_ex(ON_COMMITS_FILTER_ITEMS, work_mem, 0, ON_COMMITS_FILTER_BYTES);
+
+ MemoryContextSwitchTo(oldcxt);
+}
+
+/*
+ * on_commits_filter_reset: Reset the Bloom filter without reallocating memory,
+ * simply by resetting the bitset. It is called when starting a new transaction,
+ * that is, when the XACT_FLAGS_ACCESSEDTEMPNAMESPACE flag is cleared.
+ */
+void on_commits_filter_reset(void)
+{
+ Assert(on_commits_filter != NULL);
+
+ bloom_clear(on_commits_filter);
+}
+
+/*
+ * on_commits_filter_add: Add the temporary table OId to the Bloom filter,
+ * indicating that the current transaction has accessed this temporary table,
+ * which needs to be truncated upon commit. It is called when the temporary
+ * table is opened.
+ */
+void on_commits_filter_add(Oid relid)
+{
+ Assert(on_commits_filter != NULL);
+
+ bloom_add_element(on_commits_filter, (unsigned char *) &relid, sizeof(relid));
+}
+
+/*
+ * on_commits_filter_contains: Determine whether the specified temporary table
+ * has been accessed in the current transaction.
+ */
+bool on_commits_filter_contains(Oid relid)
+{
+ Assert(on_commits_filter != NULL);
+
+ return !bloom_lacks_element(on_commits_filter, (unsigned char *) &relid, sizeof(relid));
+}
diff --git a/src/backend/lib/bloomfilter.c b/src/backend/lib/bloomfilter.c
index 360d21ca45..3650255782 100644
--- a/src/backend/lib/bloomfilter.c
+++ b/src/backend/lib/bloomfilter.c
@@ -40,6 +40,7 @@
#include "port/pg_bitutils.h"
#define MAX_HASH_FUNCS 10
+#define MIN_BITSET_BYTES (1024 * 1024)
struct bloom_filter
{
@@ -85,6 +86,13 @@ static inline uint32 mod_m(uint32 val, uint64 m);
*/
bloom_filter *
bloom_create(int64 total_elems, int bloom_work_mem, uint64 seed)
+{
+ return bloom_create_ex(total_elems, bloom_work_mem, seed, MIN_BITSET_BYTES);
+}
+
+bloom_filter *
+bloom_create_ex(int64 total_elems, int bloom_work_mem, uint64 seed,
+ uint64 min_bitset_bytes)
{
bloom_filter *filter;
int bloom_power;
@@ -292,3 +300,8 @@ mod_m(uint32 val, uint64 m)
return val & (m - 1);
}
+
+void bloom_clear(bloom_filter *filter)
+{
+ memset(filter->bitset, 0, filter->m / BITS_PER_BYTE);
+}
\ No newline at end of file
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 0805398e24..ccdd9e7af8 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -33,6 +33,7 @@
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
#include "catalog/pg_tablespace.h"
+#include "commands/tablecmds.h"
#include "libpq/auth.h"
#include "libpq/libpq-be.h"
#include "mb/pg_wchar.h"
@@ -818,6 +819,9 @@ InitPostgres(const char *in_dbname, Oid dboid,
InitCatalogCache();
InitPlanCache();
+ /* Initialize on_commits filter */
+ on_commits_filter_init();
+
/* Initialize portal manager */
EnablePortalManager();
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index 85cbad3d0c..29fb74d6ab 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -107,4 +107,9 @@ extern void RangeVarCallbackOwnsRelation(const RangeVar *relation,
extern bool PartConstraintImpliedByRelConstraint(Relation scanrel,
List *partConstraint);
+extern void on_commits_filter_init(void);
+extern void on_commits_filter_reset(void);
+extern void on_commits_filter_add(Oid relid);
+extern bool on_commits_filter_contains(Oid relid);
+
#endif /* TABLECMDS_H */
diff --git a/src/include/lib/bloomfilter.h b/src/include/lib/bloomfilter.h
index 6ec7173843..13a0670e92 100644
--- a/src/include/lib/bloomfilter.h
+++ b/src/include/lib/bloomfilter.h
@@ -17,11 +17,14 @@ typedef struct bloom_filter bloom_filter;
extern bloom_filter *bloom_create(int64 total_elems, int bloom_work_mem,
uint64 seed);
+extern bloom_filter *bloom_create_ex(int64 total_elems, int bloom_work_mem,
+ uint64 seed, uint64 min_bitset_bytes);
extern void bloom_free(bloom_filter *filter);
extern void bloom_add_element(bloom_filter *filter, unsigned char *elem,
size_t len);
extern bool bloom_lacks_element(bloom_filter *filter, unsigned char *elem,
size_t len);
extern double bloom_prop_bits_set(bloom_filter *filter);
+extern void bloom_clear(bloom_filter *filter);
#endif /* BLOOMFILTER_H */
"=?ISO-8859-1?B?ZmVpY2hhbmdob25n?=" <feichanghong@qq.com> writes:
PostgreSQL maintains a list of temporary tables for 'on commit
drop/delete rows' via an on_commits list in the session. Once a
transaction accesses a temp table or namespace, the
XACT_FLAGS_ACCESSEDTEMPNAMESPACE flag is set. Before committing, the
PreCommit_on_commit_actions function truncates all 'commit delete
rows' temp tables, even those not accessed in the current transaction.
Commit performance can degrade if there are many such temp tables.
Hmm. I can sympathize with wanting to improve the performance of
this edge case, but it is an edge case: you are the first to
complain about it. You cannot trash the performance of more typical
cases in order to get there ...
In the attached patch (based on HEAD):
- A Bloom filter (can also be a list or hash table) maintains
the temp tables accessed by the current transaction.
... and I'm afraid this proposal may do exactly that. Our bloom
filters are pretty heavyweight objects, so making one in situations
where it buys nothing is likely to add a decent amount of overhead.
(I've not tried to quantify that for this particular patch.)
I wonder if we could instead add marker fields to the OnCommitItem
structs indicating whether their rels were touched in the current
transaction, and use those to decide whether we need to truncate.
Another possibility is to make the bloom filter only when the
number of OnCommitItems exceeds some threshold (compare d365ae705).
BTW, I wonder if we could improve PreCommit_on_commit_actions by
having it just quit immediately if XACT_FLAGS_ACCESSEDTEMPNAMESPACE
is not set. I think that must be set if any ON COMMIT DROP tables
have been made, so there should be nothing to do if not. In normal
cases that's not going to buy much because the OnCommitItems list
is short, but in your scenario maybe it could win.
regards, tom lane
Thank you for your attention and suggestions.
On Jul 6, 2024, at 00:15, Tom Lane <tgl@sss.pgh.pa.us> wrote:
<feichanghong@qq.com> writes:
PostgreSQL maintains a list of temporary tables for 'on commit
drop/delete rows' via an on_commits list in the session. Once a
transaction accesses a temp table or namespace, the
XACT_FLAGS_ACCESSEDTEMPNAMESPACE flag is set. Before committing, the
PreCommit_on_commit_actions function truncates all 'commit delete
rows' temp tables, even those not accessed in the current transaction.
Commit performance can degrade if there are many such temp tables.Hmm. I can sympathize with wanting to improve the performance of
this edge case, but it is an edge case: you are the first to
complain about it. You cannot trash the performance of more typical
cases in order to get there ...In the attached patch (based on HEAD):
- A Bloom filter (can also be a list or hash table) maintains
the temp tables accessed by the current transaction.... and I'm afraid this proposal may do exactly that. Our bloom
filters are pretty heavyweight objects, so making one in situations
where it buys nothing is likely to add a decent amount of overhead.
(I've not tried to quantify that for this particular patch.)
Yes, this is an edge case, but we have more than one customer facing the issue,
and unfortunately, they are not willing to modify their service code.
We should indeed avoid negatively impacting typical cases:
- Each connection requires an extra 1KB for the filter (the original bloom filter
implementation had a minimum of 1MB, which I've adjusted to this smaller value).
- The filter is reset at the start of each transaction, which is unnecessary for
sessions that do not access temporary tables.
- In the PreCommit_on_commit_actions function, each 'on commit delete rows'
temporary table has to be filtered through the bloom filter, which incurs some
CPU overhead. However, this might be negligible compared to the IO cost of
truncation.
Adding a threshold for using the bloom filter is a good idea. We can create the
bloom filter only when the current number of OnCommitItems exceeds the threshold
at the start of a transaction, which should effectively avoid affecting typical
cases. I will provide a new patch later to implement this.
I wonder if we could instead add marker fields to the OnCommitItem
structs indicating whether their rels were touched in the current
transaction, and use those to decide whether we need to truncate.
Adding a flag to OnCommitItem to indicate whether the temp table was accessed
by the current transaction is feasible. But, locating the OnCommitItem by relid
efficiently when opening a relation may require an extra hash table to map relids
to OnCommitItems.
Another possibility is to make the bloom filter only when the
number of OnCommitItems exceeds some threshold (compare d365ae705).BTW, I wonder if we could improve PreCommit_on_commit_actions by
having it just quit immediately if XACT_FLAGS_ACCESSEDTEMPNAMESPACE
is not set. I think that must be set if any ON COMMIT DROP tables
have been made, so there should be nothing to do if not. In normal
cases that's not going to buy much because the OnCommitItems list
is short, but in your scenario maybe it could win.
I also think when XACT_FLAGS_ACCESSEDTEMPNAMESPACE is not set, it's unnecessary
to iterate over on_commits (unless I'm overlooking something), which would be
beneficial for the aforementioned scenarios as well.
Best Regards,
Fei Changhong
The patch in the attachment, compared to the previous one, adds a threshold for
using the bloom filter. The current ON_COMMITS_FILTER_THRESHOLD is set to 64,
which may not be the optimal value. Perhaps this threshold could be configured
as a GUC parameter?
Best Regards,
Fei Changhong
Attachments:
v1-0001-Optimize-commit-with-temp-tables.patchapplication/octet-stream; charset=ISO-8859-1; name=v1-0001-Optimize-commit-with-temp-tables.patchDownload
diff --git a/src/backend/access/common/relation.c b/src/backend/access/common/relation.c
index d8a313a2c9..74b1415ad5 100644
--- a/src/backend/access/common/relation.c
+++ b/src/backend/access/common/relation.c
@@ -70,7 +70,10 @@ relation_open(Oid relationId, LOCKMODE lockmode)
/* Make note that we've accessed a temporary relation */
if (RelationUsesLocalBuffers(r))
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relationId);
+ }
pgstat_init_relation(r);
@@ -120,7 +123,10 @@ try_relation_open(Oid relationId, LOCKMODE lockmode)
/* Make note that we've accessed a temporary relation */
if (RelationUsesLocalBuffers(r))
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relationId);
+ }
pgstat_init_relation(r);
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index d119ab909d..dc7bde98ab 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -2122,6 +2122,9 @@ StartTransaction(void)
forceSyncCommit = false;
MyXactFlags = 0;
+ /* When starting a new transaction, init or reset the oncommit filter. */
+ on_commits_filter_init_or_reset();
+
/*
* reinitialize within-transaction counters
*/
diff --git a/src/backend/commands/lockcmds.c b/src/backend/commands/lockcmds.c
index cd20ae76ba..77ad85576d 100644
--- a/src/backend/commands/lockcmds.c
+++ b/src/backend/commands/lockcmds.c
@@ -98,7 +98,10 @@ RangeVarCallbackForLockTable(const RangeVar *rv, Oid relid, Oid oldrelid,
*/
relpersistence = get_rel_persistence(relid);
if (relpersistence == RELPERSISTENCE_TEMP)
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relid);
+ }
/* Check permissions. */
aclresult = LockTableAclCheck(relid, lockmode, GetUserId());
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index dbfe0d6b1c..09035653e1 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -68,6 +68,7 @@
#include "executor/executor.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
+#include "lib/bloomfilter.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -128,6 +129,14 @@ typedef struct OnCommitItem
static List *on_commits = NIL;
+/*
+ * Filter out the temporary tables accessed in the current transaction.
+ * For a Bloom filter with 64*8 items and a size of 1KB, the false positive
+ * rate is far less than 1%.
+ */
+#define ON_COMMITS_FILTER_THRESHOLD 64
+#define ON_COMMITS_FILTER_BYTES (1 * 1024)
+static bloom_filter* on_commits_filter = NULL;
/*
* State information for ALTER TABLE
@@ -17429,7 +17438,23 @@ PreCommit_on_commit_actions(void)
* tables, as they must still be empty.
*/
if ((MyXactFlags & XACT_FLAGS_ACCESSEDTEMPNAMESPACE))
- oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+ {
+ if (on_commits_filter_contains(oc->relid))
+ oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+#ifdef USE_ASSERT_CHECKING
+ else
+ {
+ /*
+ * All temporary tables not in the filter should either
+ * have no physical files or a file size of zero.
+ */
+ Relation rel;
+ rel = table_open(oc->relid, AccessShareLock);
+ Assert(!rel->rd_smgr || !smgrexists(rel->rd_smgr, MAIN_FORKNUM) || smgrnblocks(rel->rd_smgr, MAIN_FORKNUM) == 0);
+ table_close(rel, AccessShareLock);
+ }
+#endif
+ }
break;
case ONCOMMIT_DROP:
oids_to_drop = lappend_oid(oids_to_drop, oc->relid);
@@ -20766,3 +20791,54 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Keep the lock until commit. */
table_close(newPartRel, NoLock);
}
+
+/*
+ * on_commits_filter_init_or_reset: Initialize or reset the bloom filter,
+ * ensuring it is only initialized when the number of on_commits exceeds the
+ * ON_COMMITS_FILTER_THRESHOLD. It is called when starting a new transaction,
+ * that is, when the XACT_FLAGS_ACCESSEDTEMPNAMESPACE flag is cleared.
+ */
+void on_commits_filter_init_or_reset(void)
+{
+ if (on_commits_filter != NULL)
+ bloom_clear(on_commits_filter);
+ else if (list_length(on_commits) > ON_COMMITS_FILTER_THRESHOLD)
+ {
+ MemoryContext oldcxt;
+
+ oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
+ on_commits_filter =
+ bloom_create_ex(ON_COMMITS_FILTER_THRESHOLD*8, work_mem, 0, ON_COMMITS_FILTER_BYTES);
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+}
+
+/*
+ * on_commits_filter_add: Add the temporary table OId to the Bloom filter,
+ * indicating that the current transaction has accessed this temporary table,
+ * which needs to be truncated upon commit. It is called when the temporary
+ * table is opened.
+ */
+void on_commits_filter_add(Oid relid)
+{
+ if (on_commits_filter != NULL)
+ bloom_add_element(on_commits_filter, (unsigned char *) &relid, sizeof(relid));
+}
+
+/*
+ * on_commits_filter_contains: Determine whether the specified temporary table
+ * has been accessed in the current transaction.
+ */
+bool on_commits_filter_contains(Oid relid)
+{
+ /*
+ * If the on_commits filter is not initialized, assuming that the current
+ * transaction has accessed the temp table defaultly, keeping the logic
+ * consistent with when there is no filter.
+ */
+ if (on_commits_filter == NULL)
+ return true;
+
+ return !bloom_lacks_element(on_commits_filter, (unsigned char *) &relid, sizeof(relid));
+}
diff --git a/src/backend/lib/bloomfilter.c b/src/backend/lib/bloomfilter.c
index 360d21ca45..3650255782 100644
--- a/src/backend/lib/bloomfilter.c
+++ b/src/backend/lib/bloomfilter.c
@@ -40,6 +40,7 @@
#include "port/pg_bitutils.h"
#define MAX_HASH_FUNCS 10
+#define MIN_BITSET_BYTES (1024 * 1024)
struct bloom_filter
{
@@ -85,6 +86,13 @@ static inline uint32 mod_m(uint32 val, uint64 m);
*/
bloom_filter *
bloom_create(int64 total_elems, int bloom_work_mem, uint64 seed)
+{
+ return bloom_create_ex(total_elems, bloom_work_mem, seed, MIN_BITSET_BYTES);
+}
+
+bloom_filter *
+bloom_create_ex(int64 total_elems, int bloom_work_mem, uint64 seed,
+ uint64 min_bitset_bytes)
{
bloom_filter *filter;
int bloom_power;
@@ -292,3 +300,8 @@ mod_m(uint32 val, uint64 m)
return val & (m - 1);
}
+
+void bloom_clear(bloom_filter *filter)
+{
+ memset(filter->bitset, 0, filter->m / BITS_PER_BYTE);
+}
\ No newline at end of file
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index 85cbad3d0c..c5744e4f7c 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -107,4 +107,8 @@ extern void RangeVarCallbackOwnsRelation(const RangeVar *relation,
extern bool PartConstraintImpliedByRelConstraint(Relation scanrel,
List *partConstraint);
+extern void on_commits_filter_reset(void);
+extern void on_commits_filter_add(Oid relid);
+extern bool on_commits_filter_contains(Oid relid);
+
#endif /* TABLECMDS_H */
diff --git a/src/include/lib/bloomfilter.h b/src/include/lib/bloomfilter.h
index 6ec7173843..13a0670e92 100644
--- a/src/include/lib/bloomfilter.h
+++ b/src/include/lib/bloomfilter.h
@@ -17,11 +17,14 @@ typedef struct bloom_filter bloom_filter;
extern bloom_filter *bloom_create(int64 total_elems, int bloom_work_mem,
uint64 seed);
+extern bloom_filter *bloom_create_ex(int64 total_elems, int bloom_work_mem,
+ uint64 seed, uint64 min_bitset_bytes);
extern void bloom_free(bloom_filter *filter);
extern void bloom_add_element(bloom_filter *filter, unsigned char *elem,
size_t len);
extern bool bloom_lacks_element(bloom_filter *filter, unsigned char *elem,
size_t len);
extern double bloom_prop_bits_set(bloom_filter *filter);
+extern void bloom_clear(bloom_filter *filter);
#endif /* BLOOMFILTER_H */
Hi feichanghong
Thanks for updating the patch ,I think could be configured as a GUC
parameter,PostgreSQL has too many static variables that are written to
death and explicitly stated in the code comments may later be designed as
parameters. Now that more and more applications that previously used oracle
are migrating to postgresql, there will be more and more scenarios where
temporary tables are heavily used.Because oracle will global temporary
tablespace optimised for this business scenario, which works well in
oracle, migrating to pg faces very tricky performance issues,I'm sure the
patch has vaule
Best Regards
feichanghong <feichanghong@qq.com> 于2024年7月6日周六 03:40写道：
Show quoted text
The patch in the attachment, compared to the previous one, adds a
threshold for
using the bloom filter. The current ON_COMMITS_FILTER_THRESHOLD is set to
64,
which may not be the optimal value. Perhaps this threshold could be
configured
as a GUC parameter?
------------------------------
Best Regards,
Fei Changhong
Hi wenhui,
Thank you for your suggestions. I have supplemented some performance tests.
Here is the TPS performance data for different numbers of temporary tables
under different thresholds, as compared with the head (98347b5a). The testing
tool used is pgbench, with the workload being to insert into one temporary
table (when the number of temporary tables is 0, the workload is SELECT 1):
| table num | 0 | 1 | 5 | 10 | 100 | 1000 |
|---------------|--------------|--------------|-------------|-------------|-------------|-------------|
| head 98347b5a | 39912.722209 | 10064.306268 | 7452.071689 | 5641.487369 | 1073.203851 | 114.530958 |
| threshold 1 | 40332.367414 | 7078.117192 | 7044.951156 | 7020.249434 | 6893.652062 | 5826.597260 |
| threshold 5 | 40173.562744 | 10017.532933 | 7023.770203 | 7024.283577 | 6919.769315 | 5806.314494 |
Here is the TPS performance data for different numbers of temporary tables
at a threshold of 5, compared with the head (commit 98347b5a). The testing tool
is pgbench, with the workload being to insert into all temporary tables:
| table num | 1 | 5 | 10 | 100 | 1000 |
|---------------|-------------|-------------|-------------|------------|-----------|
| head 98347b5a | 7243.945042 | 3627.290594 | 2262.594766 | 297.856756 | 27.745808 |
| threshold 5 | 7287.764656 | 3130.814888 | 2038.308763 | 288.226032 | 27.705149 |
According to test results, the patch does cause some performance loss with
fewer temporary tables, but benefits are substantial when many temporary tables
are used. The specific threshold could be set to 10 (HDDs may require a smaller
one).
I've provided two patches in the attachments, both with a default threshold of 10.
One has the threshold configured as a GUC parameter, while the other is hardcoded
to 10.
Best Regards,
Fei Changhong
Attachments:
v1-0002-Optimize-commit-with-temp-tables-guc.patchapplication/octet-stream; charset=ISO-8859-1; name=v1-0002-Optimize-commit-with-temp-tables-guc.patchDownload
diff --git a/src/backend/access/common/relation.c b/src/backend/access/common/relation.c
index d8a313a2c9..74b1415ad5 100644
--- a/src/backend/access/common/relation.c
+++ b/src/backend/access/common/relation.c
@@ -70,7 +70,10 @@ relation_open(Oid relationId, LOCKMODE lockmode)
/* Make note that we've accessed a temporary relation */
if (RelationUsesLocalBuffers(r))
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relationId);
+ }
pgstat_init_relation(r);
@@ -120,7 +123,10 @@ try_relation_open(Oid relationId, LOCKMODE lockmode)
/* Make note that we've accessed a temporary relation */
if (RelationUsesLocalBuffers(r))
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relationId);
+ }
pgstat_init_relation(r);
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index d119ab909d..dc7bde98ab 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -2122,6 +2122,9 @@ StartTransaction(void)
forceSyncCommit = false;
MyXactFlags = 0;
+ /* When starting a new transaction, init or reset the oncommit filter. */
+ on_commits_filter_init_or_reset();
+
/*
* reinitialize within-transaction counters
*/
diff --git a/src/backend/commands/lockcmds.c b/src/backend/commands/lockcmds.c
index cd20ae76ba..77ad85576d 100644
--- a/src/backend/commands/lockcmds.c
+++ b/src/backend/commands/lockcmds.c
@@ -98,7 +98,10 @@ RangeVarCallbackForLockTable(const RangeVar *rv, Oid relid, Oid oldrelid,
*/
relpersistence = get_rel_persistence(relid);
if (relpersistence == RELPERSISTENCE_TEMP)
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relid);
+ }
/* Check permissions. */
aclresult = LockTableAclCheck(relid, lockmode, GetUserId());
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index dbfe0d6b1c..41d5c841c3 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -68,6 +68,7 @@
#include "executor/executor.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
+#include "lib/bloomfilter.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -128,6 +129,16 @@ typedef struct OnCommitItem
static List *on_commits = NIL;
+/*
+ * Filter out the temporary tables accessed in the current transaction.
+ * The threshold for using the filter is configured as a GUC parameter for easy
+ * adjustment in different scenarios.
+ * The number of bits for the bloom filter is fixed at 1KB, which provides a
+ * good false positive rate even with 1000 items.
+ */
+#define ON_COMMITS_FILTER_BYTES (1 * 1024)
+int on_commits_filter_threshold = 10;
+static bloom_filter* on_commits_filter = NULL;
/*
* State information for ALTER TABLE
@@ -662,6 +673,7 @@ static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
AlterTableUtilityContext *context);
static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
PartitionCmd *cmd, AlterTableUtilityContext *context);
+static bool on_commits_filter_contains(Oid relid);
/* ----------------------------------------------------------------
* DefineRelation
@@ -17429,7 +17441,23 @@ PreCommit_on_commit_actions(void)
* tables, as they must still be empty.
*/
if ((MyXactFlags & XACT_FLAGS_ACCESSEDTEMPNAMESPACE))
- oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+ {
+ if (on_commits_filter_contains(oc->relid))
+ oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+#ifdef USE_ASSERT_CHECKING
+ else
+ {
+ /*
+ * All temporary tables not in the filter should either
+ * have no physical files or a file size of zero.
+ */
+ Relation rel;
+ rel = table_open(oc->relid, AccessShareLock);
+ Assert(!rel->rd_smgr || !smgrexists(rel->rd_smgr, MAIN_FORKNUM) || smgrnblocks(rel->rd_smgr, MAIN_FORKNUM) == 0);
+ table_close(rel, AccessShareLock);
+ }
+#endif
+ }
break;
case ONCOMMIT_DROP:
oids_to_drop = lappend_oid(oids_to_drop, oc->relid);
@@ -20766,3 +20794,68 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Keep the lock until commit. */
table_close(newPartRel, NoLock);
}
+
+/*
+ * on_commits_filter_init_or_reset: Initialize or reset the bloom filter,
+ * ensuring it is only initialized when the number of on_commits exceeds the
+ * on_commits_filter_threshold. It is called when starting a new transaction,
+ * that is, when the XACT_FLAGS_ACCESSEDTEMPNAMESPACE flag is cleared.
+ */
+void
+on_commits_filter_init_or_reset(void)
+{
+ if (on_commits_filter_threshold < 0 || list_length(on_commits) < on_commits_filter_threshold)
+ {
+ if (on_commits_filter != NULL)
+ {
+ bloom_free(on_commits_filter);
+ on_commits_filter = NULL;
+ }
+ return;
+ }
+
+ if (on_commits_filter != NULL)
+ bloom_clear(on_commits_filter);
+ else
+ {
+ MemoryContext oldcxt;
+ int64 total_items;
+
+ oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
+ total_items = Max(on_commits_filter_threshold * 8, 128);
+ on_commits_filter =
+ bloom_create_ex(total_items, work_mem, 0, ON_COMMITS_FILTER_BYTES);
+ MemoryContextSwitchTo(oldcxt);
+ }
+}
+
+/*
+ * on_commits_filter_add: Add the temporary table OId to the Bloom filter,
+ * indicating that the current transaction has accessed this temporary table,
+ * which needs to be truncated upon commit. It is called when the temporary
+ * table is opened.
+ */
+void
+on_commits_filter_add(Oid relid)
+{
+ if (on_commits_filter != NULL)
+ bloom_add_element(on_commits_filter, (unsigned char *) &relid, sizeof(relid));
+}
+
+/*
+ * on_commits_filter_contains: Determine whether the specified temporary table
+ * has been accessed in the current transaction.
+ */
+static bool
+on_commits_filter_contains(Oid relid)
+{
+ /*
+ * If the on_commits filter is not initialized, assuming that the current
+ * transaction has accessed the temp table defaultly, keeping the logic
+ * consistent with when there is no filter.
+ */
+ if (on_commits_filter == NULL)
+ return true;
+
+ return !bloom_lacks_element(on_commits_filter, (unsigned char *) &relid, sizeof(relid));
+}
diff --git a/src/backend/lib/bloomfilter.c b/src/backend/lib/bloomfilter.c
index 360d21ca45..3650255782 100644
--- a/src/backend/lib/bloomfilter.c
+++ b/src/backend/lib/bloomfilter.c
@@ -40,6 +40,7 @@
#include "port/pg_bitutils.h"
#define MAX_HASH_FUNCS 10
+#define MIN_BITSET_BYTES (1024 * 1024)
struct bloom_filter
{
@@ -85,6 +86,13 @@ static inline uint32 mod_m(uint32 val, uint64 m);
*/
bloom_filter *
bloom_create(int64 total_elems, int bloom_work_mem, uint64 seed)
+{
+ return bloom_create_ex(total_elems, bloom_work_mem, seed, MIN_BITSET_BYTES);
+}
+
+bloom_filter *
+bloom_create_ex(int64 total_elems, int bloom_work_mem, uint64 seed,
+ uint64 min_bitset_bytes)
{
bloom_filter *filter;
int bloom_power;
@@ -292,3 +300,8 @@ mod_m(uint32 val, uint64 m)
return val & (m - 1);
}
+
+void bloom_clear(bloom_filter *filter)
+{
+ memset(filter->bitset, 0, filter->m / BITS_PER_BYTE);
+}
\ No newline at end of file
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index d28b0bcb40..4d27b612a1 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -39,6 +39,7 @@
#include "catalog/storage.h"
#include "commands/async.h"
#include "commands/event_trigger.h"
+#include "commands/tablecmds.h"
#include "commands/tablespace.h"
#include "commands/trigger.h"
#include "commands/user.h"
@@ -3649,6 +3650,16 @@ struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"on_commits_filter_threshold", PGC_USERSET, CUSTOM_OPTIONS,
+ gettext_noop("Minimum number of OnCommitItem to utilize bloom filter optimization, or negative to disable."),
+ NULL,
+ },
+ &on_commits_filter_threshold,
+ 10, -1, 10000,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL, NULL
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index 85cbad3d0c..56598a240d 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -107,4 +107,8 @@ extern void RangeVarCallbackOwnsRelation(const RangeVar *relation,
extern bool PartConstraintImpliedByRelConstraint(Relation scanrel,
List *partConstraint);
+extern int on_commits_filter_threshold;
+extern void on_commits_filter_init_or_reset(void);
+extern void on_commits_filter_add(Oid relid);
+
#endif /* TABLECMDS_H */
diff --git a/src/include/lib/bloomfilter.h b/src/include/lib/bloomfilter.h
index 6ec7173843..13a0670e92 100644
--- a/src/include/lib/bloomfilter.h
+++ b/src/include/lib/bloomfilter.h
@@ -17,11 +17,14 @@ typedef struct bloom_filter bloom_filter;
extern bloom_filter *bloom_create(int64 total_elems, int bloom_work_mem,
uint64 seed);
+extern bloom_filter *bloom_create_ex(int64 total_elems, int bloom_work_mem,
+ uint64 seed, uint64 min_bitset_bytes);
extern void bloom_free(bloom_filter *filter);
extern void bloom_add_element(bloom_filter *filter, unsigned char *elem,
size_t len);
extern bool bloom_lacks_element(bloom_filter *filter, unsigned char *elem,
size_t len);
extern double bloom_prop_bits_set(bloom_filter *filter);
+extern void bloom_clear(bloom_filter *filter);
#endif /* BLOOMFILTER_H */
v1-0002-Optimize-commit-with-temp-tables-without-guc.patchapplication/octet-stream; charset=ISO-8859-1; name=v1-0002-Optimize-commit-with-temp-tables-without-guc.patchDownload
diff --git a/src/backend/access/common/relation.c b/src/backend/access/common/relation.c
index d8a313a2c9..74b1415ad5 100644
--- a/src/backend/access/common/relation.c
+++ b/src/backend/access/common/relation.c
@@ -70,7 +70,10 @@ relation_open(Oid relationId, LOCKMODE lockmode)
/* Make note that we've accessed a temporary relation */
if (RelationUsesLocalBuffers(r))
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relationId);
+ }
pgstat_init_relation(r);
@@ -120,7 +123,10 @@ try_relation_open(Oid relationId, LOCKMODE lockmode)
/* Make note that we've accessed a temporary relation */
if (RelationUsesLocalBuffers(r))
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relationId);
+ }
pgstat_init_relation(r);
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index d119ab909d..dc7bde98ab 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -2122,6 +2122,9 @@ StartTransaction(void)
forceSyncCommit = false;
MyXactFlags = 0;
+ /* When starting a new transaction, init or reset the oncommit filter. */
+ on_commits_filter_init_or_reset();
+
/*
* reinitialize within-transaction counters
*/
diff --git a/src/backend/commands/lockcmds.c b/src/backend/commands/lockcmds.c
index cd20ae76ba..77ad85576d 100644
--- a/src/backend/commands/lockcmds.c
+++ b/src/backend/commands/lockcmds.c
@@ -98,7 +98,10 @@ RangeVarCallbackForLockTable(const RangeVar *rv, Oid relid, Oid oldrelid,
*/
relpersistence = get_rel_persistence(relid);
if (relpersistence == RELPERSISTENCE_TEMP)
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relid);
+ }
/* Check permissions. */
aclresult = LockTableAclCheck(relid, lockmode, GetUserId());
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index dbfe0d6b1c..e2282e6d54 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -68,6 +68,7 @@
#include "executor/executor.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
+#include "lib/bloomfilter.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -128,6 +129,12 @@ typedef struct OnCommitItem
static List *on_commits = NIL;
+/*
+ * Filter out the temporary tables accessed in the current transaction.
+ */
+#define ON_COMMITS_FILTER_THRESHOLD 10
+#define ON_COMMITS_FILTER_BYTES (1 * 1024)
+static bloom_filter* on_commits_filter = NULL;
/*
* State information for ALTER TABLE
@@ -662,6 +669,7 @@ static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
AlterTableUtilityContext *context);
static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
PartitionCmd *cmd, AlterTableUtilityContext *context);
+static bool on_commits_filter_contains(Oid relid);
/* ----------------------------------------------------------------
* DefineRelation
@@ -17429,7 +17437,23 @@ PreCommit_on_commit_actions(void)
* tables, as they must still be empty.
*/
if ((MyXactFlags & XACT_FLAGS_ACCESSEDTEMPNAMESPACE))
- oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+ {
+ if (on_commits_filter_contains(oc->relid))
+ oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+#ifdef USE_ASSERT_CHECKING
+ else
+ {
+ /*
+ * All temporary tables not in the filter should either
+ * have no physical files or a file size of zero.
+ */
+ Relation rel;
+ rel = table_open(oc->relid, AccessShareLock);
+ Assert(!rel->rd_smgr || !smgrexists(rel->rd_smgr, MAIN_FORKNUM) || smgrnblocks(rel->rd_smgr, MAIN_FORKNUM) == 0);
+ table_close(rel, AccessShareLock);
+ }
+#endif
+ }
break;
case ONCOMMIT_DROP:
oids_to_drop = lappend_oid(oids_to_drop, oc->relid);
@@ -20766,3 +20790,67 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Keep the lock until commit. */
table_close(newPartRel, NoLock);
}
+
+/*
+ * on_commits_filter_init_or_reset: Initialize or reset the bloom filter,
+ * ensuring it is only initialized when the number of on_commits exceeds the
+ * ON_COMMITS_FILTER_THRESHOLD. It is called when starting a new transaction,
+ * that is, when the XACT_FLAGS_ACCESSEDTEMPNAMESPACE flag is cleared.
+ */
+void
+on_commits_filter_init_or_reset(void)
+{
+ if (list_length(on_commits) < ON_COMMITS_FILTER_THRESHOLD)
+ {
+ if (on_commits_filter != NULL)
+ {
+ bloom_free(on_commits_filter);
+ on_commits_filter = NULL;
+ }
+ return;
+ }
+ if (on_commits_filter != NULL)
+ bloom_clear(on_commits_filter);
+ else
+ {
+ MemoryContext oldcxt;
+ int64 total_items;
+
+ oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
+ total_items = Max(ON_COMMITS_FILTER_THRESHOLD * 8, 128);
+ on_commits_filter =
+ bloom_create_ex(total_items, work_mem, 0, ON_COMMITS_FILTER_BYTES);
+ MemoryContextSwitchTo(oldcxt);
+ }
+}
+
+/*
+ * on_commits_filter_add: Add the temporary table OId to the Bloom filter,
+ * indicating that the current transaction has accessed this temporary table,
+ * which needs to be truncated upon commit. It is called when the temporary
+ * table is opened.
+ */
+void
+on_commits_filter_add(Oid relid)
+{
+ if (on_commits_filter != NULL)
+ bloom_add_element(on_commits_filter, (unsigned char *) &relid, sizeof(relid));
+}
+
+/*
+ * on_commits_filter_contains: Determine whether the specified temporary table
+ * has been accessed in the current transaction.
+ */
+static bool
+on_commits_filter_contains(Oid relid)
+{
+ /*
+ * If the on_commits filter is not initialized, assuming that the current
+ * transaction has accessed the temp table defaultly, keeping the logic
+ * consistent with when there is no filter.
+ */
+ if (on_commits_filter == NULL)
+ return true;
+
+ return !bloom_lacks_element(on_commits_filter, (unsigned char *) &relid, sizeof(relid));
+}
diff --git a/src/backend/lib/bloomfilter.c b/src/backend/lib/bloomfilter.c
index 360d21ca45..d2e2513150 100644
--- a/src/backend/lib/bloomfilter.c
+++ b/src/backend/lib/bloomfilter.c
@@ -40,6 +40,7 @@
#include "port/pg_bitutils.h"
#define MAX_HASH_FUNCS 10
+#define MIN_BITSET_BYTES (1024 * 1024)
struct bloom_filter
{
@@ -85,6 +86,13 @@ static inline uint32 mod_m(uint32 val, uint64 m);
*/
bloom_filter *
bloom_create(int64 total_elems, int bloom_work_mem, uint64 seed)
+{
+ return bloom_create_ex(total_elems, bloom_work_mem, seed, MIN_BITSET_BYTES);
+}
+
+bloom_filter *
+bloom_create_ex(int64 total_elems, int bloom_work_mem, uint64 seed,
+ uint64 min_bitset_bytes)
{
bloom_filter *filter;
int bloom_power;
@@ -292,3 +300,9 @@ mod_m(uint32 val, uint64 m)
return val & (m - 1);
}
+
+void
+bloom_clear(bloom_filter *filter)
+{
+ memset(filter->bitset, 0, filter->m / BITS_PER_BYTE);
+}
\ No newline at end of file
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index 85cbad3d0c..461bc1ea47 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -107,4 +107,7 @@ extern void RangeVarCallbackOwnsRelation(const RangeVar *relation,
extern bool PartConstraintImpliedByRelConstraint(Relation scanrel,
List *partConstraint);
+extern void on_commits_filter_init_or_reset(void);
+extern void on_commits_filter_add(Oid relid);
+
#endif /* TABLECMDS_H */
diff --git a/src/include/lib/bloomfilter.h b/src/include/lib/bloomfilter.h
index 6ec7173843..13a0670e92 100644
--- a/src/include/lib/bloomfilter.h
+++ b/src/include/lib/bloomfilter.h
@@ -17,11 +17,14 @@ typedef struct bloom_filter bloom_filter;
extern bloom_filter *bloom_create(int64 total_elems, int bloom_work_mem,
uint64 seed);
+extern bloom_filter *bloom_create_ex(int64 total_elems, int bloom_work_mem,
+ uint64 seed, uint64 min_bitset_bytes);
extern void bloom_free(bloom_filter *filter);
extern void bloom_add_element(bloom_filter *filter, unsigned char *elem,
size_t len);
extern bool bloom_lacks_element(bloom_filter *filter, unsigned char *elem,
size_t len);
extern double bloom_prop_bits_set(bloom_filter *filter);
+extern void bloom_clear(bloom_filter *filter);
#endif /* BLOOMFILTER_H */
Hi feichanghong
I don't think it's acceptable to introduce a patch to fix a problem
that leads to performance degradation, or can we take tom's suggestion to
optimise PreCommit_on_commit_actions? I think it to miss the forest for
the trees
Best Regards,
feichanghong <feichanghong@qq.com> 于2024年7月8日周一 10:35写道：
Show quoted text
Hi wenhui,
Thank you for your suggestions. I have supplemented some performance tests.
Here is the TPS performance data for different numbers of temporary tables
under different thresholds, as compared with the head (98347b5a). The
testing
tool used is pgbench, with the workload being to insert into one temporary
table (when the number of temporary tables is 0, the workload is SELECT 1):| table num | 0 | 1 | 5 | 10
| 100 | 1000 ||---------------|--------------|--------------|-------------|-------------|-------------|-------------|
| head 98347b5a | 39912.722209 | 10064.306268 | 7452.071689 | 5641.487369
| 1073.203851 | 114.530958 |
| threshold 1 | 40332.367414 | 7078.117192 | 7044.951156 | 7020.249434
| 6893.652062 | 5826.597260 |
| threshold 5 | 40173.562744 | 10017.532933 | 7023.770203 | 7024.283577
| 6919.769315 | 5806.314494 |Here is the TPS performance data for different numbers of temporary tables
at a threshold of 5, compared with the head (commit 98347b5a). The testing
tool
is pgbench, with the workload being to insert into all temporary tables:| table num | 1 | 5 | 10 | 100 |
1000 ||---------------|-------------|-------------|-------------|------------|-----------|
| head 98347b5a | 7243.945042 | 3627.290594 | 2262.594766 | 297.856756 |
27.745808 |
| threshold 5 | 7287.764656 | 3130.814888 | 2038.308763 | 288.226032 |
27.705149 |According to test results, the patch does cause some performance loss with
fewer temporary tables, but benefits are substantial when many temporary
tables
are used. The specific threshold could be set to 10 (HDDs may require a
smaller
one).I've provided two patches in the attachments, both with a default
threshold of 10.
One has the threshold configured as a GUC parameter, while the other is
hardcoded
to 10.
------------------------------
Best Regards,
Fei Changhong
Hi wenhui,
On Jul 8, 2024, at 12:18, wenhui qiu <qiuwenhuifx@gmail.com> wrote:
Hi feichanghong
I don't think it's acceptable to introduce a patch to fix a problem that leads to performance degradation, or can we take tom's suggestion to optimise PreCommit_on_commit_actions? I think it to miss the forest for the trees
You're right, any performance regression is certainly unacceptable. That's why
we've introduced a threshold. The bloom filter optimization is only applied
when the number of temporary tables exceeds this threshold. Test data also
reveals that with a threshold of 10, barring cases where all temporary tables
are implicated in a transaction, there's hardly any performance loss.
"Improve PreCommit_on_commit_actions by having it just quit immediately if
XACT_FLAGS_ACCESSEDTEMPNAMESPACE is not set" can only reduce the overhead of
traversing the OnCommitItem List but still doesn't address the issue with
temporary table truncation.
Looking forward to more suggestions!
Best Regards,
Fei Changhong
Hi feichanghong
I think adding an intercept this way is better than implementing a
global temp table,there is a path to implement a global temporary table (
/messages/by-id/1A1A6EDC-D0EC-47B0-BD21-C2ACBAEA65E4@alibaba-inc.com),you
can consult with them ,they work at Alibaba
Best Regards,
feichanghong <feichanghong@qq.com> 于2024年7月8日周一 12:42写道：
Show quoted text
Hi wenhui,
On Jul 8, 2024, at 12:18, wenhui qiu <qiuwenhuifx@gmail.com> wrote:
Hi feichanghong
I don't think it's acceptable to introduce a patch to fix a problem
that leads to performance degradation, or can we take tom's suggestion to
optimise PreCommit_on_commit_actions? I think it to miss the forest for
the treesYou're right, any performance regression is certainly unacceptable. That's
whywe've introduced a threshold. The bloom filter optimization is only applied
when the number of temporary tables exceeds this threshold. Test data also
reveals that with a threshold of 10, barring cases where all temporary
tablesare implicated in a transaction, there's hardly any performance loss.
"Improve PreCommit_on_commit_actions by having it just quit immediately if
XACT_FLAGS_ACCESSEDTEMPNAMESPACE is not set" can only reduce the overhead
oftraversing the OnCommitItem List but still doesn't address the issue with
temporary table truncation.
Looking forward to more suggestions!
Best Regards,
Fei Changhong
Hi wenhui,
I carefully analyzed the reason for the performance regression with fewer
temporary tables in the previous patch (v1-0002-): the k_hash_funcs determined
by the bloom_create function were 10(MAX_HASH_FUNCS), which led to an excessive
calculation overhead for the bloom filter.
Based on the calculation formula for the bloom filter, when the number of items
is 100 and k_hash_funcs is 2, the false positive rate for a 1KB bloom filter is
0.0006096; when the number of items is 1000, the false positive rate is
0.048929094. Therefore, k_hash_funcs of 2 can already achieve a decent false
positive rate, while effectively reducing the computational overhead of the
bloom filter.
I have re-implemented a bloom_create_v2 function to create a bloom filter with
a specified number of hash functions and specified memory size.
From the test data below, it can be seen that the new patch in the attachment
(v1-0003-) does not lead to performance regression in any scenario.
Furthermore, the default threshold value can be lowered to 2.
Here is the TPS performance data for different numbers of temporary tables
under different thresholds, as compared with the head (98347b5a). The testing
tool used is pgbench, with the workload being to insert into one temporary
table (when the number of temporary tables is 0, the workload is SELECT 1):
|tablenum |0 |1 |2 |5 |10 |100 |1000 |
|--------------|------------|------------|-----------|-----------|-----------|-----------|-----------|
|head(98347b5a)|39912.722209|10064.306268|9183.871298|7452.071689|5641.487369|1073.203851|114.530958 |
|threshold-2 |40097.047974|10009.598155|9982.172866|9955.651235|9999.338901|9785.626296|8278.828828|
Here is the TPS performance data for different numbers of temporary tables
at a threshold of 2, compared with the head (commit 98347b5a). The testing tool
is pgbench, with the workload being to insert into all temporary tables:
|table num |1 |2 | 5 |10 |100 |1000 |
|--------------|-----------|-----------|-----------|-----------|----------|---------|
|head(98347b5a)|7243.945042|5734.545012|3627.290594|2262.594766|297.856756|27.745808|
|threshold-2 |7289.171381|5740.849676|3626.135510|2207.439931|293.145036|27.020953|
I have previously researched the implementation of the Global Temp Table (GTT)
you mentioned, and it have been used in Alibaba Cloud's PolarDB (Link [1]https://www.alibabacloud.com/help/en/polardb/polardb-for-oracle/using-global-temporary-tables?spm=a3c0i.23458820.2359477120.1.66e16e9bUpV7cK).
GTT can prevent truncation operations on temporary tables that have not been
accessed by the current session (those not in the OnCommitItem List), but GTT
that have been accessed by the current session still need to be truncated at
commit time.Therefore, GTT also require the optimizations mentioned in the
above patch.
Best Regards,
Fei Changhong
Attachments:
v1-0003-Optimize-commit-with-temp-tables-guc.patchapplication/octet-stream; charset=ISO-8859-1; name=v1-0003-Optimize-commit-with-temp-tables-guc.patchDownload
diff --git a/src/backend/access/common/relation.c b/src/backend/access/common/relation.c
index d8a313a2c9..74b1415ad5 100644
--- a/src/backend/access/common/relation.c
+++ b/src/backend/access/common/relation.c
@@ -70,7 +70,10 @@ relation_open(Oid relationId, LOCKMODE lockmode)
/* Make note that we've accessed a temporary relation */
if (RelationUsesLocalBuffers(r))
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relationId);
+ }
pgstat_init_relation(r);
@@ -120,7 +123,10 @@ try_relation_open(Oid relationId, LOCKMODE lockmode)
/* Make note that we've accessed a temporary relation */
if (RelationUsesLocalBuffers(r))
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relationId);
+ }
pgstat_init_relation(r);
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index d119ab909d..dc7bde98ab 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -2122,6 +2122,9 @@ StartTransaction(void)
forceSyncCommit = false;
MyXactFlags = 0;
+ /* When starting a new transaction, init or reset the oncommit filter. */
+ on_commits_filter_init_or_reset();
+
/*
* reinitialize within-transaction counters
*/
diff --git a/src/backend/commands/lockcmds.c b/src/backend/commands/lockcmds.c
index cd20ae76ba..77ad85576d 100644
--- a/src/backend/commands/lockcmds.c
+++ b/src/backend/commands/lockcmds.c
@@ -98,7 +98,10 @@ RangeVarCallbackForLockTable(const RangeVar *rv, Oid relid, Oid oldrelid,
*/
relpersistence = get_rel_persistence(relid);
if (relpersistence == RELPERSISTENCE_TEMP)
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relid);
+ }
/* Check permissions. */
aclresult = LockTableAclCheck(relid, lockmode, GetUserId());
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index dbfe0d6b1c..54eaef3e51 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -68,6 +68,7 @@
#include "executor/executor.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
+#include "lib/bloomfilter.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -128,6 +129,17 @@ typedef struct OnCommitItem
static List *on_commits = NIL;
+/*
+ * Filter out the temporary tables accessed in the current transaction.
+ * The threshold for using the filter is configured as a GUC parameter for easy
+ * adjustment in different scenarios.
+ * The number of bits for the bloom filter is fixed at 1KB, which provides a
+ * good false positive rate even with 1000 items.
+ */
+#define ON_COMMITS_FILTER_K_HASH_FUNCS 2
+#define ON_COMMITS_FILTER_KB 1
+int on_commits_filter_threshold = 10;
+static bloom_filter* on_commits_filter = NULL;
/*
* State information for ALTER TABLE
@@ -662,6 +674,7 @@ static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
AlterTableUtilityContext *context);
static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
PartitionCmd *cmd, AlterTableUtilityContext *context);
+static bool on_commits_filter_contains(Oid relid);
/* ----------------------------------------------------------------
* DefineRelation
@@ -17429,7 +17442,23 @@ PreCommit_on_commit_actions(void)
* tables, as they must still be empty.
*/
if ((MyXactFlags & XACT_FLAGS_ACCESSEDTEMPNAMESPACE))
- oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+ {
+ if (on_commits_filter_contains(oc->relid))
+ oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+#ifdef USE_ASSERT_CHECKING
+ else
+ {
+ /*
+ * All temporary tables not in the filter should either
+ * have no physical files or a file size of zero.
+ */
+ Relation rel;
+ rel = table_open(oc->relid, AccessShareLock);
+ Assert(!rel->rd_smgr || !smgrexists(rel->rd_smgr, MAIN_FORKNUM) || smgrnblocks(rel->rd_smgr, MAIN_FORKNUM) == 0);
+ table_close(rel, AccessShareLock);
+ }
+#endif
+ }
break;
case ONCOMMIT_DROP:
oids_to_drop = lappend_oid(oids_to_drop, oc->relid);
@@ -20766,3 +20795,66 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Keep the lock until commit. */
table_close(newPartRel, NoLock);
}
+
+/*
+ * on_commits_filter_init_or_reset: Initialize or reset the bloom filter,
+ * ensuring it is only initialized when the number of on_commits exceeds the
+ * on_commits_filter_threshold. It is called when starting a new transaction,
+ * that is, when the XACT_FLAGS_ACCESSEDTEMPNAMESPACE flag is cleared.
+ */
+void
+on_commits_filter_init_or_reset(void)
+{
+ if (on_commits_filter_threshold < 0 ||
+ list_length(on_commits) < on_commits_filter_threshold)
+ {
+ if (on_commits_filter != NULL)
+ {
+ bloom_free(on_commits_filter);
+ on_commits_filter = NULL;
+ }
+ return;
+ }
+ if (on_commits_filter != NULL)
+ bloom_clear(on_commits_filter);
+ else
+ {
+ MemoryContext oldcxt;
+
+ oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
+ on_commits_filter =
+ bloom_create_v2(ON_COMMITS_FILTER_K_HASH_FUNCS, ON_COMMITS_FILTER_KB, 0);
+ MemoryContextSwitchTo(oldcxt);
+ }
+}
+
+/*
+ * on_commits_filter_add: Add the temporary table OId to the Bloom filter,
+ * indicating that the current transaction has accessed this temporary table,
+ * which needs to be truncated upon commit. It is called when the temporary
+ * table is opened.
+ */
+void
+on_commits_filter_add(Oid relid)
+{
+ if (on_commits_filter != NULL)
+ bloom_add_element(on_commits_filter, (unsigned char *) &relid, sizeof(relid));
+}
+
+/*
+ * on_commits_filter_contains: Determine whether the specified temporary table
+ * has been accessed in the current transaction.
+ */
+static bool
+on_commits_filter_contains(Oid relid)
+{
+ /*
+ * If the on_commits filter is not initialized, assuming that the current
+ * transaction has accessed the temp table defaultly, keeping the logic
+ * consistent with when there is no filter.
+ */
+ if (on_commits_filter == NULL)
+ return true;
+
+ return !bloom_lacks_element(on_commits_filter, (unsigned char *) &relid, sizeof(relid));
+}
diff --git a/src/backend/lib/bloomfilter.c b/src/backend/lib/bloomfilter.c
index 360d21ca45..c33ffd1d3c 100644
--- a/src/backend/lib/bloomfilter.c
+++ b/src/backend/lib/bloomfilter.c
@@ -119,6 +119,42 @@ bloom_create(int64 total_elems, int bloom_work_mem, uint64 seed)
return filter;
}
+/*
+ * Create Bloom filter with a specified memory size and specified number of
+ * hash functions in the caller's memory context.
+ * bloom_work_mem is sized in KB, in line with the general work_mem convention.
+ * This determines the size of the underlying bitset (trivial bookkeeping space
+ * isn't counted).
+ */
+bloom_filter *
+bloom_create_v2(int64 k_hash_funcs, int bloom_work_mem, uint64 seed)
+{
+ bloom_filter *filter;
+ int bloom_power;
+ uint64 bitset_bytes;
+ uint64 bitset_bits;
+
+ Assert(bloom_work_mem > 0);
+ bitset_bytes = bloom_work_mem * UINT64CONST(1024);
+
+ /*
+ * Size in bits should be the highest power of two <= target. bitset_bits
+ * is uint64 because PG_UINT32_MAX is 2^32 - 1, not 2^32
+ */
+ bloom_power = my_bloom_power(bitset_bytes * BITS_PER_BYTE);
+ bitset_bits = UINT64CONST(1) << bloom_power;
+ bitset_bytes = bitset_bits / BITS_PER_BYTE;
+
+ /* Allocate bloom filter with unset bitset */
+ filter = palloc0(offsetof(bloom_filter, bitset) +
+ sizeof(unsigned char) * bitset_bytes);
+ filter->k_hash_funcs = k_hash_funcs;
+ filter->seed = seed;
+ filter->m = bitset_bits;
+
+ return filter;
+}
+
/*
* Free Bloom filter
*/
@@ -292,3 +328,8 @@ mod_m(uint32 val, uint64 m)
return val & (m - 1);
}
+
+void bloom_clear(bloom_filter *filter)
+{
+ memset(filter->bitset, 0, filter->m / BITS_PER_BYTE);
+}
\ No newline at end of file
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index d28b0bcb40..0a2e3bf4b6 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -39,6 +39,7 @@
#include "catalog/storage.h"
#include "commands/async.h"
#include "commands/event_trigger.h"
+#include "commands/tablecmds.h"
#include "commands/tablespace.h"
#include "commands/trigger.h"
#include "commands/user.h"
@@ -3649,6 +3650,16 @@ struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"on_commits_filter_threshold", PGC_USERSET, CUSTOM_OPTIONS,
+ gettext_noop("Minimum number of OnCommitItem to utilize bloom filter optimization, or negative to disable."),
+ NULL,
+ },
+ &on_commits_filter_threshold,
+ 2, -1, 10000,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL, NULL
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index 85cbad3d0c..56598a240d 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -107,4 +107,8 @@ extern void RangeVarCallbackOwnsRelation(const RangeVar *relation,
extern bool PartConstraintImpliedByRelConstraint(Relation scanrel,
List *partConstraint);
+extern int on_commits_filter_threshold;
+extern void on_commits_filter_init_or_reset(void);
+extern void on_commits_filter_add(Oid relid);
+
#endif /* TABLECMDS_H */
diff --git a/src/include/lib/bloomfilter.h b/src/include/lib/bloomfilter.h
index 6ec7173843..11b0ab1264 100644
--- a/src/include/lib/bloomfilter.h
+++ b/src/include/lib/bloomfilter.h
@@ -17,11 +17,14 @@ typedef struct bloom_filter bloom_filter;
extern bloom_filter *bloom_create(int64 total_elems, int bloom_work_mem,
uint64 seed);
+extern bloom_filter *bloom_create_v2(int64 k_hash_funcs, int bloom_work_mem,
+ uint64 seed);
extern void bloom_free(bloom_filter *filter);
extern void bloom_add_element(bloom_filter *filter, unsigned char *elem,
size_t len);
extern bool bloom_lacks_element(bloom_filter *filter, unsigned char *elem,
size_t len);
extern double bloom_prop_bits_set(bloom_filter *filter);
+extern void bloom_clear(bloom_filter *filter);
#endif /* BLOOMFILTER_H */
v1-0003-Optimize-commit-with-temp-tables-without-guc.patchapplication/octet-stream; charset=ISO-8859-1; name=v1-0003-Optimize-commit-with-temp-tables-without-guc.patchDownload
diff --git a/src/backend/access/common/relation.c b/src/backend/access/common/relation.c
index d8a313a2c9..74b1415ad5 100644
--- a/src/backend/access/common/relation.c
+++ b/src/backend/access/common/relation.c
@@ -70,7 +70,10 @@ relation_open(Oid relationId, LOCKMODE lockmode)
/* Make note that we've accessed a temporary relation */
if (RelationUsesLocalBuffers(r))
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relationId);
+ }
pgstat_init_relation(r);
@@ -120,7 +123,10 @@ try_relation_open(Oid relationId, LOCKMODE lockmode)
/* Make note that we've accessed a temporary relation */
if (RelationUsesLocalBuffers(r))
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relationId);
+ }
pgstat_init_relation(r);
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index d119ab909d..dc7bde98ab 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -2122,6 +2122,9 @@ StartTransaction(void)
forceSyncCommit = false;
MyXactFlags = 0;
+ /* When starting a new transaction, init or reset the oncommit filter. */
+ on_commits_filter_init_or_reset();
+
/*
* reinitialize within-transaction counters
*/
diff --git a/src/backend/commands/lockcmds.c b/src/backend/commands/lockcmds.c
index cd20ae76ba..77ad85576d 100644
--- a/src/backend/commands/lockcmds.c
+++ b/src/backend/commands/lockcmds.c
@@ -98,7 +98,10 @@ RangeVarCallbackForLockTable(const RangeVar *rv, Oid relid, Oid oldrelid,
*/
relpersistence = get_rel_persistence(relid);
if (relpersistence == RELPERSISTENCE_TEMP)
+ {
MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+ on_commits_filter_add(relid);
+ }
/* Check permissions. */
aclresult = LockTableAclCheck(relid, lockmode, GetUserId());
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index dbfe0d6b1c..e3695cee16 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -68,6 +68,7 @@
#include "executor/executor.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
+#include "lib/bloomfilter.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -128,6 +129,15 @@ typedef struct OnCommitItem
static List *on_commits = NIL;
+/*
+ * Filter out the temporary tables accessed in the current transaction.
+ * The number of bits for the bloom filter is fixed at 1KB, which provides a
+ * good false positive rate even with 1000 items.
+ */
+#define ON_COMMITS_FILTER_K_HASH_FUNCS 2
+#define ON_COMMITS_FILTER_THRESHOLD 2
+#define ON_COMMITS_FILTER_KB 1
+static bloom_filter* on_commits_filter = NULL;
/*
* State information for ALTER TABLE
@@ -662,6 +672,7 @@ static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
AlterTableUtilityContext *context);
static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
PartitionCmd *cmd, AlterTableUtilityContext *context);
+static bool on_commits_filter_contains(Oid relid);
/* ----------------------------------------------------------------
* DefineRelation
@@ -17429,7 +17440,23 @@ PreCommit_on_commit_actions(void)
* tables, as they must still be empty.
*/
if ((MyXactFlags & XACT_FLAGS_ACCESSEDTEMPNAMESPACE))
- oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+ {
+ if (on_commits_filter_contains(oc->relid))
+ oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+#ifdef USE_ASSERT_CHECKING
+ else
+ {
+ /*
+ * All temporary tables not in the filter should either
+ * have no physical files or a file size of zero.
+ */
+ Relation rel;
+ rel = table_open(oc->relid, AccessShareLock);
+ Assert(!rel->rd_smgr || !smgrexists(rel->rd_smgr, MAIN_FORKNUM) || smgrnblocks(rel->rd_smgr, MAIN_FORKNUM) == 0);
+ table_close(rel, AccessShareLock);
+ }
+#endif
+ }
break;
case ONCOMMIT_DROP:
oids_to_drop = lappend_oid(oids_to_drop, oc->relid);
@@ -20766,3 +20793,65 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Keep the lock until commit. */
table_close(newPartRel, NoLock);
}
+
+/*
+ * on_commits_filter_init_or_reset: Initialize or reset the bloom filter,
+ * ensuring it is only initialized when the number of on_commits exceeds the
+ * ON_COMMITS_FILTER_THRESHOLD. It is called when starting a new transaction,
+ * that is, when the XACT_FLAGS_ACCESSEDTEMPNAMESPACE flag is cleared.
+ */
+void
+on_commits_filter_init_or_reset(void)
+{
+ if (list_length(on_commits) < ON_COMMITS_FILTER_THRESHOLD)
+ {
+ if (on_commits_filter != NULL)
+ {
+ bloom_free(on_commits_filter);
+ on_commits_filter = NULL;
+ }
+ return;
+ }
+ if (on_commits_filter != NULL)
+ bloom_clear(on_commits_filter);
+ else
+ {
+ MemoryContext oldcxt;
+
+ oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
+ on_commits_filter =
+ bloom_create_v2(ON_COMMITS_FILTER_K_HASH_FUNCS, ON_COMMITS_FILTER_KB, 0);
+ MemoryContextSwitchTo(oldcxt);
+ }
+}
+
+/*
+ * on_commits_filter_add: Add the temporary table OId to the Bloom filter,
+ * indicating that the current transaction has accessed this temporary table,
+ * which needs to be truncated upon commit. It is called when the temporary
+ * table is opened.
+ */
+void
+on_commits_filter_add(Oid relid)
+{
+ if (on_commits_filter != NULL)
+ bloom_add_element(on_commits_filter, (unsigned char *) &relid, sizeof(relid));
+}
+
+/*
+ * on_commits_filter_contains: Determine whether the specified temporary table
+ * has been accessed in the current transaction.
+ */
+static bool
+on_commits_filter_contains(Oid relid)
+{
+ /*
+ * If the on_commits filter is not initialized, assuming that the current
+ * transaction has accessed the temp table defaultly, keeping the logic
+ * consistent with when there is no filter.
+ */
+ if (on_commits_filter == NULL)
+ return true;
+
+ return !bloom_lacks_element(on_commits_filter, (unsigned char *) &relid, sizeof(relid));
+}
diff --git a/src/backend/lib/bloomfilter.c b/src/backend/lib/bloomfilter.c
index 360d21ca45..c33ffd1d3c 100644
--- a/src/backend/lib/bloomfilter.c
+++ b/src/backend/lib/bloomfilter.c
@@ -119,6 +119,42 @@ bloom_create(int64 total_elems, int bloom_work_mem, uint64 seed)
return filter;
}
+/*
+ * Create Bloom filter with a specified memory size and specified number of
+ * hash functions in the caller's memory context.
+ * bloom_work_mem is sized in KB, in line with the general work_mem convention.
+ * This determines the size of the underlying bitset (trivial bookkeeping space
+ * isn't counted).
+ */
+bloom_filter *
+bloom_create_v2(int64 k_hash_funcs, int bloom_work_mem, uint64 seed)
+{
+ bloom_filter *filter;
+ int bloom_power;
+ uint64 bitset_bytes;
+ uint64 bitset_bits;
+
+ Assert(bloom_work_mem > 0);
+ bitset_bytes = bloom_work_mem * UINT64CONST(1024);
+
+ /*
+ * Size in bits should be the highest power of two <= target. bitset_bits
+ * is uint64 because PG_UINT32_MAX is 2^32 - 1, not 2^32
+ */
+ bloom_power = my_bloom_power(bitset_bytes * BITS_PER_BYTE);
+ bitset_bits = UINT64CONST(1) << bloom_power;
+ bitset_bytes = bitset_bits / BITS_PER_BYTE;
+
+ /* Allocate bloom filter with unset bitset */
+ filter = palloc0(offsetof(bloom_filter, bitset) +
+ sizeof(unsigned char) * bitset_bytes);
+ filter->k_hash_funcs = k_hash_funcs;
+ filter->seed = seed;
+ filter->m = bitset_bits;
+
+ return filter;
+}
+
/*
* Free Bloom filter
*/
@@ -292,3 +328,8 @@ mod_m(uint32 val, uint64 m)
return val & (m - 1);
}
+
+void bloom_clear(bloom_filter *filter)
+{
+ memset(filter->bitset, 0, filter->m / BITS_PER_BYTE);
+}
\ No newline at end of file
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index 85cbad3d0c..461bc1ea47 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -107,4 +107,7 @@ extern void RangeVarCallbackOwnsRelation(const RangeVar *relation,
extern bool PartConstraintImpliedByRelConstraint(Relation scanrel,
List *partConstraint);
+extern void on_commits_filter_init_or_reset(void);
+extern void on_commits_filter_add(Oid relid);
+
#endif /* TABLECMDS_H */
diff --git a/src/include/lib/bloomfilter.h b/src/include/lib/bloomfilter.h
index 6ec7173843..11b0ab1264 100644
--- a/src/include/lib/bloomfilter.h
+++ b/src/include/lib/bloomfilter.h
@@ -17,11 +17,14 @@ typedef struct bloom_filter bloom_filter;
extern bloom_filter *bloom_create(int64 total_elems, int bloom_work_mem,
uint64 seed);
+extern bloom_filter *bloom_create_v2(int64 k_hash_funcs, int bloom_work_mem,
+ uint64 seed);
extern void bloom_free(bloom_filter *filter);
extern void bloom_add_element(bloom_filter *filter, unsigned char *elem,
size_t len);
extern bool bloom_lacks_element(bloom_filter *filter, unsigned char *elem,
size_t len);
extern double bloom_prop_bits_set(bloom_filter *filter);
+extern void bloom_clear(bloom_filter *filter);
#endif /* BLOOMFILTER_H */