#19 CPU

[Cecil Chan]

Hi JP:
I migrated logger to version "V3.0.7" from 2.7. I found that CPU increased a lot with same number of parameters on targed ASE. The new version use above 5K ms verse older version use 1K ms. The only new thing is the new SP that I installed for V 3.0.7, and ASE parameter did not changes.
Would you suggest what to check and change.

Another issue is the logger is trying to clean object <ASE server="">_SysMon table. How do I remove that from clean up routine.

Cecil

[Cecil Chan]

Hi JP, I think OpObjAct_V157.xml that cause high CPU. Would you advice on solution. Below is the statement executed . We have over 230 databases in this ASE.

select DBID,ObjectID,IndexID, LogicalReads, PhysicalReads, APFReads, PagesRead, PhysicalWrites, PagesWritten, UsedCount, RowsInserted, RowsDeleted, RowsUpdated, Operations, LockRequests, LockWaits, OptSelectCount,
HkgcRequests, HkgcPending, HkgcOverflows,
SharedLockWaitTime, ExclusiveLockWaitTime, UpdateLockWaitTime,
-- Fix jConnect error when date is 'Jan 1 1900 12:00AM'
ObjectCacheDate=case when ObjectCacheDate < '01/01/1901' then '01/01/1901' else ObjectCacheDate end
into #tmpOpObjectAct
from master..monOpenObjectActivity
where LogicalReads > 0
and ObjectID is not null

select dbname=db_name(O.DBID),objname=case when D.name is null then convert(varchar,ObjectID) else (case when O.DBID in (142,178,156,31,41,90,162,122,32,33,73,176,174,57,175,151,109,94,36,40,179,106,159,110,85,25,113,114,115,116,117,118,119,160,46,107,37,26,290,167,130,170,50,60,112,38,108,146,121,138,93,86,81,145,154,21,70,7,104,12,13,47,75,10,141,64,6,51,152,44,45,153,11,76,163,83,171,169,72,155,77,140,79,92,80,9,82,129,91,30,23,96,100,42,29,24,139,20,88,123,147,4,27,74,28,17,18,161,84,177,111,157,168,158,134,144,8,19,149,165,166,5,53,48,1,3,39,31513,31514,2,15,128,143,172,173,126,127,49,16,89) then object_name(ObjectID, O.DBID) else convert(varchar,ObjectID) end) end, IndID=IndexID,
LogicalReads, PhysicalReads, APFReads, PagesRead, PhysicalWrites, PagesWritten, UsedCount, RowsInserted, RowsDeleted, RowsUpdated,
Operations, LockRequests, LockWaits, OptSelectCount,
indname=case when D.name is null then convert(varchar,IndexID) else (case when O.DBID in (142,178,156,31,41,90,162,122,32,33,73,176,174,57,175,151,109,94,36,40,179,106,159,110,85,25,113,114,115,116,117,118,119,160,46,107,37,26,290,167,130,170,50,60,112,38,108,146,121,138,93,86,81,145,154,21,70,7,104,12,13,47,75,10,141,64,6,51,152,44,45,153,11,76,163,83,171,169,72,155,77,140,79,92,80,9,82,129,91,30,23,96,100,42,29,24,139,20,88,123,147,4,27,74,28,17,18,161,84,177,111,157,168,158,134,144,8,19,149,165,166,5,53,48,1,3,39,31513,31514,2,15,128,143,172,173,126,127,49,16,89) then index_name(O.DBID,ObjectID,IndexID) else convert(varchar,IndexID) end) end,
HkgcRequests, HkgcPending, HkgcOverflows,
SharedLockWaitTime, ExclusiveLockWaitTime, UpdateLockWaitTime, ObjectCacheDate,
ObjectID,
ownerid=case when O.DBID in (142,178,156,31,41,90,162,122,32,33,73,176,174,57,175,151,109,94,36,40,179,106,159,110,85,25,113,114,115,116,117,118,119,160,46,107,37,26,290,167,130,170,50,60,112,38,108,146,121,138,93,86,81,145,154,21,70,7,104,12,13,47,75,10,141,64,6,51,152,44,45,153,11,76,163,83,171,169,72,155,77,140,79,92,80,9,82,129,91,30,23,96,100,42,29,24,139,20,88,123,147,4,27,74,28,17,18,161,84,177,111,157,168,158,134,144,8,19,149,165,166,5,53,48,1,3,39,31513,31514,2,15,128,143,172,173,126,127,49,16,89) then object_owner_id(ObjectID, O.DBID) else null end
from #tmpOpObjectAct O left outer join master..sysdatabases D
on O.DBID=D.dbid
/ and D.status&1 != 1 / / database upgrading /
and D.status&32 != 32/ database created for load /
and D.status&64 != 64 / database recovery /
and D.status&256 != 256 / database suspect /
and D.status&2048 != 2048 / dbo use only /
and D.status&4096 != 4096 / single user /
and D.status2&16 != 16 / database offline /
and D.status2&32 != 32 / database offline /
and D.status2&512 != 512 / database currently upgrading /
and D.status3&8 != 8 / databse in shutdown /
and D.status3&8192 != 8192 / A drop database is in progress. /
where O.DBID is not null
and case when D.name is null then convert(varchar,ObjectID) else (case when O.DBID in (142,178,156,31,41,90,162,122,32,33,73,176,174,57,175,151,109,94,36,40,179,106,159,110,85,25,113,114,115,116,117,118,119,160,46,107,37,26,290,167,130,170,50,60,112,38,108,146,121,138,93,86,81,145,154,21,70,7,104,12,13,47,75,10,141,64,6,51,152,44,45,153,11,76,163,83,171,169,72,155,77,140,79,92,80,9,82,129,91,30,23,96,100,42,29,24,139,20,88,123,147,4,27,74,28,17,18,161,84,177,111,157,168,158,134,144,8,19,149,165,166,5,53,48,1,3,39,31513,31514,2,15,128,143,172,173,126,127,49,16,89) then object_name(ObjectID, O.DBID) else convert(varchar,ObjectID) end) end is not null
and IndexID is not null
drop table #tmpOpObjectAct

