数据库性能优化

1、性能问题探讨性能问题探讨2009年9月架构架构我们的系统部署方式虽然多种多样的，但是从整个系统架构来说，不外乎左边图所示的结构。大纲大纲 操作系统优化操作系统优化 网络优化 数据库优化 中间件优化 应用优化操作系统优化操作系统优化目前我公司应用系统部署到的操作系统有：AIX、HP-UX、Solaris、Linux、Windows 2003。操作系统引起问题的地方主要有两个方面：内核问题和资源问题。操作系统优化操作系统优化内核问题内核问题内核问题主要是内核参数的设置方面。这个参数主要是分为两部分：内存相关的和网络相关的。比如：我们在安装Oracle时未按照官方说明，修改内存相关的内核参数，就会导

2、致无法使用大的共享内存块。再比如，一些网络参数会影响weblogic的吞吐量。案例：适当调整案例：适当调整udp_sendspace值值网络参数udp_sendspace是UDP的发送缓存，默认值为8K，为减少I/O异常,需调整为32k。 操作系统优化操作系统优化案例：案例：适录调整适录调整nofiles值值在东莞和南海都遇到了以下错误： 这个错误主要就是因为应用系统打开了太多的文件句柄。因为在Unix下，默认一个用户只能打开2000个文件句柄。一旦出现这个错误，weblogic将无法对外提供响应。这个可以通过增加资源限制中的nofiles的值来解决。资源问题资源问题Unix和Linux对每一

3、个用户都有一个资源限制。这些限制可能包括能创建的文件大小、能同时打开的文件数量、能同时启动的进程数、能使用的最大内存等。UNIX环境中可以使用ulimit查看。大纲大纲 操作系统优化 网络优化网络优化 数据库优化 中间件优化 应用优化网络优化网络优化网络的问题主要是两个方面：响应时间和吞吐量。网络优化网络优化响应时间响应时间这里的响应时间主要是查找和连接网络的时间。响应时间过长的常见原因是DNS的解析造成的。案例：案例：DNS解析造成服务启动慢解析造成服务启动慢海南EIP在项目实施中，遇到服务启动很慢的问题，一个服务的启动时间需要20多分钟。后来发现系统走了DNS解析，没有走本地解析。修改为本

4、地解析后，启动时间缩短为4分钟左右。网络优化网络优化吞吐量吞吐量吞吐量主要是指网络的传输速度。这里只讨论网络设备的设置问题。案例：交换机传输模式影响网速案例：交换机传输模式影响网速由于惠州供电局交换机传输模式是“半双工自适应”，造成网速很慢，表现为我们的系统响应很慢，后改成“全双工”模式，系统响应速度明显提高，输电部、计量部、变电部登录系统时间，及不同设备界面切换时间响应在2秒以内。大纲大纲 操作系统优化 网络优化 数据库优化数据库优化 中间件优化 应用优化数据库优化数据库优化目前我公司系统使用的数据库主要是Oracle。Oracle优化主要从5个方面来考虑：软件版本、内存、CPU、IO、数据

5、库对象争用和SQL。数据库优化数据库优化软件版本软件版本软件版本可以从两个方面考虑，与操作系统响应的版本、与版本相关的补丁。如果版本与操作系统不一致，即便能跑起来，也会产生系统问题。另外，有些版本本身有bug，这些bug可能会造成性能问题，所以需要打上补丁。一般2.4以上的版本被视为稳定版本。案例：案例：64位机器上安装位机器上安装32位位oracle的性能问题的性能问题惠州供电局原来的数据库服务器cpu是64位，但是为了图方便，安装了32位windows 2003和32位的oracle 10g，造成只能使用1.7g左右的内存，对于8g内存的机器来说，很浪费。后来按照oracle给出的扩大内存

6、的方法进行调整，依然无效。原因就是该机器cpu是64位的。后来改装64位windows 2003和64位oracle 10g，性能提高了几倍。数据库优化数据库优化内存优化内存优化一般Oracle建议将操作系统物理内存的70%分配给它。比如，物理内存是8G，则分配给Oracle的内存就是5.6G。Oracle的内存主要分两块：SGA和PGA。按照70%的物理内存来划分，则是：SGA为60%，PGA为10%。案例：内存设置太小造成性能问题案例：内存设置太小造成性能问题有人反映，他的系统很慢，感觉主要是数据库慢。连上去看他的数据库，发现SGA只有300M，PGA只有几十M。按照70%的比例进行调整后

