From: Dawid Kuroczko <qnex42@gmail.com>
To: Josh Close <narshe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow queries, possibly disk io
Date: Thu, 26 May 2005 23:23:15 +0200
Message-ID: <758d5e7f05052614232f6c9164@mail.gmail.com> (raw)
In-Reply-To: <4a0cafe205052613242e0d4d10@mail.gmail.com>
References: <4a0cafe205052613242e0d4d10@mail.gmail.com>
On 5/26/05, Josh Close <narshe@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: qnex42@gmail.com, narshe@gmail.com
Subject: Re: slow queries, possibly disk io
In-Reply-To: <758d5e7f05052614232f6c9164@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox