Repost: Help, Sybase CPU Utilisation
JonG
C'mon folks, someone must have some hints on this one ;-)
I have a system (RS6000 59H) running Sybase 10, on which, the CPU is 95%
utilized. I have recently slowed down the speed at which several external
daemons access the server in an attempt to reduce CPU loading.
The effect of this was to shift the CPU breakdown from (roughly) 50/50 to
80/20 (sys/usr) without decreasing the overall utilisation.
I am assuming that this is because the CPU is now 'free' to carry out lower
priority system tasks such as writing the data out to disk. Is this a valid
assumption ?
Also, sp_sysmon figures for %idle, %io and %cpu only seem to relate to the
OS's(i.e. vmstat) %usr - is this correct ?
Any comments or suggestions are gratefully appreciated.
TIA
JonG.
Bslade
When you say "the CPU is 95%" do you mean CPU usage as measured in Unix,
or CPU as measured by sp_monitor inside Sybase? You probably mean as
measured in Unix.
A lot of Sybase servers basically poll for I/O instead of using interrupt
driven code. So if you have a small amount of work (sp_sysmon/sp_monitor
reports low cpu usage) you can still have a high Unix CPU usage (vmstat,
sar, or top).
You can get back some of the Unix CPU, at the expense of response time in
sybase by setting the cschedspins param down (at least in sys 10, I'm not
sure about sys 11).
Personally, I've always been a little confused as to why a high end
program like a Sybase dataserver would poll like a freshman programming
project. Bad style in a multitasking operating system.
Ben
bsl...@aol.com
http://iwebvote.web.aol.com:1080/cgi-bin/questions.pl?vb=4
******************************************
>Subject: Repost: Help, Sybase CPU Utilisation
>From: "JonG" <jo...@bigfoot.com>
Date: Mon, 05 May 1997 22:40:04 -0400
Message-ID: <01bc59bf$93661e20$e58951a6@gittjon>
Karen Hogoboom
Bslade wrote:
> A lot of Sybase servers basically poll for I/O instead of using interrupt
> driven code. So if you have a small amount of work (sp_sysmon/sp_monitor
> reports low cpu usage) you can still have a high Unix CPU usage (vmstat,
> sar, or top).
>
> Personally, I've always been a little confused as to why a high end
> program like a Sybase dataserver would poll like a freshman programming
> project. Bad style in a multitasking operating system.
[...]
Here's what I understand to be the situation.
Sybase dataserver (Sybase SQL Server) runs as one process as
viewed by the OS. Threads are implemented inside of SQL Server.
SQL Server manages the task switching between threads. They are
Sybase-written threads since the product was designed to be
minimally operating system/machine dependent (unlike most
freshman or grad student programming projects), and not all
OS's support the same sorts of threads, uniformly, on all
platform/OS/version combinations that Sybase's customers run
their servers on. (Especially circa 1985.) Some OS versions
don't even have a kernel thread package, so you'd end up using
user-level threads after all. In short, one Sybase thread
cannot count on using a generic OS blocking system call without
blocking the whole server.
So, why not have dataserver be a collection of independent
processes? Well, think about how to make all those processes
cooperate while all sharing the same disk blocks all the time.
And what process manages the locks after all? Now you get some
flavor of message passing/shared memory hack to implement a
centralized lock manager process, plus who knows how much more
semaphore mgmt. This all yields code complexity and performance
drag.
Next, user level thread switching can be much faster than a kernel
trap/context switch, and in fact, dataserver usually finds other
stuff to do rather than constant polling. You should only see a lot
of polling when it's otherwise sitting around twiddling its
thumbs with nothing to do other than run Housekeeper (if sys 11).
(I suppose you could make it block at this point, but then you don't
want it to get paged out and controlling that is another system
dependent thing.)
In fact, Sybase's implementation of threads also allows
Sybase to have control of performance optimization, rather than
relying on finding all the various and sundry ways to extract the
best performance from each OS/platform's unique combination
of features, and, uh, other things.
All that said, I believe that Sybase decided to use NT threads
for its 11.x SQL Server on NT/Intel because that's the best way
to get screaming performance. We may be seeing more system
dependence (when it's worth it), or a movement to the more
uniform threads packages that are available now, and that weren't
around when dataserver was first written. Here is one reason I
think Sybase's adaptive server approach is a stroke of brilliance.
It's much easier to take advantage of advances in technology &
standards this way.
-karenh http://www.hogoboom.com/
JonG
Ben,
Thanks for the reply, yes when I said 95% Util, I meant from the OS.
I have searched for the cschedspins parameter, but cannot find it in either
sp_configure or any of the manuals, where can I set this ?
Also, as an aside, does anyone know if Sybase 11 behaves in the same way ?
TIA
Jon.
Bslade <bsl...@aol.com> wrote in article
<01bc5f55$2d5475a0$e58951a6@gittjon>...
> When you say "the CPU is 95%" do you mean CPU usage as measured in Unix,
> or CPU as measured by sp_monitor inside Sybase? You probably mean as
> measured in Unix.
>
> A lot of Sybase servers basically poll for I/O instead of using interrupt
> driven code. So if you have a small amount of work (sp_sysmon/sp_monitor
> reports low cpu usage) you can still have a high Unix CPU usage (vmstat,
> sar, or top).
>
> You can get back some of the Unix CPU, at the expense of response time in
> sybase by setting the cschedspins param down (at least in sys 10, I'm not
> sure about sys 11).
>
> Personally, I've always been a little confused as to why a high end
> program like a Sybase dataserver would poll like a freshman programming
> project. Bad style in a multitasking operating system.
>
Ranga Gopalan
In system 10 you could to use the buildmaster command to change the value
of cschedspins.
Shut down the server
buildmaster -y cschedspins=1 -d <master_device_path> /* to set cschedspins
to 1 */
buildmaster -yall -d <master_device_path> /* to display all config
parameters including cschedspins */
With System 10, I think you may also be able to use 'dbcc tune' on some
platforms to set the value of this parameter at runtime without rebooting
the server. I'm not sure of this.
In system XI, you need to change the parameter 'runnable process search
count' using sp_configure. You need not reboot the server for the new value
to take effect. (The effect of the parameter is pretty much the same with
System XI).
HTH,
Ranga.
==============================================================
Ranga Gopalan *** My opinions only, not my employer's ***
Stratus Professional and Technical Services,
Stratus Computer Inc, Marlboro, MA Email: Ranga_...@stratus.com
==============================================================
JonG <jo...@bigfoot.com> wrote in article
<01bc5f57$3ffbeec0$e58951a6@gittjon>...
John McVicker
Ranga,
Yes, as you said, you can use buildmaster - but be very careful.
Forgetting to use -y properly could rebuild the master device (ouch).
Also, you mentioned the use of DBCC Tune. The DBA would need to
use DBCC Tune in the following manner: DBCC TUNE("schedspins", <n>).
This is only valid in System 10 - not in 4.x or 11.
Do not preceed with a 'c' when using DBCC TUNE, but with buildmaster,
the syntax does include a 'c' so the -ycschedspins=<n> is used. The
'c' means "configuration block parameter".
However, this is permanent (just like buildmaster) in System 10. It
is also dynamic - meaning it takes effect immediatly - without reboot.
Changing the master device does require a reboot. So, DBCC TUNE
wins out on usability and its permanent. In System 11, it is also
dynamic with the mentioned sp_configure "runnable process search count",
<n>
Ranga Gopalan wrote:
>
> In system 10 you could to use the buildmaster command to change the value
> of cschedspins.
>
Greg Thain
When changing cschedspins, it is important to experiment with several
values, and understand their impact on the OS and SQL clients.
Despite what the system 10 doc claims, 1 is not necessarily the
optimal value for uniprocessor systems. In fact, I've seen dramatic
slowdowns occur when this value is set to 1.
-greg
"Ranga Gopalan"<Ranga_...@stratus.com > writes:
>
> In system 10 you could to use the buildmaster command to change the value
> of cschedspins.
>
> Shut down the server
> buildmaster -y cschedspins=1 -d <master_device_path > /* to set cschedspins
> to 1 */
> buildmaster -yall -d <master_device_path > /* to display all config
> parameters including cschedspins */
>
> HTH,
>
> Ranga.
> ==============================================================
> Ranga Gopalan *** My opinions only, not my employer's ***
> Stratus Professional and Technical Services,
> Stratus Computer Inc, Marlboro, MA Email: Ranga_...@stratus.com
> ==============================================================
>
>
> JonG <jo...@bigfoot.com > wrote in article
> <01bc5f57$3ffbeec0$e58951a6@gittjon >...
> > Ben,
> > Thanks for the reply, yes when I said 95% Util, I meant from the OS.
> >
> > I have searched for the cschedspins parameter, but cannot find it in
> either
> > sp_configure or any of the manuals, where can I set this ?
> >
> > Also, as an aside, does anyone know if Sybase 11 behaves in the same way
> ?
> >
> > TIA
> >
> > Jon.
>
--
Greg Thain (520) 733-6396 voice
Sunquest Information System (520) 733-6602 FAX
th...@sunquest.sunquest.com