7、，发现数据库性能明显提高，相应的系统吞吐量也大幅提高。数据库优化数据库优化CPU优化优化这里把Oracle中的Processes归为CPU方面，其实也不准确。主要是考虑到它们和CPU的利用率有关。Processes参数表明允许同时运行的最大服务器进程数。在独占式连接模式下，一个Session就启动一个Process，你也可以认为建立一个JDBC连接就启动一个Process。案例：案例：Processes设置太小造成性能问题设置太小造成性能问题如果Weblogic中JDBC连接池需要建立的连接数大于Processes的值时，则会出现连接失败而等待。这时候增大Processes的值就会消除这种等待

8、而提高性能。数据库优化数据库优化IOIO主要是两个方面：每个用户使用自己单独的临时表空间、尽量关闭数据表空间的自动扩展功能。案例：每个用户使用自己单独的临时表空间案例：每个用户使用自己单独的临时表空间如果没有给用户设置单独的临时表空间，那么用户就会使用系统临时表空间，从而引起资源竞争。上次东莞供电局邀请第三方做性能诊断时，看到我们的用户没有建立单独的临时表空间。对于这种常识性的错误，被别人揪出来会让人怀疑我们的专业性的。案例：尽量关闭数据表空间的自动扩展功能案例：尽量关闭数据表空间的自动扩展功能在建表空间时就应该估算到表空间的大小，至少是未来一段时间的空间大小。而不要使用自动扩展，因为自动扩展

9、在实时系统上也是很影响性能的。数据库优化数据库优化数据库对象争用数据库对象争用数据库对象的争用，主要是指表、索引、序列等的争用。争用这个意思也很好理解，一个对象被引用的多了自然就存在争用，争用就会产生等待，等待就会影响性能。案例：表被关联的外键太多案例：表被关联的外键太多如果表被关联的外健太多，当多个关联表同时发生数据变更时，就可能产生等待。所以在设计上，是不是非要坚持范式设计，也是需要我们自己取舍一下。案例：表上的索引太多案例：表上的索引太多如果表上的索引很多，那么在更新表的数据时，这些索引都需要被更新。所以对于没有使用过的索引可以删除。可以通过dba_indexes视图来查询哪些索引没有使

10、用过。数据库优化数据库优化数据库对象争用数据库对象争用案例：实时表的数据太多案例：实时表的数据太多原则上来说，实时表的数据应该保持在一个比较小的规模。随着数据的增长，应该考虑历史表和实时表并存。但是很多时候，我们为了查询的方便，所有数据保存在一个表中。造成效率的低下。这种情况下，可以采取的措施有：分表：根据时间（比如：年、月或日为单位），把历史数据分成多个表；分区：根据时间或其他条件字段，对该表分区；绑定到内存：将整个表绑定到内存，对数据的检索会有一定的提高，但是对于内存要求太高。数据库优化数据库优化数据库对象争用数据库对象争用案例：合理地使用分区能极大提高查询效率案例：合理地使用分区能极大提

11、高查询效率深圳供电局生产系统中的缺陷模块，有一个视图生产系统中的缺陷模块，有一个视图V_DEFECT_FLOWCONTROL，这个查询主要是针对流程跟踪表WFT_FLOWCONTROL进行查询的。测试时，该表约有195万条记录，缺陷的记录约有17万条。在对流程跟踪表WFT_FLOWCONTROL建立分区前，这个视图的查询时间为13.29秒；对流程跟踪表的缺陷的数据建立分区后，查询时间为02.43秒，查询效率提高了约5.5倍。将缺陷的数据导入另外一个表，对该表建立相同的视图进行查询，时间为02.26秒。对比可知，建立合适的分区与分表的效率是比较接近的。数据库优化数据库优化数据库对象争用数据库对象

12、争用案例：避免字段太多的大表案例：避免字段太多的大表字段太多的表和JAVA中的大类相似，阅读困难，更主要的是会影响性能。每次Insert、Update操作针对部分字段，而我们要操作全部字段。案例：避免案例：避免Blob、Clob 字段与其他类型的字段放在一张表中字段与其他类型的字段放在一张表中对于EJB的实体bean来说，每次读写都要操作这些大字段，很影响性能，并且操作时，加锁时间也长，影响其他用户操作。案例：定长字符串应该使用案例：定长字符串应该使用char类型字段类型字段定长的char类型字段比可变的varchar2有更好的性能数据库优化数据库优化SQLSQL语句对性能的影响是显而易见的。

