pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLOCK TABLE & speeding up mass data loads
10+ messages / 4 participants
[nested] [flat]
* LOCK TABLE & speeding up mass data loads
@ 2003-01-25 18:57 Ron Johnson <ron.l.johnson@cox.net>
0 siblings, 1 reply; 10+ messages in thread
From: Ron Johnson @ 2003-01-25 18:57 UTC (permalink / raw)
To: pgsql-performance
Hi,
Would LOCK TABLE ACCESS EXCLUSIVE MODE speed things up, when I have
a script that loads data by setting transactions, and then committing
works after every few thousand INSERTs?
Thanks,
Ron
--
+---------------------------------------------------------------+
| Ron Johnson, Jr. mailto:ron.l.johnson@cox.net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| "Fear the Penguin!!" |
+---------------------------------------------------------------+
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: LOCK TABLE & speeding up mass data loads
@ 2003-01-25 19:07 Rod Taylor <rbt@rbt.ca>
parent: Ron Johnson <ron.l.johnson@cox.net>
0 siblings, 1 reply; 10+ messages in thread
From: Rod Taylor @ 2003-01-25 19:07 UTC (permalink / raw)
To: Ron Johnson <ron.l.johnson@cox.net>; +Cc: pgsql-performance
On Sat, 2003-01-25 at 13:57, Ron Johnson wrote:
> Hi,
>
> Would LOCK TABLE ACCESS EXCLUSIVE MODE speed things up, when I have
> a script that loads data by setting transactions, and then committing
> works after every few thousand INSERTs?
If you're the only person working on the database, then no. If you're
fighting for resources with a bunch of other people -- then possibly,
but the others won't get anything done during this timeframe (of
course).
Oh, and you're using COPY right?
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: LOCK TABLE & speeding up mass data loads
@ 2003-01-25 19:19 Ron Johnson <ron.l.johnson@cox.net>
parent: Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 10+ messages in thread
From: Ron Johnson @ 2003-01-25 19:19 UTC (permalink / raw)
To: pgsql-performance
On Sat, 2003-01-25 at 13:07, Rod Taylor wrote:
> On Sat, 2003-01-25 at 13:57, Ron Johnson wrote:
> > Hi,
> >
> > Would LOCK TABLE ACCESS EXCLUSIVE MODE speed things up, when I have
> > a script that loads data by setting transactions, and then committing
> > works after every few thousand INSERTs?
>
> If you're the only person working on the database, then no. If you're
> fighting for resources with a bunch of other people -- then possibly,
> but the others won't get anything done during this timeframe (of
> course).
Ok.
> Oh, and you're using COPY right?
No. Too much data manipulation to do 1st. Also, by committing every
X thousand rows, then if the process must be aborted, then there's
no huge rollback, and the script can then skip to the last comitted
row and pick up from there.
--
+---------------------------------------------------------------+
| Ron Johnson, Jr. mailto:ron.l.johnson@cox.net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| "Fear the Penguin!!" |
+---------------------------------------------------------------+
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: LOCK TABLE & speeding up mass data loads
@ 2003-01-26 23:10 Curt Sampson <cjs@cynic.net>
parent: Ron Johnson <ron.l.johnson@cox.net>
0 siblings, 2 replies; 10+ messages in thread
From: Curt Sampson @ 2003-01-26 23:10 UTC (permalink / raw)
To: Ron Johnson <ron.l.johnson@cox.net>; +Cc: pgsql-performance
On Sun, 25 Jan 2003, Ron Johnson wrote:
> > Oh, and you're using COPY right?
>
> No. Too much data manipulation to do 1st. Also, by committing every
> X thousand rows, then if the process must be aborted, then there's
> no huge rollback, and the script can then skip to the last comitted
> row and pick up from there.
I don't see how the amount of data manipulation makes a difference.
Where you now issue a BEGIN, issue a COPY instead. Where you now INSERT,
just print the data for the columns, separated by tabs. Where you now
issue a COMMIT, end the copy.
cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: LOCK TABLE & speeding up mass data loads
@ 2003-01-27 09:08 Ron Johnson <ron.l.johnson@cox.net>
parent: Curt Sampson <cjs@cynic.net>
1 sibling, 2 replies; 10+ messages in thread
From: Ron Johnson @ 2003-01-27 09:08 UTC (permalink / raw)
To: pgsql-performance
On Sun, 2003-01-26 at 17:10, Curt Sampson wrote:
> On Sun, 25 Jan 2003, Ron Johnson wrote:
>
> > > Oh, and you're using COPY right?
> >
> > No. Too much data manipulation to do 1st. Also, by committing every
> > X thousand rows, then if the process must be aborted, then there's
> > no huge rollback, and the script can then skip to the last comitted
> > row and pick up from there.
>
> I don't see how the amount of data manipulation makes a difference.
> Where you now issue a BEGIN, issue a COPY instead. Where you now INSERT,
> just print the data for the columns, separated by tabs. Where you now
> issue a COMMIT, end the copy.
Yes, create an input file for COPY. Great idea.
However, If I understand you correctly, then if I want to be able
to not have to roll-back and re-run and complete COPY (which may
entail millions of rows), then I'd have to have thousands of seperate
input files (which would get processed sequentially).
Here's what I'd like to see:
COPY table [ ( column [, ...] ) ]
FROM { 'filename' | stdin }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ] ]
[COMMIT EVERY ... ROWS WITH LOGGING] <<<<<<<<<<<<<
[SKIP ... ROWS] <<<<<<<<<<<<<
This way, if I'm loading 25M rows, I can have it commit every, say,
1000 rows, and if it pukes 1/2 way thru, then when I restart the
COPY, it can SKIP past what's already been loaded, and proceed apace.
--
+---------------------------------------------------------------+
| Ron Johnson, Jr. mailto:ron.l.johnson@cox.net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| "Fear the Penguin!!" |
+---------------------------------------------------------------+
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: LOCK TABLE & speeding up mass data loads
@ 2003-01-27 09:45 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
parent: Curt Sampson <cjs@cynic.net>
1 sibling, 2 replies; 10+ messages in thread
From: Shridhar Daithankar @ 2003-01-27 09:45 UTC (permalink / raw)
To: pgsql-performance
On 27 Jan 2003 at 3:08, Ron Johnson wrote:
> Here's what I'd like to see:
> COPY table [ ( column [, ...] ) ]
> FROM { 'filename' | stdin }
> [ [ WITH ]
> [ BINARY ]
> [ OIDS ]
> [ DELIMITER [ AS ] 'delimiter' ]
> [ NULL [ AS ] 'null string' ] ]
> [COMMIT EVERY ... ROWS WITH LOGGING] <<<<<<<<<<<<<
> [SKIP ... ROWS] <<<<<<<<<<<<<
>
> This way, if I'm loading 25M rows, I can have it commit every, say,
> 1000 rows, and if it pukes 1/2 way thru, then when I restart the
> COPY, it can SKIP past what's already been loaded, and proceed apace.
IIRc, there is a hook to \copy, not the postgreSQL command copy for how many
transactions you would like to see. I remember to have benchmarked that and
concluded that doing copy in one transaction is the fastest way of doing it.
DOn't have a postgresql installation handy, me being in linux, but this is
definitely possible..
Bye
Shridhar
--
I still maintain the point that designing a monolithic kernel in 1991 is
afundamental error. Be thankful you are not my student. You would not get
ahigh grade for such a design :-)(Andrew Tanenbaum to Linus Torvalds)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: LOCK TABLE & speeding up mass data loads
@ 2003-01-27 09:54 Ron Johnson <ron.l.johnson@cox.net>
parent: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1 sibling, 0 replies; 10+ messages in thread
From: Ron Johnson @ 2003-01-27 09:54 UTC (permalink / raw)
To: pgsql-performance
On Mon, 2003-01-27 at 03:45, Shridhar Daithankar wrote:
> On 27 Jan 2003 at 3:08, Ron Johnson wrote:
>
> > Here's what I'd like to see:
> > COPY table [ ( column [, ...] ) ]
> > FROM { 'filename' | stdin }
> > [ [ WITH ]
> > [ BINARY ]
> > [ OIDS ]
> > [ DELIMITER [ AS ] 'delimiter' ]
> > [ NULL [ AS ] 'null string' ] ]
> > [COMMIT EVERY ... ROWS WITH LOGGING] <<<<<<<<<<<<<
> > [SKIP ... ROWS] <<<<<<<<<<<<<
> >
> > This way, if I'm loading 25M rows, I can have it commit every, say,
> > 1000 rows, and if it pukes 1/2 way thru, then when I restart the
> > COPY, it can SKIP past what's already been loaded, and proceed apace.
>
> IIRc, there is a hook to \copy, not the postgreSQL command copy for how many
I'll have to look into that.
> transactions you would like to see. I remember to have benchmarked that and
> concluded that doing copy in one transaction is the fastest way of doing it.
Boy Scout motto: Be prepared!! (Serves me well as a DBA.)
So it takes a little longer. In case of failure, the time would be
more than made up. Also, wouldn't the WAL grow hugely if many millions
of rows were inserted in one txn?
--
+---------------------------------------------------------------+
| Ron Johnson, Jr. mailto:ron.l.johnson@cox.net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| "Fear the Penguin!!" |
+---------------------------------------------------------------+
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: LOCK TABLE & speeding up mass data loads
@ 2003-01-27 09:57 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
parent: Ron Johnson <ron.l.johnson@cox.net>
1 sibling, 0 replies; 10+ messages in thread
From: Shridhar Daithankar @ 2003-01-27 09:57 UTC (permalink / raw)
To: pgsql-performance
On 27 Jan 2003 at 15:15, Shridhar Daithankar wrote:
> IIRc, there is a hook to \copy, not the postgreSQL command copy for how many
> transactions you would like to see. I remember to have benchmarked that and
> concluded that doing copy in one transaction is the fastest way of doing it.
>
> DOn't have a postgresql installation handy, me being in linux, but this is
> definitely possible..
I am sleeping. That should have read XP rather than linux.
Grrr..
Bye
Shridhar
--
Lowery's Law: If it jams -- force it. If it breaks, it needed replacing
anyway.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: LOCK TABLE & speeding up mass data loads
@ 2003-01-27 10:00 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
parent: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1 sibling, 0 replies; 10+ messages in thread
From: Shridhar Daithankar @ 2003-01-27 10:00 UTC (permalink / raw)
To: pgsql-performance
On 27 Jan 2003 at 3:54, Ron Johnson wrote:
> On Mon, 2003-01-27 at 03:45, Shridhar Daithankar wrote:
> > transactions you would like to see. I remember to have benchmarked that and
> > concluded that doing copy in one transaction is the fastest way of doing it.
>
> Boy Scout motto: Be prepared!! (Serves me well as a DBA.)
Goes for everything else as well..
>
> So it takes a little longer. In case of failure, the time would be
> more than made up. Also, wouldn't the WAL grow hugely if many millions
> of rows were inserted in one txn?
Nops.. If WAL starts recycling, postgresql should start flishing data from WAL
to data files.
At any given moment, WAL will not exceed of what you have configured. They are
just read ahead logs most of the times intended for crash recovery.
(Consequently it does not help setting WAL bigger than required.)
Bye
Shridhar
--
nominal egg: New Yorkerese for expensive.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: LOCK TABLE & speeding up mass data loads
@ 2003-01-27 10:23 Curt Sampson <cjs@cynic.net>
parent: Ron Johnson <ron.l.johnson@cox.net>
1 sibling, 0 replies; 10+ messages in thread
From: Curt Sampson @ 2003-01-27 10:23 UTC (permalink / raw)
To: Ron Johnson <ron.l.johnson@cox.net>; +Cc: pgsql-performance
On Mon, 27 Jan 2003, Ron Johnson wrote:
> > I don't see how the amount of data manipulation makes a difference.
> > Where you now issue a BEGIN, issue a COPY instead. Where you now INSERT,
> > just print the data for the columns, separated by tabs. Where you now
> > issue a COMMIT, end the copy.
>
> Yes, create an input file for COPY. Great idea.
That's not quite what I was thinking of. Don't create an input file,
just send the commands directly to the server (if your API supports it).
If worst comes to worst, you could maybe open up a subprocess for a psql
and write to its standard input.
> However, If I understand you correctly, then if I want to be able
> to not have to roll-back and re-run and complete COPY (which may
> entail millions of rows), then I'd have to have thousands of seperate
> input files (which would get processed sequentially).
Right.
But you can probably commit much less often than 1000 rows. 10,000 or
100,000 would probably be more practical.
cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2003-01-27 10:23 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-25 18:57 LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
2003-01-25 19:07 ` Rod Taylor <rbt@rbt.ca>
2003-01-25 19:19 ` Ron Johnson <ron.l.johnson@cox.net>
2003-01-26 23:10 ` Curt Sampson <cjs@cynic.net>
2003-01-27 09:08 ` Ron Johnson <ron.l.johnson@cox.net>
2003-01-27 09:57 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-01-27 10:23 ` Curt Sampson <cjs@cynic.net>
2003-01-27 09:45 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-01-27 09:54 ` Ron Johnson <ron.l.johnson@cox.net>
2003-01-27 10:00 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
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