sybase数据库性能调整

核心提示：2．1．4 缓冲策略 缓冲策略是指把数据提前读入内存的机制，分预取策略(Prefetch rategy，sybase数据库性能调整(2)，即大I/O策略)和取后马上丢弃策略(Fetch-and-Discard)、提示策(Hints)等几种，可以在三个级别上设置表数据的预取策略(Prefetch Strategy， 注意

2．1．4 缓冲策略
缓冲策略是指把数据提前读入内存的机制，分预取策略(Prefetch rategy，即大I/O策略)和取后马上丢弃策略(Fetch-and-Discard)、提示策(Hints)等几种。可以在三个级别上设置表数据的预取策略(Prefetch Strategy，即大I/O策略)于：对象级，会话级，查询级。如果三个级别上都有设置，它们发生作用的优先顺序是：对象级 > 会话级 > 查询级。对于如何在查询级利用指定的缓冲池，可以查看下面例子（使用4K缓冲池）：
select au_fname, au_lname
from authers (prefetch 4)
where au_id in ( A372020631, ..., A1887081515 )
go
DSS应用往往得益于大的I/O，应该放开largeI/Ostrategy预取策略。
如果一个应用倾向于OLTP特征，用户能在会话级关掉Prefetch来提高性能。对于OLTP应用，关闭largeI/Ostrategy预取策略。对于所取到的页不会有重用的情况，放开fetch-and-discard策略。客票系统对存根数据进行统计的应用，如财收日结账，营销分析数据整理模块和综合查询等，都可以利用这一结论。查看几个操作频繁且较大的表上的缓冲策略，用如下命令：
sp_cachestrategy center,seat_area
sp_cachestrategy center,sale_record0505

2．2 管理锁
2．2．1 页锁升级阀限
优化锁的重要考虑是设置页级锁升级升级成表级锁的阀限。要尽量避免页锁很快升级成表级锁。在某客票数据库中，用sp_configure ‘lock’可以看到如下结果：
deadlock checking period 500 0 1000 1000
number of locks 5000 46875 200000 200000
page lock promotion HWM 200 0 10000 10000
page lock promotion LWM 200 0 200 200
page lock promotion PCT 100 0 90 90
可以看到页锁升级的阀限有三个：HWM（最高点） 为10000，LWM（最低点）为200，PCT为90。Sybase数据库内部根据PCT值按公式PCT*TAB_SZ/100得出计算阀限，如果计算阀限 < LWM, 锁升级发生在LWM值；如果计算阀限 < HWM，锁升级发生在HWM值。如果 LWM < 计算阀限 < HWM ，锁升级发生在PCT*TAB_SZ/100值。
锁升级阀限设置分对象级和服务器级两种。
针对对象级设置（数据库上的表或表上的索引），配置命令是：
sp_setpglockpromote {"database" | "table"}, objname, new_lwm,new_hwm, new_pct
针对服务器级设置，配置命令是：
sp_setpglockpromote server, NULL, new_lwm, new_hwm, new_pct。
如果要删除掉对象级上的页锁升级阀限，用：
sp_dropglockpromote {"database" | "table"}, objname

2．2．2 减少锁争夺的方法：
1）降范式设计数据库，创建冗余表。
2）把堆表（没有聚族索引的表）分区。
3）对于小表，使用fillfactor和max_rows_per_page来减少行密度，从而使各行数据分布到许多页（此方法适用于SQL Server 11版，对于11.9.2版以后的Sybase数据，有了行级锁，此方法必要性不大）。

2．3 管理临时库(tempdb)
管理临时库一个重要原则是要避免临时表跨多个设备，可以把tempdb从master设备中分离出来，放到一个单独的设备上去。这样可以减少存取系统表时对I/O资源的争夺。用sp_dropsegment 存储过程从master设备中移除tempdb的default段和system段。为了进一步提高tempdb的I/O速度，可以考虑把tempdb整个放在RAM 驱动器或固态存储设备上，存取速度是一般磁盘的1000倍。一般情况下，tempdb会非常频繁地争夺和占用缺省数据缓冲，因为查询会话中有许多临时表要创建、计算和删除。所以推荐把tempdb绑定到它自己的命名缓冲，这样可以防止临时对象在内存中的活动冲洗掉缺省数据缓冲中的其他对象，利于在多个缓冲间展开I/O。在使用临时表的时候，还有一个原则：尽量缩小表规模和行的宽度，每一行只包括必要的列。例如在用select * into生成临时表时，如果只需要几个列的数值，就不要用这样的语句，而直接选取需要的列。

2．4 使用多引擎（Multiple Network Engines）
如果操作系统使用了多个CPU，那么用sp_configure 配置数据库的参数：在线引擎数(max online engines)，可以扩展系统的网络I/O容量，分布网络I/O到各个引擎，从而提高性能，允许更多的用户连接。
在用户登录数据库时，总是先登录到引擎0，由引擎0在可用引擎队列中选择一个挂最少连接的引擎来传递socket描述符，从而重定向连接到那个引擎，由该引擎去处理跟此用户连接相关的所有网络活动。
对于多引擎SMP结构，SQL Server引入了自旋锁(spinlock)的一种数据结构，在多个引擎间共享。对于不同类型的任务，在哈希表上分配不同的自旋锁，有页锁自旋锁、表锁自旋锁和地址自旋锁。
自旋锁的配置：
sp_configure "page lock spinlock ratio", newval
sp_configure "table lock spinlock ratio", newval
sp_configure "address lock spinlock ratio", newval
增大数值，可以减少碰撞，提高并发操作度。但是每一个自旋锁结构要占用256字节的内存。
如果数据库发生1279错，可能原因：
1）不允许足够的锁，解决办法是用sp_configure 调大 number of locks 数值
2）在engine freelock 缓冲中没有足够的锁，解决办法是用sp_configure调大 max engine freelocks 数值。
如果数据库系统使用了4个引擎，那么每个引擎的自由锁缓冲中包含：each engine-specific freelock cache 包含 5000 * .20 /4 = 250 个锁。
在平时，经常用sp_monitor和sp_sysmon监视CPU使用率，如果所有CPU的利用率高于85%，增加CPU，然后增大数据库的引擎数，可以改善性能。

2．5 设备使用的优化
把最常插入的表分区，放在多个设备上，这样可以创建多个页链，从而改善多个并发插入时的性能，因为每一个插入都要找到页链，页链有多个，就允许多个插入同时进行。这一点，尤其适用于客票系统的存根表和订票存根表（CG30_RRT），所带来的性能改善会非常明显。
物理I/O的代价远大于逻辑I/O，所以要尽量减少磁盘进行物理I/O的次数，尽量多进行内存中的逻辑I/O。使用statistics io工具和sp_sysmon，来观察磁盘I/O。可以配置使用大的I/O来减少物理I/O的次数，方法有三个：
1）用更多的磁盘；
2）表和索引分开到不同的磁盘；
3）增加一次I/O系统参数值的大小。
SQL Server总是为I/O请求建立一个磁盘检查的调度环，用sp_configure "I/O polling process count"来提高数值，加长环，可以降低引擎的检查次数，提高吞吐量。但较小的值一般有助于减少响应时间。
对于可用的磁盘I/O控制块，要查看操作系统文档，用sp_configure "diskI/Ostructures"配置，这个数值要尽可能高。
分离日志和数据，到不同的设备；给tempdb自己的设备；分离表和索引到不同的设备。这些方法都可以减少I/O。

2．6 对事务处理的调优
2．6．1 事务类型
事务处理无外乎三种：1,OLTP; 2, DSS; 3, OLTP + DSS 的混合负载
OLTP（联机事务处理）的特点：
数据插入、修改和删除频繁。
经常操作的是单个记录。
当不适当设计时，倾向于碰撞和冲突。
DSS（决策支持系统）的特点：
数据修改不太频繁。
如果有插入和删除，是大批量的。
平时一般是只读操作。
表连接很常见。
有比较特别的查询。
OLTP + DSS 混合负载的特点权衡：
在性能方面要比较，是要吞吐量还是响应时间。
在锁方面要比较，是要并发性强呢还是要数据一致性强。

2．6．2 事务管理原则
一般的事务管理原则有：
1) 分解大的事务成多个小的事务。如客票数据的备份操作中，要删除过期数据，如果设计小事务做循环，便不会影响应用，完全可以做到任何时候备份和删除，不一定非得等服务器闲的时候做。
2) 避免在单个事务中更新或删除大量的数据行。比如客票系统的席位库数据清理，即使在服务器闲的时候做这种操作，也会锁定整个表，影响售票。
3) 尽量用可以接受的最低孤立级(isolation level)，来提高并发度。如在余票查询等功能的应用中，使用这种孤立级，便可以最大程度地降低对售票的影响。
4) 提高事务吞吐量的措施包括：避免延迟更新；尽可能使用存储过程等等。

