【转载】Sybase15学习笔记

一、        ASE15概述
1、        SYBASE ASE 包括的服务
Adaptive server：是整个SYBASE的核心数据库，用于管理整个数据库资源
Backup server：用于备份与恢复数据用的服务
XP server：用于执行扩展存储过程的服务（扩展存储过程，它们是以C语言等编写的外部程序，以动态链接库（Dll）形式存储在服务器上，SQL Server可以动态装载并执行它们。编写好扩展存储过程后，固定服务器角色（sysadamin)成员即可在Sql Server服务器上注册该扩展存储过程，并将它们的执行权限授权其他用户。扩展存储过程只能添加到Master数据库。）
Monitor server：用于性能调优采集数据的服务
Historical server：用于保存monitor server的数据，用于将来分析用

2、        SYBASE ASE包括的数据库
Master、tempdb、model、sybsystemdb、sybsystemprocs、sybsecurity、sybsyntax、dbccdb、pubs1..等
必备数据库：
Master：包含主要的系统表，用来管理adaptive server服务，不需要太大，一般在180M左右就可以了
Tempdb：主要处理临时操作
Model：数据库模板，用来创建新的数据库
Sybsystemprocs：存放系统存储过程
Sybsystemdb：主要处理分布式事务管理功能
附加功能数据库：
Sybsyntax：包括SQL关键词的语法帮助，可用sp_syntax创建
Sybsecurity：审计用数据库
Dbccdb：检查数据库的一致性
例子数据库：
Pubs1、pubs2：主要做测试用
应用数据库：客户自己根据需要创建的数据库

3、        系统表与系统存储过程
系统表：如sysdatabases、sysobjects
系统存储过程
当用户执行存储过程时，系统按以下顺序进行查找：
首先在当前数据库查找
如果不在，到sybsystemprocs数据库查找
如果不在，到master数据库查找
如果不在，返回错误信息

4、        客户端与运用程序
客户端工具有以下几种：
Isql
Interactive sql
Sybase central
Jisql
Sql advantage

5、        接口文件
存放于％sybase％/sql.ini目录下，主要记录客户端与服务器的连接信息。

6、        系统全局变量
请参考SYBASE快速参考手册

7、        启动与停止SYBASE服务
启动SYBASE服务：
UNIX语法：Startserver [[-f runserver_file][-m]]
NT语法：net start sybsql_servername

停止SYBASE服务：
Shutdown [server_name] [with {wait|nowait}]
Wait:让正在执行的事务完成后再shutdown
No wait:立即shutdown，会造成下次启动比较慢

关闭数据库服务器前，先关闭备份服务器

可以利用SET dsquery=servername 设置缺省服务名，下次用ISQL登录时就可以不用指定服务名了

 

二、        常用参数配置
1、        Server级参数设置
常用的SERVER参数：
        Max memory：最大内存，一般为系统内存的60-70％
        Max online engines：引擎数，一般为CPU个数减1，1个CPU配1个，2个CPU可为1或2，考滤双核的情况。
        Number of engines of startup：
        Number of user connections：用户连接数
        Number of lock：锁的数量，小型企业一般为5－10万，中型企业一般为20-30万
        Number of devices：允许最多设备数
        Number of open database：同一时间打开数据库最大数
        Number of open indexes：同一时间打开索引最大数
        Number of open objects：同一时间打开对象最大数
        Procedure cache size：存储过程缓存，一般为100M-200M
        Default data cache：默认数据缓存，一般为max memory的一半
        Default network packet size：默认网络包大小，一般为512的倍数
        Max network packet size：最大网络包大小
        Stack size：为每个进程使用的执行堆栈的大小

目前我们系统里也用到了部分参数设置，如下图：


以上参数都存放在配置文件 $sybase/servername.cfg 中，记录服务器参数的所有信息。分为动态参数与静态参数两种：
动态参数为修改后立即起作用的参数
静态参数为修改后需要重新启动服务才起作用的参数
SYBASE启动adaptive server时，首先会按照配置文件的设置分配资源，然后备份配置文件为servername.bak，将上次备份的文件复盖掉


2、        Database级参数设置
常用数据库选项如下：
        Allow nulls by default：可以将数据库中表的列的缺省值由NOT NULL改为NULL
        Auto identity：指定该参数后，可以表中未指定主键、唯一索引、identity列的情况下，自动为表创建一个identity列。
        Dbo use only：选中后，只有数据库所有者可以操作数据库，其它用户为只读
        Ddl in tran：可指定用户在事务中执行数据操作语言（DDL）
        Indentity in nouninque index：可以使在逻辑上表中非唯一的索引在内部唯一，前提是表中必须有identity列，可与auto identity选项结合使用。
        Read only：数据库只读选项
        Signle user：设置只能单用户访问，tempdb数据库除外。
        Unique auto_indentity index：向表中添加一个具有唯一非聚集索引的identity列
        Abort tran on log full：超出阈值时，如何处理正在运行的事务，如果选中，写入日志事务的查询将被注销，直到日志中的空间被释放，如果不选，则只能等到日志空间释放。
        No chkpt on recovery：保留数据库最新副本
        No free space acctg：禁止对非日志段执行可用空间计数与阈值操作
        Select into/bulkcopy/pllsort：是否允许此操作。
        Trunk log on chkpt：控制事务日志在执行checkpoint操作时，是否截断日志。


