Re: [HACKERS] Heh, the disappearing problem!

On Mon, 9 Mar 1998, Karl Denninger wrote:

> On Mon, Mar 09, 1998 at 11:05:49PM -0500, Bruce Momjian wrote:
> > > I've seen this same kind of behavior in a few other places as well; in
> > > places where you are doing reads and writes in a mixed environment (ie: read
> > > something, write something (typically in a different table) based on what 
> > > you read) performance of 6.3 has gone in the toilet.  Update jobs that used 
> > > to run in tens of seconds are requiring several minutes to complete now.
> > > 
> > > And again, we're not seeing much disk I/O during this period - but we *ARE*
> > > seeing a hell of a lot of CPU activity, almost all in user mode.
> > 
> > OK, how about using postgres -t option or profiling to get the function
> > using so much cpu?  This may help us tell where the problem lies.  Does
> > anyone else see such problems?  All other reports I hear was that 6.3
> > was faster.
> 
> Docs on this somewhere?
> 
> I'll be happy to profile it if I can figure out how! :-)

I have just figured this out. First, you can get some timing information 
from postgresql itself by including '-tpa -tpl -te' on the backend 
options, second, you can recompile postgresql with profiling on (look in 
Makefile.global for profiling options, adjust them for your syetem). 
Then, *don't do a 'make install'* but only copy the postgres binary (in 
backend dir) to your postgresql bin-dir, as postgres.profile. Whenever 
you want to profile, edit your start-script to use that postgres.profile 
as a backend. Run your queries, then copy the profile-output (gmon.out 
for gcc profiling) from the *database* directory you did your queries on 
(ie. if you connected to database 'template1', it'll be in 
data/base/template1).

Then do normal profiling stuff (foor gcc profiling, run 'gprof gmon.out 
bin/postgres.profile > prof.out' to get readable output).

If you have questions, please mail me privately.

Maarten

_____________________________________________________________________________
| TU Delft, The Netherlands, Faculty of Information Technology and Systems  |
|                   Department of Electrical Engineering                    |
|           Computer Architecture and Digital Technique section             |
|                          M.Boekhold@et.tudelft.nl                         |
-----------------------------------------------------------------------------