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 A546A8B9CD9 for ; Mon, 31 Jan 2005 20:42:13 +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 54271-10 for ; Mon, 31 Jan 2005 20:41:59 +0000 (GMT) Received: from smtp20.libero.it (smtp20.libero.it [193.70.192.147]) by svr1.postgresql.org (Postfix) with ESMTP id 5DD0F8B9CA5 for ; Mon, 31 Jan 2005 20:41:47 +0000 (GMT) Received: from localhost (172.16.1.84) by smtp20.libero.it (7.0.027-DD01) id 41D02C980070AF3E for pgsql-performance@postgresql.org; Mon, 31 Jan 2005 21:41:46 +0100 Received: from [62.98.119.56] (62.98.119.56) by smtp1.libero.it (7.0.027-DD01) (authenticated as tdezotti@inwind.it) id 41BF654A0367664B for pgsql-performance@postgresql.org; Mon, 31 Jan 2005 21:41:42 +0100 Message-ID: <41FE97FC.3060106@streppone.it> Date: Mon, 31 Jan 2005 21:41:32 +0100 From: Cosimo Streppone User-Agent: Mozilla Thunderbird 0.9 (X11/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Postgresql Performance list Subject: High end server and storage for a PostgreSQL OLTP system 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: 200501/638 X-Sequence-Number: 10328 Hi all, I've been following this list for nearly a year now. I've always managed to get PostgreSQL 7.1.x right for the job, which in my case is a large and complex oltp system, run under Pg for 6 years now. We were already planning the switch from 7.1 to 7.4 (or even 8.0). The last project we're facing with has a transaction volume that is something we've never dealt with. By "transaction" I mean something involving 10 to 10,000 (and more) sql queries (a complex mix of insert/ update/ delete/ select). I'd like to ask: 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... 2) The goal is to make the db handle 100 tps (something like 100 users). What kind of server and storage should I provide? The actual servers our application runs on normally have 2 Intel Xeon processors, 2-4 Gb RAM, RAID 0/1/5 SCSI disk storage with hard drives @ 10,000 rpm 3) Highest I/O throughput SCSI adapters? Adaptec? 4) Is it correct to suppose that multiple RAID 1 arrays can provide the fastest I/O ? I usually reserve one RAID1 array to db data directory, one RAID1 array to pg_xlog directory and one RAID1 array for os and application needs. 5) OS and Pg specific tuning? Usually I modify shared memory settings and most of postgresql.conf available settings for 7.1, like `effective_cache', `shared_buffers', `wal_buffers', `wal_files', and so on. -- Cosimo