Thread: What could be the reason for EXTREMELY slow INSERTs?
I have this little test application that compares retreiving and inserting data using indexes and not using indexes. I have tried this program on two different machines. One is a PIII 533 256 meg memory and SCSI. The other one is a dual 400 (something) 512 meg memory. The thing is that the PII _SELECTS_ faster (this is OK since the program uses one thread/process) than the dual 400 but inserts is a complete different story. 10000 inserts takes 665 seconds on the PIII 533, while on the dual 400 it takes about 13 seconds (*GULP!*). I have done a clean reinstallation (dpkg --purge postgresql, apt- get install postgresql) and tried it again. Same results. Do you guys know what could be the reason? Thanks. Daniel Åkerud
Daniel ?erud <zilch@home.se> writes:
> 10000 inserts takes 665 seconds on the PIII 533, while on
> the dual 400 it takes about 13 seconds (*GULP!*).
Maybe postmaster running with -F on one system but not the other?
Or referential integrity checks defined on one table and
not the other?
regards, tom lane
Try to run a vacuum on the box with slow inserts (don't know if that helps, though). Run a /sbin/hdparm -t <harddisk>, to compare the speed of the harddisks. HTH, Poul L. Christiansen Daniel ?erud wrote: > > I have this little test application that compares > retreiving and inserting data using indexes and not using > indexes. I have tried this program on two different > machines. One is a PIII 533 256 meg memory and SCSI. The > other one is a dual 400 (something) 512 meg memory. The > thing is that the PII _SELECTS_ faster (this is OK since > the program uses one thread/process) than the dual 400 but > inserts is a complete different story. > > 10000 inserts takes 665 seconds on the PIII 533, while on > the dual 400 it takes about 13 seconds (*GULP!*). I have > done a clean reinstallation (dpkg --purge postgresql, apt- > get install postgresql) and tried it again. Same results. > Do you guys know what could be the reason? > > Thanks. > > Daniel Åkerud > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Daniel ?erud wrote: > > 10000 inserts takes 665 seconds on the PIII 533, while on > the dual 400 it takes about 13 seconds (*GULP!*). I have > done a clean reinstallation (dpkg --purge postgresql, apt- > get install postgresql) and tried it again. Same results. > Do you guys know what could be the reason? Are you running the same version on each machine? There was a discussion recently where the first thousand inserts were very fast, the next thousand fast, the next thousand acceptable... until it grinds to a halt. I think it was patched before 7.1RC1 - check the list archives for details on the discussion. Oh - you don't have constraints on the one but not the other, do you? - Richard Huxton
On the "fast machine", I did a clean install and run my test-project. This constist of some cpp files and a shell script to run them. Anyway, I totally reinstalled postgres on the slow machine and there was no difference. Not much anyway. And i've tried it on both a 6.x version (comes with debian potato) and a 7.x version. Could a migrate from 6.x/7.x to 7.1 make a 665->13 seconds difference? It's actually a HP server machine. And by the way, the SELECT test is faster on the PIII 533 than the 400 mhz. About 13 seconds / 11 seconds or something. This is really strange... both databases were in other words just re-CREATEed. Daniel Åkerud > Daniel ?erud wrote: > > > > 10000 inserts takes 665 seconds on the PIII 533, while on > > the dual 400 it takes about 13 seconds (*GULP!*). I have > > done a clean reinstallation (dpkg --purge postgresql, apt- > > get install postgresql) and tried it again. Same results. > > Do you guys know what could be the reason? > > Are you running the same version on each machine? There was a discussion > recently where the first thousand inserts were very fast, the next > thousand fast, the next thousand acceptable... until it grinds to a halt. > > I think it was patched before 7.1RC1 - check the list archives for > details on the discussion. > > Oh - you don't have constraints on the one but not the other, do you? > > - Richard Huxton > > ---------------------------(end of broadcast)------------- -------------- > TIP 4: Don't 'kill -9' the postmaster >