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

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 Herrera http://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

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

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: /messages/by-id/52401AEA.9000608@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 Herrera http://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

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: /messages/by-id/52401AEA.9000608@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

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 Herrera http://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

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

On Wed, Apr 16, 2014 at 4:39 PM, Josh Berkus <josh@agliodbs.com> wrote:

Hmm, are you sure it's INT_MAX and not 4244967297? Heikki reported
that: /messages/by-id/52401AEA.9000608@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

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 Herrera http://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

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

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 <bruce@momjian.us> 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

On Tue, Sep 2, 2014 at 8:18 PM, Bruce Momjian <bruce@momjian.us> 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

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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

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 <bruce@momjian.us> 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

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 Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

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 Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services