Amrit,
I realize you may be stuck with 7.3.x but you should be aware that 7.4 is considerably faster, and 8.0 appears to be even faster yet.
I would seriously consider upgrading, if at all possible.
A few more hints.
Random page cost is quite conservative if you have reasonably fast disks.
Speaking of fast disks, not all disks are created equal, some RAID drives are quite slow (Bonnie++ is your friend here)
Sort memory can be set on a per query basis, I'd consider lowering it quite low and only increasing it when necessary.
Which brings us to how to find out when it is necessary.
Turn logging on and turn on log_pid, and log_duration, then you will need to sort through the logs to find the slow queries.
There are some special cases where postgresql can be quite slow, and minor adjustments to the query can improve it significantly
For instance pre-8.0 select * from foo where id = '1'; where id is a int8 will never use an index even if it exists.
Regards,
Dave
amrit@health2.moph.go.th wrote:
The common wisdom of shared buffers is around 6-10% of available memory.
Your proposal below is about 50% of memory.
I'm not sure what the original numbers actually meant, they are quite large.
I will try to reduce shared buffer to 1536 [1.87 Mb].
also effective cache is the sum of kernel buffers + shared_buffers so it
should be bigger than shared buffers.
also make the effective cache to 2097152 [2 Gb].
I will give you the result , because tomorrow [4/12/05] will be the official day
of my hospital [which have more than 1700 OPD patient/day].
Also turning hyperthreading off may help, it is unlikely it is doing any
good unless you are running a relatively new (2.6.x) kernel.
Why , could you give me the reason?
I presume you are vacuuming on a regular basis?
Yes , vacuumdb daily.
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561