Date: Thu, 25 Oct 2007 15:15:31 -0400
From: Jean-David Beyer <jeandavid8@verizon.net>
Subject: Re: Bunching "transactions"
In-reply-to: <60k5pbcc50.fsf@dba2.int.libertyrms.com>
To: pgsql-performance@postgresql.org
Message-id: <4720EB53.6070009@verizon.net>
Organization: Institute for Regimented Whimsey
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7bit
References: <4720B680.5060407@verizon.net>
	<60k5pbcc50.fsf@dba2.int.libertyrms.com>
User-Agent: Thunderbird 1.5.0.9 (X11/20070317)

Chris Browne wrote:
> jeandavid8@verizon.net (Jean-David Beyer) writes:
>> But what is the limitation on such a thing? In this case, I am just
>> populating the database and there are no other users at such a time. I am
>> willing to lose the whole insert of a file if something goes wrong -- I
>> would fix whatever went wrong and start over anyway.
>>
>> But at some point, disk IO would have to be done. Is this just a function of
>> how big /pgsql/data/postgresql.conf's shared_buffers is set to? Or does it
>> have to do with wal_buffers and checkpoint_segments?
> 
> I have done bulk data loads where I was typically loading hundreds of
> thousands of rows in as a single transaction, and it is worth
> observing that loading in data from a pg_dump will do exactly the same
> thing, where, in general, each table's data is loaded as a single
> transaction.

I guess a reasonable standard of performance would be that if my initial
population of the database takes only a little longer than a restore of the
database using pg_restore, I am pretty close, and that is good enough. Of
course, the restore depends on how fast my tape drive can pull the tape --
it claims up to 12 MB/sec transfer rate, so it looks as though it will be
tape-limited rather than postgreSQL-limited.
> 
> It has tended to be the case that increasing the number of checkpoint
> segments is helpful, though it's less obvious that this is the case in
> 8.2 and later versions, what with the ongoing changes to checkpoint
> flushing.

I am running postgresql-8.1.9-1.el5 because that is what comes with RHEL5.
I probably will not upgrade until a little while after RHEL7 comes out,
since I hate upgrading.
> 
> In general, this isn't something that typically needs to get tuned
> really finely; if you tune your DB, in general, "pretty big
> transactions" should generally work fine, up to rather large sizes of
> "pretty big."


-- 
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 15:05:01 up 2 days, 7:23, 5 users, load average: 4.11, 4.22, 4.16