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