Skip site navigation (1) Skip section navigation (2)

Re: slow queries, possibly disk io

From: Dawid Kuroczko <qnex42(at)gmail(dot)com>
To: Josh Close <narshe(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow queries, possibly disk io
Date: 2005-05-26 21:23:15
Message-ID: 758d5e7f05052614232f6c9164@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 5/26/05, Josh Close <narshe(at)gmail(dot)com> wrote:
> I have some queries that have significan't slowed down in the last
> couple days. It's gone from 10 seconds to over 2 mins.
> 
> The cpu has never gone over 35% in the servers lifetime, but the load
> average is over 8.0 right now. I'm assuming this is probably due to
> disk io.
> 
> I need some help setting up postgres so that it doesn't need to go to
> disk. I think the shared_buffers and effective_cache_size values are
> the one's I need to look at.

Few "mandatory" questions:

1. Do you vacuum your db on regular basis? :)

2. Perhaps statistics for tables in question are out of date, did you
    try alter table set statistics?

3. explain analyze of the slow query?

4. if you for some reason cannot give explain analyze, please try to
describe the type of query (what kind of join(s)) and amount of data
found in the tables.

2 minutes from 10 seconds is a huge leap, and it may mean that
PostgreSQL for some reason is not planning as well as it could.
Throwing more RAM at the problem can help, but it would be better
to hint the planner to do the right thing.  It may be a good time to
play with planner variables. :)

   Regards,
     Dawid

In response to

Responses

pgsql-performance by date

Next:From: Christopher Kings-LynneDate: 2005-05-27 01:29:52
Subject: Re: slow queries, possibly disk io
Previous:From: John Arbash MeinelDate: 2005-05-26 21:14:39
Subject: Re: slow queries, possibly disk io

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group