Thread: What could be the reason for EXTREMELY slow INSERTs?

What could be the reason for EXTREMELY slow INSERTs?

From
Daniel ?erud
Date:
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


Re: What could be the reason for EXTREMELY slow INSERTs?

From
Tom Lane
Date:
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

Re: What could be the reason for EXTREMELY slow INSERTs?

From
"Poul L. Christiansen"
Date:
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

Re: What could be the reason for EXTREMELY slow INSERTs?

From
Richard Huxton
Date:
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

Re: Re: What could be the reason for EXTREMELY slow INSERTs?

From
Daniel ?erud
Date:
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
>