[Jean-Paul Martin]

Hi Cecil,
the only big difference between 2.7 and 3.0.7 for this collector is that I check in a CASE if database is accessible (not offline, not recovering ...) to get to object_name and object_owner_id.
Anyway if you have too many databases or objects you can add a filter clause in your config file.
For example :

<md filter_clause=" and LogicalReads>100000 and DBID not in (xx, yy, zz)"> OpObjAct_switch.xml </md>

The less objects selected, the less calls to object_name() and object_owner_id() functions, and less CPU.
Best regards
Jpm

[Jean-Paul Martin]

For your other question, what do you mean by "the logger is trying to clean object <ASE server="">_SysMon table" ?
Do mean the standard purge activity ?
You can change the retention days for a single collector this way (to keep more or less days than the default) in your config file :

<md daysToKeep="xx"> SysMon.xml   </md>

where "xx" is the number of days you want to keep in your archive database for this collector.

Cheers
Jpm

[Cecil Chan]

Thanks JP. Hope everything well.

1. I removed OpObjAct_V157.xml to reduce CPU usages.
2. I did not add MonSQL.xml, but it still try to purge <ASE>_SysMon table. It produce these message below . Should I be concern , and how to elimiated these message.

2018/03/08 13:30:46.386 Purge <ASE> - Purge Thread : archive, error in loop. com.sybase.jdbc4.jdbc.SybSQLException: <ASE_name>_SysMon not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

com.sybase.jdbc4.jdbc.SybSQLException: <ASE>_SysMon not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

    at com.sybase.jdbc4.tds.Tds.processEed(Tds.java:4112)
    at com.sybase.jdbc4.tds.Tds.nextResult(Tds.java:3229)
    at com.sybase.jdbc4.jdbc.ResultGetter.nextResult(ResultGetter.java:78)
    at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:302)
    at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:284)
    at com.sybase.jdbc4.jdbc.SybStatement.updateLoop(SybStatement.java:2762)
    at com.sybase.jdbc4.jdbc.SybStatement.executeUpdate(SybStatement.java:2746)
    at com.sybase.jdbc4.jdbc.SybStatement.executeUpdate(SybStatement.java:596)
    at asemon_logger.PurgeThread.archiveIndic(PurgeThread.java:87)
    at asemon_logger.PurgeThread.run(PurgeThread.java:158)

2018/03/08 13:30:46.387 Purge <ASE> - Purge Thread - archive,Closing connection
2018/03/08 13:30:46.409 Purge <ASE> - Purge Thread : error in loop. java.lang.NullPointerExc

[Jean-Paul Martin]

Hi Cecil, this is very strange. Did you drop the xxx_SysMon table ??? The purge thread is trying to purge it and doesn't find it !

The xxx_SysMon table is managed by the SysMon collector (not MonSQL as you refer in your post)

By the way you should modify your config file in ordre to use the new xxxx_switch collector (see the "sampleConfig.xml" file at the root of the asemon_logger distribution)
xxxx_switch.xml (rather than xxx_V157 for example) allow asemon_logger to choose automatically the proper version of the collector according to the version of the monitored server
But I am sure this not the source of your problem

Cheeers
Jpm

[Cecil Chan]

Hi JP, I did not found that in tar file asemon_logger_V3.0.7.tar . Where can I download the correct version that has the template.

[Jean-Paul Martin]

The file "sampleConfig.xml" is in the root directory of the asemon_logger_V3.0.7.tar.
I just checked, it is there
Or do you talk about another file ?