SQL Anywhere 9.0.2.3850 - 100% CPU usage
darkwerks
100% CPU usage, need to restart DB, how can i find out what causes the
Problem?
Server Infos:
ActiveReq 3
AvailIO 240
BuildChange 612369
BuildClient dbbuild_90_90head
BuildProduction YES
BuildReproducible 1
BytesReceived 3843159040
BytesReceivedUncomp 1207232369
BytesSent 57068063
BytesSentUncomp 3606518814
C2 NO
CacheAllocated 11990
CacheFile 1287649
CacheFileDirty 1932
CacheFree 65252
CacheHitsEng 3172121839
CachePanics 0
CachePinned 24355
CacheReadEng 3191207621
CacheReplacements 0
CacheScavengeVisited 1364800
CacheScavenges 1364739
CarverHeapPages 781
CharSet cp1252
CommandLine -c 16000M -cc+ -cr+ -cv+ -ti 0 -x tcpip -zl -zx 60000 -zo
F:\\debug.log D:\\DATABASE.db
CompactPlatformVer W.NET #3790 SP 2
CompanyName iAnywhere Solutions, Inc.
ConnsDisabled OFF
ConsoleLogFile
CurrentCacheSize 16383996
DefaultCollation 1252LATIN1
DiskReadEng 999964
FipsMode NO
FreeBuffers 100
FunctionName max
HeapsCarver 105
HeapsLocked 4294964104
HeapsQuery 4294967258
HeapsRelocatable 932
HttpPorts
IdleTimeout 0
IsFipsAvailable NO
IsIQ NO
IsJavaAvailable NO
IsNetworkServer YES
IsRuntimeServer NO
JavaGlobFix 0
JavaObjectsEnabled NO
Language us_english
LegalCopyright Copyright © 1989-2007 Sybase, Inc. Portions Copyright ©
2002-2007, iAnywhere Solutions, Inc. All rights reserved. All
unpublished rights reserved.
LegalTrademarks Sybase is a trademark of Sybase, Inc.
LicenseCount 999999999
LicenseType networked seat (per-seat)
LicensedCompany XY
LicensedUser XY
LicensesInUse 224
LivenessTimeout 120
LockedCursorPages 12230
LockedHeapPages 12365
LogExpensiveQueries 60000
MachineName XY
MainHeapBytes 59014720
MainHeapPages 5069
MapPhysicalMemoryEng 0
MaxCacheSize 16383996
MaxMessage 738
MessageWindowSize 400
MinCacheSize 16383996
MultiPacketsReceived 58200
MultiPacketsSent 794482
MultiPageAllocs 3
Name XY
NativeProcessorArchitecture X86_64
NumProcessorsAvail 4
NumProcessorsMax 0
OmniIdentifier 1057019703:XY:XY:15792000
PacketsReceived 115949821
PacketsReceivedUncomp 116016560
PacketsSent 117021046
PacketsSentUncomp 117502212
PageSize 4096
PeakCacheSize 16383996
Platform Windows2003
PlatformVer Windows 2003 Build 3790 Service Pack 2
ProcessCPU 56279.765625
ProcessCPUSystem 7566.046875
ProcessCPUUser 48713.718750
ProcessorArchitecture X86_64
ProductName Adaptive Server Anywhere
ProductVersion 9.0.2.3850
ProfileFilterConn -1
ProfileFilterUser
QueryHeapPages 4294967208
QuittingTime none
RememberLastPlan OFF
RememberLastStatement ON
RemoteputWait 14724
Req 115465945
RequestFilterConn -1
RequestFilterDB -1
RequestLogFile F:\\debug.log
RequestLogMaxSize 0
RequestLogNumFiles -1
RequestLogging NONE
RequestTiming OFF
SendFail 0
StartDBPermission DBA
StartTime 2009-11-24 11:03:18.354
TempDir C:\\WINDOWS\\TEMP
Threads 20
TimeZoneAdjustment 60
TotalBuffers 2180
UnschReq 0
Database Infos:
AuditingTypes none
Authenticated NO
BlankPadding OFF
BlobArenas ON
CacheHits 2328435284
CacheRead 2402345801
CacheReadIndInt 0
CacheReadIndLeaf 242465475
CacheReadTable 1826077290
Capabilities 41BFFFDD
CaseSensitive OFF
CaseSensitivePasswords OFF
CharSet cp1252
CheckpointLogBitmapPagesWritten 54
CheckpointLogBitmapSize 1
CheckpointLogCommitToDisk 554
CheckpointLogPageInUse 1032
CheckpointLogPagesRelocated 0
CheckpointLogPagesWritten 65312
CheckpointLogSavePreimage 65318
CheckpointLogSize 7305
CheckpointLogWrites 4351
CheckpointUrgency 6
Checksum OFF
Chkpt 26
ChkptFlush 21500
ChkptPage 65318
ClusteredIndexes ON
Collation 1252DEU
CommitFile 149940
CompressedBTrees ON
Compression OFF
ConnCount 519
ConnsDisabled OFF
CurrIO 34
CurrRead 12
CurrWrite 20
CurrentRedoPos 197333861423
DBFileFragments 11853
DiskRead 956548
DiskReadIndInt 2726
DiskReadIndLeaf 73532
DiskReadTable 1109511
DiskWrite 288078
DriveType FIXED
Encryption None
ExprCacheAbandons 0
ExprCacheDropsToReadOnly 0
ExprCacheEvicts 143
ExprCacheHits 854472
ExprCacheInserts 133861
ExprCacheLookups 989754
ExprCacheResumesOfReadWrite 0
ExprCacheStarts 30210
ExtendDB 29502
ExtendTempWrite 27588
File D:\\DATABASE.db
FileSize 7057166
FileVersion 41
FreePageBitMaps ON
FreePages 2521833
FullCompare 312059
GetData 37163332
GlobalDBId 2147483647
HashForcedPartitions 0
HashRowsFiltered 23931154
HashRowsPartitioned 0
HashWorkTables 0
HistogramHashFix OFF
Histograms ON
IOToRecover 2709
IQStore OFF
IdleCheck 316222
IdleChkTime 0
IdleChkpt 23
IdleWrite 101144
IndAdd 580458
IndLookup 24720758
IndexStatistics OFF
JavaHeapSize 0
JavaNSSize 0
LTMGeneration 0
LTMTrunc 18446744073709551615
Language de,en
LargeProcedureIDs ON
LockTablePages 1
LogFileFragments 40
LogFreeCommit 41
LogName I:\\cymed.log
LogWrite 147685
MapPages 4294965209
MaxIO 273
MaxRead 33
MaxWrite 256
MultiByteCharSet OFF
NamedConstraints OFF
PageRelocations 0
PageSize 4096
PreserveSource ON
ProcedurePages 76
ProcedureProfiling OFF
QueryBypassed 4579781
QueryCachePages 10
QueryCachedPlans 2
QueryJHToJNLOptUsed 1
QueryLowMemoryStrategy 0
QueryOptimized 9669445
QueryReused 92
QueryRowsBufferFetch 75304379
QueryRowsMaterialized 6301610
ReadOnly OFF
RecoveryUrgency 0
RecursiveIterations 0
RecursiveIterationsHash 0
RecursiveIterationsNested 0
RecursiveJNLMisses 0
RecursiveJNLProbes 0
RelocatableHeapPages 19033
RemoteTrunc 18446744073709551615
RollbackLogPages 0
SeparateCheckpointLog ON
SeparateForeignKeys ON
SortMergePasses 0
SortRowsMaterialized 0
SortRunsWritten 0
SortSortedRuns 621892
SortWorkTables 0
StringHistogramsFix ON
SyncTrunc 18446744073709551615
TableBitMaps ON
TablesQualTriggers ON
TempFileName C:\\WINDOWS\\TEMP\\asat0001.tmp
TempTablePages 271
TransactionsSpanLogs ON
TriggerPages 5
UniqueIdentifier OFF
VariableHashSize ON
ViewPages 15
Jeff Albion [Sybase iAnywhere]
darkwerks wrote:
> Hi,
> 100% CPU usage, need to restart DB, how can i find out what causes the
> Problem?
Some questions:
1) Does the server ever come out of the 100% CPU situation if you jsut
let it run?
2) How have you verified the 100% CPU situation? Do you have multiple
cores - does this happen across all cores or just one core?
3) Have you noticed a pattern of when it gets into this situation - do
you run certain reports, is it at a particular time of day, how many
times has it happened already ...?
4) What is the "normal" workload characteristic for the server? Have you
tried monitoring the database using the Windows Performance Monitor?
What's the I/O like on the disk during these high CPU times?
---
There are generally a few techniques to try to solve 100% CPU
situations. The first is request-level logging to see if you can
identify any incoming queries that may be causing the issue:
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbugen9/00000169.htm
Running with the "-z" switch on the server may help identifying which
applications are connecting and disconnecting.
---
Outside of that, opening a technical support case and working with a
technical support rep to obtain internal engineering tools to better
identify the problem inside the server may be appropriate:
http://www.sybase.com/contactus/support/
Regards,
--
Jeff Albion, Sybase iAnywhere
iAnywhere Developer Community :
http://www.sybase.com/developer/library/sql-anywhere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0
Report a Bug/Open a Case : http://case-express.sybase.com/cx/
darkwerks
1.) no, can't do that. it's a customers db, he restarts. it's always
urgent that the user can work again.
2.) 4 Cores, all of them at 100%
3.) no
4.) The usual workload isn't that high. it's a high-performance
system.
Breck Carter [TeamSybase]
http://www.risingroad.com/LogExpensiveQueries/LogExpensiveQueries.html
Breck
--
Breck Carter - Blog: http://sqlanywhere.blogspot.com/
SQLA questions and answers: http://sqla.stackexchange.com
RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail