pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kevin White <kwhite@digital-ics.com>
To: Andrew Sullivan <andrew@libertyrms.info>
Cc: pgsql-performance@postgresql.org
Subject: Re: Really bad insert performance: what did I do wrong?
Date: Fri, 21 Feb 2003 12:21:38 -0500
Message-ID: <3E566022.5010200@digital-ics.com> (raw)
In-Reply-To: <20030221115917.A16866@mail.libertyrms.com>
References: <3E564695.7030906@digital-ics.com>
	<20030221115917.A16866@mail.libertyrms.com>

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?)

> My experiences in moving from a Linux box to a low-end Sun is pretty
> similar.  The problem usually turns out to be a combination of
> overhead on fsync (which shows up as processor load instead of i/o,
> oddly); and memory contention, especially in case there are too-large
> numbers of shared buffers 

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.

Shridhar Daithankar wrote:
 > First, check vmstat or similar on SunOS. See what is maxing out. 
Second tunr
 > postgresql trace on and see where it is specnding most of the CPU.

Do you mean turning on the statistics generators, or gprof?

 > Needless to say, did you tune shared buffers?

Like I mentioned above, I haven't yet found good info on what to do to 
actually tune shared buffers...I know how to change them, but that's 
about it.  I'll poke around some more.


Tom Lane wrote:
 > You should be able to find details in the archives, but the key point
 > is to do
 > 	cd .../src/backend
 > 	gmake clean
 > 	gmake PROFILE="-pg" all
 > to build a profile-enabled backend.  You may need a more complex
 > incantation than -pg on Solaris, but it works on other platforms.

I did this, but my gmon.out doesn't appear to have much data from the 
actual child postgres process, just the parent.  I might be wrong, and 
I'm letting some stats generate.

However, to everyone, I DID find a problem in my code that was making it 
take super forever long....the code doesn't just insert.  It is also 
written to do updates if it needs to, and because of what I'm doing, I 
end up doing selects back against the table during the load to find 
previously loaded rows.  In this case, there aren't any, because the 
table's been trunced, but...having turned the indexes off, those selects 
were really really slow.  Using the stats tools told me that.

So, that may have been a large part of my problem.  I'm still seeing the 
process just SIT there, though, for seconds at a time, so there's 
probably something else that I can fix.  Maybe I should just randomly 
try a larger buffers setting...

Being able to analyze the output of gmon would be nice, but as I said 
before, it doesn't appear to actually change much...

Right now, the load has been running continuously for several minutes. 
It is 12:20pm, but the time on the gmon.out file is 12:18pm.  I should 
be able to let the load finish while I'm at lunch, and I might be able 
to get something out of gmon when it is done...maybe writing to gmon 
just doesn't happen constantly.

Thanks all...

Kevin




view thread (11+ messages)  latest in thread

Message-ID: <3E566022.5010200@digital-ics.com>
Permalink:  ../3E566022.5010200@digital-ics.com/
Also on:    postgresql.org/message-id/3E566022.5010200@digital-ics.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: kwhite@digital-ics.com, andrew@libertyrms.info
  Subject: Re: Really bad insert performance: what did I do wrong?
  In-Reply-To: <3E566022.5010200@digital-ics.com>
  References: <3E564695.7030906@digital-ics.com> <20030221115917.A16866@mail.libertyrms.com> <3E566022.5010200@digital-ics.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox