X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id BD4A79FB2A3 for ; Tue, 8 Aug 2006 06:17:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 59116-05 for ; Tue, 8 Aug 2006 06:17:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from my.endian.it (unknown [62.146.87.34]) by postgresql.org (Postfix) with ESMTP id 1FEEC9FB29E for ; Tue, 8 Aug 2006 06:17:22 -0300 (ADT) Received: from dell.home.lan (host177-104.pool875.interbusiness.it [87.5.104.177]) (authenticated (0 bits)) by my.endian.it (8.11.6/8.11.6) with ESMTP id k789P1T15053; Tue, 8 Aug 2006 11:25:01 +0200 Subject: Re: Performance tuning question From: Chris Mair To: Benjamin Krajmalnik Cc: pgsql-admin@postgresql.org In-Reply-To: References: Content-Type: text/plain Date: Tue, 08 Aug 2006 11:17:15 +0200 Message-Id: <1155028635.4467.11.camel@dell.home.lan> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-27) Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.185 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200608/86 X-Sequence-Number: 22630 > I just finished running some benchmarks on an underpowered server > compared to the one I am running in production. > My initial tests were run on an ampty database, pg_xlog on the same > spindle. > Stored procedure execution speed was ~15 ms. > > I then restored the production database so I would have ~3GB database. > Execution time for the stored procedure went up to about 40 ms average > (with a miuch higher variance), but with a processing speed of 18 stored > procedure calls per second. > > I them moved pg_xlog to a separate spindle. > Execution time went down to about 17 ms Good! > Beyond moving pg_xlog to a separate spindle, are there any other things > you can think of which may improve the performance? You could bundle more work into a single transaction. I don't know what your call is doing, but 1 call that does 20 inserts in a transaction is certainly more efficient than 2 calls doing two transactions with 10 inserts each. If you can use bigger bundles of work, do that. Maybe reasoning in "MByte (or whatever) per second" rather than "time to do a small piece of the work" helps. Otherwise you're into real time stuff and RDBMS' and real time stuff don't mix well. Bye :) Chris. -- Chris Mair http://www.1006.org