CPU Utilization

[former_member197005]

Hi ,

Have a some strange situation.

1 week ago had some work with database (insert a lot of data, drop data).

In my database have many operation and many users, now, time to time I send 100% cpu.

And utilization fall only when I remove many connection(300-400 30% of all connection).

Utilization to increase very quicly.

Mabe somebody had this sutyation?

How define the reason?(processes of strongly utilization cpu I don't find in mon tables)

Susmon&cfg in attach(arhive rar)

sysmon_cfg_test_aix8k.txt.zip

[former_member197005]

Hi Mark

thanks for your answer.

I attach file where we had 100% cpu.

I see all monitoring,and all mon tables but we haven't process what really have a big cpu.

But when we had 100% cpu all process start have a big counters on cpu in monprocessstatement,because havent acccess for processor.

And we haven't smooth growth utilization, we have race.

@30-40% after 20 second we had 98%.

And this situation  during some period repeats.

About disabling 'object lockwait timing

We disable "enable monitorin" but haven't rezult.

This is same effect or need desable 'object lockwait timing??

sysmon.txt.zip

[Mark_A_Parsons]

I would explicitly disable 'object lockwait timing' just to be sure.

[former_member197005]

ok

may be some alse?

we see a big contention,  on default cache.

we have 32 partition, but big contention only on 2 every different partition.

[Mark_A_Parsons]

Could the issue be something other than 'object lockwait timing' ? Sure, but without some actual performance metrics all I could do is *guess* at what the issue might be.

The newest sp_sysmon output shows a grand total of 200K IOs per second on the default data cache with spinlock contention of 7-9%. The cache hits (200K/second) are tiny relative to the number of engines, and while the 7-9% spinlock contention is a concern, I don't see this explaining all of the 100% cpu utilization. [Yes, there are some steps you could take to reduce the default data cache spinlock contention, but that's another discussion.]

Transactional activity is quite low (for this number of engines). Cache hit rates are miniscule compared to what should be possible with 64 engines. There is some spinlock contention on descriptors and the default data cache, but not enough (in my opinion) to justify the 100% cpu utilization. There's little/no query compilation going on. I'm not seeing any 'normal' reasons for high cpu utilization.

I just noticed that you have disk mirroring enabled.  If you're not actively using disk mirroring I'd suggest turning this configuration option off, too. [This requires a dataserver reboot for the config change to take effect.]  [There is a small bit of overhead for ASE to manage disk mirroring, even if it's not being used.]

I'd suggest you explicitly disable 'object lockwait timing' and see if the issue still occurs.  If you still see high cpu utilization then I'd try collecting the other data (see my previous post) and/or open a case with Sybase tech support.

[Former Member]

I think Mark is right.

I also only see from sysmon quite high "Spinlock Contention" on default data cache.

First you shoud eliminate this problem and then check if this help.

Very interesting post about spinlock is:

http://scn.sap.com/community/ase-custom-applications/blog/2014/07/17/ase-16-managing-vldb-s-with-vlh...

This is more about 15.7 and 16 but aslo for you could help.

I think you could start with:

- increase default data cache to 64 partitions

- create extra cache for some "hot" objects

- create extra cache for system tables

-create extra cache for log