cancel
Showing results for 
Search instead for 
Did you mean: 

database engine used HIGH CPU!!!

Ahmad Munawwar
Frequent Advisor

database engine used HIGH CPU!!!

 
10 REPLIES
Massimo Bianchi
Honored Contributor

Re: database engine used HIGH CPU!!!

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
Frequent Advisor

Re: database engine used HIGH CPU!!!

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
Honored Contributor

Re: database engine used HIGH CPU!!!

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
Trusted Contributor

Re: database engine used HIGH CPU!!!

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
Frequent Advisor

Re: database engine used HIGH CPU!!!

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
Frequent Advisor

Re: database engine used HIGH CPU!!!

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
Honored Contributor

Re: database engine used HIGH CPU!!!

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
Honored Contributor

Re: database engine used HIGH CPU!!!

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

Re: database engine used HIGH CPU!!!

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
Ahmad Munawwar
Frequent Advisor

Re: database engine used HIGH CPU!!!

Hi,

Lastly, I found where the root cause of the problem. My user database full whne checking using "sp_helpdb " no free kbytes at all (0). I dump the databse and get some free space. Now, I'm thinking of alter (extend) this user databse. As I never did before, what are preliminary parameter to consider and how to entend the database.

Regards,
Munawwar