X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 88881D1C9ED for ; Tue, 5 Aug 2003 16:28:21 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 21978-02 for ; Tue, 5 Aug 2003 13:27:02 -0300 (ADT) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by svr1.postgresql.org (Postfix) with ESMTP id B5F07D1C4E0 for ; Tue, 5 Aug 2003 13:28:10 -0300 (ADT) Received: from dev6.int.libertyrms.com ([10.1.2.212] helo=libertyrms.info) by mail.libertyrms.com with esmtp (Exim 3.22 #3 (Debian)) id 19k4fS-0000o4-00; Tue, 05 Aug 2003 12:28:10 -0400 Received: by libertyrms.info (Postfix, from userid 1079) id 31F7BBBADC; Tue, 5 Aug 2003 12:28:10 -0400 (EDT) To: Trevor Astrope Cc: pgsql-performance@postgresql.org Subject: Re: How Many Inserts Per Transactions From: Christopher Browne Date: Tue, 05 Aug 2003 12:28:10 -0400 Message-ID: <60d6fkhxwl.fsf@dev6.int.libertyrms.info> User-Agent: Gnus/5.1003 (Gnus v5.10.3) XEmacs/21.4 (Rational FORTRAN, linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/77 X-Sequence-Number: 2894 Trevor Astrope wrote: > I was wondering if anyone found a sweet spot regarding how many > inserts to do in a single transaction to get the best performance? > Is there an approximate number where there isn't any more > performance to be had or performance may drop off? > > It's just a general question...I don't have any specific scenario, > other than there are multiple backends doing many inserts. The ideal should be enough to make the work involved in establishing the transaction context be a small part of the cost of processing the queries. Thus, 2 inserts should be twice as good as 1, by virtue of dividing the cost of the transaction 2 ways. Increasing the number of inserts/updates to 10 means splitting the cost 10 ways. Increasing the number to 1000 means splitting the cost 1000 ways, which, while better than merely splitting the cost 10 ways, probably _isn't_ a stunningly huge further improvement. The benefits of grouping more together drops off; you'll probably NOT notice much difference between grouping 10,000 updates together into a transaction as compared to grouping 15,000 updates together. Fortunately, it doesn't drop off to being downright WORSE. On Oracle, I have seen performance Suck Badly when using SQL*Load; if I grouped too many updates together, it started blowing up the "rollback segment," which was a Bad Thing. And in that kind of context, there will typically be some "sweet spot" where you want to commit transactions before they grow too big. In contrast, pg_dump/pg_restore puts the load of each table into a single COPY statement, so that if there are 15,000,000 entries in the table, that gets grouped into a single (rather enormous) transaction. And doing things that way presents no particular problem. -- output = ("cbbrowne" "@" "libertyrms.info") Christopher Browne (416) 646 3304 x124 (land)