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 0DD158B9E20 for ; Tue, 1 Feb 2005 06:27:14 +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 14151-09 for ; Tue, 1 Feb 2005 06:27:11 +0000 (GMT) Received: from smtp0.libero.it (smtp0.libero.it [193.70.192.33]) by svr1.postgresql.org (Postfix) with ESMTP id 623188B9E1B for ; Tue, 1 Feb 2005 06:27:10 +0000 (GMT) Received: from localhost (172.16.1.84) by smtp0.libero.it (7.0.027-DD01) id 41BDA86900A54093; Tue, 1 Feb 2005 07:27:00 +0100 Received: from [62.98.87.210] (62.98.87.210) by smtp0.libero.it (7.0.027-DD01) (authenticated as tdezotti@inwind.it) id 41BF5F48036C89F4; Tue, 1 Feb 2005 07:26:59 +0100 Message-ID: <41FF212D.8010500@streppone.it> Date: Tue, 01 Feb 2005 07:26:53 +0100 From: Cosimo Streppone User-Agent: Mozilla Thunderbird 0.9 (X11/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: Postgresql Performance list Subject: Re: High end server and storage for a PostgreSQL OLTP system References: <41FE97FC.3060106@streppone.it> <4429.1107207308@sss.pgh.pa.us> In-Reply-To: <4429.1107207308@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at libero.it serv5 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/8 X-Sequence-Number: 10337 Tom Lane wrote: > Cosimo writes: > >>1) What kind of performance gain can I expect switching from >> 7.1 to 7.4 (or 8.0)? Obviously I'm doing my own testing, >> but I'm not very impressed by 8.0 speed, may be I'm doing >> testing on a low end server... > > Most people report a noticeable speedup in each new release > [...] > I'm surprised that you're not seeing any gain at all. > What was your test case exactly? Have you perhaps tuned your app > so specifically to 7.1 that you need to detune it? We tend to use the lowest common SQL features that will allow us to work with any db, so probably the problem is the opposite, there is no pg-specific overtuning. Also, the real pg load, that should be my ideal test case, is somewhat difficult to reproduce (~ 50 users with handhelds and browser clients). Another good test is a particular procedure that opens several (~1000) subsequent transactions, composed of many repeated selection queries with massive write loads on 6/7 different tables, as big as 300/400k tuples. Every transaction ends with either commit or rollback state Indexing here should be ok, for I've analyzed every single query also under database "stress". Probably one big issue is that I need to vacuum/reindex too often to keep db performances at a good(tm) level. I realize that this has been addressed in several ways with newer PGs. However, I need to do a lot of application and performance tests and do them more seriously. Then I'll report the results here. -- Cosimo