Re: slow speeds after 2 million rows inserted

[Frank Finner <postgresql(at)finner(dot)de>]

When do you commit these inserts? I occasionally found similiar problems, when I do heavy inserting/updating within one single transaction. First all runs fast, after some time everything slows down. If I commit the inserts every some 1000 rows (large rows, small engine), this phenomenon does not occur. Maybe some buffer chokes if the transaction ist too big.

In your case I'd recommend to commit after every one or two million rows (if possible).

Regards, Frank.


On Fri, 29 Dec 2006 12:39:03 -0500 James Neff <jneff(at)tethyshealth(dot)com> thought long, then sat down and wrote:

> Greetings,
> 
> Ive got a java application I am reading data from a flat file and 
> inserting it into a table.  The first 2 million rows (each file 
> contained about 1 million lines) went pretty fast.  Less than 40 mins to 
> insert into the database.
> 
> After that the insert speed is slow.  I think I may be able to type the 
> data faster than what is being done by the java application on the third 
> file.

-- 
Frank Finner

Invenius - Lösungen mit Linux
Köpfchenstraße 36
57072 Siegen
Telefon: 0271 231 8606    Mail: frank(dot)finner(at)invenius(dot)de
Telefax: 0271 231 8608    Web:  http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF  6E6A A74E 67E4 E788 2651

Attachment: pgpPW56LuvOXH.pgp
Description: PGP signature