X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 18DBA3A3BA1 for ; Mon, 3 Jan 2005 14:10:55 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 14914-05 for ; Mon, 3 Jan 2005 14:10:39 +0000 (GMT) Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by svr1.postgresql.org (Postfix) with SMTP id 69C0E3A3B82 for ; Mon, 3 Jan 2005 14:10:42 +0000 (GMT) Received: (qmail 23547 invoked from network); 3 Jan 2005 14:10:15 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.36?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 3 Jan 2005 14:10:15 -0000 Message-ID: <41D9525D.3000409@fastcrypt.com> Date: Mon, 03 Jan 2005 09:10:37 -0500 From: Dave Cramer Reply-To: pg@fastcrypt.com Organization: Postgres International User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: amrit@health2.moph.go.th Cc: PGsql-performance Subject: Re: Low Performance for big hospital server .. References: <1104634472.41d762681b548@webmail.moph.go.th> <20050102140828.GA21826@pobox.com> <1104683293.41d8211d62221@webmail.moph.go.th> <41D827C1.3050707@fastcrypt.com> <1104717243.41d8a5bb9cb9b@webmail.moph.go.th> In-Reply-To: <1104717243.41d8a5bb9cb9b@webmail.moph.go.th> Content-Type: multipart/alternative; boundary="------------000801040400030508060009" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.056 tagged_above=0 required=5 tests=AWL, HTML_30_40, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: X-Archive-Number: 200501/16 X-Sequence-Number: 9706 This is a multi-part message in MIME format. --------------000801040400030508060009 Content-Type: text/plain; charset=TIS-620; format=flowed Content-Transfer-Encoding: 7bit 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 --------------000801040400030508060009 Content-Type: text/html; charset=TIS-620 Content-Transfer-Encoding: 7bit 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
--------------000801040400030508060009--