pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion to transaction ID wraparound
12+ messages / 4 participants
[nested] [flat]
* Question to transaction ID wraparound
@ 2009-02-25 10:05 Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
0 siblings, 3 replies; 12+ messages in thread
From: Lentes, Bernd @ 2009-02-25 10:05 UTC (permalink / raw)
To: pgsql-admin
Hello ML,
I'm very new to Postgres.
In the log, i got messages concerning transaction ID wraparound.
I red the documentation and made a vaccum on that database.
In the documentation i also found the hint to look in the database with
"SELECT datname, age(datfrozenxid) FROM pg_database;", which i did.
I did the command several times in a few seconds, and this number is growing very quickly,
although i'm not expecting transactions in the database.
Sometimes the number grows by 5 in one second.
I'm also astonished about the fact that all databases grow by the same amount.
Do you have any explanations for this behaviour ?
Why are the numbers growing so quickly ?
Thanks in advance.
Bernd
--
Bernd Lentes
Systemadministration
Institut für Entwicklungsgenetik
HelmholtzZentrum münchen
bernd.lentes@helmholtz-muenchen.de
089 3187 1241
http://www.helmholtz-muenchen.de/idg
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Question to transaction ID wraparound
@ 2009-02-26 15:15 Lee Azzarello <lee@dropio.com>
parent: Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
2 siblings, 1 reply; 12+ messages in thread
From: Lee Azzarello @ 2009-02-26 15:15 UTC (permalink / raw)
To: pgsql-admin
On Wed, Feb 25, 2009 at 5:05 AM, Lentes, Bernd
<bernd.lentes@helmholtz-muenchen.de> wrote:
> Hello ML,
>
> I'm very new to Postgres.
> In the log, i got messages concerning transaction ID wraparound.
> I red the documentation and made a vaccum on that database.
> In the documentation i also found the hint to look in the database with
> "SELECT datname, age(datfrozenxid) FROM pg_database;", which i did.
> I did the command several times in a few seconds, and this number is growing very quickly,
> although i'm not expecting transactions in the database.
> Sometimes the number grows by 5 in one second.
> I'm also astonished about the fact that all databases grow by the same amount.
> Do you have any explanations for this behaviour ?
> Why are the numbers growing so quickly ?
What kind of work load is on the database?
How frequent are UPDATE/INSERT/DELETE statements called by that workload?
If you don't already have the answer to these questions, I would
recommend a reporting tool like pgfouine to get them easily.
-lee
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Question to transaction ID wraparound
@ 2009-02-26 15:47 Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
parent: Lee Azzarello <lee@dropio.com>
0 siblings, 1 reply; 12+ messages in thread
From: Lentes, Bernd @ 2009-02-26 15:47 UTC (permalink / raw)
To: pgsql-admin
Lee Azzarello wrote:
> > Sometimes the number grows by 5 in one second.
> > I'm also astonished about the fact that all databases grow
> by the same amount.
> > Do you have any explanations for this behaviour ?
> > Why are the numbers growing so quickly ?
>
> What kind of work load is on the database?
> How frequent are UPDATE/INSERT/DELETE statements called by
> that workload?
>
> If you don't already have the answer to these questions, I
> would recommend a reporting tool like pgfouine to get them easily.
>
> -lee
>
I don't have the exact answers to that question.
I think it should be a very tiny work load.
What is pgfouine ?
Bernd
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Question to transaction ID wraparound
@ 2009-02-26 15:51 AmitKumar Jain <amitjain@synechron.com>
parent: Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
0 siblings, 1 reply; 12+ messages in thread
From: AmitKumar Jain @ 2009-02-26 15:51 UTC (permalink / raw)
To: Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>; +Cc: pgsql-admin
Its a log analyzer tool through which you can trap the running queries
and have reports about update/delete/insterts which are executing at
database system.
http://pgfouine.projects.postgresql.org/
Lentes, Bernd wrote:
> Lee Azzarello wrote:
>
>>> Sometimes the number grows by 5 in one second.
>>> I'm also astonished about the fact that all databases grow
>>>
>> by the same amount.
>>
>>> Do you have any explanations for this behaviour ?
>>> Why are the numbers growing so quickly ?
>>>
>> What kind of work load is on the database?
>> How frequent are UPDATE/INSERT/DELETE statements called by
>> that workload?
>>
>> If you don't already have the answer to these questions, I
>> would recommend a reporting tool like pgfouine to get them easily.
>>
>> -lee
>>
>>
> I don't have the exact answers to that question.
> I think it should be a very tiny work load.
> What is pgfouine ?
>
> Bernd
>
>
--
*Amit Jain *
*Synechron Technology Pvt. Ltd. 4th Floor, B Wing, Block 1.2, Embassy
Techzone,*
Plot No. PL-3, Rajiv Gandhi Infotech Park Hinjewadi Phase II, Pune 411 057.
Phone : +91.20.40262000 Ext 2162 | Mobile:+91.9373415075 | Fax:
+91.20.30513500
http://www.synechron.com | MailTo: amitjain@synechron.com
*Synechron:
- A CMMi Level 3 Certified Company
- Top 5 Best Employers (Dataquest Magazine)
- Emerging India Finalist (CNBC India )
*****
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Question to transaction ID wraparound
@ 2009-02-26 16:19 Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
parent: AmitKumar Jain <amitjain@synechron.com>
0 siblings, 0 replies; 12+ messages in thread
From: Lentes, Bernd @ 2009-02-26 16:19 UTC (permalink / raw)
To: AmitKumar Jain <amitjain@synechron.com>; +Cc: pgsql-admin
Amit Jain wrote :
Its a log analyzer tool through which you can trap the running
queries and have reports about update/delete/insterts which are
executing at database system.
http://pgfouine.projects.postgresql.org/
Lentes, Bernd wrote:
Lee Azzarello wrote:
Sometimes the number grows by 5 in one
second.
I'm also astonished about the fact that
all databases grow
by the same amount.
Do you have any explanations for this
behaviour ?
Why are the numbers growing so quickly ?
What kind of work load is on the database?
How frequent are UPDATE/INSERT/DELETE statements
called by
that workload?
If you don't already have the answer to these
questions, I
would recommend a reporting tool like pgfouine
to get them easily.
-lee
I don't have the exact answers to that question.
I think it should be a very tiny work load.
What is pgfouine ?
Bernd
I don't know this tool. I will install it and give
information, when i'm ready.
Bernd
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Question to transaction ID wraparound
@ 2009-02-26 17:18 Scott Marlowe <scott.marlowe@gmail.com>
parent: Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
2 siblings, 1 reply; 12+ messages in thread
From: Scott Marlowe @ 2009-02-26 17:18 UTC (permalink / raw)
To: Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>; +Cc: pgsql-admin
On Wed, Feb 25, 2009 at 3:05 AM, Lentes, Bernd
<bernd.lentes@helmholtz-muenchen.de> wrote:
> Hello ML,
>
> I'm very new to Postgres.
> In the log, i got messages concerning transaction ID wraparound.
What exact message did you get?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Question to transaction ID wraparound
@ 2009-02-27 09:49 Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Lentes, Bernd @ 2009-02-27 09:49 UTC (permalink / raw)
To: pgsql-admin
Scott Marlowe wrote:
> -----Ursprüngliche Nachricht-----
> Von: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> Gesendet: Donnerstag, 26. Februar 2009 18:18
> An: Lentes, Bernd
> Cc: pgsql-admin@postgresql.org
> Betreff: Re: [ADMIN] Question to transaction ID wraparound
>
> On Wed, Feb 25, 2009 at 3:05 AM, Lentes, Bernd
> <bernd.lentes@helmholtz-muenchen.de> wrote:
> > Hello ML,
> >
> > I'm very new to Postgres.
> > In the log, i got messages concerning transaction ID wraparound.
>
> What exact message did you get?
>
The message is in german:
Grenze für Transaktionsnummernüberlauf ist 1133954883, begrenzt durch Datenbank "genetrap"
I try to translate:
transaction ID wrap limit is 113..., limited by database "genetrap"
Bernd
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Question to transaction ID wraparound
@ 2009-02-27 10:02 Scott Marlowe <scott.marlowe@gmail.com>
parent: Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
0 siblings, 0 replies; 12+ messages in thread
From: Scott Marlowe @ 2009-02-27 10:02 UTC (permalink / raw)
To: Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>; +Cc: pgsql-admin
On Fri, Feb 27, 2009 at 2:49 AM, Lentes, Bernd
<bernd.lentes@helmholtz-muenchen.de> wrote:
>
>
>
>
> Scott Marlowe wrote:
>> -----Ursprüngliche Nachricht-----
>> Von: Scott Marlowe [mailto:scott.marlowe@gmail.com]
>> Gesendet: Donnerstag, 26. Februar 2009 18:18
>> An: Lentes, Bernd
>> Cc: pgsql-admin@postgresql.org
>> Betreff: Re: [ADMIN] Question to transaction ID wraparound
>>
>> On Wed, Feb 25, 2009 at 3:05 AM, Lentes, Bernd
>> <bernd.lentes@helmholtz-muenchen.de> wrote:
>> > Hello ML,
>> >
>> > I'm very new to Postgres.
>> > In the log, i got messages concerning transaction ID wraparound.
>>
>> What exact message did you get?
>>
> The message is in german:
> Grenze für Transaktionsnummernüberlauf ist 1133954883, begrenzt durch Datenbank "genetrap"
> I try to translate:
> transaction ID wrap limit is 113..., limited by database "genetrap"
That's more an information message than something to get worried
about. It means that you can perform 1.13Billion transactions before
things have to get vacuumed to prevent wrap around. If it says 1M or
something, then start worrying. and the db will emit warnings and
such at that point.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Question to transaction ID wraparound
@ 2009-02-27 10:03 Scott Marlowe <scott.marlowe@gmail.com>
parent: Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
2 siblings, 2 replies; 12+ messages in thread
From: Scott Marlowe @ 2009-02-27 10:03 UTC (permalink / raw)
To: Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>; +Cc: pgsql-admin
On Wed, Feb 25, 2009 at 3:05 AM, Lentes, Bernd
<bernd.lentes@helmholtz-muenchen.de> wrote:
> Hello ML,
>
> I'm very new to Postgres.
> In the log, i got messages concerning transaction ID wraparound.
> I red the documentation and made a vaccum on that database.
> In the documentation i also found the hint to look in the database with
> "SELECT datname, age(datfrozenxid) FROM pg_database;", which i did.
> I did the command several times in a few seconds, and this number is growing very quickly,
> although i'm not expecting transactions in the database.
Please note that nearly EVERYTHING that happens in postgres is a
transaction. if you type a single command, boom, it's a transaction.
You actually get fewer transaction events if you enclose a bunch of
stuff in begin; commit; pairs than if you run single commands.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Question to transaction ID wraparound
@ 2009-02-27 10:30 Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Lentes, Bernd @ 2009-02-27 10:30 UTC (permalink / raw)
To: pgsql-admin
> -----Ursprüngliche Nachricht-----
> Von: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> Gesendet: Freitag, 27. Februar 2009 11:04
> An: Lentes, Bernd
> Cc: pgsql-admin@postgresql.org
> Betreff: Re: [ADMIN] Question to transaction ID wraparound
>
> On Wed, Feb 25, 2009 at 3:05 AM, Lentes, Bernd
> <bernd.lentes@helmholtz-muenchen.de> wrote:
> > Hello ML,
> >
> > I'm very new to Postgres.
> > In the log, i got messages concerning transaction ID wraparound.
> > I red the documentation and made a vaccum on that database.
> > In the documentation i also found the hint to look in the database
> > with "SELECT datname, age(datfrozenxid) FROM pg_database;",
> which i did.
> > I did the command several times in a few seconds, and this
> number is
> > growing very quickly, although i'm not expecting
> transactions in the database.
>
Scott Marlowe wrote:
> Please note that nearly EVERYTHING that happens in postgres
> is a transaction. if you type a single command, boom, it's a
> transaction.
> You actually get fewer transaction events if you enclose a
> bunch of stuff in begin; commit; pairs than if you run single
> commands.
>
Thanks, Scott
Things become to come clear for me.
I was _very_astonished_ that this number was growing so rapidly.
The number grows by 1 every second.
Is that normal ?
Bernd
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Question to transaction ID wraparound
@ 2009-02-27 11:42 Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Lentes, Bernd @ 2009-02-27 11:42 UTC (permalink / raw)
To: pgsql-admin
Scott Marlowe wrote:
> -----Ursprüngliche Nachricht-----
> Von: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> Gesendet: Freitag, 27. Februar 2009 11:04
> An: Lentes, Bernd
> Cc: pgsql-admin@postgresql.org
> Betreff: Re: [ADMIN] Question to transaction ID wraparound
>
> On Wed, Feb 25, 2009 at 3:05 AM, Lentes, Bernd
> <bernd.lentes@helmholtz-muenchen.de> wrote:
> > Hello ML,
> >
> > I'm very new to Postgres.
> > In the log, i got messages concerning transaction ID wraparound.
> > I red the documentation and made a vaccum on that database.
> > In the documentation i also found the hint to look in the database
> > with "SELECT datname, age(datfrozenxid) FROM pg_database;",
> which i did.
> > I did the command several times in a few seconds, and this
> number is
> > growing very quickly, although i'm not expecting
> transactions in the database.
>
> Please note that nearly EVERYTHING that happens in postgres
> is a transaction. if you type a single command, boom, it's a
> transaction.
> You actually get fewer transaction events if you enclose a
> bunch of stuff in begin; commit; pairs than if you run single
> commands.
>
Scott, why does the number increase ?
If it says how many transactions are still possible, the number should decrease, not increase.
Bernd
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Question to transaction ID wraparound
@ 2009-02-28 00:17 Scott Marlowe <scott.marlowe@gmail.com>
parent: Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
0 siblings, 0 replies; 12+ messages in thread
From: Scott Marlowe @ 2009-02-28 00:17 UTC (permalink / raw)
To: Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>; +Cc: pgsql-admin
On Fri, Feb 27, 2009 at 4:42 AM, Lentes, Bernd
<bernd.lentes@helmholtz-muenchen.de> wrote:
>
>
> Scott Marlowe wrote:
>> -----Ursprüngliche Nachricht-----
>> Von: Scott Marlowe [mailto:scott.marlowe@gmail.com]
>> Gesendet: Freitag, 27. Februar 2009 11:04
>> An: Lentes, Bernd
>> Cc: pgsql-admin@postgresql.org
>> Betreff: Re: [ADMIN] Question to transaction ID wraparound
>>
>> On Wed, Feb 25, 2009 at 3:05 AM, Lentes, Bernd
>> <bernd.lentes@helmholtz-muenchen.de> wrote:
>> > Hello ML,
>> >
>> > I'm very new to Postgres.
>> > In the log, i got messages concerning transaction ID wraparound.
>> > I red the documentation and made a vaccum on that database.
>> > In the documentation i also found the hint to look in the database
>> > with "SELECT datname, age(datfrozenxid) FROM pg_database;",
>> which i did.
>> > I did the command several times in a few seconds, and this
>> number is
>> > growing very quickly, although i'm not expecting
>> transactions in the database.
>>
>> Please note that nearly EVERYTHING that happens in postgres
>> is a transaction. if you type a single command, boom, it's a
>> transaction.
>> You actually get fewer transaction events if you enclose a
>> bunch of stuff in begin; commit; pairs than if you run single
>> commands.
>>
> Scott, why does the number increase ?
> If it says how many transactions are still possible, the number should decrease, not increase.
What I wrote was about the number you saw from the log entries. That
tells you how many transactions you have left before wraparound would
happen.
Read up on what the age() stuff means here:
http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2009-02-28 00:17 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-02-25 10:05 Question to transaction ID wraparound Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
2009-02-26 15:15 ` Lee Azzarello <lee@dropio.com>
2009-02-26 15:47 ` Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
2009-02-26 15:51 ` AmitKumar Jain <amitjain@synechron.com>
2009-02-26 16:19 ` Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
2009-02-26 17:18 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-02-27 09:49 ` Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
2009-02-27 10:02 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-02-27 10:03 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-02-27 10:30 ` Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
2009-02-27 11:42 ` Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de>
2009-02-28 00:17 ` Scott Marlowe <scott.marlowe@gmail.com>
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