浅析Sybase数据库系统性能调优
来源：百度文库 编辑：神马文学网 时间：2024/04/30 20:30:34
[转]浅析Sybase数据库系统性能调优
浅析Sybase数据库系统性能调优
“性能调优”是对应用程序的性能优化。SYBASE数据库“性能调优”的主要目的是减少对系统公共资源的争用。对sybase数据库系统的性能进行优化,是一项长期且受诸多因素影响的工作,它可划分为以下4个层次：
(1) 服务器层：包括对内存的合理分配,锁操作和临时表的使用,与系统配置关联的磁盘的I/O性能。
(2) 数据库层：:包括数据库对象的设计,索引的创建!表中数据类型的选择,数据库设备的分配及使用。
(3) 应用层：包括T_SQL查询语句的优化,应用级封锁,事务和游标的使用。
(4) 运行环境层:包括硬件、操作系统和网络对总体性能的影响。
在数据库应用系统的管理维护中，运行环境引起的性能劣化只有通过硬件的升级才能得到优化，在系统硬件配置和网络设计确定的情况下，影响系统性能的主要是数据库层和服务器层。笔者就数据层和服务器层优化进行总结。
一、数据库表的优化
对于Sybase11.9以前的版本，由于数据库管理系统只提供了页级锁和表级锁,因此减少页的竞争将有助于提高系统的性能.
当大多数更新都发生在同一页上时，该页将成为热点，通过分析性能监测报告,我们可以得到系统的页竞争情况,例如下述报告表明有99%的插入发生在堆表的最后一页上,并且正在等待锁:
Last Page Loocks on Heaps
Granted 3.0 0.4 185 88.1%
Waited 4.0 0.0 25 11.9%
解决上述问题的方法有两种:一种是将表分区,表分区后可产生多个页链,这样就有多个“最后一页”来满足插入要求，从而减少并发插入时的相互等待；另一种是采用非簇类索引，将更新分布于表中不同的数据页上，但该方法会增加数据物理顺序的开销。
对于一些竞争非常激烈且记录条数较少的表，我们可以通过减少数据页或索引页上行的数量来进行优化，即将一条记录分布在一页上， 这样各进程需要的页都不相同，从而可以大大减少数据页的竞争。
在建表时Sybase提供了fillfactor和max_roms_per_page两个参数，分别用来改变索引页和数据页的填充程度。
在定义数据库表时,字段的数据类型选择是否合理对数据库的性能和操作有很大影响,
（1）Identify字段不要作为表的主键与其它表关联，这将会影响该表的数据迁移。
（2）TEXT与IMACE字段常用来存放二进制对象，这类数据的操作相比其它数据类型较慢，因此要避免使用。
建立一个好的索引对优化数据库的查询性能是非常重要的。要设计一个合理，索引关键就在于创建什么字段作为索引以及创建哪种类型的索引，这是因为定义哪个字段作为索引，涉及到执行一次详细的查询分析需检查其查找子句中哪些字段引用以及索引的有用性，并把这些查询按重要性排队。由于SQL SERVER一般在每张表上只选一个索引来满足查询，因此索引中的第一个元素最好是惟一性最好的。
群集索引通常用于主键标，因为主键标一般是一张表的主访问路径。不过，在下列情况下也可采用群集索引。
范围查找，含有大量重复值的字段；
ORDER BY中常引用的字段；
连接子句中引用的不是主键标的字段；
非常频繁地被访问的字段。
非群集索引一般用于以下情况：
单行查找；
连接运算以及在选择性很高的字段上的查询。
带有小范围检索的查询。
虽然采用索引可以提高数据库的查询性能，但过多的索引会适得其反，这是因为在修改、插入或删除数据时为了保持最新的索引，必须引发系统I/O开销。因此当索引列中的大量数据被增加、改变或删除时，应使用命令UP_DATE STATISTICS保持索引的最新状况。
同时，SQL SERVER所具有的基于成本的查询优化器将比较表扫描与利用索引进行查询系统的I/O开销,以找出最佳途径,因此,并非在表上建立了群集索引或非群集索引就一定会被使用,而是取决于对检索数据的查寻命令的写法和应用的要求，索引的使用效果在相当程度上是依赖于应用程序的设计的,究竟是让索引满足程序的设计需要,或是程序的设计遵循已建立的索引,两者之间是相辅相成的,只有正确地使索引与程序结合起来,才能使系统的性能优化到最佳状态。
二、内存性能调优
为了最大限度的减少对应用系统运行状态的影响，对sybase数据库的调优主要从内存的使用和tempdb的优化来进行
（一）、ASE对内存使用
1、内存对ASE性能的影响
由于访问内存比访问硬盘快，有充足内存可减少硬盘I/O，从而提高ASE性能
2、ASE内存分配情况：见下图
ASE安装好后可供数据库使用的内存有两大部分：过程缓存和数据缓存。其中： 过程缓存：用于存放查询计划、存贮过程和触发器；数据缓存：用于所有数据、索引和日志数据页。过程缓存、数据缓存的大小用系统存储过程 “sp_configure”进行配置。对内存的忧化可从以下几点进行：
①、 查看并配置ASE内存：
Sybase ASE安装完成后“total memory”缺省为21504页（2K页），在实际应用中应根据服务器的物理内存大小进行配置：例如256M的物理内存“total memory”可配置为90000-100000
Sp_configure “total memory”,100000
配置完成后重启ASE服务使配置生效。
②、配置足够大的命名数据高速缓存以容纳关键表和索引。这样可防止其它服务器活动争用高速缓存空间，并加速使用这些表的查询，因为所需页始终都可在高速缓存中找到。同时，可以考虑将“热”表如：用户应用程序对其需求较大的表绑定到一个高速缓存上，而表上的索引绑定到其它高速缓存，以提高并发性。
具体做法如下：
创建命名缓存
sp_cacheconfig cache_name,”size[P|K|M|G]”
例如创建一个10MB的命名缓存pubs_cache ：sp_cacheconfig pubs_cache,”10M”
把表绑定到指定的命名缓存：
sp_bindcache cache_name,dbname[,[owner.]table_name[,indexname|”text only”]]
例如把titles表绑定到上面刚建的命名缓存中：
sp_bindcache pubs_cache,pubs2..titles
③、配置完成后，可以使用dbcc命令检查内存的使作情况。
（二）对tempdb使用优化
缺省情况下，tempdb数据库是放置在master设备上，容量为2M，而临时数据库是活动最为平凡的数据库常常被用来排序、创建临时表、重格式化等操作，所以tempdb的优化应该受到特别的关注,缺省情况下，用于tempdb的system、default和logsegment段在主设备上分配了2MB空间。将第二个设备分配给tempdb后，即可在default和logsegment段中将主设备删除。使用这种方式，可以确保tempdb中的工作表和其它临时表不会和主设备上的其它使用相互争用。
具体操作如下：
第一步：将tempdb移到新的设备上
(1) 创建tempdb_dev设备，扩展tempdb空间
例如d:\device\dempdb_dev.dat
disk init
name:”tempdb_dev”
phyname:”d:\device\dempdb_dev.dat”,
vdevno=4,size=5120
（2）将tempdb移动到新的设备tempdb_dev
alter database tempdb on tempdb_dev=5
（3）从tempdb段上移走master设备
（4）校验defaule段已包括主设备
说明：若将临时数据库放在多个磁盘设备上，可以更好的利用并行查询特性来提高查询性能。
第二步：将临时数据库与高速缓冲进行绑定。
由于临时表的创建、使用，临时数据库会频繁地使用数据缓存，所以应为临时数据库创建高速缓存，从而可以使其常驻内存并有助于分散I/O：
1、创建命名高速缓存：
sp_cacheconfig“tempdb_cache”,“1000m”,”mixed”
查询命名CACHE情况,sp_cacheconfig或sp_helpcache
2、重新启动server使配置参数生效
3、捆绑临时数据库到tempdb_cache高速缓存：
sp_bindcache “tempdb_cache”,tempdb
若有大的I/O，配置内存池
sp_poolcache“tempdb_cache”，“5M”，“16k”
第三步：优化临时表
大多数临时表的使用是简单的，很少需要优化。但需要对临时表进行复杂的访问则应通过使用多个过程或批处理来把表的创建和索引分开。以下两种技术可以改善临时表的优化
1、在临时表上创建索引
1）临时表必须存在
2）统计页必须存在（即不能在空表上创建索引）
2、把对临时表的复杂的使用分散到多个批处理或过程中，以便为优化器提供信息。
为了便于确定性能问题所在，我们把数据库应用系统分为几个层次(或称为调优层次)。下面就几个调优层提几点建议：
l 应用层： OLTP与DSS、事务设计(尽量采用短事务从而减少锁争用)、索引可增加查询速度但减少数据修改速度、用参照完整性会在修改数据时需表的连接、使用存贮过程l 数据库层：把数据分布在不同数据库设备上以减少I/O争用、将关键表和索引放在缓存中、对有大量数据加载的表可将该表分片l 服务器层：调整内存和其它服务器参数、配置缓存和I/O大小、增加多个CPU、避免批操作与OLTP争用。设备层：使用多个中等大小设备和多个硬盘控制器可提高I/Ol
网络层：配置网包大小、配置多个网络引擎、配置子网、提高主干网络速度
l 硬件层： CPU吞吐量、硬盘读写速度、内存使用效率
l 操作系统层： 文件系统与裸设备的选择 ：文件系统作为数据库设备，速度快但安全性差，一般用于开发环境；裸设备作为数据库设备，速度慢但安全性好。
loading...
浅析Sybase数据库系统性能调优
“性能调优”是对应用程序的性能优化。SYBASE数据库“性能调优”的主要目的是减少对系统公共资源的争用。对sybase数据库系统的性能进行优化,是一项长期且受诸多因素影响的工作,它可划分为以下4个层次：
(1) 服务器层：包括对内存的合理分配,锁操作和临时表的使用,与系统配置关联的磁盘的I/O性能。
(2) 数据库层：:包括数据库对象的设计,索引的创建!表中数据类型的选择,数据库设备的分配及使用。
(3) 应用层：包括T_SQL查询语句的优化,应用级封锁,事务和游标的使用。
(4) 运行环境层:包括硬件、操作系统和网络对总体性能的影响。
在数据库应用系统的管理维护中，运行环境引起的性能劣化只有通过硬件的升级才能得到优化，在系统硬件配置和网络设计确定的情况下，影响系统性能的主要是数据库层和服务器层。笔者就数据层和服务器层优化进行总结。
一、数据库表的优化
对于Sybase11.9以前的版本，由于数据库管理系统只提供了页级锁和表级锁,因此减少页的竞争将有助于提高系统的性能.
当大多数更新都发生在同一页上时，该页将成为热点，通过分析性能监测报告,我们可以得到系统的页竞争情况,例如下述报告表明有99%的插入发生在堆表的最后一页上,并且正在等待锁:
Last Page Loocks on Heaps
Granted 3.0 0.4 185 88.1%
Waited 4.0 0.0 25 11.9%
解决上述问题的方法有两种:一种是将表分区,表分区后可产生多个页链,这样就有多个“最后一页”来满足插入要求，从而减少并发插入时的相互等待；另一种是采用非簇类索引，将更新分布于表中不同的数据页上，但该方法会增加数据物理顺序的开销。
对于一些竞争非常激烈且记录条数较少的表，我们可以通过减少数据页或索引页上行的数量来进行优化，即将一条记录分布在一页上， 这样各进程需要的页都不相同，从而可以大大减少数据页的竞争。
在建表时Sybase提供了fillfactor和max_roms_per_page两个参数，分别用来改变索引页和数据页的填充程度。
在定义数据库表时,字段的数据类型选择是否合理对数据库的性能和操作有很大影响,
（1）Identify字段不要作为表的主键与其它表关联，这将会影响该表的数据迁移。
（2）TEXT与IMACE字段常用来存放二进制对象，这类数据的操作相比其它数据类型较慢，因此要避免使用。
建立一个好的索引对优化数据库的查询性能是非常重要的。要设计一个合理，索引关键就在于创建什么字段作为索引以及创建哪种类型的索引，这是因为定义哪个字段作为索引，涉及到执行一次详细的查询分析需检查其查找子句中哪些字段引用以及索引的有用性，并把这些查询按重要性排队。由于SQL SERVER一般在每张表上只选一个索引来满足查询，因此索引中的第一个元素最好是惟一性最好的。
群集索引通常用于主键标，因为主键标一般是一张表的主访问路径。不过，在下列情况下也可采用群集索引。
范围查找，含有大量重复值的字段；
ORDER BY中常引用的字段；
连接子句中引用的不是主键标的字段；
非常频繁地被访问的字段。
非群集索引一般用于以下情况：
单行查找；
连接运算以及在选择性很高的字段上的查询。
带有小范围检索的查询。
虽然采用索引可以提高数据库的查询性能，但过多的索引会适得其反，这是因为在修改、插入或删除数据时为了保持最新的索引，必须引发系统I/O开销。因此当索引列中的大量数据被增加、改变或删除时，应使用命令UP_DATE STATISTICS保持索引的最新状况。
同时，SQL SERVER所具有的基于成本的查询优化器将比较表扫描与利用索引进行查询系统的I/O开销,以找出最佳途径,因此,并非在表上建立了群集索引或非群集索引就一定会被使用,而是取决于对检索数据的查寻命令的写法和应用的要求，索引的使用效果在相当程度上是依赖于应用程序的设计的,究竟是让索引满足程序的设计需要,或是程序的设计遵循已建立的索引,两者之间是相辅相成的,只有正确地使索引与程序结合起来,才能使系统的性能优化到最佳状态。
二、内存性能调优
为了最大限度的减少对应用系统运行状态的影响，对sybase数据库的调优主要从内存的使用和tempdb的优化来进行
（一）、ASE对内存使用
1、内存对ASE性能的影响
由于访问内存比访问硬盘快，有充足内存可减少硬盘I/O，从而提高ASE性能
2、ASE内存分配情况：见下图
ASE安装好后可供数据库使用的内存有两大部分：过程缓存和数据缓存。其中： 过程缓存：用于存放查询计划、存贮过程和触发器；数据缓存：用于所有数据、索引和日志数据页。过程缓存、数据缓存的大小用系统存储过程 “sp_configure”进行配置。对内存的忧化可从以下几点进行：
①、 查看并配置ASE内存：
Sybase ASE安装完成后“total memory”缺省为21504页（2K页），在实际应用中应根据服务器的物理内存大小进行配置：例如256M的物理内存“total memory”可配置为90000-100000
Sp_configure “total memory”,100000
配置完成后重启ASE服务使配置生效。
②、配置足够大的命名数据高速缓存以容纳关键表和索引。这样可防止其它服务器活动争用高速缓存空间，并加速使用这些表的查询，因为所需页始终都可在高速缓存中找到。同时，可以考虑将“热”表如：用户应用程序对其需求较大的表绑定到一个高速缓存上，而表上的索引绑定到其它高速缓存，以提高并发性。
具体做法如下：
创建命名缓存
sp_cacheconfig cache_name,”size[P|K|M|G]”
例如创建一个10MB的命名缓存pubs_cache ：sp_cacheconfig pubs_cache,”10M”
把表绑定到指定的命名缓存：
sp_bindcache cache_name,dbname[,[owner.]table_name[,indexname|”text only”]]
例如把titles表绑定到上面刚建的命名缓存中：
sp_bindcache pubs_cache,pubs2..titles
③、配置完成后，可以使用dbcc命令检查内存的使作情况。
（二）对tempdb使用优化
缺省情况下，tempdb数据库是放置在master设备上，容量为2M，而临时数据库是活动最为平凡的数据库常常被用来排序、创建临时表、重格式化等操作，所以tempdb的优化应该受到特别的关注,缺省情况下，用于tempdb的system、default和logsegment段在主设备上分配了2MB空间。将第二个设备分配给tempdb后，即可在default和logsegment段中将主设备删除。使用这种方式，可以确保tempdb中的工作表和其它临时表不会和主设备上的其它使用相互争用。
具体操作如下：
第一步：将tempdb移到新的设备上
(1) 创建tempdb_dev设备，扩展tempdb空间
例如d:\device\dempdb_dev.dat
disk init
name:”tempdb_dev”
phyname:”d:\device\dempdb_dev.dat”,
vdevno=4,size=5120
（2）将tempdb移动到新的设备tempdb_dev
alter database tempdb on tempdb_dev=5
（3）从tempdb段上移走master设备
（4）校验defaule段已包括主设备
说明：若将临时数据库放在多个磁盘设备上，可以更好的利用并行查询特性来提高查询性能。
第二步：将临时数据库与高速缓冲进行绑定。
由于临时表的创建、使用，临时数据库会频繁地使用数据缓存，所以应为临时数据库创建高速缓存，从而可以使其常驻内存并有助于分散I/O：
1、创建命名高速缓存：
sp_cacheconfig“tempdb_cache”,“1000m”,”mixed”
查询命名CACHE情况,sp_cacheconfig或sp_helpcache
2、重新启动server使配置参数生效
3、捆绑临时数据库到tempdb_cache高速缓存：
sp_bindcache “tempdb_cache”,tempdb
若有大的I/O，配置内存池
sp_poolcache“tempdb_cache”，“5M”，“16k”
第三步：优化临时表
大多数临时表的使用是简单的，很少需要优化。但需要对临时表进行复杂的访问则应通过使用多个过程或批处理来把表的创建和索引分开。以下两种技术可以改善临时表的优化
1、在临时表上创建索引
1）临时表必须存在
2）统计页必须存在（即不能在空表上创建索引）
2、把对临时表的复杂的使用分散到多个批处理或过程中，以便为优化器提供信息。
为了便于确定性能问题所在，我们把数据库应用系统分为几个层次(或称为调优层次)。下面就几个调优层提几点建议：
l 应用层： OLTP与DSS、事务设计(尽量采用短事务从而减少锁争用)、索引可增加查询速度但减少数据修改速度、用参照完整性会在修改数据时需表的连接、使用存贮过程l 数据库层：把数据分布在不同数据库设备上以减少I/O争用、将关键表和索引放在缓存中、对有大量数据加载的表可将该表分片l 服务器层：调整内存和其它服务器参数、配置缓存和I/O大小、增加多个CPU、避免批操作与OLTP争用。设备层：使用多个中等大小设备和多个硬盘控制器可提高I/Ol
网络层：配置网包大小、配置多个网络引擎、配置子网、提高主干网络速度
l 硬件层： CPU吞吐量、硬盘读写速度、内存使用效率
l 操作系统层： 文件系统与裸设备的选择 ：文件系统作为数据库设备，速度快但安全性差，一般用于开发环境；裸设备作为数据库设备，速度慢但安全性好。
loading...