2．6．3 跟事务特征相关的数据库可调参数或特性
相对于OLTP应用，SQL Server有一些特性来满足要求。
1) 命名缓冲（Named cache）
对于命名缓冲，可以配置多个不同大小的内存池，来满足不同的应用需求。对于多个引擎的情况，命名缓冲还有一项重要的功能是降低自旋锁的内部争夺。
2) 日志I/O缓冲大小可配置
sp_logiosize ["default" | "size" | "all" ]
缺省值是4K，但如果4K内存池没有配置，SQL Server会使用2K大小的内存池
3) 堆表可分区，分布插入操作到各个设备
适用于频繁插入的表和有并发BCP倒入数据的表，如客票系统的售票存根和退票存根表。
4) 锁升级阀限可配置。

相对于DSS应用，SQL Server也有一些特性来满足要求
1) 应用大的I/O缓冲池
用sp_poolconfig来配置。
2) 绑定热表到命名缓冲
如 sysindexes, syslogs, 注意如果把 syslogs 放到单独的缓冲中，可以减少在缺省或其他命名缓冲上的自旋锁争夺。对于客票系统的train_dir, stop_time, 票价表, 取票存储过程相关的表都可以放在单独的命名缓冲上。
3) 取后丢弃缓冲策略 (Fetch-and-discard cache strategy)
不会冲洗掉缓冲中的常用对象，可以减少MRU链的争夺，较少对OLTP事务的干扰。
对于收入统计应用，统计过往存根表中的数据，可以应用这一策略。

2．7 网络方面的调优
Sybase客户和服务器之建传递的是TDS包，缺省大小是512字节。对于要传输大批量数据的应用，如BCP、 文本/图像的取用、大结果集SQL语句，要用下面的配置命令配置大的TDS包大小。
sp_configure "default network packet size", nnn
sp_configure "maximum network packet size", nnn
对于isql 和bcp，可以在应用级指定TDS包的大小：isql -Usa -P –Annn，bcp -Usa -P –Annn。
注意在调大maximum network packet size的参数后，要增大 additional network memory 参数，来适应 maximum network packet size 的要求。

仅供参考