pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@libertyrms.info>
To: Trevor Astrope <astrope@e-corp.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: How Many Inserts Per Transactions
Date: Tue, 05 Aug 2003 12:28:10 -0400
Message-ID: <60d6fkhxwl.fsf@dev6.int.libertyrms.info> (raw)

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")
<http://dev6.int.libertyrms.com/;
Christopher Browne
(416) 646 3304 x124 (land)



view thread (5+ messages)  latest in thread

Message-ID: <60d6fkhxwl.fsf@dev6.int.libertyrms.info>
Permalink:  ../60d6fkhxwl.fsf@dev6.int.libertyrms.info/
Also on:    postgresql.org/message-id/60d6fkhxwl.fsf@dev6.int.libertyrms.info

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: cbbrowne@libertyrms.info, astrope@e-corp.net
  Subject: Re: How Many Inserts Per Transactions
  In-Reply-To: <60d6fkhxwl.fsf@dev6.int.libertyrms.info>
  References: <60d6fkhxwl.fsf@dev6.int.libertyrms.info>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox