Sybase数据库的日常维护

转自：http://www.blogjava.net/shanben/archive/2008/07/05/212751.html

维护目的：

监测数据库的当前运行状况，保证数据库稳定运行。

做好数据库的日常的备份工作，减轻问题发生时的风险和责任

检测数据库的整体运行状况，对数据库的性能进行调整，保证数据库高效的运行。

可以减少紧急故障发生频率，减少对系统的影响。

尽早发现系统存在的潜在问题，使可能的故障消除在萌芽状态。

数据库日常维护的主要内容
监测数据库运行情况

监控CPU和I/O的使用情况

监控空间的使用情况

监控数据库的错误日志errorlog

制定一个合理的备份计划

检查数据库的一致性

数据库的排错

数据库性能调整

数据库日常维护的方法

数据库服务和备份服务的启动和关闭方法

数据库和备份服务的启动

$cd $SYBASE/ASE-12_*/install

$startserver –f RUN_Servername(Servername为你的数据库服务名)

$startserver –f RUN_Servername_back

数据库和备份服务的关闭

isql –Usa –Pxxx –Sservername

>shutdown SYB_BACKUP (关闭备份服务)

>go

>use dbname (用户库)

>go

>checkpoint

>go

>shutdown

>go

查看sybase用户的运行环境是否正常

$env

查看SYBASE,SYBASE_ASE,SYBASE_OCS等环境是否正常

查看数据库服务和备份服务进程是否运行正常

$ps –ef | grep dataserver

$ps –ef | grep backupserver

查看数据库的版本和补丁信息

$dataserver –v

或是

isql –Usa –Pxxx –Sservername

>select @@version

检查数据库的配置是否合理.

检查数据库内存分配、锁个数、存储过程缓冲、多CPU配置、用户连接配置、网络包尺寸等重要参数的设置。

命令：

sp_configure ‘allocate max shared memory’

sp_configure ‘max memory‘

sp_configure ‘procedure cache size‘

sp_configure ‘user log cache size’

sp_configure ‘default network packet size’

sp_cacheconfig

sp_configure ‘number of user connections‘

sp_configure ‘number of locks‘

sp_configure ‘number of engines at startup’

sp_configure ‘max online engines’

sp_configure “number of open index”

sp_configure “number of open objects”

sp_configure “number of open partitions”

查看数据库的用户，数据库的锁状况

sp_who(用户)

sp_lock(锁)

select spid,blocked from sysprocesses where blocked>0(检查阻塞进程)

查看最早进程，发现不完整事务

select * from master..syslogshold

其中对应的spid为最早的进程，如果最早的进程开始时间距离当前时间已经非常长(如2、3天)，则可能该进程有问题，确认后，应该将其kill，下面有一个存储过程来检测不完整的事务，可以将该存储过程放在操作系统的crontab中。

create proc proc_kill
@last_min int=720

AS

/*

** kill the processes that didn’t commit for a impossible time

** the parameter's time unit is minute,default is 12 hours

** the procedure is just for user transactions ,not for xa transaction

** you can add this procedure to your opration system crontab

*/

declare @spid int

declare @cspid char(20)

select spid into #killtab from master..syslogshold where datediff(mi,starttime,getdate()) >@last_min and spid>0

declare t1_cur cursor for select * from #killtab

open t1_cur

fetch t1_cur into @spid

while @@sqlstatus!=2

begin

select @cspid=convert(char(20),@spid)

select "kill "+@cspid
exec("kill "+@cspid)
fetch t1_cur into @spid

end

查看CPU和I/O的使用

#sar #iostat

#vmstat

查看Sybase的资源使用

sp_sysmon “00:10:00”

如果CPU或是I/O的使用率长期高于80%，则要看系统使用是否正常还是系统资源配置不够，具体细节可参考下面数据库的优化部分。

定期检查数据库日志使用的情况，定期清除数据库的日志

如果数据库日志满，则业务无法进行。

sp_helpdb dbname(用户库名)

发现数据库的日志空间不足，应立刻清除已经完成的事务，或是根据需要扩大日志空间。

定期清除日志：

dump tran dbname with truncate_only

可以将上述的命令放在crontab中。

定期检查磁盘空间的使用。 如果发现操作系统有磁盘分区使用达到100%，应尽快处理。

#df -k

定期检查数据库的错误日志(errorlog)，如果错误日志中发现有数据库的严重错误，应立即处理。

有计划的截断数据库的错误日志。

数据库运行一定时间后，可以在数据库没有业务时，（下班或周末），将数据库stop后，将错误日志更名，然后重新启动数据库，产生一个新的数据库错误日志。

