Re: [PERFORM] VERY slow queries at random

2007-06-08 Thread Kristo Kaiv
On 07.06.2007, at 22:42, Greg Smith wrote: On Thu, 7 Jun 2007, Gunther Mayer wrote: wal checkpoint config is on pg defaults everywhere, all relevant config options are commented out. I'm no expert in wal stuff but I don't see how that could cause the problem? Checkpoints are very resourc

Re: [PERFORM] VERY slow queries at random

2007-06-07 Thread Greg Smith
On Thu, 7 Jun 2007, Gunther Mayer wrote: wal checkpoint config is on pg defaults everywhere, all relevant config options are commented out. I'm no expert in wal stuff but I don't see how that could cause the problem? Checkpoints are very resource intensive and can cause other processes (incl

Re: [PERFORM] VERY slow queries at random

2007-06-07 Thread Alvaro Herrera
Gunther Mayer wrote: > On another note, autovacuum couldn't cause such issues, could it? I do > have autovacuum enabled (autovacuum=on as well as > stats_start_collector=on, stats_block_level = on and stats_row_level = > on), is there any possibility that autovacuum is not as resource > friend

Re: [PERFORM] VERY slow queries at random

2007-06-07 Thread Andrew Sullivan
On Thu, Jun 07, 2007 at 04:22:47PM +0200, Gunther Mayer wrote: > There are a whole bunch of update queries that fire all the time but > afaik none of them ever lock the entire table. To the best of my > knowledge UPDATE ... WHERE ... only locks those rows that it actually > operates on, in my ca

Re: [PERFORM] VERY slow queries at random

2007-06-07 Thread Gunther Mayer
Kristo Kaiv wrote: could be that the checkpoints are done too seldom. what is your wal checkpoint config? wal checkpoint config is on pg defaults everywhere, all relevant config options are commented out. I'm no expert in wal stuff but I don't see how that could cause the problem? Gunther -

Re: [PERFORM] VERY slow queries at random

2007-06-07 Thread Gunther Mayer
Scott Marlowe wrote: Gunther Mayer wrote: Hi there, We run a small ISP with a FreeBSD/freeradius/postgresql 8.2.4 backend and 200+ users. Authentication happens via UAM/hotspot and I see a lot of authorisation and accounting packets that are handled via PL/PGSQL functions directly in the databa

Re: [PERFORM] VERY slow queries at random

2007-06-07 Thread Gunther Mayer
Andrew Sullivan wrote: On Wed, Jun 06, 2007 at 09:20:54PM +0200, Gunther Mayer wrote: What the heck could cause such erratic behaviour? I suspect some type of resource problem but what and how could I dig deeper? Is something (perhaps implicitly) locking the table? That will cause thi

Re: [PERFORM] VERY slow queries at random

2007-06-07 Thread Kristo Kaiv
could be that the checkpoints are done too seldom. what is your wal checkpoint config? Kristo On 07.06.2007, at 0:27, Scott Marlowe wrote: Gunther Mayer wrote: Hi there, We run a small ISP with a FreeBSD/freeradius/postgresql 8.2.4 backend and 200+ users. Authentication happens via UAM/hotspo

Re: [PERFORM] VERY slow queries at random

2007-06-06 Thread Scott Marlowe
Gunther Mayer wrote: Hi there, We run a small ISP with a FreeBSD/freeradius/postgresql 8.2.4 backend and 200+ users. Authentication happens via UAM/hotspot and I see a lot of authorisation and accounting packets that are handled via PL/PGSQL functions directly in the database. Everything seems

Re: [PERFORM] VERY slow queries at random

2007-06-06 Thread Andrew Sullivan
On Wed, Jun 06, 2007 at 09:20:54PM +0200, Gunther Mayer wrote: > > What the heck could cause such erratic behaviour? I suspect some type of > resource problem but what and how could I dig deeper? Is something (perhaps implicitly) locking the table? That will cause this. A -- Andrew Sullivan

[PERFORM] VERY slow queries at random

2007-06-06 Thread Gunther Mayer
Hi there, We run a small ISP with a FreeBSD/freeradius/postgresql 8.2.4 backend and 200+ users. Authentication happens via UAM/hotspot and I see a lot of authorisation and accounting packets that are handled via PL/PGSQL functions directly in the database. Everything seems to work 100% except th