Skip site navigation (1) Skip section navigation (2)

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

From: Andrew Sullivan <andrew(at)libertyrms(dot)info>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Really bad insert performance: what did I do wrong?
Date: 2003-02-21 17:37:19
Message-ID: 20030221123719.C16866@mail.libertyrms.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)libertyrms(dot)info>                              M2P 2A8
                                         +1 416 646 3304 x110


In response to

pgsql-performance by date

Next:From: scott.marloweDate: 2003-02-21 17:55:41
Subject: Re: Really bad insert performance: what did I do wrong?
Previous:From: Kevin WhiteDate: 2003-02-21 17:21:38
Subject: Re: Really bad insert performance: what did I do wrong?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group