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(dot)Boekhold(at)et(dot)tudelft(dot)nl |
-----------------------------------------------------------------------------