pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
LOCK 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>
  2003-01-25 19:07 ` Re: LOCK TABLE & speeding up mass data loads Rod Taylor <rbt@rbt.ca>
  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 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   ` Re: LOCK TABLE & speeding up mass data loads 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 18:57 LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-25 19:07 ` Re: LOCK TABLE & speeding up mass data loads Rod Taylor <rbt@rbt.ca>
@ 2003-01-25 19:19   ` Ron Johnson <ron.l.johnson@cox.net>
  2003-01-26 23:10     ` Re: LOCK TABLE & speeding up mass data loads Curt Sampson <cjs@cynic.net>
  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-25 18:57 LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-25 19:07 ` Re: LOCK TABLE & speeding up mass data loads Rod Taylor <rbt@rbt.ca>
  2003-01-25 19:19   ` Re: LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
@ 2003-01-26 23:10     ` Curt Sampson <cjs@cynic.net>
  2003-01-27 09:08       ` Re: LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-27 09:45       ` Re: LOCK TABLE & speeding up mass data loads Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  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-25 18:57 LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-25 19:07 ` Re: LOCK TABLE & speeding up mass data loads Rod Taylor <rbt@rbt.ca>
  2003-01-25 19:19   ` Re: LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-26 23:10     ` Re: LOCK TABLE & speeding up mass data loads Curt Sampson <cjs@cynic.net>
@ 2003-01-27 09:08       ` Ron Johnson <ron.l.johnson@cox.net>
  2003-01-27 09:57         ` Re: LOCK TABLE & speeding up mass data loads Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-01-27 10:23         ` Re: LOCK TABLE & speeding up mass data loads 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-25 18:57 LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-25 19:07 ` Re: LOCK TABLE & speeding up mass data loads Rod Taylor <rbt@rbt.ca>
  2003-01-25 19:19   ` Re: LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-26 23:10     ` Re: LOCK TABLE & speeding up mass data loads Curt Sampson <cjs@cynic.net>
  2003-01-27 09:08       ` Re: LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
@ 2003-01-27 09:57         ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  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-25 18:57 LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-25 19:07 ` Re: LOCK TABLE & speeding up mass data loads Rod Taylor <rbt@rbt.ca>
  2003-01-25 19:19   ` Re: LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-26 23:10     ` Re: LOCK TABLE & speeding up mass data loads Curt Sampson <cjs@cynic.net>
  2003-01-27 09:08       ` Re: LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
@ 2003-01-27 10:23         ` Curt Sampson <cjs@cynic.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

* Re: LOCK TABLE & speeding up mass data loads
  2003-01-25 18:57 LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-25 19:07 ` Re: LOCK TABLE & speeding up mass data loads Rod Taylor <rbt@rbt.ca>
  2003-01-25 19:19   ` Re: LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-26 23:10     ` Re: LOCK TABLE & speeding up mass data loads Curt Sampson <cjs@cynic.net>
@ 2003-01-27 09:45       ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-01-27 09:54         ` Re: LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-27 10:00         ` Re: LOCK TABLE & speeding up mass data loads Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  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-25 18:57 LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-25 19:07 ` Re: LOCK TABLE & speeding up mass data loads Rod Taylor <rbt@rbt.ca>
  2003-01-25 19:19   ` Re: LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-26 23:10     ` Re: LOCK TABLE & speeding up mass data loads Curt Sampson <cjs@cynic.net>
  2003-01-27 09:45       ` Re: LOCK TABLE & speeding up mass data loads Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-01-27 09:54         ` Ron Johnson <ron.l.johnson@cox.net>
  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-25 18:57 LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-25 19:07 ` Re: LOCK TABLE & speeding up mass data loads Rod Taylor <rbt@rbt.ca>
  2003-01-25 19:19   ` Re: LOCK TABLE & speeding up mass data loads Ron Johnson <ron.l.johnson@cox.net>
  2003-01-26 23:10     ` Re: LOCK TABLE & speeding up mass data loads Curt Sampson <cjs@cynic.net>
  2003-01-27 09:45       ` Re: LOCK TABLE & speeding up mass data loads Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-01-27 10:00         ` 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


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