database engine used HIGH CPU!!!

[Massimo Bianchi]

Hi,
i have no knoledge of sybase, but i know oracle, and i can give you some general guidelines.

First thing i would check that there are not other process consuming CPU or DISKS.

Expecially those that have an high disks access rate can impair performance of the sybase.

Swap usage: check swap usage, and determine if it is higher than normal.

Was there a patch installation, recently, that can have caused the performance loss ?

Is the H/W all correct, are there no single bit errors ?

When we have discovered that there is nothing external the database, we ca go in.

Was this the first occurence of this error, or was it pre-existent ?


Did you sybase code change, your developers released new version of the code ?

Were there any massive load in the dataase?
Were index rebuild properly ?
Are statistcs up-to-date ?

Once i had a sybase db that had some CPU with high usage, it turned out that there was a poorly written query in loop. we killed it each time.

Is it always the same query ?


HTH,
Massimo

[Ahmad Munawwar]

Hi,

From the output of sp_sysmon, it show that Total Disk I/O checks is 15839096 and Checks return I/O is 28945, it is only baout 2% of the time a requested I/O had completed. And I also found that device path /var/sybase/rdsk/rdatadev1 datadev1 VERY BUSY on "reads" process about 99.9%. I only know this info but do not how to handle it.


br,
Munawwar

[Massimo Bianchi]

O.K.,
so it looks like the time is spent on reading.

What data is there on that rawdisk ?
Can an index be usefull ?
Are statistics (if exists) running ?

Massimo

[Fragon]

Hi Munawwar,
What's the result for
#sar -u 5 5
Please decide which module actually take most of the CPU resource!

Some time a disk bottle-neck will take most CPU for I/O process especially for DB.

-ux

[Ahmad Munawwar]

Hi,

Massimo:
We never done update statistics on the database table and stored procedure recompile.
There is a rawdata in the rawdisk contains data from other network elements.

-ux:
I don't have sar input right know, i will do it soon. From the "glance" ouput it show that sybase use up most of the CPU resources.

Regards,
Munawwar

[Ahmad Munawwar]

Hi,

The following is sar -u 5 5 output:

sar -u 5 5

HP-UX s09brf01 B.10.20 B 9000/869 07/02/03

11:40:12 %usr %sys %wio %idle
11:40:17 12 4 0 84
11:40:22 1 1 0 98
11:40:27 4 2 0 94
11:40:32 1 1 0 99
11:40:37 2 1 0 97

Average 4 2 0 94

br,
Munawwar

[Con O'Kelly]

Hi

To me those figures indicate you have no problem at all with CPU at that time. You are only averaging 4% Utilisation for that time period.

If you want to collect sar performance data for historical purposes (especially if you don't have MWA) you can add the following entry to cron
0,15,30,45 * * * * /usr/lbin/sa/sa1

Ensure that if it doesn't exist you create the /var/adm/sa directory.

This will collect sar data every 15mins and can be then be used to investigate future performance issues. Also ensure that you have enough space in /var filesystem as the sar data files can be quite large depending on system activity etc.

Cheers
Con

[Con O'Kelly]

Hi

To me those figures indicate you have no problem at all with CPU at that time. You are only averaging 4% Utilisation for that time period.

If you want to collect sar performance data for historical purposes (especially if you don't have MWA) you can add the following entry to cron
0,15,30,45 * * * * /usr/lbin/sa/sa1

Ensure that if it doesn't exist you create the /var/adm/sa directory.

This will collect sar data every 15mins and can be then be used to investigate future performance issues. Also ensure that you have enough space in /var filesystem as the sar data files can be quite large depending on system activity etc.

Cheers
Con

[Dan Herington]

I am only slightly familiar with Sybase. I understand it does "spin-waits", in which it will use CPU even when it has nothing to do. It does this to keep the CPU cache hot and make sure it has lots of requests in the process run queue. That way, when it does have something to do, it can get it going very quickly.

You may want to consider using a partitioning technology to limit how much CPU Sybase can consume. PRM will allow you to access CPU cycles to multiple workloads in a ratio that you want - rather than what Sybase wants!

Dan