紧急救助，公司sybase数据库突然变慢。已将SP_SYSMON输出结果已附件形式上传
请各位高手支招啊，在线等 Cache: default data cacheper sec per xact count% of total
---------------------------------------------------------------------
Spinlock Contention n/a n/a n/a 13.0 %
先来解决这个问题 请问这说明了什么问题，又该如何着手解决？ 查了一下，自旋锁争用。但是该怎么解决一头雾水 继续求助中，在线等 仅仅有sp_sysmon output不管用，你需要找到resource consuming users/queries/procs，然后才能发现root cause：
1. stale stats? stats out of date?
2. table scan?
3. data skew?
建议你作如下：
1. update stats if the stats are too old
2. 找到 resource consumer, 可用下法如自己没有scripts
v12
* sp_clearstats
* dump tran master with truncate_only (the above step will generate trans in master)
* sp_reportstats (focus on users消耗最多的CPU or IO)
v15
* sp_monitor 'connection','cpu'
* sp_monitor 'connection','diskio'
* sp_monitor 'connection','elapsed time'
找到后
* find out the queries running by resource consuming users; MDA tables woud be helpful
* generate the query plan
* fix the table scans if there are any
3. increase the steps for stats if there is data skew Just saw your sp_sysmon output, I believe the issue came from table scans on big tables, you can easily pin-point the root cause as indicated above for v12.
CPU usage was very high for 3min sampling duration
Engine Busy UtilizationCPU Busy I/O Busy Idle
Average 87.9 % 0.3 % 11.8 %
Most likely they were from ad-hoc queries:
Statements Not Cached 64.6 7.0 11629 n/a
accessing history database(s)
Device:
/dev/vg01/rhyt_historydev5
hyt_historydev5 per sec per xact count% of total
---------------------------------------------------------------------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 5.8 0.6 1048 100.0 %
Writes 0.0 0.0 0 0.0 %
---------------------------------------------------------------------
Total I/Os 5.8 0.6 1048 18.8 %
-----------------------------------------------------------------------------
Device:
/dev/vg01/rhyt_historydev6
hyt_historydev6 per sec per xact count% of total
---------------------------------------------------------------------
Reads
APF 0.0 0.0 3 0.6 %
Non-APF 2.9 0.3 522 99.4 %
Writes 0.0 0.0 0 0.0 %
---------------------------------------------------------------------
Total I/Os 2.9 0.3 525 9.4 %
-----------------------------------------------------------------------------
Device:
/dev/vg01/rhyt_historydev7
hyt_historydev7 per sec per xact count% of total
---------------------------------------------------------------------
Reads
APF 0.0 0.0 2 0.8 %
Non-APF 1.4 0.1 243 99.2 %
Writes 0.0 0.0 0 0.0 %
---------------------------------------------------------------------
Total I/Os 1.4 0.1 245 4.4 %
Please also look into the writes on maindev1
Device:
/dev/vg01/rhyt_maindev1
hyt_maindev1 per sec per xact count% of total
---------------------------------------------------------------------
Reads
APF 0.0 0.0 1 0.1 %
Non-APF 0.9 0.1 163 20.6 %
Writes 3.5 0.4 628 79.3 %
---------------------------------------------------------------------
Total I/Os 4.4 0.5 792 14.2 % 这个系统看起来不是很关键，每秒才9条transactions. 主要业务：
Transaction Detail per sec per xact count% of total
---------------------------------------------------------------------
Inserts
APL Heap Table 93.5 10.2 16821 33.0 %
APL Clustered Table 181.3 19.7 32637 64.0 %
Data Only Lock Table 8.3 0.9 1498 2.9 %
---------------------------------------------------------------------
大量apl插入操作，如没有大的报表查询操作，考虑是否可以将apl改为dol表。
CPU任务切换原因有日志尾页竞争：
Last Log Page Writes 4.2 0.5 749 1.4 %
by Single Log Record 9.1 1.0 1635 43.1 %
可考虑修增大ULC
少量APF拒绝
APF Limit Overloads 35.7 3.9 6433 1.5 %
调大APL比例 对于事务频繁的系统，绝对赞成用DOL table替换APL table，LZ的问题是：系统一直运行正常，突然变慢，这只能从数据、stats 和code 找原因。对于history DB，数据更改上在晚上或周末发生，白天基本是查询，似乎APL table更合适。
页:
[1]