Обсуждение: 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
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
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
> 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
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
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
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
>> 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
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
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
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
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. +
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
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. +
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
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