Re: Really bad insert performance: what did I do wrong? - Mailing list pgsql-performance

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