Thread: 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