SYBASE数据库性能优化案例

ehxz

免费乐捐，感谢支持

　　提 要
　　为了提高数据库系统的性能，本文针对资料入库，资料检索速度的提高进行了研究和探讨，并提出了相应的的技术措施。
　　关键词：性能提高 入库 检索
　　1．问题的提出
　　使用SYBASE商用数据库管理系统，但在应用中也发现一些性能问题，例如用户反映常规资料入库较慢、探空资料检索不够快等。针对这些情况，我们进行了分析、试验和探讨，在目前运行平台的环境下找到一些改进方法，取得了较好的效果。
　　2．库生成慢的原因分析和技术措施
　　2．1库生成慢的原因分析
　　在常规资料入库的整个过程中主要包括对报文的解码和数据插表两个大的步骤，因此应先分清究竟是解码过程慢还是数据插表过程慢。为此在主站的调试环境IBM RS6000-59H的平台上做了测试，发现对一天常规资料只解码不入库只需4分钟，但整个库生成却需要4小时11分；显然绝大多数时间是花在数据入库上。而进入数据库的报类多种多样，插入的时间也有长有短。因此有必要在这所有的资料中，找出是哪几种资料入库的时间长。用一个时间统计程序分别对各类报的插入时间做了统计，结果发现地面要素报的插入时间最长，在总共4小时11分的插入时间中占1小时56分钟。
　　于是我们在测试库（IBMRS6000-H50 B系平台）中用SYBASE提供的监测程序过程sp_sysmon监测插入一天的地面要素（28239条记录，即28239个事务）的运行情况。在sp_sysmon的输出结果（见表1）中，
　　表1 插一天地面要素的运行情况

每秒收到客户端的字节数	5681．9
每个事务所占用网包数	3.0
每秒收到客户端的网包数	14.7
插入时间（秒）	5837

　　发现描述每个事务所占用网包数的参数（Total TDS Packets Received per xact）等于3，也就是说一条插入命令要分三次才能送到服务器端。这引起我们的深思，业务上已将Server的缺省网络包大小（default network packet size）、最大网络包大小（max network packet size）由512字节改为1024字节，为何一条插入命令要分三个网包，我们立即查证一条地面要素插入命令的长度，大部分在1100字节左右，最长的是1174字节，超过了1024的长度，但仍然让人不解的是：一条长为1136字节插入语句用1024字节的网包发送，应该使用两个网包就够了，为什么会用到3个呢？除非512字节的网包才要用到这个数。这是什么原因呢？向SYBASE公司咨询，得到的答案是不仅要在Server端修改参数，还必须在客户端程序中用DBSETLPACKET()函数显式指定和default network packet size一样大小的网包值，改动才起作用。而以前并未在程序中作指定，因此服务器与客户端之间的通信仍采用SYBASE的缺省网包长度512字节。
　　在SYBASE的处理中是需接收一条完整的命令后才能进行处理。如果一条命令跨网包传送，Server在收到第一个网包时并不能立即对它进行处理，还必须等待下一个网包将完整的命令送过来，这就涉及到TCP协议的延迟确认问题。它的主要内容是：面向连接的传输协议要求对发送的每一个包都要进行确认，通常对所收到数据进行确认的包都很小，为了避免在网络上产生过多的短包，接收端在收到数据之后并不立即给发送方发送确认信息，而是要延迟一段时间，看本方是否有数据需要发送至对方，如果有则将数据连同对上次收到数据的确认消息一起发送给对方，这中间就有延迟时间。关于延迟时间的长短，不同的机器有不同的设置，TCP协议规定不超过500毫秒，有的机器取200毫秒，这是个相当长的时间。
　　我们从sp_sysmon的输出结果的另一参数，即 Server端每秒收到客户端的字节数的参数Total Bytes Received per sec的值仅为5681.9，这说明平均一秒中只有5个事务送到服务器端等待处理，即使忽略掉处理时间，一秒中也最多只能处理5个事务。实际的情况是插28239个事务用了5837秒，平均每秒处理4.87个事务。当将网包长度改为1536后，同样的资料全部入库只需285秒的时间。可见网包长度不当是影响库生成的一个原因。
　　2．2 网包长度的试验和对比分析
　　SYBASE是基于客户机服务器体系结构的数据库系统，客户机和服务器之间的是网络通信，因此网包长度是SYBASE性能调优的一个重要参数，是一个很重要也很值得探讨的问题。不同的应用对网包大小有不同的需求，9210数据库接收的资料多种多样，有的数据的一条插入命令长达10多K字节（如公报），有的命令却只有100多个字节（如探空要素）。我们该如何综合考虑选取一个合适的网包长度呢？
　　为此我们针对一天的公报资料、探空资料、地面要素，采用不同的网包长度，利用单个进程和三个并发进程等多种方式进行插表试验， 其运行结果见表2：
　　表2 不同网包长度插表试验运行时间
　　  

	网包大小（字节）	512	1536	2048	11242
