Re: [HACKERS] Need Multixact Freezing Docs
Jim Nasby wrote:
> On 6/14/15 9:50 AM, Alvaro Herrera wrote:
> >+values[0] = MultiXactState->oldestMultiXactId;
>
> What about oldestOffset and offsetStopLimit? Seems those would be useful
> too. Looks good other than that.
Yeah, that's what I was trying to say. How about this?
I realized that pg_get_multixact_members() was not documented, so I
added a blurb about it too. I guess I could backpatch that part to 9.3
because it's been present all along.
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 9a2a1f6..1925c6c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14874,6 +14874,57 @@ SELECT collation for ('foo' COLLATE "de_DE");
For example 10:20:10,14,15 means
xmin=10, xmax=20, xip_list=10, 14, 15.
+
+
+pg_get_multixact_members
+
+
+
+pg_get_multixact_range
+
+
+
+The functions shown in
+obtain information relative to multixacts in the system.
+
+
+
+Multixact Functions
+
+
+ Name Return Type Description
+
+
+
+
+ pg_get_multixact_range()
+
+oldestmultixid,
+nextmultixid,
+stopmultixid,
+oldestmemberxid,
+nextmemberxid,
+stopmemberxid
+
+
+get current oldest, next and stop limits for multixact IDs,
+and oldest, next and stop limit for multixact members
+
+
+
+
+ pg_get_multixact_members(xid)
+
+xidxid,
+modetext
+
+
+get Xid and mode of the members of the given multixact
+
+
+
+
+
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 4383862..db4d457 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -662,6 +662,14 @@ HINT: Stop the postmaster and use a standalone backend to VACUUM in "mydb".
Both of these kinds of whole-table scans will occur even if autovacuum is
nominally disabled.
+
+
+ To know the limits of the live range of both the multixact ID counter and
+ of the members storage area, and the limits which would cause further
+ multixact creation to be rejected, see
+ .
+
+
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 5c25c2f..296c85b 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -66,6 +66,7 @@
*/
#include "postgres.h"
+#include "access/htup_details.h"
#include "access/multixact.h"
#include "access/slru.h"
#include "access/transam.h"
@@ -3308,3 +3309,38 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funccxt);
}
+
+Datum
+pg_get_multixact_range(PG_FUNCTION_ARGS)
+{
+ TupleDesc tupdesc;
+ HeapTuple htup;
+ Datum values[6];
+ bool nulls[6];
+
+ /* Build a tuple descriptor for our result type */
+ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
+ tupdesc = BlessTupleDesc(tupdesc);
+
+ MemSet(nulls, 0, sizeof(nulls));
+
+ LWLockAcquire(MultiXactGenLock, LW_SHARED);
+ values[0] = MultiXactState->oldestMultiXactId;
+ values[1] = MultiXactState->nextMXact;
+ values[2] = MultiXactState->multiStopLimit;
+ if (MultiXactState->oldestOffsetKnown)
+ values[3] = MultiXactState->oldestOffset;
+ else
+ nulls[3] = true;
+ values[4] = MultiXactState->nextOffset;
+ if (MultiXactState->offsetStopLimitKnown)
+ values[5] = MultiXactState->offsetStopLimit;
+ else
+ nulls[5] = true;
+ LWLockRelease(MultiXactGenLock);
+
+ htup = heap_form_tuple(tupdesc, values, nulls);
+
+ PG_RETURN_DATUM(HeapTupleGetDatum(htup));
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 92af36d..8241732 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2911,6 +2911,8 @@ DATA(insert OID = 1065 ( pg_prepared_xact PGNSP PGUID 12 1 1000 0 0 f f f f t t
DESCR("view two-phase transactions");
DATA(insert OID = 3819 ( pg_get_multixact_members PGNSP PGUID 12 1 1000 0 0 f f f f t t v 1 0 2249 "28" "{28,28,25}" "{i,o,o}" "{multixid,xid,mode}" _null_ pg_get_multixact_members _null_ _null_ _null_ ));
DESCR("view members of a multixactid");
+DATA(insert OID = 3846 ( pg_get_multixact_range PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 2249 "" "{28,28,28,28,28,28}" "{o,o,o,o,o,o}" "{oldestmulti,nextmulti,stopmulti,oldestmember,nextmember,stopmember}" _null_ pg_get_multixact_range _null_ _null_ _null_ ));
+DESCR("get range and limits of multixacts");
DATA(insert OID = 3537 ( pg_describe_object PGNSP PGUID 12 1 0 0 0 f f f f t f s 3 0 25 "26 26 23" _null_ _null_ _null_ _null_ pg_describe_object _null_ _null_ _null_ ));
DESCR("get identification of SQL object");
diff --git a/src/include/utils/builtins.h b/src/i
Re: [HACKERS] Need Multixact Freezing Docs
On 6/14/15 9:50 AM, Alvaro Herrera wrote: + values[0] = MultiXactState->oldestMultiXactId; What about oldestOffset and offsetStopLimit? Seems those would be useful too. Looks good other than that. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
Josh Berkus wrote:
>
> > You can see the current multixact value in pg_controldata output. Keep
> > timestamped values of that somewhere (a table?) so that you can measure
> > consumption rate. I don't think we provide SQL-level access to those
> > values.
>
> Bleh. Do we provide SQL-level access in 9.4? If not, I think that's a
> requirement before release. Telling users to monitor a setting using a
> restricted-permission command-line utility which produces a
> version-specific text file they have to parse is not going to win us a
> lot of fans.
I found that I had written a very quick accessor function to multixact
shared state data awhile ago. This might be useful for monitoring
purposes. What do people think of including this for 9.5? It needs a
small change to add the newly added oldestOffset (plus a little cleanup
and docs).
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 2134f7928cad8c8d1a1e2d752c9443fb44a92a7e Mon Sep 17 00:00:00 2001
From: Alvaro Herrera
Date: Sun, 14 Jun 2015 11:48:58 -0300
Subject: [PATCH] pg_get_multixact_range
---
src/backend/access/transam/multixact.c | 31 +++
src/include/catalog/pg_proc.h | 2 ++
2 files changed, 33 insertions(+)
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 516a89f..dedded3 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -3274,3 +3274,34 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funccxt);
}
+
+#include "access/htup_details.h"
+
+Datum
+pg_get_multixact_range(PG_FUNCTION_ARGS);
+
+Datum
+pg_get_multixact_range(PG_FUNCTION_ARGS)
+{
+ TupleDesc tupdesc;
+ HeapTuple htup;
+ Datum values[3];
+ bool nulls[3];
+
+ /* Build a tuple descriptor for our result type */
+ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
+ tupdesc = BlessTupleDesc(tupdesc);
+
+ LWLockAcquire(MultiXactGenLock, LW_SHARED);
+ values[0] = MultiXactState->oldestMultiXactId;
+ values[1] = MultiXactState->nextMXact;
+ values[2] = MultiXactState->nextOffset;
+ LWLockRelease(MultiXactGenLock);
+
+ nulls[0] = nulls[1] = nulls[2] = false;
+
+ htup = heap_form_tuple(tupdesc, values, nulls);
+
+ PG_RETURN_DATUM(HeapTupleGetDatum(htup));
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 6b3d194..f87d3d0 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3079,6 +3079,8 @@ DATA(insert OID = 1065 ( pg_prepared_xact PGNSP PGUID 12 1 1000 0 0 f f f f t t
DESCR("view two-phase transactions");
DATA(insert OID = 3819 ( pg_get_multixact_members PGNSP PGUID 12 1 1000 0 0 f f f f t t v 1 0 2249 "28" "{28,28,25}" "{i,o,o}" "{multixid,xid,mode}" _null_ _null_ pg_get_multixact_members _null_ _null_ _null_ ));
DESCR("view members of a multixactid");
+DATA(insert OID = 3401 ( pg_get_multixact_range PGNSP PGUID 12 10 0 0 f f f f t f s 0 0 2249 "" "{28,28,28}" "{o,o,o}" "{oldestmulti,nextmulti,nextoffset}" _null_ _null_ pg_get_multixact_range _null_ _null_ _null_ ));
+DESCR("get range of live multixacts");
DATA(insert OID = 3581 ( pg_xact_commit_timestamp PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 1184 "28" _null_ _null_ _null_ _null_ _null_ pg_xact_commit_timestamp _null_ _null_ _null_ ));
DESCR("get commit timestamp of a transaction");
--
2.1.4
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
On Fri, Sep 5, 2014 at 07:39:36PM -0400, Bruce Momjian wrote: > On Wed, Sep 3, 2014 at 05:17:17PM -0400, Robert Haas wrote: > > > I had a look at this and came upon a problem --- there is no multi-xid > > > SQL data type, and in fact the system catalogs that store mxid values > > > use xid, e.g.: > > > > > > relminmxid | xid | not null > > > > > > With no mxid data type, there is no way to do function overloading to > > > cause age to call the mxid variant. > > > > > > Should we use an explicit mxid_age() function name? Add an mxid data > > > type? > > > > Maybe both. But mxid_age() is probably the simpler way forward just to > > start. > > OK, patch applied using mxid_age() and no new data type. Applied. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
On Wed, Sep 3, 2014 at 05:17:17PM -0400, Robert Haas wrote:
> > I had a look at this and came upon a problem --- there is no multi-xid
> > SQL data type, and in fact the system catalogs that store mxid values
> > use xid, e.g.:
> >
> > relminmxid | xid | not null
> >
> > With no mxid data type, there is no way to do function overloading to
> > cause age to call the mxid variant.
> >
> > Should we use an explicit mxid_age() function name? Add an mxid data
> > type?
>
> Maybe both. But mxid_age() is probably the simpler way forward just to start.
OK, patch applied using mxid_age() and no new data type.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
new file mode 100644
index cf174f0..d692308
*** a/doc/src/sgml/maintenance.sgml
--- b/doc/src/sgml/maintenance.sgml
*** HINT: Stop the postmaster and vacuum th
*** 640,646
possible multixact ID still appearing in any tuple of that table.
If this value is older than
, a whole-table
! scan is forced. Whole-table VACUUM scans, regardless of
what causes them, enable advancing the value for that table.
Eventually, as all tables in all databases are scanned and their
oldest multixact values are advanced, on-disk storage for older
--- 640,651
possible multixact ID still appearing in any tuple of that table.
If this value is older than
, a whole-table
! scan is forced. mxid_age() can be used on
! pg_class.relminmxid to find its age.
!
!
!
! Whole-table VACUUM scans, regardless of
what causes them, enable advancing the value for that table.
Eventually, as all tables in all databases are scanned and their
oldest multixact values are advanced, on-disk storage for older
diff --git a/src/backend/utils/adt/xid.c b/src/backend/utils/adt/xid.c
new file mode 100644
index 602a9e5..ecb3cf5
*** a/src/backend/utils/adt/xid.c
--- b/src/backend/utils/adt/xid.c
***
*** 16,21
--- 16,22
#include
+ #include "access/multixact.h"
#include "access/transam.h"
#include "access/xact.h"
#include "libpq/pqformat.h"
*** xid_age(PG_FUNCTION_ARGS)
*** 100,105
--- 101,121
PG_RETURN_INT32(INT_MAX);
PG_RETURN_INT32((int32) (now - xid));
+ }
+
+ /*
+ * mxid_age - compute age of a multi XID (relative to latest stable mxid)
+ */
+ Datum
+ mxid_age(PG_FUNCTION_ARGS)
+ {
+ TransactionId xid = PG_GETARG_TRANSACTIONID(0);
+ MultiXactId now = ReadNextMultiXactId();
+
+ if (!MultiXactIdIsValid(xid))
+ PG_RETURN_INT32(INT_MAX);
+
+ PG_RETURN_INT32((int32) (now - xid));
}
/*
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
new file mode 100644
index e1b62a5..e0875b7
*** a/src/include/catalog/catversion.h
--- b/src/include/catalog/catversion.h
***
*** 53,58
*/
/* mmddN */
! #define CATALOG_VERSION_NO 201408281
#endif
--- 53,58
*/
/* mmddN */
! #define CATALOG_VERSION_NO 201409021
#endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
new file mode 100644
index 5176ed0..09e138b
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*** DATA(insert OID = 1180 ( abstime PG
*** 1277,1282
--- 1277,1284
DESCR("convert timestamp with time zone to abstime");
DATA(insert OID = 1181 ( age PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 23 "28" _null_ _null_ _null_ _null_ xid_age _null_ _null_ _null_ ));
DESCR("age of a transaction ID, in transactions before current transaction");
+ DATA(insert OID = 3218 ( mxid_age PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 23 "28" _null_ _null_ _null_ _null_ mxid_age _null_ _null_ _null_ ));
+ DESCR("age of a multi-transaction ID, in multi-transactions before current multi-transaction");
DATA(insert OID = 1188 ( timestamptz_mi PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1186 "1184 1184" _null_ _null_ _null_ _null_ timestamp_mi _null_ _null_ _null_ ));
DATA(insert OID = 1189 ( timestamptz_pl_interval PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0 1184 "1184 1186" _null_ _null_ _null_ _null_ timestamptz_pl_interval _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
new file mode 100644
index 78cc0a0..d88e7a3
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
*** extern Datum xidrecv(PG_FUNCTION_ARGS);
*** 845,850
--- 845,851
extern Datum xidsend(PG_FUNCTION_ARGS);
extern Datum xideq(PG_FUNCTION_ARGS);
extern Datum xid_age(PG_FUNCTION_ARGS);
+ extern Datum mxid_age(PG_FUNCTION_ARGS);
extern int xidComparator(const void *arg1, const void *arg2);
extern Datum cidin(PG_FUNCTION_ARGS);
extern Datum
Re: [HACKERS] Need Multixact Freezing Docs
On Tue, Sep 2, 2014 at 8:18 PM, Bruce Momjian wrote: > On Thu, Aug 28, 2014 at 09:32:17AM -0700, Josh Berkus wrote: >> On 08/28/2014 09:09 AM, Alvaro Herrera wrote: >> > Josh Berkus wrote: >> >> On 04/16/2014 01:30 PM, Alvaro Herrera wrote: >> >>> Josh Berkus wrote: >> >> > You can see the current multixact value in pg_controldata output. Keep >> > timestamped values of that somewhere (a table?) so that you can measure >> > consumption rate. I don't think we provide SQL-level access to those >> > values. >> >> Bleh. Do we provide SQL-level access in 9.4? If not, I think that's a >> requirement before release. >> >>> >> >>> Yeah, good idea. Want to propose a patch? >> >> >> >> Yeah, lemme dig into this. I really think we need it for 9.4, feature >> >> frozen or not. >> >> Got sidetracked by JSONB. > > I had a look at this and came upon a problem --- there is no multi-xid > SQL data type, and in fact the system catalogs that store mxid values > use xid, e.g.: > > relminmxid | xid | not null > > With no mxid data type, there is no way to do function overloading to > cause age to call the mxid variant. > > Should we use an explicit mxid_age() function name? Add an mxid data > type? Maybe both. But mxid_age() is probably the simpler way forward just to start. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
On Thu, Aug 28, 2014 at 09:32:17AM -0700, Josh Berkus wrote: > On 08/28/2014 09:09 AM, Alvaro Herrera wrote: > > Josh Berkus wrote: > >> On 04/16/2014 01:30 PM, Alvaro Herrera wrote: > >>> Josh Berkus wrote: > > > You can see the current multixact value in pg_controldata output. Keep > > timestamped values of that somewhere (a table?) so that you can measure > > consumption rate. I don't think we provide SQL-level access to those > > values. > > Bleh. Do we provide SQL-level access in 9.4? If not, I think that's a > requirement before release. > >>> > >>> Yeah, good idea. Want to propose a patch? > >> > >> Yeah, lemme dig into this. I really think we need it for 9.4, feature > >> frozen or not. > > Got sidetracked by JSONB. I had a look at this and came upon a problem --- there is no multi-xid SQL data type, and in fact the system catalogs that store mxid values use xid, e.g.: relminmxid | xid | not null With no mxid data type, there is no way to do function overloading to cause age to call the mxid variant. Should we use an explicit mxid_age() function name? Add an mxid data type? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
On 08/28/2014 09:09 AM, Alvaro Herrera wrote: > Josh Berkus wrote: >> On 04/16/2014 01:30 PM, Alvaro Herrera wrote: >>> Josh Berkus wrote: > You can see the current multixact value in pg_controldata output. Keep > timestamped values of that somewhere (a table?) so that you can measure > consumption rate. I don't think we provide SQL-level access to those > values. Bleh. Do we provide SQL-level access in 9.4? If not, I think that's a requirement before release. >>> >>> Yeah, good idea. Want to propose a patch? >> >> Yeah, lemme dig into this. I really think we need it for 9.4, feature >> frozen or not. Got sidetracked by JSONB. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
Josh Berkus wrote: > On 04/16/2014 01:30 PM, Alvaro Herrera wrote: > > Josh Berkus wrote: > >> > >>> You can see the current multixact value in pg_controldata output. Keep > >>> timestamped values of that somewhere (a table?) so that you can measure > >>> consumption rate. I don't think we provide SQL-level access to those > >>> values. > >> > >> Bleh. Do we provide SQL-level access in 9.4? If not, I think that's a > >> requirement before release. > > > > Yeah, good idea. Want to propose a patch? > > Yeah, lemme dig into this. I really think we need it for 9.4, feature > frozen or not. Ping? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
On Wed, Apr 16, 2014 at 4:39 PM, Josh Berkus wrote: >> Hmm, are you sure it's INT_MAX and not 4244967297? Heikki reported >> that: http://www.postgresql.org/message-id/52401aea.9000...@vmware.com >> The absolute value is not important; I think that's mostly harmless. I >> don't think applying age() to a multixact value is meaningful, though; >> that's only good for Xids. > > Yeah, I'm sure: > > josh=# select relname, age(relminmxid) from pg_class; > relname |age > -+ > pg_statistic| 2147483647 > pg_type | 2147483647 > random | 2147483647 > dblink_pkey_results | 2147483647 > pg_toast_17395 | 2147483647 > > ... > > So if age() doesn't mean anything, then how are users to know when the > need to freeze? Or, in other words, this is another example of xid-freezing infrastructure that needed to be copied for mxid-freezing and wasn't. We need an analogue of age() for mxids. Perhaps just mxid_age()? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
>> So if age() doesn't mean anything, then how are users to know when the >> need to freeze? > > I don't understand. Autovacuum will freeze this automatically when the > threshold is reached. Users don't need to do anything. What I'm asking is: - how do users know if Autovacuum is keeping up with multixact feezing? - how do users get data on multixact usage so that they can tune the parameters? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
Josh Berkus wrote: > > Josh Berkus wrote: > >> > >>> You can see the current multixact value in pg_controldata output. Keep > >>> timestamped values of that somewhere (a table?) so that you can measure > >>> consumption rate. I don't think we provide SQL-level access to those > >>> values. > >> > >> Bleh. Do we provide SQL-level access in 9.4? If not, I think that's a > >> requirement before release. > > > > Yeah, good idea. Want to propose a patch? > > Yeah, lemme dig into this. I really think we need it for 9.4, feature > frozen or not. Great, thanks. > josh=# select relname, age(relminmxid) from pg_class; > relname |age > -+ > pg_statistic| 2147483647 > pg_type | 2147483647 > random | 2147483647 > dblink_pkey_results | 2147483647 > pg_toast_17395 | 2147483647 > > ... > > So if age() doesn't mean anything, then how are users to know when the > need to freeze? I don't understand. Autovacuum will freeze this automatically when the threshold is reached. Users don't need to do anything. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
On 04/16/2014 01:30 PM, Alvaro Herrera wrote: > Josh Berkus wrote: >> >>> You can see the current multixact value in pg_controldata output. Keep >>> timestamped values of that somewhere (a table?) so that you can measure >>> consumption rate. I don't think we provide SQL-level access to those >>> values. >> >> Bleh. Do we provide SQL-level access in 9.4? If not, I think that's a >> requirement before release. > > Yeah, good idea. Want to propose a patch? Yeah, lemme dig into this. I really think we need it for 9.4, feature frozen or not. Also: how do I check the multixact age of a table? There doesn't seem to be any data for this ... >>> >>> pg_class.relminmxid is the oldest multixact value that might be present >>> in a table. >> >> On every database I've tested, age(relminmxid) returns int_max. So this >> is apparently broken. > > Hmm, are you sure it's INT_MAX and not 4244967297? Heikki reported > that: http://www.postgresql.org/message-id/52401aea.9000...@vmware.com > The absolute value is not important; I think that's mostly harmless. I > don't think applying age() to a multixact value is meaningful, though; > that's only good for Xids. Yeah, I'm sure: josh=# select relname, age(relminmxid) from pg_class; relname |age -+ pg_statistic| 2147483647 pg_type | 2147483647 random | 2147483647 dblink_pkey_results | 2147483647 pg_toast_17395 | 2147483647 ... So if age() doesn't mean anything, then how are users to know when the need to freeze? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
Josh Berkus wrote: > > > You can see the current multixact value in pg_controldata output. Keep > > timestamped values of that somewhere (a table?) so that you can measure > > consumption rate. I don't think we provide SQL-level access to those > > values. > > Bleh. Do we provide SQL-level access in 9.4? If not, I think that's a > requirement before release. Yeah, good idea. Want to propose a patch? > >> Also: how do I check the multixact age of a table? There doesn't seem > >> to be any data for this ... > > > > pg_class.relminmxid is the oldest multixact value that might be present > > in a table. > > On every database I've tested, age(relminmxid) returns int_max. So this > is apparently broken. Hmm, are you sure it's INT_MAX and not 4244967297? Heikki reported that: http://www.postgresql.org/message-id/52401aea.9000...@vmware.com The absolute value is not important; I think that's mostly harmless. I don't think applying age() to a multixact value is meaningful, though; that's only good for Xids. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
> You can see the current multixact value in pg_controldata output. Keep > timestamped values of that somewhere (a table?) so that you can measure > consumption rate. I don't think we provide SQL-level access to those > values. Bleh. Do we provide SQL-level access in 9.4? If not, I think that's a requirement before release. Telling users to monitor a setting using a restricted-permission command-line utility which produces a version-specific text file they have to parse is not going to win us a lot of fans. > >> Also: how do I check the multixact age of a table? There doesn't seem >> to be any data for this ... > > pg_class.relminmxid is the oldest multixact value that might be present > in a table. On every database I've tested, age(relminmxid) returns int_max. So this is apparently broken. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
Josh Berkus wrote: > On 04/15/2014 02:25 PM, Josh Berkus wrote: > > Hackers, > > > > We need documentation on how users should intelligently set the > > multixact freeze settings. I'm happy to write the actual text, but I > > definitely don't have any idea how to set these myself. Under what > > circumstances should they be different from freeze_max_age? How? Measure consumption rate of multixacts, compare to consumption rate of xids, and set the freeze ages so that they are reached more-or-less at the same time, so that freezing for any of them would also freeze the other one. You need to set both table_freeze_ages to values that would be reached later than both min_freeze_ages would be reached, if you get what I mean. The idea is that full scan of a table would fix both things at once, saving a followup full scan shortly after the first one. You can see the current multixact value in pg_controldata output. Keep timestamped values of that somewhere (a table?) so that you can measure consumption rate. I don't think we provide SQL-level access to those values. > Also: how do I check the multixact age of a table? There doesn't seem > to be any data for this ... pg_class.relminmxid is the oldest multixact value that might be present in a table. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Need Multixact Freezing Docs
On 04/15/2014 02:25 PM, Josh Berkus wrote: > Hackers, > > We need documentation on how users should intelligently set the > multixact freeze settings. I'm happy to write the actual text, but I > definitely don't have any idea how to set these myself. Under what > circumstances should they be different from freeze_max_age? How? > Also: how do I check the multixact age of a table? There doesn't seem to be any data for this ... -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Need Multixact Freezing Docs
Hackers, We need documentation on how users should intelligently set the multixact freeze settings. I'm happy to write the actual text, but I definitely don't have any idea how to set these myself. Under what circumstances should they be different from freeze_max_age? How? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
