Re: Really bad insert performance: what did I do wrong?

On Fri, Feb 21, 2003 at 12:21:38PM -0500, Kevin White wrote:
> Andrew Sullivan wrote:
> > What's the disk subsystem?  Is fsync turned on in both cases?  And is
> > your IDE drive lying to you about what it's doing.
>
> It is IDE.  How do I turn fsync on or off?  (All I can find in the man
> is a function call to fsync...is there something else?)

By default, Postgres calls fsync() at every COMMIT.  Lots of IDE
drives lie about whether the fsync() succeeded, so you get better
performance than you do with SCSI drives; but you're not really
getting that performance, because the fsync isn't effectve.

On Linux, I think you can use hdparm to fix this.  I believe the
write caching is turned off under Solaris, but I'm not sure.

Anyway, you can adjust your postgresql.conf file to turn off fsync.

> This box only has 1 gig, and I've only set up 200 shared buffers...at
> this point, it is only me hitting it.  Increasing the shared buffers
> might help, but I haven't yet found the info I need to do that
> intelligently.

For stright inserts, it shouldn't matter, and that seems low enough
that it shouldn't be a problem.

You should put WAL on another disk, as well, if you can.

Also, try using truss to see what the backend is doing.  (truss -c
gives you a count and gives some time info.)

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110