RE: [GENERAL] So slow inserts... Why? - Mailing list pgsql-general

1) Are you re-connecting for each insert?
   a) through TCP/IP for each insert?
       yes - If you can connect via unix sockets it will speed up your
connection time
   yes - Why would you be doing that?  Since in an earlier message you said
the your placing BEGIN and END around the insert to do them in one
transaction I'll assume you're smarter than that.
   no - Good, that would be insane
2) Are you sending -F to the backend at startup (this is done through the
postmaster startup with the -o param.)
   no - Then the backend is fsync'ing between each insert.  Which means you
are disk bound buddy.  If you are using a SCSI-raid then I'm wrong, if you
are using IDE then you get what you pay for.
  yes - Good the only reason I've found not to include that option is if you
don't trust your OS to properly buffer files for you, or not to crash once a
day.  (And if that's the case you should stop using Windows ASAP - just a
joke)
3) What plans do your queries give you? (the ones in the triggers).
   you should remember that those triggers will be called for each row
inserted or updated.
4) also remember to use TRANSACTION as much as possible with your
inserts/updates for the same reasons outlined in 2 above.

    -DEJ
> I removed your first mail, but if i remembered it right you was using PHP.
> I have seen on my server (dual P5/100) that the "startup" cost of the
> backend is rather high.
> If you is using PHP and not using persistent connection (or whatever it
> was
> called) you may have a lot of
> start/stop of backends.
>
> Whatewer the reason is, 1 transaction/sec is awfully slow. Something must
> be wrong.
> What other things is running on this machine. Is it highly loaded?
>
> /Johan
>
>
>
>
> Michal Samek <webmaster@tony.cz> on 99-05-26 16:15:10
>
> To:   Johan Granlund/Solid AB
> cc:   pgsql-general@postgresql.org
> Subject:  Re: [GENERAL] So slow inserts... Why?
>
>
>
>
> johan.granlund@solid.se wrote:
> >
> > As i dont know anything about triggers:)
> > Have you done a vacuum on the databases to clear out old transactions?
>
> I recreated the table, so I think there is not necessary to vacuum it
> (it takes unbelieveable amount of time); maybe when I'm filling the
> table by sending a lot of INSERT queries I should sometimes between
> inserts do a vacuum ? It looks strange for me.
>
> And many thanks for your help; I was thinking about dropping indexes and
> trigger, but It's impossible. Imagine this is not just a series of
> insert queries, the process I'm using is (simplicied): For each dbase
> record (the catalogue from the music label) to look-up if exist and if
> so, update; if no, insert. So I can't drop indexes which are needed by
> look-up function; nor can't drop trigger because it tracks inserts /
> updates of my table.
> I'm testing to group inserts / updates to blocks enclosed with
> transaction BEGIN; END;, but it now looks that it will help only little.
>
> PS I plan to rewrite the whole thing to Mysql (even it's no triggers, it
> will be harder but possible of course) and to report the result here if
> will be interesting)
>
>
> --
> Michal Samek, Tony distribuce s.r.o.
> webmaster@tony.cz  (++420659/321350)
> ICQ: 38607210
>
>
>
>