13、案例：使用绑定变量案例：使用绑定变量绑定变量可以减少我们相同SQL语句的解析时间，从而提高执行效率。但是通过东莞的那次性能诊断，还是看到系统中有不少地方没有使用绑定变量。案例：合理的使用索引案例：合理的使用索引索引对于在大表中检索少量数据，其效率是非常高的。但是很多时候我们对索引的使用不正确而造成效率低下。主要有以下几个方面：没有建立索引：数据量小的时候无所谓，大了就看到效率低下了；索引使用不上：建了和查询条件不匹配的索引，根本用不上。要知道，数据库不是有索引就一定会用，而且对于一张表来说，一次查询只能使用一个索引；索引统计信息没有更新：还有些情况下，由于索引的统计信息没有更新，导致数据库选择

14、不使用索引。这时候就需要更新索引状态。Oracle 10g以后默认自动更新。数据库优化数据库优化SQLSQL语句对性能的影响是显而易见的。案例：正确的使用临时表案例：正确的使用临时表正确的使用临时表也会大幅提高系统的性能。因为临时表是和会话绑定的，一个会话一个临时表，所以它不存在锁的问题，而且临时表的数据都是在内存中。有时候当查询很复杂的时候，我们可以把查询分解，把一下中间过程数据放到临时表中，然后对临时表查询，这样会大大提高性能。案例：合理的使用案例：合理的使用hintHint主要用来告诉数据库按照我们设定的执行计划来抽取数据。比如，尽管索引的统计信息没有更新，我们也可以通过Hint来使用索

15、引。但是如果数据量变少了，可能使用索引的效率还不如全表扫描，但是你使用了Hint，它还是会走索引。所以说，Hint是一把双刃剑，需要谨慎使用。案例：合理的使用视图的索引案例：合理的使用视图的索引视图使用索引，实际上是使用视图中原始表的索引。但是如果在视图定义中对原始表的索引字段定义了别名，则不会用到原始表的索引。这一点要注意。大纲大纲 操作系统优化 网络优化 数据库优化 中间件优化中间件优化 应用优化中间件优化中间件优化目前我司系统使用的中间件主要是Weblogic。中间件优化包括内存设置和参数调整。中间件优化中间件优化Weblogic建域模式建域模式Weblogic的建域模式分为开发模式和产

16、品模式，两者有些参数的设置是不同的。案例：案例：Weblogic产品模式提升性能产品模式提升性能我在进行南海EIP测试时，使用产品模式的性能要比开发模式的性能高。中间件优化中间件优化Weblogic集群集群如果应用因为负载过重而导致性能问题，应该考虑集群Weblogic日志级别设置要适当日志级别设置要适当生成日志会影响性能，尽可能少的生成日志有助于提升性能。中间件优化中间件优化Weblogic内存设置内存设置Weblogic的内存调整就是调整Java虚拟机的内存。32位系统上JVM的可配置的最大内存在1.5G左右。64位则不受此限制。通常情况在设置普通堆的大小时，为了避免扩展带来的性能问题，建

17、议普通堆的最小值和最大值相同。但是一个非常重要的例外是IBM的JVM则要求两个不能相同，否则会出现垃圾回收方面的性能问题。案例：设置案例：设置IBM的的JVM普通堆的最小值和最大值不同普通堆的最小值和最大值不同海南局的EIP是基于IBM 的JVM，开始的时候基于以前的习惯将普通堆的最小值和最大值设置相同(-Xms4096m Xmx4096m)，后来根据网上同行的意见设置为(-Xms1024m Xmx4096m)。中间件优化中间件优化Weblogic参数调整参数调整案例：合理调整案例：合理调整Execute Queue and Threads Count值值执行队列可以理解为一个排队的任务群。每

18、当客户端发出一个任务请求，就会被放到执行队列中。执行队列的线程数的意思是同时启动多少个线程来执行队列中的任务。如果队列中的任务数大于线程数，那就表明有的任务需要等待。所以线程数的最大值应该和系统的最大并发数一致，相应的，最小值应该和系统的最小并发量一致。如果是WebLogic 9以上版本，线程数由系统自己分配，无需配置。中间件优化中间件优化Weblogic参数调整参数调整案例：案例：合理调整合理调整Accept Backlog值值Accept Backlog参数表明Weblogic向操作系统请求的队列大小。客户端连接weblogic服务器时，在服务器上就监听到一个客户端的连接请求。但是管理客户

