Re: [GENERAL] Transaction ID not logged if no explicit transaction used
Tom Lane wrote: > Alvaro Herrera writes: > > Mart�n Marqu�s wrote: > >> This really gives little use for recovery_target_xid. :( > > > Hmm, you can still use pg_xlogdump to figure it out from the actual WAL, > > which has the correct XIDs. It's obviously a worse solution though from > > the user's POV, because it's hard to figure out what WAL record > > corresponds to the change you care about ... > > To what extent does the commit_ts infrastructure fix this? I don't think it does at all. You could try to find out the XID using a timestamp you obtain from the log file (knowing that the lookups are the opposite way, i.e. you give it an XID and it returns a timestamp); but if that's the scenario, I think it's simpler to use the timestamp in recovery_target_time directly. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction ID not logged if no explicit transaction used
Alvaro Herrera writes: > Martín Marqués wrote: >> This really gives little use for recovery_target_xid. :( > Hmm, you can still use pg_xlogdump to figure it out from the actual WAL, > which has the correct XIDs. It's obviously a worse solution though from > the user's POV, because it's hard to figure out what WAL record > corresponds to the change you care about ... To what extent does the commit_ts infrastructure fix this? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction ID not logged if no explicit transaction used
Martín Marqués wrote: > El 10/02/16 a las 21:46, Tom Lane escribió: > > We could maybe fix this by redefining %x as "the current or most recent > > xid", so that it'd still be valid for messages issued post-commit. > > But I'm afraid that would add about as many bad behaviors as it would > > remove. In your example above, that would result in a pretty misleading > > xid attached to the "begin" statement, since at that point we have > > started a new transaction but not assigned it any xid. > > This really gives little use for recovery_target_xid. :( Hmm, you can still use pg_xlogdump to figure it out from the actual WAL, which has the correct XIDs. It's obviously a worse solution though from the user's POV, because it's hard to figure out what WAL record corresponds to the change you care about ... -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction ID not logged if no explicit transaction used
El 10/02/16 a las 21:46, Tom Lane escribió: > > Think you're outta luck on that. If we logged the duration before > commit, it would be entirely misleading for short commands, because > the time needed to commit wouldn't be included. So we log it after, > when there's no longer any active transaction. Any other way to log the DDL and DML statements (maybe delay writing the log until the xid is available)? Yes, I know the complexity of this, but I'm surprised this hasn't come up in the list before (maybe it did and i missed the mail). > We could maybe fix this by redefining %x as "the current or most recent > xid", so that it'd still be valid for messages issued post-commit. > But I'm afraid that would add about as many bad behaviors as it would > remove. In your example above, that would result in a pretty misleading > xid attached to the "begin" statement, since at that point we have > started a new transaction but not assigned it any xid. This really gives little use for recovery_target_xid. :( Regards, -- Martín Marquéshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction ID not logged if no explicit transaction used
El 10/02/16 a las 20:11, Adrian Klaver escribió: >> >> So, my question is: Is this a bug, or a feature? I recall being able to >> log xids on DDLs but can't find the correct settings now. > > Maybe?: > > %v Virtual transaction ID (backendID/localXID) AFAICS that value won't help if I need the xid to do a PITR up to that xid not included. Regards, -- Martín Marquéshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction ID not logged if no explicit transaction used
=?UTF-8?Q?Mart=c3=adn_Marqu=c3=a9s?= writes: > [ log_line_prefix %x frequently reports zero ] > <2016-02-10 17:41:19 EST [5729]: [1] xid=0 > db=data,user=postgres,app=psql,client=[local]>LOG: duration: 17.242 ms > statement: create table test_xid (id int); > <2016-02-10 17:41:21 EST [5729]: [2] xid=0 > db=data,user=postgres,app=psql,client=[local]>LOG: duration: 0.055 ms > statement: begin; > <2016-02-10 17:41:32 EST [5729]: [3] xid=31063 > db=data,user=postgres,app=psql,client=[local]>LOG: duration: 6.858 ms > statement: drop table test_xid; > <2016-02-10 17:41:34 EST [5729]: [4] xid=0 > db=data,user=postgres,app=psql,client=[local]>LOG: duration: 2.540 ms > statement: end; > It's clear that if the command isn't executed in an explicit > transaction, you don't get the xid in the logs. Very annoying! Think you're outta luck on that. If we logged the duration before commit, it would be entirely misleading for short commands, because the time needed to commit wouldn't be included. So we log it after, when there's no longer any active transaction. We could maybe fix this by redefining %x as "the current or most recent xid", so that it'd still be valid for messages issued post-commit. But I'm afraid that would add about as many bad behaviors as it would remove. In your example above, that would result in a pretty misleading xid attached to the "begin" statement, since at that point we have started a new transaction but not assigned it any xid. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction ID not logged if no explicit transaction used
On 02/10/2016 02:58 PM, Martín Marqués wrote: Hi, I've been fooling around on a test environment where I wanted to run some PITR tests using recovery_target_xid. So I started setting up postgresql.conf with log_statement='ddl' (and 'mod' also) and the %x placeholder in log_line_prefix: Odd result was that I always got a zero as the xid. So after some chats (and a power outage at home :( ) I tried setting log_statement to 'none' and log_min_duration_statement = 0 with the same log_line_prefix: I ran these commands to test: data=# create table test_xid (id int); CREATE TABLE data=# begin; BEGIN data=# drop table test_xid; DROP TABLE data=# end; COMMIT Which give these logs: <2016-02-10 17:41:19 EST [5729]: [1] xid=0 db=data,user=postgres,app=psql,client=[local]>LOG: duration: 17.242 ms statement: create table test_xid (id int); <2016-02-10 17:41:21 EST [5729]: [2] xid=0 db=data,user=postgres,app=psql,client=[local]>LOG: duration: 0.055 ms statement: begin; <2016-02-10 17:41:32 EST [5729]: [3] xid=31063 db=data,user=postgres,app=psql,client=[local]>LOG: duration: 6.858 ms statement: drop table test_xid; <2016-02-10 17:41:34 EST [5729]: [4] xid=0 db=data,user=postgres,app=psql,client=[local]>LOG: duration: 2.540 ms statement: end; It's clear that if the command isn't executed in an explicit transaction, you don't get the xid in the logs. Very annoying! Worst, I guess most people would want the xid of a DROP TABLE to be logged, as well as with other DDLs and/or DMLs with log_statement configured accordingly. I suppose this is not so simple to achieve. So, my question is: Is this a bug, or a feature? I recall being able to log xids on DDLs but can't find the correct settings now. Maybe?: %v Virtual transaction ID (backendID/localXID) -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Transaction ID not logged if no explicit transaction used
Hi, I've been fooling around on a test environment where I wanted to run some PITR tests using recovery_target_xid. So I started setting up postgresql.conf with log_statement='ddl' (and 'mod' also) and the %x placeholder in log_line_prefix: Odd result was that I always got a zero as the xid. So after some chats (and a power outage at home :( ) I tried setting log_statement to 'none' and log_min_duration_statement = 0 with the same log_line_prefix: I ran these commands to test: data=# create table test_xid (id int); CREATE TABLE data=# begin; BEGIN data=# drop table test_xid; DROP TABLE data=# end; COMMIT Which give these logs: <2016-02-10 17:41:19 EST [5729]: [1] xid=0 db=data,user=postgres,app=psql,client=[local]>LOG: duration: 17.242 ms statement: create table test_xid (id int); <2016-02-10 17:41:21 EST [5729]: [2] xid=0 db=data,user=postgres,app=psql,client=[local]>LOG: duration: 0.055 ms statement: begin; <2016-02-10 17:41:32 EST [5729]: [3] xid=31063 db=data,user=postgres,app=psql,client=[local]>LOG: duration: 6.858 ms statement: drop table test_xid; <2016-02-10 17:41:34 EST [5729]: [4] xid=0 db=data,user=postgres,app=psql,client=[local]>LOG: duration: 2.540 ms statement: end; It's clear that if the command isn't executed in an explicit transaction, you don't get the xid in the logs. Very annoying! Worst, I guess most people would want the xid of a DROP TABLE to be logged, as well as with other DDLs and/or DMLs with log_statement configured accordingly. I suppose this is not so simple to achieve. So, my question is: Is this a bug, or a feature? I recall being able to log xids on DDLs but can't find the correct settings now. -- Martín Marquéshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction ID Wraparound Monitoring
On 8/4/15 2:47 AM, Jan Keirse wrote: CAST (age(relfrozenxid) AS real) / CAST(trunc(((2^32)/2)-1-100) AS real) >AS perc_until_wraparound_server_freeze > > >(Note that we do this at the table level rather than the database level like >you did, though, so that we have the information we need to tune the >settings for individual tables.) Thanks for the correction regarding the 1.000.000 safety margin! I chose to monitor only the total value value to limit the amount of extra metrics in the monitoring database. In case the value increased we'll execute the queries to find out what table(s) is/are causing the problem interactively. Something to be aware of; with certain workloads you can actually run out of MXIDs faster than XIDs, and I don't think there's any easy way to monitor MXID consumption. BTW, you might find my talk from pgCon or the related video useful: http://www.pgcon.org/2015/schedule/events/829.en.html https://www.youtube.com/watch?v=b1fcvkl0ffQ -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction ID Wraparound Monitoring
On Tue, Aug 4, 2015 at 6:24 AM, William Dunn wrote:
> Hello Jan,
>
> I think your calculation is slightly off because per the docs when
> PostgreSQL comes within 1 million of the age at which an actual wraparound
> occurs it will go into the safety shutdown mode. Thus the calculation should
> be ((2^32)-1)/2-100 rather than just ((2^32)-1)/2 as I think you are
> using.
>
> When I first started building out my group's PostgreSQL monitoring solution
> I too found the wording of transaction freeze to be a bit difficult to
> understand. For my team's internal documentation I have summarized it as
> follows, I hope it might be more clear:
>
> ...normal XIDs are compared using modulo-2^32 arithmetic, which means that
> ~(2^32-1)/2- transactions appear in the future and ~(2^32-1)/2 transactions
> appear in the past.
>
> This [Transaction ID freeze] behavior of autovacuum is primarily dependent
> on the settings autovacuum_freeze_table_age and autovacuum_freeze_max_age,
> which are set as database defaults but can also be specified on a per table
> basis (as storage parameters in CREATE TABLE or ALTER TABLE)
>
> When a table's oldest transaction reaches autovacuum_freeze_table_age, the
> next autovacuum that is performed on that table will be a vacuum freeze
>
> PostgreSQL implicitly caps autovacuum_freeze_table_age at
> 0.95*autovacuum_freeze_max_age.
>
> When a table reaches autovacuum_freeze_max_age PostgreSQL will force an
> autovacuum freeze on that table, even if the table would not otherwise be
> autovacuumed or autovacuum is disabled.
>
> PostgreSQL implicitly caps autovacuum_freeze_max_age at 2 billion
> (20)
>
> The actual age that a wraparound occurs is ((2^32)/2)-1. When a PostgreSQL
> database comes within 1 million of this age (2^32/2-1-100) the database
> will go into the safety shutdown mode" and no longer accept commands,
> including the vacuum commands, and your only recovery option is to stop the
> server and use a single-user backend (where shutdown mode is not enforced)
> to execute VACUUM. This should, obviously, be avoided at all costs.
>
> References:
>
> http://www.PostgreSQL.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
> http://www.PostgreSQL.org/docs/current/static/runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE
> http://www.PostgreSQL.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE
>
>
> Based on the above explanation we consider the following to be the most
> correct check for how close you are to an actual wraparound freeze:
>
> CAST (age(relfrozenxid) AS real) / CAST(trunc(((2^32)/2)-1-100) AS real)
> AS perc_until_wraparound_server_freeze
>
>
> (Note that we do this at the table level rather than the database level like
> you did, though, so that we have the information we need to tune the
> settings for individual tables.)
Thanks for the correction regarding the 1.000.000 safety margin! I
chose to monitor only the total value value to limit the amount of
extra metrics in the monitoring database. In case the value increased
we'll execute the queries to find out what table(s) is/are causing the
problem interactively.
> However it is better to set autovacuum max freeze age well below that value
> and monitor that instead. Autovacuum should always do a vacuum freeze for a
> table that has exceeded max freeze age, and if you are monitoring for that
> you should avoid a wrap around freeze:
>
> CAST (age(relfrozenxid) AS real) / CAST ((least(autovacuum_freeze_max_age,
> 20)) AS real) AS perc_until_freeze_max_age
>
>
> And ensure that value does not exceed 100%. Though it is important to note
> that max freeze age can be set on a per table basis, so to get the true
> autovacuum_freeze_max_age of a table (or the real max of the database) you
> would need to check the reloptions field of pg_class for that table and only
> if there is no value specified for '%autovacuum_freeze_table_age%' use
> current_setting('autovacuum_freeze_max_age')
I'll see to add this one to the monitoring too. Thanks for your clarifications!
--
DISCLAIMER
http://www.tvh.com/glob/en/email-disclaimer
"This message is delivered to all addressees subject to the conditions
set forth in the attached disclaimer, which is an integral part of this
message."
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction ID Wraparound Monitoring
Hello Jan,
I think your calculation is slightly off because per the docs when
PostgreSQL comes within 1 million of the age at which an actual wraparound
occurs it will go into the safety shutdown mode. Thus the calculation
should be ((2^32)-1)/2-100 rather than just ((2^32)-1)/2 as I think you
are using.
When I first started building out my group's PostgreSQL monitoring solution
I too found the wording of transaction freeze to be a bit difficult to
understand. For my team's internal documentation I have summarized it as
follows, I hope it might be more clear:
...normal XIDs are compared using modulo-2^32 arithmetic, which means that
~(2^32-1)/2- transactions appear in the future and ~(2^32-1)/2 transactions
appear in the past.
This [Transaction ID freeze] behavior of autovacuum is primarily dependent
on the settings autovacuum_freeze_table_age and autovacuum_freeze_max_age,
which are set as database defaults but can also be specified on a per table
basis (as storage parameters in CREATE TABLE or ALTER TABLE)
- When a table's oldest transaction reaches autovacuum_freeze_table_age,
the next autovacuum that is performed on that table will be a vacuum freeze
- PostgreSQL implicitly caps autovacuum_freeze_table_age at
0.95*autovacuum_freeze_max_age.
- When a table reaches autovacuum_freeze_max_age PostgreSQL will force
an autovacuum freeze on that table, even if the table would not otherwise
be autovacuumed or autovacuum is disabled.
- PostgreSQL implicitly caps autovacuum_freeze_max_age at 2 billion
(20)
The actual age that a wraparound occurs is ((2^32)/2)-1. When a PostgreSQL
database comes within 1 million of this age (2^32/2-1-100) the database
will go into the safety shutdown mode" and no longer accept commands,
including the vacuum commands, and your only recovery option is to stop the
server and use a single-user backend (where shutdown mode is not enforced)
to execute VACUUM. This should, obviously, be avoided at all costs.
References:
-
http://www.PostgreSQL.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
-
http://www.PostgreSQL.org/docs/current/static/runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE
-
http://www.PostgreSQL.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE
Based on the above explanation we consider the following to be the most
correct check for how close you are to an actual wraparound freeze:
CAST (age(relfrozenxid) AS real) / CAST(trunc(((2^32)/2)-1-100) AS
real) AS perc_until_wraparound_server_freeze
(Note that we do this at the table level rather than the database level
like you did, though, so that we have the information we need to tune the
settings for individual tables.)
However it is better to set autovacuum max freeze age well below that value
and monitor that instead. Autovacuum should always do a vacuum freeze for a
table that has exceeded max freeze age, and if you are monitoring for that
you should avoid a wrap around freeze:
CAST (age(relfrozenxid) AS real) / CAST ((least(autovacuum_freeze_max_age,
20)) AS real) AS perc_until_freeze_max_age
And ensure that value does not exceed 100%. Though it is important to note
that max freeze age can be set on a per table basis, so to get the true
autovacuum_freeze_max_age of a table (or the real max of the database) you
would need to check the reloptions field of pg_class for that table and
only if there is no value specified for '%autovacuum_freeze_table_age%' use
current_setting('autovacuum_freeze_max_age')
Let me know if I missed something or can clarify it further.
*Will J. Dunn*
*willjdunn.com *
On Thu, Jul 30, 2015 at 11:44 AM, Adrian Klaver
wrote:
> On 07/30/2015 08:41 AM, Jan Keirse wrote:
>
>> On Thu, Jul 30, 2015 at 2:56 PM, Adrian Klaver
>> wrote:
>>
>>> On 07/30/2015 02:55 AM, Jan Keirse wrote:
>>>
Hello,
we have some very write heavy databases and I have our monitoring
system watch the transaction age of my databases to be alerted before
we get into problems in case autovacuum can't keep up to avoid
transaction ID wraparound.
The query I am executing is this:
SELECT max(age(pg_database.datfrozenxid)) / 2147483648.0 * 100.0 AS
"Percentage of transaction ID's used" FROM pg_database;
My believe was that if this reaches 100 the database will stop
accepting writes and one must vacuum. I have set alerts on 50 and 90,
the result is around 9 so my believe was autovacuum is working fine
for my workload.
I often see autovacuum kicking in to prevent XID Wraparround, I
thought that was just to be on the safe side and vacuum well before
it's too late.
However today I saw this post:
http://blog.getsentry.com/2015/07/23/transaction-id-wraparound-in-postgres.html
The following line has me worried:
... that dat
Re: [GENERAL] Transaction ID Wraparound Monitoring
On 07/30/2015 08:41 AM, Jan Keirse wrote: On Thu, Jul 30, 2015 at 2:56 PM, Adrian Klaver wrote: On 07/30/2015 02:55 AM, Jan Keirse wrote: Hello, we have some very write heavy databases and I have our monitoring system watch the transaction age of my databases to be alerted before we get into problems in case autovacuum can't keep up to avoid transaction ID wraparound. The query I am executing is this: SELECT max(age(pg_database.datfrozenxid)) / 2147483648.0 * 100.0 AS "Percentage of transaction ID's used" FROM pg_database; My believe was that if this reaches 100 the database will stop accepting writes and one must vacuum. I have set alerts on 50 and 90, the result is around 9 so my believe was autovacuum is working fine for my workload. I often see autovacuum kicking in to prevent XID Wraparround, I thought that was just to be on the safe side and vacuum well before it's too late. However today I saw this post: http://blog.getsentry.com/2015/07/23/transaction-id-wraparound-in-postgres.html The following line has me worried: ... that database is going to reach a situation where the XID counter has reached its maximum value. The absolute peak is something around 2 billion, but it can be far lower than that in some situations... Could someone shed some light on this? Is my query insufficient? Can the transaction wrapparound freeze problem indeed occur earlier? And if so, could someone suggest a better query to monitor? I would look at: http://www.postgresql.org/docs/9.4/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND Which includes some query examples. Yes, I have seen that documentation and it is because of it that I believed that my queries were ok, but now I think I may be misinterpreting or misunderstanding the documentation and have to look at more information, like autovacuum_multixact_freeze_max_age? Well if you click on the parameters in the above page you will go to their definitions: So for autovacuum_multixact_freeze_max_age: http://www.postgresql.org/docs/9.4/interactive/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction ID Wraparound Monitoring
On Thu, Jul 30, 2015 at 2:56 PM, Adrian Klaver wrote: > On 07/30/2015 02:55 AM, Jan Keirse wrote: >> >> Hello, >> >> we have some very write heavy databases and I have our monitoring >> system watch the transaction age of my databases to be alerted before >> we get into problems in case autovacuum can't keep up to avoid >> transaction ID wraparound. >> >> The query I am executing is this: >> SELECT max(age(pg_database.datfrozenxid)) / 2147483648.0 * 100.0 AS >> "Percentage of transaction ID's used" FROM pg_database; >> >> My believe was that if this reaches 100 the database will stop >> accepting writes and one must vacuum. I have set alerts on 50 and 90, >> the result is around 9 so my believe was autovacuum is working fine >> for my workload. >> I often see autovacuum kicking in to prevent XID Wraparround, I >> thought that was just to be on the safe side and vacuum well before >> it's too late. >> >> However today I saw this post: >> >> http://blog.getsentry.com/2015/07/23/transaction-id-wraparound-in-postgres.html >> >> The following line has me worried: >> ... that database is going to reach a situation where the XID counter >> has reached its maximum value. The absolute peak is something around 2 >> billion, but it can be far lower than that in some situations... >> >> Could someone shed some light on this? Is my query insufficient? Can >> the transaction wrapparound freeze problem indeed occur earlier? And >> if so, could someone suggest a better query to monitor? > > > I would look at: > > http://www.postgresql.org/docs/9.4/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND > > Which includes some query examples. Yes, I have seen that documentation and it is because of it that I believed that my queries were ok, but now I think I may be misinterpreting or misunderstanding the documentation and have to look at more information, like autovacuum_multixact_freeze_max_age? -- DISCLAIMER http://www.tvh.com/glob/en/email-disclaimer "This message is delivered to all addressees subject to the conditions set forth in the attached disclaimer, which is an integral part of this message." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction ID Wraparound Monitoring
On 07/30/2015 02:55 AM, Jan Keirse wrote: Hello, we have some very write heavy databases and I have our monitoring system watch the transaction age of my databases to be alerted before we get into problems in case autovacuum can't keep up to avoid transaction ID wraparound. The query I am executing is this: SELECT max(age(pg_database.datfrozenxid)) / 2147483648.0 * 100.0 AS "Percentage of transaction ID's used" FROM pg_database; My believe was that if this reaches 100 the database will stop accepting writes and one must vacuum. I have set alerts on 50 and 90, the result is around 9 so my believe was autovacuum is working fine for my workload. I often see autovacuum kicking in to prevent XID Wraparround, I thought that was just to be on the safe side and vacuum well before it's too late. However today I saw this post: http://blog.getsentry.com/2015/07/23/transaction-id-wraparound-in-postgres.html The following line has me worried: ... that database is going to reach a situation where the XID counter has reached its maximum value. The absolute peak is something around 2 billion, but it can be far lower than that in some situations... Could someone shed some light on this? Is my query insufficient? Can the transaction wrapparound freeze problem indeed occur earlier? And if so, could someone suggest a better query to monitor? I would look at: http://www.postgresql.org/docs/9.4/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND Which includes some query examples. Kind Regards, Jan Keirse -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Transaction ID Wraparound Monitoring
Hello, we have some very write heavy databases and I have our monitoring system watch the transaction age of my databases to be alerted before we get into problems in case autovacuum can't keep up to avoid transaction ID wraparound. The query I am executing is this: SELECT max(age(pg_database.datfrozenxid)) / 2147483648.0 * 100.0 AS "Percentage of transaction ID's used" FROM pg_database; My believe was that if this reaches 100 the database will stop accepting writes and one must vacuum. I have set alerts on 50 and 90, the result is around 9 so my believe was autovacuum is working fine for my workload. I often see autovacuum kicking in to prevent XID Wraparround, I thought that was just to be on the safe side and vacuum well before it's too late. However today I saw this post: http://blog.getsentry.com/2015/07/23/transaction-id-wraparound-in-postgres.html The following line has me worried: ... that database is going to reach a situation where the XID counter has reached its maximum value. The absolute peak is something around 2 billion, but it can be far lower than that in some situations... Could someone shed some light on this? Is my query insufficient? Can the transaction wrapparound freeze problem indeed occur earlier? And if so, could someone suggest a better query to monitor? Kind Regards, Jan Keirse -- DISCLAIMER http://www.tvh.com/glob/en/email-disclaimer "This message is delivered to all addressees subject to the conditions set forth in the attached disclaimer, which is an integral part of this message." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction ID wraparound, Oracle style
On Jan 18, 2012, at 2:15 PM, Scott Marlowe wrote: > On Wed, Jan 18, 2012 at 11:21 AM, Igor Polishchuk > wrote: >> Here is an article on a recently discovered Oracle flaw, which allows SCN to >> reach its limit. >> http://www.computerworld.com/s/article/9223506/Fundamental_Oracle_flaw_revea >> led?taxonomyId=18&pageNumber=1 >> >> Please don't beat me for posting a link for an Oracle related article. >> If you despise a very notion of mentioning Oracle, please just don't read >> the post. >> This article may be interesting to any RDBMS professional, no mater what db >> flavor he/she is working with. >> Also, this story may be a lesson for the Postgresql community on how not do >> things. I'm not a developer, but it seems that having synchronized >> transaction id between let say streaming-replicated databases would give >> some advantages if done properly. > > Wow, interesting difference between postgresql which occasionally > resets its smaller transaction id to prevent wrap whereas oracle just > uses a bigger number. If my calcs are right, Oracle has about 500 > years to figure out the wrap around limit at 16ktps etc. > > Thanks for the link, it was a fascinating read. By the way, this is called a Lamport clock. http://en.wikipedia.org/wiki/Lamport_timestamps?banner=none "On receiving a message, the receiver process sets its counter to be greater than the maximum of its own value and the received value before it considers the message received." Cheers, M -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction ID wraparound, Oracle style
On Wed, Jan 18, 2012 at 11:21 AM, Igor Polishchuk wrote: > Here is an article on a recently discovered Oracle flaw, which allows SCN to > reach its limit. > http://www.computerworld.com/s/article/9223506/Fundamental_Oracle_flaw_revea > led?taxonomyId=18&pageNumber=1 > > Please don't beat me for posting a link for an Oracle related article. > If you despise a very notion of mentioning Oracle, please just don't read > the post. > This article may be interesting to any RDBMS professional, no mater what db > flavor he/she is working with. > Also, this story may be a lesson for the Postgresql community on how not do > things. I'm not a developer, but it seems that having synchronized > transaction id between let say streaming-replicated databases would give > some advantages if done properly. Wow, interesting difference between postgresql which occasionally resets its smaller transaction id to prevent wrap whereas oracle just uses a bigger number. If my calcs are right, Oracle has about 500 years to figure out the wrap around limit at 16ktps etc. Thanks for the link, it was a fascinating read. -- To understand recursion, one must first understand recursion. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Transaction ID wraparound, Oracle style
Here is an article on a recently discovered Oracle flaw, which allows SCN to reach its limit. http://www.computerworld.com/s/article/9223506/Fundamental_Oracle_flaw_revea led?taxonomyId=18&pageNumber=1 Please don't beat me for posting a link for an Oracle related article. If you despise a very notion of mentioning Oracle, please just don't read the post. This article may be interesting to any RDBMS professional, no mater what db flavor he/she is working with. Also, this story may be a lesson for the Postgresql community on how not do things. I'm not a developer, but it seems that having synchronized transaction id between let say streaming-replicated databases would give some advantages if done properly. Regards Igor Polishchuk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] transaction ID wraparound - should I use 'VACUUM' or 'VACUUM FULL' ?
Alvaro Herrera writes: > Michal Szymanski wrote: >> In old version of Postgres we have to execute 'VACUUM FULL' to solve >> problem of transaction ID wraparound, do we need to execute 'VACUUM >> FULL' in Postgres 8.3 or 8.4 to avoid this problem? > No, plain VACUUM suffices. Just to clarify: plain VACUUM has always sufficed; in *no* version of Postgres has it ever been the case that VACUUM FULL was more useful than VACUUM to protect against wraparound. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] transaction ID wraparound - should I use 'VACUUM' or 'VACUUM FULL' ?
Michal Szymanski wrote: > Hi, > In old version of Postgres we have to execute 'VACUUM FULL' to solve > problem of transaction ID wraparound, do we need to execute 'VACUUM > FULL' in Postgres 8.3 or 8.4 to avoid this problem? No, plain VACUUM suffices. > How to check using SQL if transaction ID is close to wraparound? See age(datfrozenxid) FROM pg_database, but ignore system databases. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] transaction ID wraparound - should I use 'VACUUM' or 'VACUUM FULL' ?
Hi, In old version of Postgres we have to execute 'VACUUM FULL' to solve problem of transaction ID wraparound, do we need to execute 'VACUUM FULL' in Postgres 8.3 or 8.4 to avoid this problem? How to check using SQL if transaction ID is close to wraparound? Michal Szymanski http://blog.szymanskich.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction id wraparound problem
On Wednesday November 15 2006 4:18 pm, Morris Goldstein wrote: > If I'm vacuuming every day (or two), and not running anywhere > near 1 billion transactions a day, why am I running into > transaction id wraparound problems? > Is this just complaining that template0 and template1 haven't > been vacuumed in the over 2 billion transactions encountered > by testdb? (I never touch template0 and template1.) If that's > what's going on, I take it that I have no risk of data loss? > And is there some reason to vacuum these databases, (other > than to avoid the scary messages)? I don't believe template0 ever gets vacuumed since it doesn't allow connections. Template1 can take a "vacuum" command, and I think that might be where your message is coming from. If you vacuum it, do your messages go away? Your testdb looks like it was vacuumed about 10,000 transactions ago. Ed ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Transaction id wraparound problem
I've encountered transaction wraparound problems in a long-running
test using postgresql 7.4.8. There is no critical data at risk, but I
do need to understand the problem and get a fix into our product as
quickly as possible.
My postgres log file has messages like this:
2006-11-14 04:08:19 [27203] WARNING: some databases have not been
vacuumed in over 2 billion transactions
DETAIL: You may have already suffered transaction-wraparound data loss.
2006-11-14 17:37:37 [7988] WARNING: some databases have not been
vacuumed in over 2 billion transactions
DETAIL: You may have already suffered transaction-wraparound data loss.
2006-11-15 06:37:20 [21481] WARNING: some databases have not been
vacuumed in over 2 billion transactions
DETAIL: You may have already suffered transaction-wraparound data loss.
I also see a few of these:
2006-11-05 04:16:00 [16253] WARNING: some databases have not been
vacuumed in 2055456833 transactions
2006-11-06 19:07:29 [25211] WARNING: some databases have not been
vacuumed in 2106677625 transactions
Here is output from SELECT datname, age(datfrozenxid) FROM pg_database:
datname | age
---+-
testdb| 1073752155
template1 | -1670903080
template0 | -1670903080
Here is a summary of my test:
- The database has 16 schemas with identical table declarations in
each.
- Each schema has two large tables. One has about 10M rows currently,
and the other has 20M rows. The smaller table has two indexes and the
other has one. So the entire database has about 480M rows.
- The test has been inserting and updating data nearly continuously
for three months. (I'm testing reliability and scalability of our
application.) A typical transaction creates or updates 1-3 rows (1 in
the smaller table, 2 in the larger).
- Vacuum ("vacuum analyze verbose") runs daily. Recently, vacuums have
been taking more than a day to run, so vacuum is now running
continuously.
I am sure the vacuum is indeed running as described -- the messages
above are produced at the end of the vacuum. Also, top shows my
vacuum script running every day (or two, if the vacuum takes more than
24 hours).
If I'm vacuuming every day (or two), and not running anywhere near 1
billion transactions a day, why am I running into transaction id
wraparound problems?
Is this just complaining that template0 and template1 haven't been
vacuumed in the over 2 billion transactions encountered by testdb? (I
never touch template0 and template1.) If that's what's going on, I
take it that I have no risk of data loss? And is there some reason to
vacuum these databases, (other than to avoid the scary messages)?
Morris Goldstein
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Transaction id wraparound questions
Marc Munro <[EMAIL PROTECTED]> writes: > A recent vacuum of the entire database gave us the dreaded > "You may have already suffered transaction-wraparound data loss." > warning. If you have in fact been vacuuming *every* table including all the system catalogs, then you don't need to panic; this message just indicates that the system doesn't know you're safe. On the other hand, if you haven't ... > 2) Will a full database vacuum fix the problem? If it's fixable. I would recommend that you do this IMMEDIATELY, rather than dithering, as every transaction you execute is taking you one step closer to disaster. > 3) Can it make things worse? > 4) Other than dump and restore, what options do we have? If you can conveniently take a full dump, that might give you some measure of protection, but I'm not sure. I think that if there is anything that's already wrapped around, pg_dump will not see it :-( regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Transaction id wraparound questions
It seems that we have not been vacuuming our production database properly. We have been explicitly vacuuming all tables individually but have not vacuumed the entire database. A recent vacuum of the entire database gave us the dreaded "You may have already suffered transaction-wraparound data loss." warning. We have so far encountered no problems but I am wondering about the safest course of action right now. We cannot easily take an outage to perform a full dump and restore. Questions: 1) What is likely to happen if we encounter transaction id wraparound? 2) Will a full database vacuum fix the problem? 3) Can it make things worse? 4) Other than dump and restore, what options do we have? Information: This query: select datname, datvacuumxid, datfrozenxid from pg_database; returns this: "Production",1173213507,2246955329 "template1", 938856359, 938856359 "template0",427, 427 All responses welcomed. __ Marc Munro signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Transaction id
[EMAIL PROTECTED] wrote: Is it possible to find "system change ID" in SQL or stored procedures? By "system change ID" I understand an internal serialized number which could be used to serialize all SQL submitted into the database? Thank you in advance, Laimis Not sure what you exactly envision here. What affects the serialization of statements is more the time when their transaction commits, that the time they are executed. From the point of view of the statement, that order will be determined in the future, so how do you expect this thing to work? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
