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
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
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
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
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 case
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
friendly
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
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
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 |
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
From: Chad Wagner [EMAIL PROTECTED]
To: Sidar López Cruz [EMAIL PROTECTED]
CC: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Very slow queries
Date: Tue, 30 Jan 2007 17:37:17 -0500
On 1/30/07, Sidar López Cruz [EMAIL PROTECTED] wrote:
query: Delete From ceroriesgo.salarios Where
(numero_patrono) Where
ceroriesgo.patronos.numero_patrono Is Null)
Hope that Helps,
Ted
Sidar López Cruz wrote:
From: Chad Wagner [EMAIL PROTECTED]
To: Sidar López Cruz [EMAIL PROTECTED]
CC: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Very slow queries
Date: Tue, 30 Jan 2007 17:37:17
wrote:
From: Chad Wagner [EMAIL PROTECTED]
To: Sidar López Cruz [EMAIL PROTECTED]
CC: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Very slow queries
Date: Tue, 30 Jan 2007 17:37:17 -0500
On 1/30/07, Sidar López Cruz [EMAIL PROTECTED] wrote:
query: Delete From ceroriesgo.salarios
From: Ted Allen [EMAIL PROTECTED]
To: Sidar López Cruz [EMAIL PROTECTED]
CC: pgsql-performance@postgresql.org, [EMAIL PROTECTED]
Subject: Re: [PERFORM] Very slow queries
Date: Wed, 31 Jan 2007 09:32:43 -0500
How many rows were delete last time you ran the query?
Chad's query looks good
On 1/31/07, Sidar López Cruz [EMAIL PROTECTED] wrote:
Executing these query take:
Query returned successfully: 290 rows affected, 2542387 ms execution time.
I think that's too many time
I would post the plans that you are getting, otherwise just mentioning the
execution time is not very
From: Ted Allen [EMAIL PROTECTED]
To: Sidar López Cruz [EMAIL PROTECTED]
CC: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Very slow queries
Date: Tue, 30 Jan 2007 16:14:38 -0500
What indexes do those tables have? Any?
Yes:
TABLE ceroriesgo.patronos ADD CONSTRAINT
What indexes do those tables have? Any?
Sidar López Cruz wrote:
Check this:
query: Delete From ceroriesgo.salarios Where numero_patrono Not In
(Select numero_patrono From ceroriesgo.patronos)
Seq Scan on salarios (cost=51021.78..298803854359.95 rows=14240077
width=6)
Filter: (NOT
On 1/30/07, Sidar López Cruz [EMAIL PROTECTED] wrote:
query: Delete From ceroriesgo.salarios Where numero_patrono Not In (Select
numero_patrono From ceroriesgo.patronos)
Seq Scan on salarios (cost=51021.78..298803854359.95 rows=14240077
width=6)
Filter: (NOT (subplan))
SubPlan
-
Thanks very much - there are a lot of good articles there... Reading as
fast as I can :)
Best,
Bealach
From: Thomas F. O'Connell [EMAIL PROTECTED]
To: Bealach-na Bo [EMAIL PROTECTED]
CC: PgSQL - Performance pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Very slow queries - please
On Nov 24, 2005, at 12:14 PM, Bealach-na Bo wrote:
The consensus seems to be that I need more indexes and I also need to
look into the NOT IN statement as a possible bottleneck. I've
introduced the indexes which has led to a DRAMATIC change in response
time. Now I have to experiment with INNER
Hi Folks,
I'm new to Postgresql.
I'm having great difficulties getting the performance I had hoped for
from Postgresql 8.0. The typical query below takes ~20 minutes !!
I hope an expert out there will tell me what I'm doing wrong - I hope
*I* am doing something wrong.
Hardware
Single
Typical query
SELECT n.name
FROM node n
WHERE n.name
LIKE '56x%'
AND n.type='H'
AND n.usage='TEST'
AND n.node_id
NOT IN
(select n.node_id
FROM job_log j
INNER JOIN node n
ON j.node_id = n.node_id
WHERE n.name
LIKE '56x%'
AND n.type='H'
AND n.usage='TEST'
AND
: Claus Guttesen [EMAIL PROTECTED]
To: Bealach-na Bo [EMAIL PROTECTED]
CC: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Very slow queries - please help.
Date: Thu, 24 Nov 2005 14:23:38 +0100
Typical query
SELECT n.name
FROM node n
WHERE n.name
LIKE '56x%'
AND n.type='H
Hi,
I'm also sending the EXPLAIN outputs.
Please provide EXPLAIN ANALYZE outputs instead of EXPLAIN. You will have
more information.
Indexes on your tables are obviously missing. You should try to add:
CREATE INDEX idx_node_filter ON node(name, type, usage);
CREATE INDEX
Bealach-na Bo [EMAIL PROTECTED] writes:
I'm having great difficulties getting the performance I had hoped for
from Postgresql 8.0. The typical query below takes ~20 minutes !!
You need to show us the table definition (including indexes) and the
EXPLAIN ANALYZE results for the query.
It seems
OK.
The consensus seems to be that I need more indexes and I also need to
look into the NOT IN statement as a possible bottleneck. I've
introduced the indexes which has led to a DRAMATIC change in response
time. Now I have to experiment with INNER JOIN - OUTER JOIN
variations, SET
A quick note to say that I'm very grateful for Tom Lane's input also.
Tom, I did put you on the list of recipients for my last posting to
pgsql-performance, but got:
cut here
This is an automatically generated Delivery Status Notification.
Delivery to
27 matches
Mail list logo