公报 　　资料	单进程插入时间（秒）	2703	1371	981	539
	三个并发进程插入时间（秒）	997	568	608	554
地面 　　要素	单进程插入时间（秒）	5805	296	290
	三个并发进程插入时间（秒）	1949	263	265
探空 　　资料	单进程插入时间（秒）	242	246	239
	三个并发进程插入时间（秒）	222	214	215

　　从上述试验结果可见改动网包长度能对一些插入命令长度超过512B的资料（如公报、地面要素）有作用，对于一些插入命令长度不超过512B的资料基本无作用，且网包长度从512改成1536时效果显著，即使在三个进程并发时效果也显著（在9210数据库系统中入库时是由三个进程同时进行的）。当网包长度超过1536时插入时间能减少一些，但效果不显著了。
　　2．3 关于网包长度参数设定的注意事项
　　2．3．1 有关网包长度的参数
　　（1） SYBASE在Server端有关网包长度的参数
　　缺省网包长度（Default network packet size） —— 定义所有的用户所使用的缺省网络包的大小，其缺省值为512字节，可根据需要将它设为512字节的任意倍数，其最大值524288字节。
　　最大网包长度（Max network packet size） —— 指定与Server通信的客户所能请求的网络通信包的最大值。它也可根据需要将它设为512字节的任意倍数，其最大值524288字节。
　　Additional network memory —— 该参数定义了比缺省网包大的网络包的额外内存的最大值。它的值必须是2048字节的倍数，缺省为0。
　　（2）SYBASE在客户端有关网包长度的参数
　　SYBASE在客户端有关网包长度的指定因程序的不同而不同，如isql程序用-A****（****为网包长度）指定，对于使用DB-Library函数的程序要用RETCODE DBSETLPACKET语句指定，指定方法如下：
　　RETCODE DBSETLPACKET（LOGINREC *login，short packet_size）
　　参数login为指向LOGINREC结构的指针；
　　参数packet_size为要设置的网包的大小（单位：字节）。
　　函数的返回值为SUCCESS或FAIL。
　　当客户端程序不指定网包长度的参数时取缺省网包长度512。
　　2．3．2 网包长度参数之间的关系和作用
　　Server端最大网包长度是说明本Server提供的最大网包长度，当客户端程序指定的网包长度超过最大网包长度时，连接Server出错；
　　当客户端程序指定的网包长度不当时（即客户端程序指定的网包长度大於Server端缺省网包长度，而且额外网络内存又不够大），使用Server端缺省网包长度。另外，Server根据缺省网包的大小从 Server的内存池（由total memory配置参数决定）中分配一部分为网络内存，它的值是：用户连接数 * 3 * 缺省网包长度。缺省网包长度必须小於等於最大网包长度；
　　额外网络内存是从操作系统的内存中再申请一部分来作为Server和客户间通信使用，其值为：
　　（[3 * 最大网包长度 * 用到最大包的用户连接数 * 1.02 /2048]+1） * 2048
　　客户端的网包长度是向Server申请此次连接所用网包的长度，它的长度不得大於Server端最大网包长度，当它的值大於Server端缺省网包长度时，必须有足够大的额外网络内存，若额外网络内存不够大，则使用Server端缺省网包长度，当客户端的网包长度不指定时，则使用客户端的缺省网包长度512字节。
　　综上所述，较大的网包长度是有利于Server和客户端的大数据量传送，提高SYBASE的性能，但它是以消耗内存为前提的，过多的内存消耗会影响到整个系统的性能，因此一般的情况下不配置额外网络内存，Server端缺省网包长度的长度以适当为好，所以我们把Server端缺省网包长度定为1536字节，客户端也指定为1536字节。
　　2．4 关于网包的进一步应用
　　当网包长度为1536字节时，对于探空资料的要素表，它的一条插入命令长度最长只有166字节，那每次发送给Server的网包都有大量的空闲空间。是否能将这些空间充分利用，即在一个网包中包含多条插入命令从而提高性能呢？答案是可行的，SYBASE的一个事务可包含多条插入命令，况且一份探空报告解码后包含多层探空要素，具备一次可形成多条插入命令的条件。为此，我们做了几组将几条命令放在一个网包里一次发送的测试，结果绘成如下的曲线图（图1）。

