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:
Show quoted text
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)-------------
--------------
Show quoted text
TIP 4: Don't 'kill -9' the postmaster
Import Notes
Resolved by subject fallback