在错误日志中,以下的错误级别为不严重错误,通常是用户错误,如语法,读写权限空间不足等。
10 Status information

11 Specified database object not found

12 Wrong datatype encountered

13 User transaction syntax error

14 Insufficient permissions to execute commands

15 Syntax error in SQL statement

16 Miscellaneous user error

Hardware/software errors

17 Insufficient resources

18 Non-fatal internal error

在错误级别17中,要注意错误号1105,1105表示空间不足(数据库数据或是日志)。

在错误级别18中,最多的是1608,1608表示一个客户连接不是正常方式退出的，

这类错误不用关注。

以下错误级别为严重错误,通常是系统(数据库、磁盘损坏、操作系统)错误
19 Fatal error in resource

20 Fatal error in current process

21 Fatal error in database process

22 Fatal error: table integrity suspect

23 Fatal error: database integrity suspect

24 Hardware error or system table corruption

在错误日志中，可以按照"Sybase Technical Support"来搜索，

找到的错误通常是严重错误。

检查数据库的一致性
检测系统数据库、用户数据库的数据分配页物理可用性，检查数据库系统表和用户表的数据完整性，查看是否有表损坏（可能是逻辑也可能是物理的损坏，如磁盘错误可能导致数据库的表损坏）

做数据库的一致性通常需要单用户状态，并且都是一些耗时操作（如果数据库大的话），因此，这些检查可以考虑在系统检修时运行。

dbcc checkalloc(dbname)检查数据库的空间分配，必须在单用户下执行

checkcatalog(dbname) 检查系统表的一致性

dbcc checkdb(dbname) 检查用户数据库的数据库一致性(包括系统表和用户表)

dbcc checktable(tablename) 检查一张表的数据一致性。

数据库的备份
数据库的备份对于日常的维护来说十分重要，系统管理员一定要注意数据库每天都有成功备份。需要检查备份的介质(磁盘或是磁带)是否正常。

备份命令:

dump database to ‘/xx/xxx’ （设备名或是磁盘上的文件名）

用户可以规划一个备份的计划，然后将备份的命令放在crontab 中，让系统自动定时做数据库的备份。

数据库的排错
数据库通常的错误主要是以下几种

数据库服务无法启动

解决：查看数据库的错误日志，根据错误日志找到无法启动的原因，原因通常是ip地址，端口不对或是被占用，内存配置过大，或是数据库设备的属性不对，sybase用户没有访问权限。

数据库不能恢复(recovery)

解决：查看数据库的错误日志，找到数据库不能恢复的原因，然后做相应的处理。通常是由于系统突然断电或是系统非正常关机。

用户表不能访问

解决：查看数据库的错误日志，找到不能访问的原因。通常是由于访问权限或是表损坏，

最常见的数据库重大故障分析
根据我们的经验，除去硬件故障外，造成重大数据库故障都是因为日志满，重启动时异常删除日志导致，

而日志满主要是一下几个原因造成：

1）数据库配置不合理

主要是内存、锁的配置不合理。

2）存在不完整的事务或是进程

出现这种情况是由于不完整的事务引起的。引起不完整的事务主要有两个方面的原因：第一是网络质量不佳，如果在客户端向ASE服务端进行事务时，如果网络突然中断，会导致事务的不完整。第二是应用程序存在问题，而在这种情况下，重新启动数据库服务后，数据库的恢复可能是非常缓慢的，主要是看日志的大小和事务的类型，（有时用户为了快速启动，通常会异常清除数据库的日志，就有可能会造成数据库表损坏）所以，在这种情况下千万不要急于重新启动数据库服务。

如果存在不完整的事务，在该事务之后的所有事务都不能被清除，导致数据库日志满。

不完整的事务可以从master..syslogshold表中发现，如果其中starttime（最早的事务开始时间）距离当前时间很长，比如一天，一月，则该事务应该是一个不完整的事务，可以将该事务对应的数据库进程kill。

3）出现过大的事务
这类问题完全是应用或是人为造成的问题。例如一次删除一个5000万条记录的表，导致日志满。这种情况下重启动服务，数据库的恢复也将是十分缓慢的，防止出现这类问题，是将大事务转换成许多小事务来执行。在事务之间来删除数据库的日志。例如手工删除日志或是将数据库设置成自动清日志。

数据库的性能优化
查看数据库的配置，看能否有不合理的数据库配置。

查看方法参考上一节查看数据库的配置。

更新数据库的统计信息(页锁)

update statistics tablename

对行锁表回收空间(表锁)

reorg rebuild tablename

做空间回收时需要在系统维护时做

监测数据库的运行，查看是否阻塞缩