　　根据推算，1536的网包最多能放下9条完整的命令，但实验结果是以6条为一批的效果最好，这可能是系统各个方面的综合影响之效果。由此可见，采取此种方法也能提高插入性能。
　　3．检索慢的原因分析和技术措施
　　3．1 检索慢的原因分析
　　为了查清检索慢的原因首先要确定哪些资料检索慢，我们在主站业务数据库中对下列要素资料：
　　地面要素资料（代号：SURF）； 探空要素资料（代号：TEMP）；
　　卫星探测表温、风、云、辐射二段要素资料（代号：GEO2）；
　　卫星探测表温、风、云、辐射五段要素资料（代号：GEO5）；
　　卫星探测高空温度（厚度）要素资料（代号：TOVC）；
　　卫星探测晴空辐射要素资料（代号：SARA）
　　的一天中一个时次的全部要素进行检索，检索内容存放于用户检索程序的内存数组，其结果见表3：
　　表3 各种资料检索时间
　　  

	SURF	TEMP	GEO2	GEO5	TOVC	SARA
检索资料个数	5403	538	1771	14436	760	760
检索资料字节数	637554	366098	24794	173232	78688	121456
检索时间（秒）	<2	21	< 1	<2	20	55

　　从这些测试结果中可见检索慢的资料是TEMP，TOVC，SARA，这些资料有一个共同的特点，每种资料在库中有键表（有一个唯一索引）和要素表（无索引），在检索过程中要进行表连接。我们用SYBASE提供的工具查看其检索过程及I/O次数、执行时间（即在select 语句前面加上
　　set showplan on （查看查询计划）
　　set statistics io on（查看I / O 次数）
　　set statistics time on（查看执行时间） ）
　　对探空资料检索一天中一个时次的全部要素的过程进行查看。查询语句如下：
　　select * from TEMP01_KEY,TEMP01_ELE
　　where TEMP01_KEY.UOBID=TEMP01_ELE.UOBID and LDATE='20000620' and LTIME=’00’
　　从其输出结果可看出：
　　检索过程的第一步是插入，即根据检索条件中日期、时次，使用键表的索引从键表中读出所需内容作成工作表，并对连接条件UOBID建聚簇索引；
　　第二步是检索，即对要素表进行全表扫描，读出一条记录就根据UOBID扫描工作表（这里用到了工作表的聚簇索引进行扫描），将满足条件的数据输出给用户。
　　对键表、要素表、工作表的扫描次数、I/O次数、执行时间等参数见表4中“要素表无索引”的参数。
　　表4 有无索引检索过程对比
　　

	要素表无索引	要素表有索引
