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

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

From: Kevin White <kwhite(at)digital-ics(dot)com>
To: Andrew Sullivan <andrew(at)libertyrms(dot)info>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Really bad insert performance: what did I do wrong?
Date: 2003-02-21 17:21:38
Message-ID: 3E566022.5010200@digital-ics.com (view raw or flat)
Thread:
Lists: 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


In response to

Responses

pgsql-performance by date

Next:From: Andrew SullivanDate: 2003-02-21 17:37:19
Subject: Re: Really bad insert performance: what did I do wrong?
Previous:From: Andrew SullivanDate: 2003-02-21 16:59:17
Subject: Re: Really bad insert performance: what did I do wrong?

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