public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 1/1] resownerbench
3+ messages / 2 participants
[nested] [flat]
* [PATCH 1/1] resownerbench
@ 2020-11-11 22:40 Heikki Linnakangas <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Heikki Linnakangas @ 2020-11-11 22:40 UTC (permalink / raw)
---
contrib/resownerbench/Makefile | 17 ++
contrib/resownerbench/resownerbench--1.0.sql | 14 ++
contrib/resownerbench/resownerbench.c | 154 +++++++++++++++++++
contrib/resownerbench/resownerbench.control | 6 +
contrib/resownerbench/snaptest.sql | 37 +++++
5 files changed, 228 insertions(+)
create mode 100644 contrib/resownerbench/Makefile
create mode 100644 contrib/resownerbench/resownerbench--1.0.sql
create mode 100644 contrib/resownerbench/resownerbench.c
create mode 100644 contrib/resownerbench/resownerbench.control
create mode 100644 contrib/resownerbench/snaptest.sql
diff --git a/contrib/resownerbench/Makefile b/contrib/resownerbench/Makefile
new file mode 100644
index 00000000000..9b0e1cfee1a
--- /dev/null
+++ b/contrib/resownerbench/Makefile
@@ -0,0 +1,17 @@
+MODULE_big = resownerbench
+OBJS = resownerbench.o
+
+EXTENSION = resownerbench
+DATA = resownerbench--1.0.sql
+PGFILEDESC = "resownerbench - benchmark for ResourceOwners"
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/resownerbench
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/resownerbench/resownerbench--1.0.sql b/contrib/resownerbench/resownerbench--1.0.sql
new file mode 100644
index 00000000000..d29182f5982
--- /dev/null
+++ b/contrib/resownerbench/resownerbench--1.0.sql
@@ -0,0 +1,14 @@
+/* contrib/resownerbench/resownerbench--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION resownerbench" to load this file. \quit
+
+CREATE FUNCTION snapshotbench_lifo(numkeep int, numsnaps int, numiters int)
+RETURNS double precision
+AS 'MODULE_PATHNAME', 'snapshotbench_lifo'
+LANGUAGE C STRICT VOLATILE;
+
+CREATE FUNCTION snapshotbench_fifo(numkeep int, numsnaps int, numiters int)
+RETURNS double precision
+AS 'MODULE_PATHNAME', 'snapshotbench_fifo'
+LANGUAGE C STRICT VOLATILE;
diff --git a/contrib/resownerbench/resownerbench.c b/contrib/resownerbench/resownerbench.c
new file mode 100644
index 00000000000..acfb6c39199
--- /dev/null
+++ b/contrib/resownerbench/resownerbench.c
@@ -0,0 +1,154 @@
+#include "postgres.h"
+
+#include "catalog/pg_type.h"
+#include "catalog/pg_statistic.h"
+#include "executor/spi.h"
+#include "funcapi.h"
+#include "libpq/pqsignal.h"
+#include "utils/catcache.h"
+#include "utils/syscache.h"
+#include "utils/timestamp.h"
+#include "utils/snapmgr.h"
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(snapshotbench_lifo);
+PG_FUNCTION_INFO_V1(snapshotbench_fifo);
+
+/*
+ * ResourceOwner Performance test, using RegisterSnapshot().
+ *
+ * This takes three parameters: numkeep, numsnaps, numiters.
+ *
+ * First, we register 'numkeep' snapshots. They are kept registed
+ * until the end of the test. Then, we repeatedly register and
+ * unregister 'numsnaps - numkeep' additional snapshots, repeating
+ * 'numiters' times. All the register/unregister calls are made in
+ * LIFO order.
+ *
+ * Returns the time spent, in milliseconds.
+ *
+ * The idea is to test the performance of ResourceOwnerRemember()
+ * and ReourceOwnerForget() operations, under different regimes.
+ *
+ * In the old implementation, if 'numsnaps' is small enough, all
+ * the entries fit in the resource owner's small array (it can
+ * hold 64 entries).
+ *
+ * In the new implementation, the array is much smaller, only 8
+ * entries, but it's used together with the hash table so that
+ * we stay in the "array regime" as long as 'numsnaps - numkeep'
+ * is smaller than 8 entries.
+ *
+ * 'numiters' can be adjusted to adjust the overall runtime to be
+ * suitable long.
+ */
+Datum
+snapshotbench_lifo(PG_FUNCTION_ARGS)
+{
+ int numkeep = PG_GETARG_INT32(0);
+ int numsnaps = PG_GETARG_INT32(1);
+ int numiters = PG_GETARG_INT32(2);
+ int i;
+ instr_time start,
+ duration;
+ Snapshot lsnap;
+ Snapshot *rs;
+ int numregistered = 0;
+
+ rs = palloc(Max(numsnaps, numkeep) * sizeof(Snapshot));
+
+ lsnap = GetLatestSnapshot();
+
+ PG_SETMASK(&BlockSig);
+ INSTR_TIME_SET_CURRENT(start);
+
+ while (numregistered < numkeep)
+ {
+ rs[numregistered] = RegisterSnapshot(lsnap);
+ numregistered++;
+ }
+
+ for (i = 0 ; i < numiters; i++)
+ {
+ while (numregistered < numsnaps)
+ {
+ rs[numregistered] = RegisterSnapshot(lsnap);
+ numregistered++;
+ }
+
+ while (numregistered > numkeep)
+ {
+ numregistered--;
+ UnregisterSnapshot(rs[numregistered]);
+ }
+ }
+
+ while (numregistered > 0)
+ {
+ numregistered--;
+ UnregisterSnapshot(rs[numregistered]);
+ }
+
+ INSTR_TIME_SET_CURRENT(duration);
+ INSTR_TIME_SUBTRACT(duration, start);
+ PG_SETMASK(&UnBlockSig);
+
+ PG_RETURN_FLOAT8(INSTR_TIME_GET_MILLISEC(duration));
+};
+
+
+/*
+ * Same, but do the register/unregister operations in
+ * FIFO order.
+ */
+Datum
+snapshotbench_fifo(PG_FUNCTION_ARGS)
+{
+ int numkeep = PG_GETARG_INT32(0);
+ int numsnaps = PG_GETARG_INT32(1);
+ int numiters = PG_GETARG_INT32(2);
+ int i,
+ j;
+ instr_time start,
+ duration;
+ Snapshot lsnap;
+ Snapshot *rs;
+ int numregistered = 0;
+
+ rs = palloc(Max(numsnaps, numkeep) * sizeof(Snapshot));
+
+ lsnap = GetLatestSnapshot();
+
+ PG_SETMASK(&BlockSig);
+ INSTR_TIME_SET_CURRENT(start);
+
+ while (numregistered < numkeep)
+ {
+ rs[numregistered] = RegisterSnapshot(lsnap);
+ numregistered++;
+ }
+
+ for (i = 0 ; i < numiters; i++)
+ {
+ while (numregistered < numsnaps)
+ {
+ rs[numregistered] = RegisterSnapshot(lsnap);
+ numregistered++;
+ }
+
+ for (j = numkeep; j < numregistered; j++)
+ UnregisterSnapshot(rs[j]);
+ numregistered = numkeep;
+ }
+
+ for (j = 0; j < numregistered; j++)
+ UnregisterSnapshot(rs[j]);
+ numregistered = numkeep;
+
+ INSTR_TIME_SET_CURRENT(duration);
+ INSTR_TIME_SUBTRACT(duration, start);
+ PG_SETMASK(&UnBlockSig);
+
+ PG_RETURN_FLOAT8(INSTR_TIME_GET_MILLISEC(duration));
+};
diff --git a/contrib/resownerbench/resownerbench.control b/contrib/resownerbench/resownerbench.control
new file mode 100644
index 00000000000..ada88b8eed8
--- /dev/null
+++ b/contrib/resownerbench/resownerbench.control
@@ -0,0 +1,6 @@
+# resownerbench
+
+comment = 'benchmark for ResourceOwners'
+default_version = '1.0'
+module_pathname = '$libdir/resownerbench'
+relocatable = true
diff --git a/contrib/resownerbench/snaptest.sql b/contrib/resownerbench/snaptest.sql
new file mode 100644
index 00000000000..18c54e13fc9
--- /dev/null
+++ b/contrib/resownerbench/snaptest.sql
@@ -0,0 +1,37 @@
+--
+-- Performance test RegisterSnapshot/UnregisterSnapshot.
+--
+select numkeep, numsnaps,
+ -- numiters,
+ -- round(lifo_time_ms) as lifo_total_time_ms,
+ -- round(fifo_time_ms) as fifo_total_time_ms,
+ round((lifo_time_ms::numeric / (numkeep + (numsnaps - numkeep) * numiters)) * 1000000, 1) as lifo_time_ns,
+ round((fifo_time_ms::numeric / (numkeep + (numsnaps - numkeep) * numiters)) * 1000000, 1) as fifo_time_ns
+from
+(values (0, 1, 10000000),
+ (0, 5, 2000000),
+ (0, 10, 1000000),
+ (0, 60, 100000),
+ (0, 70, 100000),
+ (0, 100, 100000),
+ (0, 1000, 10000),
+ (0, 10000, 1000),
+
+-- These tests keep 9 snapshots registered across the iterations. That
+-- exceeds the size of the little array in the patch, so this exercises
+-- the hash lookups. Without the patch, these still fit in the array
+-- (it's 64 entries without the patch)
+ (9, 10, 10000000),
+ (9, 100, 100000),
+ (9, 1000, 10000),
+ (9, 10000, 1000),
+
+-- These exceed the 64 entry array even without the patch, so these fall
+-- in the hash table regime with and without the patch.
+ (65, 70, 1000000),
+ (65, 100, 100000),
+ (65, 1000, 10000),
+ (65, 10000, 1000)
+) AS params (numkeep, numsnaps, numiters),
+lateral snapshotbench_lifo(numkeep, numsnaps, numiters) as lifo_time_ms,
+lateral snapshotbench_fifo(numkeep, numsnaps, numiters) as fifo_time_ms;
--
2.29.2
--------------1104342304EC6341F5D7C60C--
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg17 issues with not-null contraints
@ 2024-05-04 09:20 Alvaro Herrera <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Alvaro Herrera @ 2024-05-04 09:20 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; +Cc: Robert Haas <[email protected]>; Kyotaro Horiguchi <[email protected]>; [email protected]; Alexander Lakhin <[email protected]>; Dmitry Koval <[email protected]>
On 2024-May-03, Justin Pryzby wrote:
> But if it's created with LIKE:
> postgres=# CREATE TABLE t1 (LIKE t);
> postgres=# ALTER TABLE t ATTACH PARTITION t1 DEFAULT ;
>
> ..one also sees:
>
> Not-null constraints:
> "t1_i_not_null" NOT NULL "i"
Hmm, I think the problem here is not ATTACH; the not-null constraint is
there immediately after CREATE. I think this is all right actually,
because we derive a not-null constraint from the primary key and this is
definitely intentional. But I also think that if you do CREATE TABLE t1
(LIKE t INCLUDING CONSTRAINTS) then you should get only the primary key
and no separate not-null constraint. That will make the table more
similar to the one being copied.
Does that make sense to you?
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Estoy de acuerdo contigo en que la verdad absoluta no existe...
El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg17 issues with not-null contraints
@ 2024-05-06 15:56 Alvaro Herrera <[email protected]>
parent: Alvaro Herrera <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2024-05-06 15:56 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; +Cc: Robert Haas <[email protected]>; Kyotaro Horiguchi <[email protected]>; [email protected]; Alexander Lakhin <[email protected]>; Dmitry Koval <[email protected]>
On 2024-May-04, Alvaro Herrera wrote:
> On 2024-May-03, Justin Pryzby wrote:
>
> > But if it's created with LIKE:
> > postgres=# CREATE TABLE t1 (LIKE t);
> > postgres=# ALTER TABLE t ATTACH PARTITION t1 DEFAULT ;
> >
> > ..one also sees:
> >
> > Not-null constraints:
> > "t1_i_not_null" NOT NULL "i"
>
> Hmm, I think the problem here is not ATTACH; the not-null constraint is
> there immediately after CREATE. I think this is all right actually,
> because we derive a not-null constraint from the primary key and this is
> definitely intentional. But I also think that if you do CREATE TABLE t1
> (LIKE t INCLUDING CONSTRAINTS) then you should get only the primary key
> and no separate not-null constraint. That will make the table more
> similar to the one being copied.
I misspoke -- it's INCLUDING INDEXES that we need here, not INCLUDING
CONSTRAINTS ... and it turns out we already do it that way, so with this
script
CREATE TABLE t (i int PRIMARY KEY) PARTITION BY RANGE (i);
CREATE TABLE t1 (LIKE t INCLUDING INDEXES);
ALTER TABLE t ATTACH PARTITION t1 DEFAULT ;
you end up with this
55432 17devel 71313=# \d+ t
Partitioned table "public.t"
Column │ Type │ Collation │ Nullable │ Default │ Storage │ Compression │ Stats target │ Description
────────┼─────────┼───────────┼──────────┼─────────┼─────────┼─────────────┼──────────────┼─────────────
i │ integer │ │ not null │ │ plain │ │ │
Partition key: RANGE (i)
Indexes:
"t_pkey" PRIMARY KEY, btree (i)
Partitions: t1 DEFAULT
55432 17devel 71313=# \d+ t1
Table "public.t1"
Column │ Type │ Collation │ Nullable │ Default │ Storage │ Compression │ Stats target │ Description
────────┼─────────┼───────────┼──────────┼─────────┼─────────┼─────────────┼──────────────┼─────────────
i │ integer │ │ not null │ │ plain │ │ │
Partition of: t DEFAULT
No partition constraint
Indexes:
"t1_pkey" PRIMARY KEY, btree (i)
Access method: heap
which I think is what you want. (Do you really want the partition to be
created without the primary key already there?)
Now maybe in https://www.postgresql.org/docs/devel/sql-createtable.html
we need some explanation for this. Right now we have
INCLUDING INDEXES
Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original table
will be created on the new table. Names for the new indexes and constraints
are chosen according to the default rules, regardless of how the originals were
named. (This behavior avoids possible duplicate-name failures for the new
indexes.)
Maybe something like this before the naming considerations:
When creating a table like another that has a primary key and indexes
are excluded, a not-null constraint will be added to every column of
the primary key.
resulting in
INCLUDING INDEXES
Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original table
will be created on the new table. [When/If ?] indexes are excluded while
creating a table like another that has a primary key, a not-null
constraint will be added to every column of the primary key.
Names for the new indexes and constraints are chosen according to
the default rules, regardless of how the originals were named. (This
behavior avoids possible duplicate-name failures for the new
indexes.)
What do you think?
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-05-06 15:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-11 22:40 [PATCH 1/1] resownerbench Heikki Linnakangas <[email protected]>
2024-05-04 09:20 Re: pg17 issues with not-null contraints Alvaro Herrera <[email protected]>
2024-05-06 15:56 ` Re: pg17 issues with not-null contraints Alvaro Herrera <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox