pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcount on transaction ID
6+ messages / 2 participants
[nested] [flat]
* count on transaction ID
@ 2012-03-09 15:30 ddgs <gsmaths@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: ddgs @ 2012-03-09 15:30 UTC (permalink / raw)
To: pgsql-performance
It is a discussion about the transaction ID wraparound in PostgreSQL.
However, what is the fundamental definition if transaction ID.
select * from table where ID=1:10000
it is consider as one transaction or 10000 transactions.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/count-on-transaction-ID-tp5550894p5550894.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: count on transaction ID
@ 2012-03-12 14:40 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: ddgs <gsmaths@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2012-03-12 14:40 UTC (permalink / raw)
To: ddgs <gsmaths@gmail.com>; pgsql-performance
ddgs <gsmaths@gmail.com> wrote:
> It is a discussion about the transaction ID wraparound in
> PostgreSQL.
Hopefully you've seen this:
http://www.postgresql.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
> However, what is the fundamental definition if transaction ID.
As the cited page states, it is 32 bits. It is considered a
"circular" number space.
> select * from table where ID=1:10000
> it is consider as one transaction or 10000 transactions.
In SQL it would be a syntax error; it doesn't really mean anything.
And it seems that you may be confused about the difference between
transaction IDs, object IDs, and user-defined ID columns on tables.
What is the "ID=1:10000" syntax supposed to mean?
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: count on transaction ID
@ 2012-03-12 15:46 ddgs <gsmaths@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 6+ messages in thread
From: ddgs @ 2012-03-12 15:46 UTC (permalink / raw)
To: pgsql-performance
It is just a simple idea syntax, not the exact one.
Anyway, I am wonder how to get the 2^31 transaction IDs to cause the failure
But I get the wraparound error warning when I delete a large no. of rows.
So the wraparound failure is due to what reason, that I still have no idea
(at least not the transaction limit, I guess)
--
View this message in context: http://postgresql.1045698.n5.nabble.com/count-on-transaction-ID-tp5550894p5558198.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: count on transaction ID
@ 2012-03-12 16:05 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: ddgs <gsmaths@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2012-03-12 16:05 UTC (permalink / raw)
To: ddgs <gsmaths@gmail.com>; pgsql-performance
ddgs <gsmaths@gmail.com> wrote:
> It is just a simple idea syntax, not the exact one.
Then it doesn't seem possible to give an exact answer as to what it
will do. The effect on transaction IDs will depend on whether
you're talking about one DELETE statement with a range of values in
the WHERE clause or a series of DELETE statements, one for each row,
which are not bounded by a transaction through some other means.
> Anyway, I am wonder how to get the 2^31 transaction IDs to cause
> the failure
One of the easiest ways to get to such a failure is to disable
autovacuum or make it less aggressive.
> But I get the wraparound error warning when I delete a large no.
> of rows. So the wraparound failure is due to what reason, that I
> still have no idea (at least not the transaction limit, I guess)
From what little information you've provided, it's hard to tell. It
might be that the DELETEs are generating a very large number of
database transactions, each of which is consuming a transaction ID.
It might be that your DELETE is running for so long that it's
interfering with autovacuum's ability to clean up after a high
volume of other transactions. It could be that you are simply not
vacuuming aggressively enough, and the DELETE happened to come along
at the point where the issue became critical, and is thus an
"innocent bystander" and not the culprit.
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: count on transaction ID
@ 2012-03-12 16:34 ddgs <gsmaths@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 6+ messages in thread
From: ddgs @ 2012-03-12 16:34 UTC (permalink / raw)
To: pgsql-performance
Is it a good starting point to the basic reason of doing vacuum?
from the manual,
"PostgreSQL's VACUUM command must be run on a regular basis for several
reasons:
To recover disk space occupied by updated or deleted rows.
To update data statistics used by the PostgreSQL query planner.
To protect against loss of very old data due to transaction ID wraparound.
"
It seems that only transaction ID wraparound can cause system failure in
loading-in new data.
For the dead rows problem, it just a matter of space; the data statistics is
just relate to the query performance.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/count-on-transaction-ID-tp5550894p5558345.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: count on transaction ID
@ 2012-03-12 16:56 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: ddgs <gsmaths@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2012-03-12 16:56 UTC (permalink / raw)
To: ddgs <gsmaths@gmail.com>; pgsql-performance
ddgs <gsmaths@gmail.com> wrote:
> Is it a good starting point to the basic reason of doing vacuum?
> from the manual,
> "PostgreSQL's VACUUM command must be run on a regular basis for
> several reasons:
> To recover disk space occupied by updated or deleted rows.
> To update data statistics used by the PostgreSQL query planner.
> To protect against loss of very old data due to transaction ID
> wraparound.
> "
The entire page from which that quote is pulled makes a good
starting point. Turning off autovacuum or making it less aggressive
without really understanding everything on that page will generally
result in problems which take far longer to solve than any marginal
savings obtained by making the change.
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-03-12 16:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-09 15:30 count on transaction ID ddgs <gsmaths@gmail.com>
2012-03-12 14:40 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-03-12 15:46 ` ddgs <gsmaths@gmail.com>
2012-03-12 16:05 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-03-12 16:34 ` ddgs <gsmaths@gmail.com>
2012-03-12 16:56 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox