On Wed, Apr 11, 2007 at 12:50:37AM +0200, Tobias Brox wrote:
> We had problems again, caused by long running transactions. I'm
> monitoring the pg_stat_activity view, checking the query_start of all
> requests that are not idle - but this one slipped under the radar as the
> application was runnin
We had problems again, caused by long running transactions. I'm
monitoring the pg_stat_activity view, checking the query_start of all
requests that are not idle - but this one slipped under the radar as the
application was running frequent queries towards the database.
That's not what concerns me
[Tom Lane - Tue at 02:26:53PM -0400]
> > autovacuum_vacuum_cost_delay = 500
> > autovacuum_vacuum_cost_limit = 200
>
> Well, that's going to cause it to sleep half a second after every dozen
> or so page I/Os. I think you'd be well advised to reduce the delay.
Modified it to 20/250, and it defin
Tobias Brox <[EMAIL PROTECTED]> writes:
>> Perhaps you have overly aggressive vacuum cost delay settings?
> autovacuum_vacuum_cost_delay = 500
> autovacuum_vacuum_cost_limit = 200
Well, that's going to cause it to sleep half a second after every dozen
or so page I/Os. I think you'd be well advis
[Tom Lane - Tue at 02:04:55PM -0400]
> > It seems stuck, has had the same transid for a long while, and the
> > number of undeletable dead rows in our tables are increasing.
>
> Perhaps you have overly aggressive vacuum cost delay settings?
Perhaps, though I wouldn't expect it to sleep in the mid
Tobias Brox <[EMAIL PROTECTED]> writes:
> (gdb) bt
> #0 0xb7c599f8 in select () from /lib/tls/libc.so.6
> #1 0x08253c53 in pg_usleep ()
> #2 0x0812ee93 in vacuum_delay_point ()
> #3 0x0812f2a5 in lazy_vacuum_rel ()
> #4 0x0812ef7b in lazy_vacuum_rel ()
> #5 0x0812b4b6 in vac_update_relstats (
[Tom Lane - Tue at 01:18:27PM -0400]
> >> Hmph. Is the autovac process actually doing anything (strace would be
> >> revealing)?
It's definitively doing something; mostly reading, but also some few
writes, semops and opens.
> If not, can you attach to the autovac process with gdb and
> >> get a
Tobias Brox <[EMAIL PROTECTED]> writes:
> [Tom Lane - Tue at 01:09:52PM -0400]
>> Hmph. Is the autovac process actually doing anything (strace would be
>> revealing)? If not, can you attach to the autovac process with gdb and
>> get a stack trace to see where it's blocked?
> Sorry ... I SIGINT'e
[Tom Lane - Tue at 01:09:52PM -0400]
> Hmph. Is the autovac process actually doing anything (strace would be
> revealing)? If not, can you attach to the autovac process with gdb and
> get a stack trace to see where it's blocked?
Sorry ... I SIGINT'ed it, and now it's gone :-( I thought reloadin
Tobias Brox <[EMAIL PROTECTED]> writes:
>> Blocked on someone else's lock, maybe?
> hardly, the autovacuum is the only one having such a low transaction id,
> and also the only one hanging around when waiting a bit and rechecking
> the pg_locks table.
Hmph. Is the autovac process actually doing
[Tom Lane - Tue at 12:42:52PM -0400]
> > belonging to autovacuum ... how come?
>
> Blocked on someone else's lock, maybe?
hardly, the autovacuum is the only one having such a low transaction id,
and also the only one hanging around when waiting a bit and rechecking
the pg_locks table.
--
Tobias Brox <[EMAIL PROTECTED]> writes:
> [Tobias Brox - Tue at 06:39:13PM +0200]
>> Thanks a lot for the quick reply - I've already identified one
>> long-running transaction.
> belonging to autovacuum ... how come?
Blocked on someone else's lock, maybe?
regards, tom lan
[Tobias Brox - Tue at 06:39:13PM +0200]
> Thanks a lot for the quick reply - I've already identified one
> long-running transaction.
belonging to autovacuum ... how come?
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignor
[Tom Lane - Tue at 12:23:40PM -0400]
> Look in pg_locks to see the lowest-numbered transaction ID --- each
> transaction will be holding exclusive lock on its own XID. You can
> correlate that back to pg_stat_activity via the PID.
Thanks a lot for the quick reply - I've already identified one
lon
Tobias Brox <[EMAIL PROTECTED]> writes:
> If pg_stat_activity.query_start actually is the start time of the
> transaction,
... but it isn't.
> Is there any way to find the longest running transaction?
Look in pg_locks to see the lowest-numbered transaction ID --- each
transaction will be holding
While doing a verbose vacuum, I'm constantly hitting things like:
DETAIL: 3606 dead row versions cannot be removed yet.
I believe this is a problem, because I still do have some empty tables
requireing up to 3-400 ms just to check if the table is empty (see
thread "slow queue-like empty table").
16 matches
Mail list logo
