Обсуждение: Question to transaction ID wraparound

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

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

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

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  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  

 

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?

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

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.

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.

> -----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

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

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