Обсуждение: Re: [SQL] Slow Inserts Again

>> This last attempt, I bracket each insert statement with
>                               ^^^^^^^^^^^^^^^^^^^^^
>> "begin;" and "end;".
>
>Why _each_?
>Enclose ALL statements by begin; & end; to insert ALL data
>in SINGLE transaction:

This was suggested by someone on the list so that all
150,000 inserts would not be treated as one large transaction.

Like I said before, I have tried all suggestions without success.

>
> >> This last attempt, I bracket each insert statement with
> >                               ^^^^^^^^^^^^^^^^^^^^^
> >> "begin;" and "end;".
> >
> >Why _each_?
> >Enclose ALL statements by begin; & end; to insert ALL data
> >in SINGLE transaction:
>
> This was suggested by someone on the list so that all
> 150,000 inserts would not be treated as one large transaction.
>
> Like I said before, I have tried all suggestions without success.

    For  huge  amounts  of  data I usually group the inserts into
    chunks of 1000 or so and enclose  the  chunks  by  BEGIN/END.
    Have you tried that already?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

Hmmm I've had problems with punctuation and stuff when importing large quantities of test into my DB. But I've always
hadsuccess using copy. Have you tried using perl to munge your data and escape the appropriate characters?
 
 I've always used the following to import data into a clean DB.
 copy fubar from '/home/pierre/data/fubar.txt' using delimiters ',';
 How are you building your import files? That is how are you putting your data  together? 
 For me simply using a regex of: s/'/''/g  and s/,/\\,/g on each text field BEFORE I  dump it into my data file is
sufficientto allow it to be imported using the copy command.
 
 So...for a table that has three varchar columns, A/B/C my data file might look like:
 However\, I''m here.,Don''t take me serisouly.,Hi there!
 The above would be imported correctly. I may be missing something as I just started reading this thread, but I hope
thishelps...
 
 -=pierre

> 
> >> This last attempt, I bracket each insert statement with
> >                               ^^^^^^^^^^^^^^^^^^^^^
> >> "begin;" and "end;".
> >
> >Why _each_?
> >Enclose ALL statements by begin; & end; to insert ALL data
> >in SINGLE transaction:
> 
> This was suggested by someone on the list so that all
> 150,000 inserts would not be treated as one large transaction.
> 
> Like I said before, I have tried all suggestions without success.
> 
> 
> 
> 
> 
> 

>> Why _each_?
>> Enclose ALL statements by begin; & end; to insert ALL data
>> in SINGLE transaction:

But the transaction boundaries wouldn't have anything to do with
Frank's real problem, which is that the insertions are getting
slower and slower.  There's no good reason for that; and other
people are not reporting any comparable problems.  (Considering
that we *have* been getting trouble reports for more-than-2-gig
tables, it's clear that people are putting large amounts of data
into 6.5; so it's not like Frank is stressing the system more
than it has been before.)

Frank, what does the memory usage of the backend that's processing
this insertion look like; has it been growing steadily?  I'm wondering
whether you could have a problem with poor malloc behavior, or some
such.
        regards, tom lane

> >> Why _each_?
> >> Enclose ALL statements by begin; & end; to insert ALL data
> >> in SINGLE transaction:
> 
> But the transaction boundaries wouldn't have anything to do with
> Frank's real problem, which is that the insertions are getting
> slower and slower.  There's no good reason for that; and other
> people are not reporting any comparable problems.  (Considering
> that we *have* been getting trouble reports for more-than-2-gig
> tables, it's clear that people are putting large amounts of data
> into 6.5; so it's not like Frank is stressing the system more
> than it has been before.)
> 
> Frank, what does the memory usage of the backend that's processing
> this insertion look like; has it been growing steadily?  I'm wondering
> whether you could have a problem with poor malloc behavior, or some
> such.

Yes, memory use it the place to look.  We have had bugs in the past that
did not free memory, causing this problem.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Is it at all possible that the optimizer is responsible for this behaviour?
The reason I ask is that the fact that when the inserts start the optimizer is
working with a very small table, as the table grows larger the optimizer never
learns it because no VACUUM or VACUUM ANALYZE have been run.

If this is indeed the problem, then running VACUUM after every 10,000 inserts
would solve the problem.

Just curious what you folks think.

Rudy

On 3 May 99, at 12:19, Bruce Momjian wrote:

> > >> Why _each_?
> > >> Enclose ALL statements by begin; & end; to insert ALL data
> > >> in SINGLE transaction:
> > 
> > But the transaction boundaries wouldn't have anything to do with
> > Frank's real problem, which is that the insertions are getting
> > slower and slower.  There's no good reason for that; and other
> > people are not reporting any comparable problems.  (Considering
> > that we *have* been getting trouble reports for more-than-2-gig
> > tables, it's clear that people are putting large amounts of data
> > into 6.5; so it's not like Frank is stressing the system more
> > than it has been before.)
> > 
> > Frank, what does the memory usage of the backend that's processing
> > this insertion look like; has it been growing steadily?  I'm wondering
> > whether you could have a problem with poor malloc behavior, or some
> > such.
> 
> Yes, memory use it the place to look.  We have had bugs in the past that
> did not free memory, causing this problem.
> 
> -- 
>   Bruce Momjian                        |  http://www.op.net/~candle
>   maillist@candle.pha.pa.us            |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> 
> 

"Rudy Gireyev" <rgireyev@cnmnetwork.com> writes:
> Is it at all possible that the optimizer is responsible for this behaviour?

Not in the sense that it might be choosing a bad plan --- there is only
one way to insert a tuple ;-).  Size of the table doesn't matter.

If the problem is indeed some kind of memory leak, the optimizer is as
likely a place for the leak as any other, I suppose.
        regards, tom lane