Thread: Slow Inserts Again
We have been down this path, but I am at the point that I think this problem is so big, it makes postgreSQL unusable for me and could cause big problems for many of us with large databases. Especially if you need to reload your data, say, going to 6.5. I have a database that has a dozen or so tables, but one table in particular has about 60 fields that are all character fields ranging in size from 16 bytes to 128. This table has about 150,000 records. I have a text file with insert statements, one per line, that I feed through standard input to psql. I have tried many combinations of things to speed this up as you all have suggested. I have had no success using "copy" at all because of problems with quotes and other punctuation in the data. This last attempt, I bracket each insert statement with "begin;" and "end;". What I am seeing this time around is in the beginning, the inserts were reasonable in speed. Say 6 or 7 per second. But now that it is up to record 100,000 or so (3 DAYS later) the time between inserts is about 10 SECONDS. As progress is made, the inserts continue to get slower and slower. So at the current rate, I have another 138 hours before completion! This is on a fast Red Hat 5.2 machine. Plenty of RAM, Ultra-wide scsi, 450 MHz Pentium II. No index tables exist in the database while reloading. I will build them after loading the data. My biggest fear is as this database continues to grow, headed toward 500,000 records or more, it seems that it will reach the point (I may already have) that the data can not ever be reloaded again, which is a scary situation. I'm still searching for help. Thanks.
Frank Morton wrote: > > 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: begin; insert ....; insert ....; ... insert ....; end; Vadim
At 16:21 +0300 on 03/05/1999, Frank Morton wrote: > I have tried many combinations of things to speed this > up as you all have suggested. I have had no success > using "copy" at all because of problems with quotes > and other punctuation in the data. I must tell you, this doesn't sound reasonable to me. It's usually very easy, if you already have a program that writes down the fields, to make sure it scans the contents thereof and adds a backslash before each tab, newline and backslash in every one of the fields. If what you need is to dump-reload, then this is exactly what the dump program will do for you. > This last attempt, I bracket each insert statement with > "begin;" and "end;". Not necessary. An insert has an implicit begin-end around it. If you want to change that behaviour, group several inserts together in begin-end. Putting them individually is redundant. > What I am seeing this time around is in the beginning, > the inserts were reasonable in speed. Say 6 or 7 > per second. But now that it is up to record 100,000 or > so (3 DAYS later) the time between inserts is about > 10 SECONDS. As progress is made, the inserts > continue to get slower and slower. So at the current > rate, I have another 138 hours before completion! Perhaps you need to break the connection. That is, every 1000 records, disconnect from the backend, and connect again. A vacuum every so often may also help somewhat. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
Yes, it's normal in this situation to do a number in each transaction, maybe a few thousand. But the fact that copy doesn't work for you is a worry. Getting this fixed seems like the best option. Frank Morton wrote: > > We have been down this path, but I am at the point > that I think this problem is so big, it makes postgreSQL > unusable for me and could cause big problems for > many of us with large databases. Especially if you > need to reload your data, say, going to 6.5. > > I have a database that has a dozen or so tables, but > one table in particular has about 60 fields that are all > character fields ranging in size from 16 bytes to 128. > This table has about 150,000 records. I have a text > file with insert statements, one per line, that I feed > through standard input to psql. > > I have tried many combinations of things to speed this > up as you all have suggested. I have had no success > using "copy" at all because of problems with quotes > and other punctuation in the data. > > This last attempt, I bracket each insert statement with > "begin;" and "end;". > > What I am seeing this time around is in the beginning, > the inserts were reasonable in speed. Say 6 or 7 > per second. But now that it is up to record 100,000 or > so (3 DAYS later) the time between inserts is about > 10 SECONDS. As progress is made, the inserts > continue to get slower and slower. So at the current > rate, I have another 138 hours before completion! > > This is on a fast Red Hat 5.2 machine. Plenty of RAM, > Ultra-wide scsi, 450 MHz Pentium II. > > No index tables exist in the database while reloading. > I will build them after loading the data. > > My biggest fear is as this database continues to grow, > headed toward 500,000 records or more, it seems that > it will reach the point (I may already have) that the data > can not ever be reloaded again, which is a scary > situation. > > I'm still searching for help. Thanks. -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com