Performance Measurement & Process Tracking 
Author Message
 Performance Measurement & Process Tracking

I am responsible for performance management (amongst other things) for a
stockbroking system written using Uniface and Sybase. One thing I do fairly
often is to try and identify badly written stored procedures, or front end
programs, and these are my problems.

1. sp_monitor and sar (a unix activity measuement command) give contradictory
   results e.g.

                        CPU             WIO             IDLE

        sp_monitor      5               11              83
                        2                7              87
                        0               10              80
                        4                9              80
                        0                0              83
                        0                0              82

                        usr     sys     wio             idle
        sar             60      24       1              14  
                        60      25       0              14  
                        51      26       2              21  
                        53      20       1              26  
                        63      24       2              11  
                        29      16       0              55  
                        74      26       1              0  
                        73      21       1              5  
                        68      31       1              0  
                        73      26       1              0  
                        76      23       1              0  

now these figures were collected at the same time on two different terminals,
on an HP-9000 / 832 with Sybase 4.9 being the only application running apart
from such things as cron, rwhod, inetd etc.

The two things just don't agree at all.

The other thing I would like to know, is how do you determine which user process
is hogging all the CPU, sp_who seems to be totally inadequate. Sybase is great
for a lot of things, and in general I like it, but administering it is a
minor nightmare. At this point I could start {*filter*}ing, however there is little
or no point because it won't achieve anything positive. Sybase has done a lot
of things to please the programmer, and the systems integrator, but I really
feel that they have left the administrators totally in the lurch. As I will be
looking after all the systems level post sales support (ughh !!) for this
product, I don't feel I have any of my usual tools to even analyse potential
problems. Instead of providing (admittedly really nice) new features for
distributed processing, I would like to see some tools to help me benchmark
my client server applications, nothing too fancy, I can knock up a front end
in excel or lotus myself, just *PLEASE* give me some way of collecting
performance metrics on sybase processes.

Last question, why can we kill sleeping processes ? I have a 4.2 server running
on SCO Unix 4.2, and it seems pretty flakey, the server will sometimes not
return the results of a simple select on a small (but heavily indexed) table,
send the processes to sleep, and leave my front ends hung until I kill them.
This is not a consistent thing, so it is hard to say what the problem is. The
only unusual thing was that there was a number 8 in the blk column of sp_who,
which did correspond with one of the hung processes but could I kill it. NO !,
was there a message in the errorlog indicating why it might be hung NO !, would
it have have a page lock, on a SELECT ?? who knows, all I know is that It
occured during a training course at our first and most critical site, and I had
the person doing the training screaming at me, my manager screaming at me, and
the one of the clients directors asking me why the system hangs on such a
simple operation, I on the other hand had to tell everybody to take a coffee
break while I shut down the server and waited for 10 minutes for SCO to release
the network sockets before I could bring the server back up again.

I know support is hard, and from what I've read, SYBASE TS analysts in the US
are strung out from the work load. This could probably be fixed to a great
degree if they gave us users, more tools, and more information so that we
can do a lot of our own support, A lot of the problem is that we HAVE NO CHOICE
but to wait for someone else to solve our problems, and this loss of control
leads to a lot of bad feeling especially when we get heat from our superiors
who don't understand technical issues, and dont like to us cooling our heels,
or working on something else.

E.G. How about letting us know about some of thos undocumented dbcc options,
or are we too irresponsible to be trusted with such things, WE ARE NOT
CHILDREN, give us information, let us empower ourselves, and just maybe we'll
all get along together a lot better.

OK so I did {*filter*} , it made me feel better. Sybase TS here in OZ is pretty
good, except when it comes to things THEY have no control over.

Nobody every remembers an easily fixed problem, only the horrors stand out,
this colours our perceptions.

Thank you for reading this far, and if anyone has any contructive info,
please let me know

John Martin
Software Manager (Don't Believe the Hype !!)
Trilogy Business Systems

m
processing which a fairly small percentage of sybase users n
A



Mon, 07 Aug 1995 15:14:36 GMT
 Performance Measurement & Process Tracking


Quote:
>I am responsible for performance management (amongst other things) for a
>stockbroking system written using Uniface and Sybase. One thing I do fairly
>often is to try and identify badly written stored procedures, or front end
>programs, and these are my problems.
>1. sp_monitor and sar (a unix activity measuement command) give contradictory
>   results e.g.
> [... deleted ...]
>now these figures were collected at the same time on two different terminals,
>on an HP-9000 / 832 with Sybase 4.9 being the only application running apart
>from such things as cron, rwhod, inetd etc.
>The two things just don't agree at all.

The sp_monitor data shows you what's happening within the context of the
Sybase engine(s).  This shows the real database work being done.  So CPU time
represents the time spent doing processing for some Sybase thread.

There is other work that Sybase is doing that consumes CPU time.  In
particular, the time spent waking up and checking the Sybase run queue for
the next ready thread.

This is why it is often the case that a system supporting Sybase looks very
busy, when there are in reality no active transactions.

So, what you're seeing is real.  Don't adjust your set.  Its just that the
measurements view the world from two different perspecitives.  One from the
view of Sybase engines, and one from the view of the hardware.

Hope that helps.

-- jsc
----------
J. Scott Carr                    voice: (503) 578-3495

Sequent Computer Systems                ...!uunet!sequent!jsc

Disclaimer:  The views presented here are my own, and not necessiarly
shared by my employer.



Wed, 09 Aug 1995 00:15:20 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. IO scheduler vs PostgreSQL performance measurement

2. ***********************SQL - Performance Measurement***********************

3. performance measurement

4. (Q) Performance/measurement tools?

5. Performance measurement

6. Performance measurement in SQL Anywhere--how?

7. OnBar performance measurement

8. Performance measurement in Ingres [Q]

9. Consistency Protocol Performance Measurement

10. performance measurements

11. Performance measurement

12. Performance Measurement


 
Powered by phpBB® Forum Software