SYBASE巡检文档
新来这里带点礼物希望对你有用---------------------------------------
Sybase数据库巡检总结报告
1 配置信息表
1.1 主机系统配置信息
序号 1 2
主机名 cserver1 cserver2
主机机型 COMPAQ GS80 COMPAQ GS80
操作系统 Compaq Tru64 UNIX V5.1 Compaq Tru64 UNIX V5.1
CPU数量 4 4
内存 4GB 4GB
1.2 数据库主要配置信息
数据库安装路径: /sybase
版本及EBF信息:Adaptive Server Enterprise/12.0.0.4/P/SWR 10050 ESD 2/64bit
参数 1 2
total Memory: 3GB 3GB
user connections: 50 50
number of locks: 100000 100000
max online engines: 3 3
procedure cache percent: 20 20
2 巡检内容综述
检查项目 步骤 预计检查时间
1 检查数据库核心是否正常启动 1.登录操作系统sybase帐号2.运行showserver命令3.检查返回信息,以确定数据库核心程序正常启动 1-6项检查可在日常业务运行时操作,估计检查时间〈 4小时
2 检查数据库核心版本和EBF版本 1.进入isql程序登陆数据库服务器2.运行select @@version命令3.检查并记录返回信息
3 检查数据库核心的运行配置 1.检查SYBASE.cfg文件2.记录内存分配、锁个数、存储过程缓冲、多CPU配置、用户连接配置、网络包尺寸等重要参数的设置。3.对不合理的参数设置,经用户和应用开发商讨论且同意后加以调整并记录在案
4 检查数据库运行错误日志文件 1.检查errorlog文件中的各类信息包括数据库报错和操作系统报错2.一旦发现数据页损坏、日志溢出、硬盘I/O报错等严重问题须立刻进行现场解决并记录在案
5 全面检测数据库核心运行性能 1.业务运行时在数据库中运行sp_sysmon命令(5分钟)2.将输出结果定向至文件3.检查输出文件,重点监视以下指标:CPU占用、核心页交换、I/O流量、网络流量、锁竞争、内存调度等。4.发现指标异常应与开发商及用户了解情况,确定问题原因,提出改进意见,并记录在案
6 检测系统数据库/用户数据库的物理分配、空间使用情况 1.对各在用数据库(包括master库)运行sp_helpdb命令2.检查并记录输出结果3.对有问题或可能会导致问题的数据库空间分配情况实行处理,确保有足够的日志和数据空间
7 检测系统数据库/用户数据库的数据分配页物理可用性 1.对各在用用户数据库运行dbcc checkalloc()和checkcatalog()命令2.检查输出结果,将出错信息记录在案3.重大问题立刻进行现场解决并将处理结果记录在案 需停日常业务操作,估计检查时间2-3小时
8 检查日常数据库备份/恢复策略 1.了解用户的日常数据库备份/恢复步骤2.解决日常数据库备份/恢复中的问题3.提出完善性建议 日常检查,1小时
3 巡检过程综述
对巡检工作的实施过程进行总结性描述:
对以上8个方面的内容进行了全面的检查,巡检结果及建议参见附件二 巡检现场工作记录。
4 巡检总结
对巡检的结果进行总结:
经对整个系统进行全面检查及性能分析后,我们认为系统中的Sybase数据库无论是从内部还是从性能上来说都是正常的,满足现有的应用规模。
5 问题分析
系统运行正常,未见严重问题。
6 建议
因目前该系统运行上不存在性能问题,故暂时不需要进行相关性能的调整。只是平常应注意对数据库运行日志文件的监控,尽量作到及时发现问题。
7 附件一:详细数据库参数配置清单
列出在用数据库Config参数配置清单。
Group: Configuration Options
Group: Backup/Recovery
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
allow remote access 1 0 1 1
print recovery information 0 0 0 0
recovery interval in minutes 5 0 5 5
tape retention in days 0 0 0 0
(1 row affected)
Group: Cache Manager
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
global async prefetch limit 10 0 10 10
global cache partition number 1 0 1 1
memory alignment boundary 2048 0 2048 2048
number of index trips 0 0 0 0
number of oam trips 0 0 0 0
procedure cache percent 20 608588 20 20
total data cache size 0 2434350 0 2434350
total memory 47104 3145728 1572864 1572864
Group: Component Integration Services
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
cis bulk insert batch size 0 0 0 0
cis connect timeout 0 0 0 0
cis cursor rows 50 0 50 50
cis packet size 512 0 512 512
cis rpc handling 0 0 0 0
enable cis 1 0 1 1
max cis remote connections 0 0 0 0
max cis remote servers 25 19 25 25
Group: DTM Administration
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
dtm detach timeout period 0 0 0 0
dtm lock timeout period 300 0 300 300
enable xact coordination 1 0 1 1
number of dtx participants 500 149 500 500
strict dtm enforcement 0 0 0 0
txn to pss ratio 16 1164 16 16
xact coordination interval 60 0 60 60
Group: Diagnostics
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
average cap size 200 0 200 200
caps per ccb 50 0 50 50
dump on conditions 0 0 0 0
maximum dump conditions 10 0 10 10
number of ccbs 0 0 0 0
Group: Disk I/O
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
allow sql server async i/o 1 0 1 1
disable disk mirroring 0 0 0 0
disk i/o structures 256 57 256 256
number of devices 10 #25 50 50
page utilization percent 95 0 95 95
Group: Error Log
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
event log computer name LocalSystem 0 LocalSystemLocalSystem
event logging 1 0 1 1
log audit logon failure 0 0 0 0
log audit logon success 0 0 0 0
Group: Extended Stored Procedure
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
esp execution priority 8 0 8 8
esp execution stacksize 34816 0 34816 34816
esp unload dll 0 0 0 0
start mail session 0 0 0 0
xp_cmdshell context 1 0 1 1
Group: General Information
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
configuration file 0 0 0/sybase/cdm
Group: Java Services
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
enable java 0 0 0 0
size of global fixed heap 300 0 300 300
size of process object heap 300 0 300 300
size of shared class heap 3072 0 3072 3072
Group: Languages
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
default character set id 1 0 1 1
default language id 0 0 0 0
default sortorder id 50 0 50 50
disable character set conversi 0 0 0 0
enable unicode conversions 0 0 0 0
number of languages in cache 3 4 3 3
size of unilib cache 0 120 0 0
Group: Lock Manager
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
deadlock checking period 500 0 500 500
freelock transfer block size 30 0 30 30
lock address spinlock ratio 100 0 100 100
lock hashtable size 2048 48 2048 2048
lock scheme allpages 0 allpages allpages
lock spinlock ratio 85 0 85 85
lock table spinlock ratio 20 0 20 20
lock wait period 2147483647 02147483647 2147483647
max engine freelocks 10 0 10 10
number of locks 5000 23438 100000 100000
print deadlock information 0 0 0 0
read committed with lock 0 0 0 0
Group: Memory Use
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
additional network memory 0 0 0 0
allow resource limits 0 0 0 0
audit queue size 100 42 100 100
average cap size 200 0 200 200
caps per ccb 50 0 50 50
deadlock pipe max messages 0 0 0 0
default network packet size 512 #176 512 512
disk i/o structures 256 57 256 256
enable rep agent threads 0 0 0 0
errorlog pipe max messages 0 0 0 0
event buffers per engine 100 #31 100 100
executable codesize + overhead 0 13808 0 13808
lock hashtable size 2048 48 2048 2048
lock spinlock ratio 85 0 85 85
max cis remote servers 25 19 25 25
max number network listeners 5 1010 5 5
max online engines 1 1121 3 3
max roles enabled per user 20 #7 20 20
memory per worker process 1024 0 1024 1024
number of alarms 40 3 40 40
number of aux scan descriptors 200 #258 200 200
number of ccbs 0 0 0 0
number of devices 10 #25 50 50
number of languages in cache 3 4 3 3
number of large i/o buffers 6 97 6 6
number of locks 5000 23438 100000 100000
number of mailboxes 30 1 30 30
number of messages 64 3 64 64
number of open databases 12 1263 12 12
number of open indexes 500 1024 1000 1000
number of open objects 500 1121 1000 1000
number of remote connections 20 35 20 20
number of remote logins 20 23 20 20
number of remote sites 10 2014 10 10
number of user connections 25 10052 50 50
number of worker processes 0 0 40 0
partition groups 1024 904 1024 1024
permission cache entries 15 #72 15 15
plan text pipe max messages 0 0 0 0
procedure cache percent 20 608588 20 20
process wait events 0 0 0 0
remote server pre-read packets 3 #33 3 3
size of global fixed heap 300 0 300 300
size of process object heap 300 0 300 300
size of shared class heap 3072 0 3072 3072
size of unilib cache 0 120 0 0
sql text pipe max messages 0 0 0 0
stack guard size 4096 #384 4096 4096
stack size 73728 #6913 73728 73728
statement pipe max messages 0 0 0 0
total data cache size 0 2434350 0 2434350
total memory 47104 3145728 1572864 1572864
txn to pss ratio 16 1164 16 16
wait event timing 0 0 0 0
Group: Meta-Data Caches
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
number of open databases 12 1263 12 12
number of open indexes 500 1024 1000 1000
number of open objects 500 1121 1000 1000
open index hash spinlock ratio 100 0 100 100
open index spinlock ratio 100 0 100 100
open object spinlock ratio 100 0 100 100
Group: Monitoring
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
Q diagnostics active 0 0 0 0
SQL batch capture 0 0 0 0
deadlock pipe active 0 0 0 0
deadlock pipe max messages 0 0 0 0
errorlog pipe active 0 0 0 0
errorlog pipe max messages 0 0 0 0
object lockwait timing 0 0 0 0
per object statistics active 0 0 0 0
plan text pipe active 0 0 0 0
plan text pipe max messages 0 0 0 0
process wait events 0 0 0 0
sql text pipe active 0 0 0 0
sql text pipe max messages 0 0 0 0
statement pipe active 0 0 0 0
statement pipe max messages 0 0 0 0
statement statistics active 0 0 0 0
wait event timing 0 0 0 0
Group: Network Communication
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
additional network memory 0 0 0 0
allow remote access 1 0 1 1
allow sendmsg 0 0 0 0
default network packet size 512 #176 512 512
max network packet size 512 0 8192 8192
max number network listeners 5 1010 5 5
number of remote connections 20 35 20 20
number of remote logins 20 23 20 20
number of remote sites 10 2014 10 10
remote server pre-read packets 3 #33 3 3
syb_sendmsg port number 0 0 0 0
tcp no delay 0 0 0 0
Group: O/S Resources
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
max async i/os per engine 2147483647 02147483647 2147483647
max async i/os per server 2147483647 02147483647 2147483647
o/s file descriptors 0 0 0 4096
tcp no delay 0 0 0 0
Group: Parallel Query
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
max parallel degree 1 0 1 1
max scan parallel degree 1 0 1 1
memory per worker process 1024 0 1024 1024
number of worker processes 0 0 40 0
Group: Physical Resources
Group: Physical Memory
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
additional network memory 0 0 0 0
lock shared memory 0 0 0 0
max SQL text monitored 0 2 0 0
shared memory starting address 0 0 0 0
total memory 47104 3145728 1572864 1572864
Group: Processors
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
max online engines 1 1121 3 3
min online engines 1 0 1 1
Group: Rep Agent Thread Administration
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
enable rep agent threads 0 0 0 0
Group: SQL Server Administration
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
SQL Perfmon Integration 1 0 1 1
abstract plan cache 0 0 0 0
abstract plan dump 0 0 0 0
abstract plan load 0 0 0 0
abstract plan replace 0 0 0 0
allow backward scans 1 0 1 1
allow nested triggers 1 0 1 1
allow resource limits 0 0 0 0
allow updates to system tables 0 0 0 0
audit queue size 100 42 100 100
cpu accounting flush interval 200 0 200 200
cpu grace time 500 0 500 500
deadlock retries 5 0 5 5
default database size 2 0 2 2
default exp_row_size percent 5 0 5 5
default fill factor percent 0 0 0 0
enable DTM 0 0 0 0
enable HA 0 0 0 0
enable housekeeper GC 1 0 1 1
enable sort-merge join and JTC 0 0 0 0
event buffers per engine 100 #31 100 100
housekeeper free write percent 1 0 1 1
i/o accounting flush interval 1000 0 1000 1000
i/o polling process count 10 0 10 10
identity burning set factor 5000 0 5000 5000
identity grab size 1 0 1 1
license information 25 0 25 25
number of alarms 40 3 40 40
number of aux scan descriptors 200 #258 200 200
number of histogram steps 20 0 20 20
number of large i/o buffers 6 97 6 6
number of mailboxes 30 1 30 30
number of messages 64 3 64 64
number of open databases 12 1263 12 12
number of open indexes 500 1024 1000 1000
number of open objects 500 1121 1000 1000
number of pre-allocated extent 2 0 2 2
number of sort buffers 500 0 500 500
page lock promotion HWM 200 0 200 200
page lock promotion LWM 200 0 200 200
page lock promotion PCT 100 0 100 100
partition groups 1024 904 1024 1024
partition spinlock ratio 10 0 10 10
print deadlock information 0 0 0 0
row lock promotion HWM 200 0 200 200
row lock promotion LWM 200 0 200 200
row lock promotion PCT 100 0 100 100
runnable process search count 2000 0 2000 2000
size of auto identity column 10 0 10 10
sql server clock tick length 100000 0 100000 100000
text prefetch size 16 0 16 16
time slice 100 0 100 100
upgrade version 1100 0 12000 12000
Group: Security Related
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
allow procedure grouping 1 0 1 1
auditing 0 0 0 0
check password for digit 0 0 0 0
curread change w/ open cursors 1 0 1 1
current audit table 1 0 1 1
max roles enabled per user 20 #7 20 20
maximum failed logins 0 0 0 0
minimum password length 6 0 3 3
msg confidentiality reqd 0 0 0 0
msg integrity reqd 0 0 0 0
secure default login guest 0 guest guest
select on syscomments.text 1 0 1 1
suspend audit when device full 1 0 1 1
unified login required 0 0 0 0
use security services 0 0 0 0
Group: User Environment
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
default network packet size 512 #176 512 512
number of pre-allocated extent 2 0 2 2
number of user connections 25 10052 50 50
permission cache entries 15 #72 15 15
stack guard size 4096 #384 4096 4096
stack size 73728 #6913 73728 73728
systemwide password expiration 0 0 0 0
user log cache size 2048 0 2048 2048
user log cache spinlock ratio 20 0 20 20
(return status = 0)
8 附件二:巡检现场工作记录
数据库厂商、集成厂商、用户三方签字的工作记录表。
巡检时间:2003年1月6日22:20致2003年1月7日00:00
系统运行状况检测
序号 测试内容 测试目的 结果 建议
1 showserver 检查数据库核心是否启动 已启动
2 isql/select @@version 检查数据库核心版本和EBF版本 ASE 12.0.0.4/SWR 10050 ESD 2/ 64bit
3 SYBASE.cfg 检查数据库核心的运行配置,包括内存分配、锁个数、存储过程缓冲、多CPU配置、用户连接配置、网络包尺寸等 参数配置正常见附件一
4 errorlog 检查errorlog中的各类报错,包括数据库报错和操作系统报错,一旦发现数据页损坏、日志溢出、硬盘I/O报错等严重问题须立刻进行现场解决并记录在案 未见数据页损坏、日志溢出、硬盘I/O报错等严重问题 加强对errorlog文件的查看,以便能及时发现问题。
5 isql/sp_sysmon 全面检测数据库核心运行性能,重点检测以下指标:CPU占用、核心页交换、I/O流量、网络流量、锁竞争、内存调度等 性能检测报告见附件三。经分析结果,数据库性能正常。
6 isql / sp_helpdb 检测系统数据库/用户数据库的物理分配、空间使用情况,确保有足够的日志空间 见附件三
7 isql / dbcc checkalloc()checkcatalog() 检测系统数据库/用户数据库的数据分配页物理可用性,该操作将自动修复大部分基本错误,重大问题立刻进行现场解决并记录在案 因数据库运行日志文件中未见严重错误信息,且考虑系统的连续运行性要求,所以不做此非常耗时的检测工作。
8 检查日常数据库备份/恢复策略 了解数据库备份/恢复方案,解决日常数据库备份/恢复中的问题,提出完善性建议 备份正常。每3天做一次全库备份,并且定期做表备份。
三方签字:
分公司签字: 日期:集成商签字: 日期:Sybase公司现场工程师签字: 日期:
9 附件三:其它文档
数据库厂商提供的、与巡检工作或后期维护工作的相关文档或资料。 顶!
学习··· 不错~ 好的,不错 顶。虽然很久了,还是有用的。 嗯,这种的值得推荐! 时间很久的文档了,不错,很有参考的价值 学习、收获! 挖了一年的坟呀! 谢谢分享。
页:
[1]
2