3、        内存管理
内存管理中的几个概念：
        数据缓存：属于adaptive server 内存的一部分，用于存放正在执行的数据页、索引、日志页
        过程缓存：属于adaptive server 内存的一部分，用于存放正在使用的查询计划
        MRU-LRU链：缓存中的页横穿MRU-LRU链，从最近最多被使用的页（MRU）到最近最少被使用的页（LRU）转储
        自旋锁竞争：当adaptive server配置多个引擎时，自旋锁对缓存散列表的同步访问，对于高吞吐量的OLTP操作，会严重影响性能

可通过自定义命名缓存提升系统性能，好处如下：
        使用命名缓存，可以绑定热点对象到专用的内存区，可降低物料I/0和使对象驻留在内存中，不太频繁使用的对象可保存在缺省的数据缓存中，因为里面已经不包含热点表了
        使用命名缓存，可减少对缺省数据缓存的自旋锁竞争，因为每个命名缓存都有自己的缓存散列表，

创建命名缓存：
Sp_cacheconfig cachename ,20M
检查命名缓存的配置与绑定：
Sp_helpcache
修改命名缓存为只适用于日志的缓存：
Sp_cacheconfig cacehname,logonly
绑定和解绑对象到命名缓存：
Sp_bindcache “cachename”,”dbname”,”tbname”
Sp_unbindcache “dbname”,”tbname”
Sp_unbindcache_all “cachename”
删除命名缓存：
Sp_cacheconfig “cachename”,”0”

Log io 的设置可以提高吞吐量
Sp_logiosize

创建缓冲池：
Sp_poolconfig cachename,”4M”,”4K”
修改缓冲池：
Sp_poolconfig cachename,”5M”,”4K”,”16K”
删除缓冲池：
Sp_poolconfig cachename,”0”,”16K”

 

三、        数据库管理
1、        设备与数据库管理
创建一个设备：
Disk init name = “dev02”,physname = “e:dev02.dat”,dsync=false,size = “50M”
创建一个数据库：
Create database mydb on dev02=”10M” log on logo2=”2M”
扩充数据库空间：
Alter database mydb on dev02=10
护充日志空间：
Alter database mydb log on log02 = 10M

将日志与数据分别放在不同的物料设备上，有以下几点好处：
        做日志备份时节省时间和资源
        建立固定日志大小，以防止其它数据库活动竞争空间
        提高性能
        降低数据库和日志同时损坏的可能性

2、        数据库的备份与恢复
        数据库备份：
                Dump database 数据库名（mydb） to “e:mydb.dump”
        数据库恢复：
                Load database数据库名（testdb） from “e:mydb.dump” with headeronly

        日志满后需要截日志解决：
dump tran mydb with truncate_only
dump tran mydb with no_log
truncate_only 与 no_log的区别详见数据库备份与恢复
当日志满或者别的原因导致有事务挂起的时候，with   truncate_only是不管用的，因为它也会被挂起，用with   no_log是没问题的，只要数据库的状态是online，不过可能会导致数据库不一致，尤其是在此之前做过alter   database扩数据库空间的话。
        为什么有些时候无法截断日志
有两种情况，可能出现这个问题。一是应用系统给SQL Server发送了一个用户自定义事务，一直未提交，这个最早活跃事务阻碍系统截断日志。二是客户端向SQL Server发送了一个修改数量大的事务，清日志时，该事务还正在执行之中，此事务所涉及的日志只能等到事务结束后，才能被截掉。
对于第一种情况，只要督促用户退出应用或者提交事务，系统管理员便可清掉日志。因为给SQL Server发送Dump transaction with no-log或者with truncate-only，它截掉事务日志的非活跃部分。所谓非活跃部分是指服务器检查点之间的所有已提交或回退的事务。而从最早的未提交的事务到最近的日志记录之间的事务日志记录被称为活跃的。从此可以看明，打开的事务能致使日志上涨，因为在最早活跃事务之后的日志不能被截除。
对于第二种情况，道理也同上。只是在处理它时，需慎重从事。如果这个大事务已运行较长时间，应尽量想法扩大数据库日志空间，保证该事务正常结束。若该事务被强行回滚，SQL Server需要做大量的处理工作，往往是正向执行时间的几倍，系统恢复时间长，可能会影响正常使用的时间

