Re: Bunching "transactions"

Chris Browne wrote:
> jeandavid8(at)verizon(dot)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