Обсуждение: PG_XLOG grows and grows

Hi all, I have a little problem,

Every once in a while I observe that our filesystem is running out of
space due
to an continous growth of our transaction logs.
After inspecting the backend processes I see that three of the type:
idle in transaction.
And that all of our nightly vacuum runs since the "idle in transaction"
is waiting
with an: VACUUM waiting.

Is there any way of finding out exactly what the "idle in transaction"
transactions
have issued so far? What DB / logins etc. they are associated with?


Regards,

Niclas Gustafsson

"Niclas Gustafsson" <niclas.gustafsson@codesense.com> writes:
> Every once in a while I observe that our filesystem is running out of
> space due
> to an continous growth of our transaction logs.

Update.  This isn't a problem with 7.1.3 and later.

            regards, tom lane

Ok, I'm running 7.1.2 on the machine with the problem.

I'll be upgrading later on, but for now, is there a
way to see what the "idle in transaction" is waiting
for? As this probably would help us solve some flaw in
Our software?


Regards,

Niclas Gustafsson


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: den 15 maj 2002 16:17
To: Niclas Gustafsson
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PG_XLOG grows and grows


"Niclas Gustafsson" <niclas.gustafsson@codesense.com> writes:
> Every once in a while I observe that our filesystem is running out of
> space due
> to an continous growth of our transaction logs.

Update.  This isn't a problem with 7.1.3 and later.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

"Niclas Gustafsson" <niclas.gustafsson@codesense.com> writes:
> I'll be upgrading later on, but for now, is there a
> way to see what the "idle in transaction" is waiting
> for?

"Idle" means it's waiting for the client.  "Idle in transaction"
means the client has issued a BEGIN and no COMMIT.

            regards, tom lane

"Niclas Gustafsson" <niclas.gustafsson@codesense.com> writes:
> Yes, I know that, I would like to know what commands the client
> has issued sofar in the transaction as this would help us to
> track down if there is some problems in the client, and which
> client that is stalling ( As we have a bunch of different clients
> Working towards the database).

Turn on debug_print_query, log_pid, log_timestamp, and perhaps
log_connections in postgresql.conf.  The postmaster log will then
provide a pretty effective trace of what's going on.

BTW, in 7.2 you need to SIGHUP the postmaster after changing
postgresql.conf.  I forget whether 7.1 expected that or not.

            regards, tom lane

Yes, that would be the preferred way to do it, if the problem would
occur more often then it does, I've seen it happen twice in three
months time.

I'd like to peek into the transaction that _has_ stalled and see what
It has done so far. I.e. the backend that is idle in transaction.

If that is not possible, I'll just have to turn on the information
as you mentioned below, and wait a month or two for it to occur again.


Regards,

Niclas Gustafsson


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: den 15 maj 2002 17:37
To: Niclas Gustafsson
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PG_XLOG grows and grows


"Niclas Gustafsson" <niclas.gustafsson@codesense.com> writes:
> Yes, I know that, I would like to know what commands the client
> has issued sofar in the transaction as this would help us to
> track down if there is some problems in the client, and which
> client that is stalling ( As we have a bunch of different clients
> Working towards the database).

Turn on debug_print_query, log_pid, log_timestamp, and perhaps
log_connections in postgresql.conf.  The postmaster log will then
provide a pretty effective trace of what's going on.

BTW, in 7.2 you need to SIGHUP the postmaster after changing
postgresql.conf.  I forget whether 7.1 expected that or not.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html