sp_lock

sp_who

select spid,bloced from master..sysprocesses where blocked>0

对数据库系统运行进行监测，发现可能引起性能差的因素

在系统运行忙或是性能不佳时运行

sp_sysmon “00:10:00”

分析的一些常用工具：

在应用开发或是执行之前，对可能引起问题的语句检查命令：

set showplan on
set noexec on

这样，可以看到该语句的执行过程，而该语句并不执行。

set showplan off

set noexec off

off 以上的选项

set statistics io on /off
set statistics time on/off

在语句执行时可以看到I/O的实际情况（包括物理I/O,逻辑I/O)

以上语句基于session

在运行过程中查找可能有问题的语句的命令：

运行几次 sp_lock
查找其中对表意向锁 (sh_intent,ex_intent)较长时间的进程，记录下spid

( 比如，update 一张100万条记录中的一条，如果表不使用索引，最终会有一个Ex_row锁，但在表扫描期间，一直会有一个

Ex_intent意向锁）

通过spid,可以看到执行命令和执行过程：

dbcc traceon(3604)

go

dbcc sqltext(spid)

go

sp_showplan spid,null,null,null

go

查找阻塞进程：
select spid,blocked from master..sysprocesses where blocked>0

其中blocked 对应阻塞别人的进行，spid对应被阻塞经常，

查看blocked对应进程执行的命令和执行过程

方法同上

查找最耗资源的进程：下面有两个存储过程，可以分析在数据库繁忙时最消耗cpu和i/o资源的数据库进程，并显示该进程所执行的语句以及执行的过程，根据执行过程来判断问题的原因。

if exists( select name from sysobjects where type='P' and name='proc_who_do_io')
drop proc proc_who_do_io

go

/* print top n (of physical_io usages) applications 's execute plan and sql */
/* example useage : proc_who_do_io */

create proc proc_who_do_io

@inter_time char(8)='00:00:05',

@topn int=3

as

declare @spid int

select @spid=11

dbcc traceon(3604)

select spid,cmd,physical_io,hostname,program_name into #t1 from master..sysprocesses order

by spid

waitfor delay @inter_time

select spid,cmd,physical_io ,hostname,program_name into #t2 from master..sysprocesses order

by spid

select #t1.spid,#t1.cmd,#t1.program_name,#t1.physical_io as phy_io,#t1.hostname,#t2.physical_io - #t1.physical_io as
phy_io_add into #t3 from #t1,#t2

where #t1.spid=#t2.spid and abs(#t2.physical_io - #t1.physical_io) >2 order by #t2.physical_io - #t1.physical_io desc

select * from #t3

select * into #t4 from #t3 where 1=2

set rowcount 1

while @topn >0

begin

insert #t4 select * from #t3

delete #t3

select @spid=spid from #t4

select "execute plan :"+ str(@spid)

exec sp_showplan @spid,null,null,null

dbcc sqltext(@spid)

delete #t4

select @topn = @topn-1

end

set rowcount 0

go

if exists( select name from sysobjects where type='P' and name='proc_who_use_cpu')
drop proc proc_who_use_cpu

go

/* print top n (of cpu usages) applications 's execute plan and sql */
/* example useage : proc_who_use_cpu */

create proc proc_who_use_cpu

@inter_time char(8)='00:00:05',

@topn int=3

as

declare @spid int

select @spid=11

dbcc traceon(3604)

select spid,cmd,cpu,hostname,program_name into #t1 from master..sysprocesses order

by spid

waitfor delay @inter_time

select spid,cmd,cpu ,hostname,program_name into #t2 from master..sysprocesses order

by spid

select #t1.spid,#t1.cmd,#t1.program_name,#t1.cpu ,#t1.hostname,#t2.cpu - #t1.cpu as
cpu_add into #t3 from #t1,#t2

where #t1.spid=#t2.spid and abs(#t2.cpu - #t1.cpu) >2 order by #t2.cpu - #t1.cpu desc

select * from #t3

select * into #t4 from #t3 where 1=2

set rowcount 1

while @topn >0

begin

insert #t4 select * from #t3

delete #t3

select @spid=spid from #t4

select "execute plan :"+ str(@spid)

exec sp_showplan @spid,null,null,null

dbcc sqltext(@spid)

delete #t4

select @topn = @topn-1

end

set rowcount 0

go

数据库版本在 ASE12.5.0.3以上， 寻找索引使用情况
select s.SPID,s.CpuTime,t.LineNumber,t.SQLText

from monProcessStatement s,monProcessSQLText t

where s.SPID=t.SPID

order by s.CpuTime,s.SPID,t.LineNumber desc