Sybase数据库性能调优.doc

Sybase数据库性能调优广铁集团电算信息中心 王奇成，张南飞 引言铁路客票系统经过多次的技术改进，版本已经升级到4.0，现已能够比较全面充分地满足和适应客票发售和预订的需求，是铁路运输管理信息系统中的重点。客票系统结构上分成铁道部、地区中心和车站三级，技术上采用Sybase数据库，Unix操作系统，前台应用采用PB和BO等开发，是典型的Client-Server应用，但又采用了自行开发的中间件作连接交易处理和数据库通信，具有较强的复杂性。对于遍及全路大小车站，统管全路客票发售的这样一个庞大生产系统，影响举足轻重。尤其是节假日铁路售票高峰期里，大到地区中心，小到车站，只要客票主机一有故障，造成停机，便会直接影响售票，极大地减少铁路运输收入。在多年的客票系统建设和维护中，深深体会到客票系统对Sybase数据库资源的方方面面的要求，Sybase数据库性能对客票系统至关重要的影响。现在，全路的客票系统出现一种数据集中的趋势，大站带小站，多站合并，票额集中到地区中心，这样，数据库的规模便越来越大，可用性要求越来越强。如何深入调整Sybase数据库的性能，保证数据库的高可用性，来满足日益增长的客票网络的需求，尤其在过年过节等客运高峰期之需要，是每一个地区中心和每一个车站的数据库管理员的重要课题。本文结合客票系统，对如何调整优化Sybase数据库的性能做个较深入的论述。1 概述11 性能指标数据库性能一般用两个方面的指标来衡量：响应时间和吞吐量。响应越快，吞吐量越大，数据库性能越好。响应时间和吞吐量有些情况下不能一起得到改善。12 调优级别对Sybase数据库性能调优，可以从四个方面进行：一) 操作系统级：对网络性能、操作系统参数、硬件性能等作改进。二) SQL Server级：调整存取方法，改善内存管理和锁管理等。三) 数据库设计级：采用降范式设计，合理设计索引，分布存放数据等。四) 应用程序级：采用高效SQL语句，合理安排事务，应用游标，处理锁。本文对第一方面的内容不做讨论，第二方面提到的概念只适用于Sybase数据库，但第三、第四方面讨论的问题同样适用于Sybase外的其他数据库。以上各个方面的措施是相互牵连的，具体到解决一个性能问题，有时候要综合应用。13 调优工具在分析Sybase数据库的性能时，要用到一些数据库系统本身提供的性能调优工具，包括几个系统存储过程：名称功能简要介绍sp_sysmon企业级系统性能报告工具sp_lock查看锁的情况sp_who查看线程的活动情况sp_procqmode存储过程的查询处理模式sp_configure配置SQL Server系统级参数sp_estspace估计创建一个表需要的空间和时间sp_spaceused估计表的总行数及表和索引占用的空间sp_monitor监视CPU、I/O的统计活动情况在利用isql等一些工具时，还可以设置查询会话中的几个选项，来显示SQL语句执行时的各种统计分析结果：指令On 的含义set noexec on/off分析SQL语句后，还要执行set statistics io on/off统计SQL执行所需I/Oset statistics time on/off统计SQL语句执行耗时set showplan on/off显示查询计划14 sp_sysmon 的使用企业级性能报告工具、系统存储过程 sp_sysmon 的使用方法：在isql 下，首先输入 sp_sysmon begin_sample 启动一个报告采样过一段时间后，再输入 sp_sysmon end_sample结束上次报告采样或者紧跟一参数sp_sysmon end_sample, dcache 结束上次报告采样， 但只显示数据缓冲（Data Cache Management）这一部分的情况。能替换dcache的可选参数如下表所示：参数参数全称，内容范围解释DcacheData Cache Management，数据缓冲KernelKernel Utilization，有关引擎、网络和I/O等情况WpmWorker Process ManagementParallelParallel Query ManagementTaskmgmtTask ManagementAppmgmtApplication ManagementEspESP ManagementHousekeeperHousekeeper Task ActivityMonaccessMonitor Access to Executing SQLXactsumTransaction ProfileXactmgmtTransaction ManagementIndexmgmtIndex Management，索引管理MdcacheMetadata Cache ManagementLocksLock Management，锁管理PcacheProcedure Cache ManagementMemoryMemory ManagementRecoveryRecovery ManagementDiskioDisk I/O Management，磁盘I/O管理NetioNetwork I/O Management15 用sp_sysmon可以得到数据库系统的性能基准报告，但要在比较稳定的状态下产生，方可作为参考和对照的依据。16 理解存储方法只有清楚数据库存储数据的底层细节，如数据页、索引页的物理结构，每一行的大小计算，不同类型列占用的宽度等等问题，才能对各种调优措施有个深入领会。关于这个问题，比较复杂和细致，请自行参阅有关书籍。一般地，对于更改数据的操作，要尽量促进数据库进行直接更新（ Direct Updates ），所以要遵守以下几条原则：1）除非必要，避免使用允许null值的列和可变长度的列。2）如果varchar 和 varbinary 列填充得比较满，毫不犹豫转成 char 和 binary 列。对于建表时指定的页填充率（page fillfactor）参数，要权衡确定数值大小。一般：小值，适合于有许多随机插入的表，该表的数据经常被删除，又经常被增加；大值，适合于大多数的数据被增加到表末尾，如客票系统的售票存根和退票存根表。2 SQL Server级的调优21 管理共享内存数据库性能优化的首要方面是最优管理内存。数据库占用的共享内存分成数据缓冲（data cache）、存储过程缓冲（Procedure cache）等几块。在isql 下使用 sp_configure cache 可以看到存储过程缓冲所占百分比（procedure cache percent），整个数据缓冲大小（total data cache size） 等参数。211 存储过程缓冲（Procedure cache）存储过程缓冲保持以下对象的查询计划：Procedures：存储过程Triggers：触发器Views：视图Rules：规则Defaults：缺省Cursors：游标存储过程不可重入，意即每个并发用户调用都会在内存中产生一个拷贝。Procedure, triggers, and views 当它们被装载到procedure cache中时，被查询优化器优化，建立查询计划。如果存储过程在缓冲中，被调用时就不需要重新编译。如果procedure cache太小，存储过程就会经常被其他调入内存的存储过程冲洗掉，当再次被调用时，存储过程又被调入内存，再重新编译，用户请求因此不得不等待。最严重的情况，如果procedure cache不够，存储过程甚至都不能运行。所以在内存足够的情况下，procedure cache percent 参数尽可能大一些。212 数据缓冲（Data Cache）数据缓冲用来缓存数据页和索引页，是除去存储过程缓冲，系统其他占用的缓冲外的剩余内存空间。通过给服务器增加物理内存扩大数据缓冲，是最有效的方法。当然，如果不能加内存，就只能通过减少存储过程缓冲的比例等方法来扩大数据缓冲了。通过 sp_configure extent I/O buffers, 20（可调） 命令，在Data Cache中保留一些页专用于创建索引时使用，可以显著提高创建索引的性能。但要注意每开辟一个缓冲占用16K 字节的系统内存。213 命名缓冲通过如下的命令：1 sp_helpcache2 go查看某客票数据库中命名缓冲，得到的结果如下：Cache Name Config Size Run Size Overhead- - - -DS30_Tran_Log 20.00 Mb 20.00 Mb 2.05 MbSystemtable 20.00 Mb 20.00 Mb 2.05 Mbdefault data cache 0.00 Mb 4462.86 Mb 464.97 Mbleft_base_center 16.00 Mb 16.00 Mb 1.57 Mbprice_cache 8.00 Mb 8.00 Mb 0.85 Mb可以看出有4个命名缓冲，分别绑定客票系统的应用日志表、一些重要且常用的系统表、余票表、票价系列表，另外1个是缺省数据缓冲。这种配置还不是最合理，应该进一步把Systemtable这个命名缓冲细分成很多个，每一个单独存放一张系统表。214 缓冲策略缓冲策略是指把数据提前读入内存的机制，分预取策略(Prefetch Strategy，即大I/O策略)和取后马上丢弃策略(Fetch-and-Discard)、提示策略(Hints)等几种。可以在三个级别上设置表数据的预取策略(Prefetch Strategy，即大I/O策略)于：对象级，会话级，查询级。如果三个级别上都有设置，它们发生作用的优先顺序是：对象级 会话级 查询级。对于如何在查询级利用指定的缓冲池，可以查看下面例子（使用4K缓冲池）：select au_fname, au_lnamefrom authers (prefetch 4)where au_id in ( A372020631, ., A1887081515 )goDSS应用往往得益于大的I/O，应该放开large I/O strategy预取策略。如果一个应用倾向于OLTP特征，用户能在会话级关掉Prefetch来提高性能。对于OLTP应用，关闭large I/O strategy预取策略。对于所取到的页不会有重用的情况，放开fetch-and-discard策略。客票系统对存根数据进行统计的应用，如财收日结账，营销分析数据整理模块和综合查询等，都可以利用这一结论。查看几个操作频繁且较大的表上的缓冲策略，用如下命令：sp_cachestrategy center,seat_areasp_cachestrategy center,sale_record050522 管理锁221 页锁升级阀限优化锁的重要考虑是设置页级锁升级升级成表级锁的阀限。要尽量避免页锁很快升级成表级锁。在某客票数据库中，用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 计算阀限 , =, ，便不能充分利用索引。如果要充分利用索引，在 column 中不要包括函数和其他操作；expression 必须是常量或可以转化成常量。查询优化器会认为，between 相当于 = 和 = Ger and 50在titleauthor.titleid有聚族索引，这种排列顺序是把titles作为外部表，查询优化器这样确定逻辑I/O的次数：外部表titles的页数 + （外部表titles的行数 * 2 ）= 620 + （5000 * 2） = 10,620 。（注意从表titleauthor中取每一行估计有2次读，一次索引页，一次数据页）查询2：select t.title from titles t, titleauthor tawhere t.title_id = ta.title_idand royaltyper 50在titles.titleid有聚族索引，这种排列顺序是把titleauthor 作为外部表，查询优化器这样确定逻辑I/O的次数：外部表titleauthor的页数 + （外部表titleauthor的行数 * 2 ）= 68 + （2062 * 2） = 4192。可见，在表连接查询中，如果行数大的表放在后面位置可以提高速度。425 孤立级0的妙用当应用需要较好的并发性，并且近似的查询结果也可接受的情况下，SQL语句要用孤立级isolation level 0，尤其是对于有多个处理器环境下的OLTP应用。isolation level 0扫描不获取锁，所以不需要内部的重扫描，大大提高效率。在客票系统的余票查询应用中，因为余票查询的结果是个动态而近似的参考数值，只对很短的一段时间内有效，供指导售票之用，不需要很精确，所以对于余票查询模块，包括综合查询中的子功能，计划管理中的子功能，尤其是前台售票中的子功能，如果用到此孤立级，可以很好地改善售票高峰期的所以售票程序长久没有反应，相互等待的现象。但须注意，isolation level 0忽略查询优化器，依赖唯一索引，所以要慎重创建SQL语句，最好由有丰富经验的程序员来完成。如下是一些SQL语句用到isolation level 0的格式。declare cs1 cursor for select . at isolation 0 | read uncommitted select . at isolation 0readtext . at isolation 0426 存储过程的重编译存储过程执行的时候带参数 with recompile, 可以让查询优化器更新查询计划。当在表上增加索引，或者执行了 update statistics 指令后，运行 sp_recomplie table_name， 则所有依赖于此表的存储过程下次运行时被重新编译，即更新它们的查询计划。如果存储过程中会创建临时表，它总是重新生成查询计划。