Re: [PATCHES] contrib mode - pgenv
Mike Mascari writes: > Out of sheer shame for a stupid remark, I have implemented a pgenv > contrib module which provides three functions: > > set_session_variable (name, value) > get_session_variable (name) > reset_session_variables() How is this better than temporary tables? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Align large shared memory allocations
Tom Lane wrote:
Manfred Spraul <[EMAIL PROTECTED]> writes:
Attached is a patch that aligns large shared memory allocations beyond
MAXIMUM_ALIGNOF. The reason for this is that Intel's cpus have a fast
path for bulk memory copies that only works with aligned addresses.
This patch is missing a demonstration that it's actually worth anything.
What kind of performance gain do you get?
7.4cvs on a 1.13 GHz Intel Celeron mobile, 384 MB RAM, "Severn" RedHat
Linux 2.4 beta, postmaster -N 30 -B 64, data directory on ramdisk,
pgbench -c 10 -s 11 -t 1000:
Without the patch: 124 tps
with the patch: 130 tps.
I've reduced the buffer setting to 64 because without that, a too large
part of the database was cached by postgres. I expect that with all
Intel Pentium III chips, it will be worth 10-20% less system time. I had
around 30% system time after reducing the number of buffers, thus the
~5% performance improvement.
We don't really have arch-dependent header files. What I'd be inclined
to do is "#define ALIGNOF_BUFFER 32" in pg_config_manual.h, then
#define BUFFERALIGN(LEN) to parallel the other TYPEALIGN macros in c.h,
and finally use that in the ShmemAlloc code.
Ok, new patch attached.
--
Manfred
Index: src/backend/storage/ipc/shmem.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/storage/ipc/shmem.c,v
retrieving revision 1.70
diff -u -r1.70 shmem.c
--- src/backend/storage/ipc/shmem.c 4 Aug 2003 02:40:03 - 1.70
+++ src/backend/storage/ipc/shmem.c 21 Sep 2003 07:53:13 -
@@ -131,6 +131,7 @@
void *
ShmemAlloc(Size size)
{
+ uint32 newStart;
uint32 newFree;
void *newSpace;
@@ -146,10 +147,14 @@
SpinLockAcquire(ShmemLock);
- newFree = shmemseghdr->freeoffset + size;
+ newStart = shmemseghdr->freeoffset;
+ if (size >= BLCKSZ)
+ newStart = BUFFERALIGN(newStart);
+
+ newFree = newStart + size;
if (newFree <= shmemseghdr->totalsize)
{
- newSpace = (void *) MAKE_PTR(shmemseghdr->freeoffset);
+ newSpace = (void *) MAKE_PTR(newStart);
shmemseghdr->freeoffset = newFree;
}
else
Index: src/include/c.h
===
RCS file: /projects/cvsroot/pgsql-server/src/include/c.h,v
retrieving revision 1.152
diff -u -r1.152 c.h
--- src/include/c.h 4 Aug 2003 02:40:10 - 1.152
+++ src/include/c.h 21 Sep 2003 07:53:14 -
@@ -529,6 +529,7 @@
#define LONGALIGN(LEN) TYPEALIGN(ALIGNOF_LONG, (LEN))
#define DOUBLEALIGN(LEN) TYPEALIGN(ALIGNOF_DOUBLE, (LEN))
#define MAXALIGN(LEN) TYPEALIGN(MAXIMUM_ALIGNOF, (LEN))
+#define BUFFERALIGN(LEN) TYPEALIGN(ALIGNOF_BUFFER, (LEN))
/*
Index: src/include/pg_config_manual.h
===
RCS file: /projects/cvsroot/pgsql-server/src/include/pg_config_manual.h,v
retrieving revision 1.5
diff -u -r1.5 pg_config_manual.h
--- src/include/pg_config_manual.h 4 Aug 2003 00:43:29 - 1.5
+++ src/include/pg_config_manual.h 21 Sep 2003 07:53:14 -
@@ -176,6 +176,14 @@
*/
#define MAX_RANDOM_VALUE (0x7FFF)
+/*
+ * Alignment of the disk blocks in the shared memory area.
+ * A significant amount of the total system time is required for
+ * copying disk blocks between the os buffers and the cache in the
+ * shared memory area. Some cpus (most notably Intel Pentium III)
+ * prefer well-aligned addresses for memory copies.
+ */
+#define ALIGNOF_BUFFER 32
/*
*
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PATCHES] contrib mode - pgenv
Peter Eisentraut wrote:
> Mike Mascari writes:
>
>
>>Out of sheer shame for a stupid remark, I have implemented a pgenv
>>contrib module which provides three functions:
>>
>>set_session_variable (name, value)
>>get_session_variable (name)
>>reset_session_variables()
>
>
> How is this better than temporary tables?
Well, I basically implemented it as a result of this thread:
http://archives.postgresql.org/pgsql-general/2003-09/msg01347.php
But I can imagine people doing something like this:
Earlier:
CREATE TABLE budgets (
deptid text not null,
budget numeric(16,2);
INSERT INTO budgets
VALUES ('Peter', 10);
CREATE VIEW v_budgets AS
SELECT *
FROM budgets
WHERE deptid = get_session_variable('deptid');
Upon connecting:
---
SELECT set_session_variable('deptid', 'Peter');
SELECT *
FROM v_budgets;
I assume the PHP folks have other reasons as well. Although I'm not
sure...
Mike Mascari
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] contrib mode - pgenv
I wrote: > Peter Eisentraut wrote: > >>How is this better than temporary tables? > > Well, I basically implemented it as a result of this thread: > > http://archives.postgresql.org/pgsql-general/2003-09/msg01347.php I should also point out that if the idea were to be accepted as a contrib module, I'd like to submit another one that: 1) Uses int32 consistently. I use int in a few places. 2) Doesn't include trigger.h/spi.h. Copy and paste error. 3) Creates the function get_session_variable() with the STABLE option. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] contrib mode - pgenv
Mike Mascari writes: > Well, I basically implemented it as a result of this thread: > > http://archives.postgresql.org/pgsql-general/2003-09/msg01347.php We've rejected session variables many times before because they duplicate temporary tables. I don't see anything new added by this proposal. > But I can imagine people doing something like this: This is how temporary tables would operate if they followed the SQL standard. (That is, the temporary table structure is persistent but the contents are deleted at the end of the session. See compatibility section of CREATE TABLE reference page.) Perhaps implementing that would satisfy your needs. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] contrib mode - pgenv
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Mike Mascari writes: >> Well, I basically implemented it as a result of this thread: >> http://archives.postgresql.org/pgsql-general/2003-09/msg01347.php > We've rejected session variables many times before because they duplicate > temporary tables. I don't see anything new added by this proposal. I should think there would be a notable performance advantage, since one need not create a temp table (which in our current implementation is just as expensive as creating a permanent table); not to mention dropping the temp table later, vacuuming up the resulting dead rows in pg_class and pg_attribute, etc. Whether that advantage is great enough to justify a nonstandard feature is unproven, but I imagine Mike could answer it with a little experimentation. I guess the main limitation that I see in this proposal is that it can only handle a single variable datatype (namely text). In a temp-table design you can of course make the column datatype(s) whatever you need. This objection could perhaps be answered just by making the function names be get_session_variable_text() and set_session_variable_text(), to leave room for variants for other data types. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Align large shared memory allocations
Manfred Spraul <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> This patch is missing a demonstration that it's actually worth anything. >> What kind of performance gain do you get? >> > 7.4cvs on a 1.13 GHz Intel Celeron mobile, 384 MB RAM, "Severn" RedHat > Linux 2.4 beta, postmaster -N 30 -B 64, data directory on ramdisk, > pgbench -c 10 -s 11 -t 1000: > Without the patch: 124 tps > with the patch: 130 tps. I tried it on an Intel box here (P4 I think). Using postmaster -B 64 -N 30 and three tries of pgbench -s 10 -c 1 -t 1000 after creation of the test tables, I get: tps = 92.461144 (including connections establishing) tps = 92.500572 (excluding connections establishing) tps = 88.078814 (including connections establishing) tps = 88.115905 (excluding connections establishing) tps = 85.434473 (including connections establishing) tps = 85.468807 (excluding connections establishing) and with the patch: tps = 122.927066 (including connections establishing) tps = 122.998129 (excluding connections establishing) tps = 110.716370 (including connections establishing) tps = 110.773928 (excluding connections establishing) tps = 138.155991 (including connections establishing) tps = 138.245777 (excluding connections establishing) So there's definitely a visible difference on recent Pentiums. It might not help on other CPUs, but we can surely waste a couple dozen bytes in the hope that it might. Patch applied. Do you want to look at making it happen for local buffers and buffile.c as well? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] bug fix: TupleDescGetAttInMetadata/BuildTupleFromCStrings with dropped cols
I discovered that TupleDescGetAttInMetadata and BuildTupleFromCStrings
don't deal well with tuples having dropped columns. The attached fixes
the issue. Please apply.
Thanks,
Joe
Index: src/backend/executor/execTuples.c
===
RCS file: /opt/src/cvs/pgsql-server/src/backend/executor/execTuples.c,v
retrieving revision 1.71
diff -c -r1.71 execTuples.c
*** src/backend/executor/execTuples.c 8 Aug 2003 21:41:40 - 1.71
--- src/backend/executor/execTuples.c 21 Sep 2003 23:23:02 -
***
*** 674,689
* Gather info needed later to call the "in" function for each
* attribute
*/
! attinfuncinfo = (FmgrInfo *) palloc(natts * sizeof(FmgrInfo));
! attelems = (Oid *) palloc(natts * sizeof(Oid));
! atttypmods = (int32 *) palloc(natts * sizeof(int32));
for (i = 0; i < natts; i++)
{
! atttypeid = tupdesc->attrs[i]->atttypid;
! getTypeInputInfo(atttypeid, &attinfuncid, &attelems[i]);
! fmgr_info(attinfuncid, &attinfuncinfo[i]);
! atttypmods[i] = tupdesc->attrs[i]->atttypmod;
}
attinmeta->tupdesc = tupdesc;
attinmeta->attinfuncs = attinfuncinfo;
--- 674,693
* Gather info needed later to call the "in" function for each
* attribute
*/
! attinfuncinfo = (FmgrInfo *) palloc0(natts * sizeof(FmgrInfo));
! attelems = (Oid *) palloc0(natts * sizeof(Oid));
! atttypmods = (int32 *) palloc0(natts * sizeof(int32));
for (i = 0; i < natts; i++)
{
! /* Ignore dropped attributes */
! if (!tupdesc->attrs[i]->attisdropped)
! {
! atttypeid = tupdesc->attrs[i]->atttypid;
! getTypeInputInfo(atttypeid, &attinfuncid, &attelems[i]);
! fmgr_info(attinfuncid, &attinfuncinfo[i]);
! atttypmods[i] = tupdesc->attrs[i]->atttypmod;
! }
}
attinmeta->tupdesc = tupdesc;
attinmeta->attinfuncs = attinfuncinfo;
***
*** 712,733
dvalues = (Datum *) palloc(natts * sizeof(Datum));
nulls = (char *) palloc(natts * sizeof(char));
! /* Call the "in" function for each non-null attribute */
for (i = 0; i < natts; i++)
{
! if (values[i] != NULL)
{
! attelem = attinmeta->attelems[i];
! atttypmod = attinmeta->atttypmods[i];
!
! dvalues[i] = FunctionCall3(&attinmeta->attinfuncs[i],
!
CStringGetDatum(values[i]),
!
ObjectIdGetDatum(attelem),
!
Int32GetDatum(atttypmod));
! nulls[i] = ' ';
}
else
{
dvalues[i] = (Datum) 0;
nulls[i] = 'n';
}
--- 716,747
dvalues = (Datum *) palloc(natts * sizeof(Datum));
nulls = (char *) palloc(natts * sizeof(char));
! /* Call the "in" function for each non-null, non-dropped attribute */
for (i = 0; i < natts; i++)
{
! if (!tupdesc->attrs[i]->attisdropped)
{
! /* Non-dropped attributes */
! if (values[i] != NULL)
! {
! attelem = attinmeta->attelems[i];
! atttypmod = attinmeta->atttypmods[i];
!
! dvalues[i] = FunctionCall3(&attinmeta->attinfuncs[i],
!
CStringGetDatum(values[i]),
!
ObjectIdGetDatum(attelem),
!
Int32GetDatum(atttypmod));
! nulls[i] = ' ';
! }
! else
! {
! dvalues[i] = (Datum) 0;
! nulls[i] = 'n';
! }
}
else
{
+ /* Handle dropped attributes by setting to NULL */
dvalues[i] = (Datum) 0;
nulls[i] = 'n';
}
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
