From: Josh Close <narshe@gmail.com>
To: pgsql-performance@postgresql.org
Subject: slow queries, possibly disk io
Date: Thu, 26 May 2005 15:24:16 -0500
Message-ID: <4a0cafe205052613242e0d4d10@mail.gmail.com> (raw)
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.
Would setting shmmax and smmall to 90% or so of available mem and
putting a lot for postgres be helpful?
Effective cach size says this:
Sets the planner's assumption about the effective size of the disk
cache (that is, the portion of the kernel's disk cache that will be
used for PostgreSQL data files).
Does that mean the total available ram? Or what's left over from shared_buffers?
I've tried different things and not much has been working. Is there a
good way to ensure that most of the tables accessed in postgres will
be cached in mem and not have to go to disk?
If I'm joining a lot of tables, should the sort_mem be set high also?
Do shared_buffers, effective_cache_size, and sort_mem all use
different mem? Or are they seperate?
I've looked for information and haven't found any useful pages about this.
Any help would be greatly appreciated.
Thanks.
-Josh
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: narshe@gmail.com
Subject: Re: slow queries, possibly disk io
In-Reply-To: <4a0cafe205052613242e0d4d10@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