3、        数据库表管理
表在用过一段时间后会出现性能下降，插入、删除、更新数据非常慢等问题，可用optdiag命令查看当前表的统计信息，查看聚簇率是否降低，是否有页碎片等信息，可用以下几种方法消除页碎片：
对于APL表
可删除索引重建
用BCP命令将数据导一遍
用SELECT INTO 命令重新建表
对于DOL表，除用以上方式外，还可以用reorg rebuild tablename命令完成

备份表操作：
bcp [数据库名]..[表名] out e:aa.txt –C –U[用户] –P[密码] –S[服务名]

4、        Tempdb 库的管理
以下几种操作会对tempdb 有影响
        order by
利用聚集索引可以避免利用tempdb资源，因为聚集索引本身可以排序
        group by
尽可能少的使用group by
        where 关联
set sort_merge on 可以提高关联速度，不过可能会占用系统资源
        在tempdb中创建表
create table #name 会话级
create table name  shutdown级
        建索引也可以利用

使用sp_helpdb命令可查看tempdb的情况

为tempdb指定命名缓存，首先创建命名缓存，前面已经介绍创建语法，再绑定

                使用tempdb的三大问题：
1)、数据库大小的问题
扩数据库大小或可以自己建一个数据库放里面
2)、性能问题
将tempdb放到速度较快的设备上，或将tempdb放到命名缓存里，考虑将tempdb放到多个设备上
3)、锁的竞争
系统表的锁竞争，SYBASE12.3.0中支持多个临时数据库，可以将用户绑定到其它临时数据库上。

 

四、        性能优化
1、        Adaptive server 优化器：
优化器统计值存储在两个系统表中：
Systabstats：存储表和索级级的统计，如页数、行数、索引页数、聚集率等
Sysstatistics：存储列级已知的统计，如密度值、直方图值等
将统计值存放在表中的好处：
统计值的空间受数据库尺寸的限制
基于表的统计值有很高的精确度
基于表的方法提供了一个全局仓库，使排错和收集信息简单化
基于表的方法有可扩展性，对以后加强功能的处理比较简单、容易

2、        使用optdiag查看优化器统计
Optdiag是显示、修改、模拟统计值的命令行工具
查看表的统计值命令：
Optdiag statistics dbname..tbname –U –P

管理优化器统计值：
Create index 创建索引
Upate statistics 更新统计值
Delete statistics 删除统计值
Sp_flushstats 从内存中刷新统计值

使用sp_sysmon 监控数据页聚集率

3、        数据库调优
数据库系统参数配置的调优：
修改内存参数
合理的分配存储过程缓冲区与数据缓冲区的大小
合理分配网络包的大小
设置锁的个数
设置锁的机制
建立索引与表分区
数据库服务器可用CPU的个数
扩充TEMPDB的大小
增加use log cache size

4、        应用程序的调优：
尽量将事务最小化
索引的建立与SQL的写法要匹配
尽量减少网络流量
并发数多时，可让客户端承担部分工作量

扫描慢的时候加  set sort_merge on 合并连接会快很多，缺点是会在执行期间占用很多资源。会话结束后自动结束

 

五、        锁的管理
锁的粒度：表锁、页锁、行锁等
锁的基本类型：共享锁（S）、更新锁（U）、排它锁（X）
死锁：两个任务或进程在自己的表或页上加锁，并请求对访的资源，就会造成死锁
死锁查检周期：sp_configure ”deadlock checking period”,”600ms”，如果值为0，则在发生死锁时就检查，不过在没有死锁的情况下会浪费资源
打印死锁日志：Sp_configure “print deadlock information”,1

锁方案：
全页锁（APL）:插入数据时自动聚集索引
数据页锁（DPL）:插入数据时不自动聚集索引
数据行锁（DRL）:插入数据时不自动聚集索引

可配置服务器级别锁方案：
Sp_configure “lock scheme”,0,”allpages|datapages|datarows”
可在创建表时配置锁方案：
Create table(….) lock datarows
可用select into 配置锁方案：
Select col into talbe lock datarows
可用alter table 配置锁方案：
Alter table tbname lock datarows

配置锁的数量命令：
Sp_configure “number of lock”,25000

Sp_who：查询活动的进程与相关的阻塞信息
Sp_lock：查询当前系统相关锁的信息
Sp_familylock：查询正在工作族的锁
Sp_sysmon “00:00:10”：监控10秒
Sp_object_stats “00:00:10”,5：(监控排名前5位的表，10秒，发生了什么样的锁等等)

减少锁竞争的方法：
增加索引
缩短事务
减少尾页竞争

锁的升级：
锁升级一般都是从页级锁或行级锁直接升级到表锁，行级锁不能升级到页级锁。

修改锁的升级阈值：
Sp_configure
Sp_setpglockpremote
Sp_setrowlockpremote

High water mark(HWM):上限
Lower water mark(LWM):下限
Percentage (PCT):百分比
大于上限值就升，小于下限值就不升，在两者之间看百分比