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
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
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
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
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
-
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
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
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
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
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
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
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 hel
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 que
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 -0500
On 1/30/07, Sidar López Cruz <[EMAIL
Using (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 s
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 PROT
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
-> Ma
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 (subpl
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 AD
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 (subplan))
SubPlan
-> Materialize (cost=51021.78..69422.58 rows=10329
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
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
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 the
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 ENABLE_SEQSCAN
"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 se
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 idx_job_log_filter
ithout time zone) AND ((job_stop <=
'2005-11-14 05:00:00'::timestamp without time zone) OR (job_stop IS NULL)))
-> Index Scan using node_id_pkey on node n (cost=0.00..5.99
rows=1 width=4)
Index Cond: ("outer".node_id = n.node_id)
> 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=
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
29 matches
Mail list logo