参数	键表	要素表工作表键表要素表
扫描次数	1	16067931357
逻辑读（页）	57	151701216205573039
物理读（页）	0	0000
写（页）	79	0
执行时间	27206毫秒	9426毫秒 表中的逻辑读是从缓冲区中读，物理读是从磁盘中读，由于测试库中只有刚作成的

　
　　
　　表中的逻辑读是从缓冲区中读，物理读是从磁盘中读，由于测试库中只有刚作成的
　　
　　11天探空资料，数据量不大，在缓冲区中可放下，所以本检索没有物理读，表中工作表的扫描次数正好是要素表的记录数。
　　由此可见，在检索过程中，表连接时对要素表执行的是全表扫描，显然，这种极费时间的I/O操作使得检索难以加快，造成这种结果的原因可能是要素表没有索引。为此我们在要素表的连接字段UOBID上建了一个一般索引，再进行检索，可以发现，检索过程由二步变成一步（检索），工作表也不产生，检索时先扫描键表，对要素表的访问用到了索引，I/O显著减少，检索速度提高了两倍（见上表中“要素表有索引”的部分参数值）。可见，对要素表建立索引是提高检索性能的一个主要手段。
　　3．2 建立索引与插入的关系
　　但是索引也有它的副面影响，这主要是指对插入的影响。对于有索引的表，由于插入数据时，还要在索引页建相应的索引，因此建立索引会使插入变慢。当初设计表格期间，对地面要素测试网包长度之时，发现不建索引时插表的速度要比建索引后插表的速度快20%,正是基于这个原因，才对有相关键表的要素表不建索引。
　　为了得到建立索引对探空资料入库的影响，在测试库中，分别对建索引前和建索引后的探空要素表的插入情况进行了测试，结果是插一天资料（约5万多条）有索引比无索引插入时间只多5% 。由此可见 不同资料的不同的索引对插入速度的影响是不同的，于是我们在主站上对无索引的要素表都建了索引，并测试对入库实际影响，但意外地发现入库速度竟然明显加快，一天之内缩短了快一个小时。这是什么原因呢？经过调查分析，由于在每天的入库资料中都会有大量的重复报（一天有上千条），因此也就有大量的删除操作。
　　3．3 建立索引与删除关系
　　和加快检索的原理一样，当我们对探空要素表建索引后，执行删除操作时就不再进行全表扫描，而是先通过索引找到目标记录，这样操作时间大大缩短，测试库的对比结果见表5（删除语句为：
　　delete TEMP01_ELE
　　where UOBID=’06110054511’ and V07004<100. AND (V08001>=66 OR V08001<16)）。
　　可以看到，时间减为原来的1/233。而以前由于没有索引，每次都要全表扫描，花费在这上面的时间是很多的。
　　表5 有无索引删除过程对比
　　

参 数	要素表无索引	要素表有索引
扫描次数	1	1
逻辑读（页）	15170	6
物理读（页）	0	0
写（页）	1	1
执行时间	700毫秒	3毫秒

　　
　　4．应用效果
　　通过修改网包长度和对一些要素表增加索引后，在库生成和数据检索方面都有较大的改进。在主站，改网包后库生成时间缩短30 – 40分种，需要说明的是其改进效果没有试验时好，这是由于业务库所处的环境较之测试库要复杂的多，另一个原因是因为它是三个进程并发操作，由前面的测试结果也可知，它的提高效果不如单进程。而建索引这项应用效果却远比测试效果好，其结果见表6：
　　表6 对键表加索引后检索时间
　　  

	SURF	TEMP	GEO2	GEO5	TOVC	SARA
检索资料个数	5403	538	1771	14436	760	760
检索资料字节数	637554	366098	24794	173232	78688	121456
检索时间（秒）	<2	<3	< 1	<2	<3	<4 这主要是由于业务库比测试库要大的多，查询用到了磁盘

　　这主要是由于业务库比测试库要大的多，查询用到了磁盘
　　
　　I/O，而加了索引大量减少了这项极费时间的操作，因此业务库中探空报的检索速度能提高6倍。另外，建索引后对库生成作用明显，能加快一个小时。
　　除此之外，我们还对沈阳区域中心、天津站、抚顺站做了测试，效果较好。表7是这两项应用（改网包、加索引）的综合效果：
　　表7 应用效果
　　  

	库生成加快时间	探空报检索速度提高倍数
天 津	1小时40分	6
沈 阳	1小时	10
抚 顺	1．5小时	4

　　5．结束语
　　数据库系统性能的调优是与数据库的运行相伴而生的，在系统的运行过程中，要经常使用各种监测工具如sp_sysmon等监测系统的运行情况，找到影响系统性能的因素以便进行有效地调整。
　　本文仅就网包长度和索引对数据库性能的影响进行了探讨与分析，而实际上还会有其它影响性能的因素存在，因而数据库系统的优化和性能提高是一个长期性的工作。

lizhiyuan666

好贴，顶！

longjichen

很详细，感谢!

quanjin

dddddddddddddd

yph_zml

好帖，顶一个～

yu_310

学习中。。。