19、端连接请求的任务是由操作系统来完成的，而非Weblogic。操作系统把这些连接请求存储在一个先进先出的队列中。当队列中的连接请求达到了队列的最大数时，Weblogic服务器所在的主机操作系统会拒绝新的连接请求。只有当队列中的连接请求和Weblogic成功建立连接后，才会使队列腾出空位，这时队列才能继续加入新的连接请求。所以，当客户端收到类似connection refused的信息，就表明队列已经满了，这时候可以调大Accept Backlog的值。目前在部署的过程中，一般都设置为100。中间件优化中间件优化Weblogic参数调整参数调整案例：案例：合理的调整合理的调整JDBC连接数连接数这

20、里主要是指JDBC连接池中的连接数。当操作数据库的事务并发量很大时，需要调大JDBC连接数的最大值，否则会报得不到JDBC连接的问题。一般最大值的设置不应该超过执行队列中线程最大值的设置，更不要超过数据库中的Processes值。中间件优化中间件优化Weblogic参数调整参数调整案例：案例：合理的调整合理的调整JTA的事务超时时间的事务超时时间JTA就是Java事务管理接口，主要用来管理EJB的事务。我们通常关注的就是事务的超时时间。因为有些事务比较大，如果超时时间设置过短，会造成事务处理失败并回滚。中间件优化中间件优化Weblogic参数调整参数调整案例：使用案例：使用Native IO服

21、务器启动，接受到连接请求并建立好连接以后，监听线程将处理权交给Socket复用器，放置到执行队列中。当有一个请求进入执行队列，就会有一个空闲的执行线程从该队列里面取走并向调用者返回结果。Weblogic带有两个版本的Socket复用器，纯Java版和本地接口版（JNI）。访问量小：Java版性能要好一些。JNI的API调用会损失一部分性能。访问量大：JNI版的性能比较好，所以尽量保证Weblogic使用本地接口版的Socket复用器。大纲大纲 操作系统优化 网络优化 数据库优化 中间件优化 应用优化应用优化应用优化应用优化EJB、大数据量读、DAO中的SQL语句、常用数据的缓存、特定结构、Ja

22、va代码编写、JSP编写应用优化应用优化EJB优化优化案例：案例：使用容器管理的持久性使用容器管理的持久性CMP使用CMP不仅可以减少代码的编写量，而且可以允许容器进行很多优化，包括数据库访问代码的优化。案例：遵循粗粒度设计原则案例：遵循粗粒度设计原则由于调用EJB的方法都属于远程调用，对于大多数细粒度的对象来说调用中间件的开销大得简直让人无法承受。案例：减少案例：减少JNDI的查找次数的查找次数由于各种应用服务器的命名和目录服务在具体实现方法上的差异，JNDI的查找过程比较费时。JNDI对EJB资源，如:数据源、bean引用，乃至环境项的查找可能花费巨大，且避免重复查找并非易事。在这种情况下

23、可以利用缓存home的方法来提升性能。应用优化应用优化DAO中的中的SQL语句语句案例：三层嵌套的分页查询案例：三层嵌套的分页查询SQL语句更高效语句更高效每次只需要一页数据，所以从数据库中也只需要查询一页的数据，特别是对于大数据量的表。原来把所有数据全取得后，再显示一页数据的性能较低。应用优化应用优化DAO与与EJB案例：对只读数据采用案例：对只读数据采用DAO直接读取直接读取通过DAO直接访问那些更新不是很频繁的数据，从而避免了使用EJB所带来的负担，加快数据的访问。特别是取多条记录时，使用DAO读取更快并且消耗资源少。应用优化应用优化缓存缓存案例：加入缓存机制，可极大的减少案例：加入缓存机制，可极大的减少字典表和常用表字典表和常用表的访问次数的访问次数字典表、查询字段对应表、用户表、部门表、角色表都是访问频率很高的表。加入缓存，减少访问次数。应用优化应用优化特定操作的结构优化特定操作的结构优化案例：审批操作时要关联对象授权和工作流表案例：审批操作时要关联对象授权和工作流表三张或更多张表的关联，性能下除的很快。对于特定操作的结构优化，可加速局部的响应速度。工作流表：可分成将要处理数据和