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 7BC6B8BA056 for ; Wed, 9 Feb 2005 20:38:30 +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 39842-05 for ; Wed, 9 Feb 2005 20:38:22 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 686FA8B9EE9 for ; Wed, 9 Feb 2005 20:38:22 +0000 (GMT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.2/8.12.9) with ESMTP id j19KcMUm005454; (envelope-from ) Wed, 9 Feb 2005 14:38:22 -0600 (CST) Received: from [192.168.1.100] (12-217-241-0.client.mchsi.com [12.217.241.0]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j19KcI1g012126 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Wed, 9 Feb 2005 14:38:21 -0600 (CST) Message-ID: <420A74BE.8010009@arbash-meinel.com> Date: Wed, 09 Feb 2005 14:38:22 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Chris Kratz Cc: pgsql-performance@postgresql.org Subject: Re: Performance Tuning References: <200502091501.31447.chris.kratz@vistashare.com> In-Reply-To: <200502091501.31447.chris.kratz@vistashare.com> X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig957DD9393391C7D8B8EB4272" X-Virus-Scanned: ClamAV 0.80/682/Mon Jan 24 12:53:43 2005 clamav-milter version 0.80j on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV 0.80/685/Wed Jan 26 03:08:24 2005, clamav-milter version 0.75 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200502/149 X-Sequence-Number: 10478 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig957DD9393391C7D8B8EB4272 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Chris Kratz wrote: >Hello All, > >In contrast to what we hear from most others on this list, we find our >database servers are mostly CPU bound. We are wondering if this is because >we have postgres configured incorrectly in some way, or if we really need >more powerfull processor(s) to gain more performance from postgres. > > > If everything is cached in ram, it's pretty easy to be CPU bound. You very easily could be at this point if your database is only 2.6G and you don't touch all the tables often. I do believe that when CPU bound, the best thing to do is get faster CPUs. ... >Our question is simply this, is it better to invest in a faster processor at >this point, or are there configuration changes to make it faster? I've done >some testing with with 4x SCSI 10k and the performance didn't improve, in >fact it actually was slower the the sata drives marginally. One of our >developers is suggesting we should compile postgres from scratch for this >particular processor, and we may try that. Any other ideas? > >-Chris > >On this particular development server, we have: > >Athlon XP,3000 >1.5G Mem >4x Sata drives in Raid 0 > > > I'm very surprised you are doing RAID 0. You realize that if 1 drive goes out, your entire array is toast, right? I would recommend doing either RAID 10 (0+1), or even Raid 5 if you don't do a lot of writes. Probably most important, though is to look at the individual queries and see what they are doing. >Postgresql 7.4.5 installed via RPM running on Linux kernel 2.6.8.1 > >Items changed in the postgresql.conf: > >tcpip_socket = true >max_connections = 32 >port = 5432 >shared_buffers = 12288 # min 16, at least max_connections*2, 8KB each >sort_mem=16384 >vacuum_mem = 32768 # min 1024, size in KB >max_fsm_pages = 60000 # min max_fsm_relations*16, 6 bytes each >max_fsm_relations = 1000 # min 100, ~50 bytes each >effective_cache_size = 115200 # typically 8KB each >random_page_cost = 1 # units are one sequential page fetch cost > > Most of these seem okay to me, but random page cost is *way* too low. This should never be tuned below 2. I think this says "an index scan of *all* rows is as cheap as a sequential scan of all rows." and that should never be true. What could actually be happening is that you are getting index scans when a sequential scan would be faster. I don't know what you would see, but what does "explain analyze select count(*) from blah;" say. If it is an index scan, you have your machine mistuned. select count(*) always grabs every row, and this is always cheaper with a sequential scan. John =:-> --------------enig957DD9393391C7D8B8EB4272 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCCnS+JdeBCYSNAAMRAk88AJ9uqiM7XEfHC8VW8dqOSiHMcBRILACgka0R DQ8YLLNepAmU2firdlUeu88= =Iw6O -----END PGP SIGNATURE----- --------------enig957DD9393391C7D8B8EB4272--