From: Ian Cass <ian.cass@mblox.com>
To: pgsql-admin@postgresql.org
Subject: Postgres Performance
Date: Tue, 23 Apr 2002 10:18:42 +0100
Message-ID: <004501c1eaa7$dce52f90$6602a8c0@salamander> (raw)
Hi,
I'm sure this should be a FAQ, but I'm struggling with tuning my Postgres. I
wonder if someone can give me some advice or some pointers, or even a
suggested starting point for tuning.
Some details....
I've got Postgres 7.2.1 running on Debian with kernel 2.4.18. The database
is running on a e2fs filesystem on a large IDE hard drive (IBM 120GB
7200RPM).
The server is a dual 1.2Ghz Athlon with 1GB DDR Ram.
The database is going to contain 3 copies of an index header table
containing anything from 15-30 million lines. There is also 3 copies of a
detail table containing anything up to 4 times larger than it's
corresponding header table.
For the reports I've been writing, all lookups are indexed. Most of the
queries I've been writing are 'group by' selects referencing datasets of up
to 100k lines, returning about 20 lines to my application. Performance is
'adequate'. However, recently I've been trying to do date based index
lookups and these take forever, even for small lookups (~5000 lines).
According to my explains, all lookups are using indexes. I have run vacuum
analyse recently.
Currently, I have...
shared_buffers = 50000
sort_mem = 65536
I did try giving it more buffers, but performance got worse. There is no
system swapping. Disk accessing on heavy usage (indexing, etc) occurs at a
consistent 30-35MB/sec with minimal CPU hit, so disk IO is reasonable.
During a select, the cpu is mostly idle with low disk IO (1-2MB per sec). It
seems to me there's a sweet spot for buffers/system IO cache.
Hope someone can give me the benefit of their experience.
--
Ian Cass
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-admin@postgresql.org
Cc: ian.cass@mblox.com
Subject: Re: Postgres Performance
In-Reply-To: <004501c1eaa7$dce52f90$6602a8c0@salamander>
* 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