From: | Erik Jones <erik(at)myemma(dot)com> |
---|---|
To: | Jean-David Beyer <jeandavid8(at)verizon(dot)net> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Bunching "transactions" |
Date: | 2007-10-25 15:51:57 |
Message-ID: | 1041C95E-ABA0-4FD0-8B77-586374444F4F@myemma.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Oct 25, 2007, at 10:30 AM, Jean-David Beyer wrote:
> I have just changed around some programs that ran too slowly (too
> much time
> in io-wait) and they speeded up greatly. This was not unexpected,
> but I
> wonder about the limitations.
>
> By transaction, I mean a single INSERT or a few related INSERTs.
>
> What I used to do is roughly like this:
>
> for each file {
> for each record {
> BEGIN WORK;
> INSERT stuff in table(s);
> if error {
> ROLLBACK WORK
> }
> else {
> COMMIT WORK;
> }
> }
> }
>
> The speedup was the obvious one:
>
> for each file {
> BEGIN WORK;
> for each record {
> INSERT stuff in table(s);
> }
> if error {
> ROLLBACK WORK
> }
> else {
> COMMIT WORK;
> }
> }
>
> This means, of course, that the things I think of as transactions
> have been
> bunched into a much smaller number of what postgreSQL thinks of as
> large
> transactions, since there is only one per file rather than one per
> record.
> Now if a file has several thousand records, this seems to work out
> just great.
>
> 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?
You're reading data from a file and generating inserts? Can you not
use COPY? That would be the most performant.
Erik Jones
Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2007-10-25 16:06:11 | Re: Bunching "transactions" |
Previous Message | Jean-David Beyer | 2007-10-25 15:30:08 | Bunching "transactions" |