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 929FD3A3BC2 for ; Mon, 3 Jan 2005 14:02:06 +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 13353-01 for ; Mon, 3 Jan 2005 14:01:48 +0000 (GMT) Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by svr1.postgresql.org (Postfix) with SMTP id C2B3F3A3BB4 for ; Mon, 3 Jan 2005 14:01:50 +0000 (GMT) Received: (qmail 23465 invoked from network); 3 Jan 2005 14:01:22 -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:01:22 -0000 Message-ID: <41D95042.70705@fastcrypt.com> Date: Mon, 03 Jan 2005 09:01:38 -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: William Yu Cc: pgsql-performance@postgresql.org 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: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/15 X-Sequence-Number: 9705 William Yu wrote: > amrit@health2.moph.go.th wrote: > >> I will try to reduce shared buffer to 1536 [1.87 Mb]. > > > 1536 is probaby too low. I've tested a bunch of different settings on > my 8GB Opteron server and 10K seems to be the best setting. Be careful here, he is not using opterons which can access physical memory above 4G efficiently. Also he only has 4G the 6-10% rule still applies > > >>> 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]. > > > To figure out your effective cache size, run top and add free+cached. My understanding is that effective cache is the sum of shared buffers, plus kernel buffers, not sure what free + cached gives you? > > >>> 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? > > > Pre 2.6, the kernel does not know the difference between logical and > physical CPUs. Hence, in a dual processor system with hyperthreading, > it actually sees 4 CPUs. And when assigning processes to CPUs, it may > assign to 2 logical CPUs in the same physical CPU. Right, the pre 2.6 kernels don't really know how to handle hyperthreaded CPU's > > >> >> >>> I presume you are vacuuming on a regular basis? >> >> >> Yes , vacuumdb daily. > > > Do you vacuum table by table or the entire DB? I find over time, the > system tables can get very bloated and cause a lot of slowdowns just > due to schema queries/updates. You might want to try a VACUUM FULL > ANALYZE just on the system tables. You may want to try this but regular vacuum analyze should work fine as long as you have the free space map settings correct. Also be aware that pre-7.4.x the free space map is not populated on startup so you should do a vacuum analyze right after startup. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561