Thread: Need Multixact Freezing Docs

Need Multixact Freezing Docs

From
Josh Berkus
Date:
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



Re: Need Multixact Freezing Docs

From
Josh Berkus
Date:
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



Re: Need Multixact Freezing Docs

From
Alvaro Herrera
Date:
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



Re: Need Multixact Freezing Docs

From
Josh Berkus
Date:
> 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



Re: Need Multixact Freezing Docs

From
Alvaro Herrera
Date:
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.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



Re: Need Multixact Freezing Docs

From
Josh Berkus
Date:
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.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                            | 2147483647pg_type
                         | 2147483647random                                  | 2147483647dblink_pkey_results
        | 2147483647pg_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



Re: Need Multixact Freezing Docs

From
Alvaro Herrera
Date:
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



Re: Need Multixact Freezing Docs

From
Josh Berkus
Date:
>> 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



Re: Need Multixact Freezing Docs

From
Robert Haas
Date:
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: http://www.postgresql.org/message-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



Re: Need Multixact Freezing Docs

From
Alvaro Herrera
Date:
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



Re: Need Multixact Freezing Docs

From
Josh Berkus
Date:
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



Re: Need Multixact Freezing Docs

From
Bruce Momjian
Date:
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. +



Re: Need Multixact Freezing Docs

From
Robert Haas
Date:
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



Re: Need Multixact Freezing Docs

From
Bruce Momjian
Date:
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. +

Attachment

Re: Need Multixact Freezing Docs

From
Bruce Momjian
Date:
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. +



Re: Need Multixact Freezing Docs

From
Alvaro Herrera
Date:
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

Attachment

Re: Need Multixact Freezing Docs

From
Jim Nasby
Date:
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



Re: Need Multixact Freezing Docs

From
Alvaro Herrera
Date:
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

Attachment