数据库性能分析及调整一例

原创 2004年09月23日 15:39:00

故障现象

2004年6月8日上午10:00,内蒙古巴盟网通用户反映在OSS系统界面“话单查询”里查询单个用户五天的话单特别慢,查询很长时间无结果。

例如:在OSS系统界面“综合查询”内点击“收费”-〉“话单查询”,键入“用户号码,起始时间:2004-01-01 000000,结束时间:2004-06-01 230000”,点击查询后,IE进度条缓慢,很长时间不返回结果。

故障分析

经过分析,此现象和数据库的性能有关,主要是由于数据库初始化参数调整的不合理造成的性能低下。具体分析过程如下:

1.首先查询话单表的索引是否失效:

SQL>select INDEX_NAME,status from USER_IND_PARTITIONS where status!='USABLE';

no rows selected.

结果说明没有失效的话单表索引。

 

2.用top命令看到可用物理内存很低,只剩下100M,有大量的SWAP内存在使用,ORACLE单个会话占用的内存很多,经查看ORACLE初始化参数shared_pool_size的值设置的过高,应重新调整。

top的结果:
last pid:  4565;  load averages:  0.15,  0.20,  0.20   
10:09:56

170 processes: 169 sleeping, 1 on cpu

CPU states: 84.9% idle,  1.6% user,  1.1% kernel, 12.4% iowait,  0.0% swap

Memory: 4096M real, 100M free, 1343M swap in use, 6851M swap free

 

  PID USERNAME THR PRI NICE  SIZE   RES STATE   TIME    CPU COMMAND

10459 oracle     1  59    0 1978M 1953M sleep   0:53  0.79% oracle

 2258 oracle     1  10    0 1976M 1951M sleep 116:57  0.65% oracle

25639 oracle     1  58    0 1975M 1949M sleep   1:56  0.27% oracle

 1948 oracle     1  58    0 1976M 1948M sleep   3:34  0.18% oracle

 4002 wacos      6  47    4 9616K 2344K sleep  27:26  0.18% cdr_backup

 2271 oracle     1  59    0 1975M 1947M sleep  15:13  0.16% oracle

 1958 oracle     1  48    0 1976M 1949M sleep   2:26  0.13% oracle

 1928 oracle     1  58    0 1976M 1951M sleep   4:28  0.12% oracle

 1926 oracle     1  58    0 1976M 1949M sleep   2:06  0.12% oracle

 1956 oracle     1  58    0 1976M 1949M sleep   2:23  0.11% oracle

 1952 oracle     1  59    0 1976M 1949M sleep   2:19  0.10% oracle

  403 root      10  21    0 4896K 4608K sleep  16:32  0.09% picld

 1954 oracle     1  48    0 1976M 1949M sleep   2:04  0.08% oracle

 2189 oracle     1  58    0 1976M 1949M sleep  15:51  0.08% oracle

 

3.为了进一步分析ORACLE的性能,用ORACLE自带的诊断工具statspack做性能快照分析,统计时段为1小时,时间从下午17:00-18:00之间。这段时间业务比较繁忙,选择在此时段内对整个系统进行性能分析,能够得到更加准确的信息。

安装statspack性能分析工具:

SQL>connect internal

SQL>alter system set timed_statistics=true;(收集操作系统的计时信息)

SQL>@?/rdbms/admin/spcreate.sql

SQL>execute statspack.snap   (17:00的时候运行一次)

SQL>execute statspack.snap   (18:00的时候运行一次)

SQL>@?/rdbms/admin/spreport  (产生性能分析报告)

 

截取报告的部分内容如下:

STATSPACK report for

 

DB Name         DB Id    Instance     Inst Num Release     OPS Host

------------ ----------- ------------ -------- ----------- --- ------------

ORCL          1000277484 ORCL                1 8.1.7.3.0   NO  bm_db1

 

                Snap Id     Snap Time      Sessions

                ------- ------------------ --------

 Begin Snap:                             1 08-Jun-04 17:00:15      116

   End Snap:                             2 08-Jun-04 18:00:40      116

    Elapsed:                              60.42 (mins)

 

Cache Sizes

~~~~~~~~~~~

           db_block_buffers:     180000          log_buffer:

   8192000

              db_block_size:       8192    shared_pool_size:

 314572800

 

Load Profile

~~~~~~~~~~~~                            Per Second       Per Transaction

                                   ---------------       ---------------

                  Redo size:             11,005.01              2,280.39

              Logical reads:             65,704.21             13,614.83

              Block changes:                 67.96                 14.08

             Physical reads:              1,392.89                288.63

            Physical writes:                 11.61                  2.40

                 User calls:                172.63                 35.77

                     Parses:                 29.11                  6.03

                Hard parses:                  0.01                  0.00

                      Sorts:                  7.81                  1.62

                     Logons:                  0.14                  0.03

                   Executes:                101.44                 21.02

               Transactions:                  4.83

 

  % Blocks changed per Read:    0.10    Recursive Call %:               41.29

 Rollback per transaction %:    0.28       Rows per Sort:               25.55

 

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

            Buffer Nowait %:  100.00       Redo NoWait %:              100.00

            Buffer  Hit   %:   97.88    In-memory Sort %:              100.00

            Library Hit   %:   99.98        Soft Parse %:               99.96

         Execute to Parse %:   71.30         Latch Hit %:               99.99

Parse CPU to Parse Elapsd %:   62.24     % Non-Parse CPU:               99.99

 

 Shared Pool Statistics        Begin   End

                               ------  ------

             Memory Usage %:   24.15   24.44

    % SQL with executions>1:   75.04   76.95

  % Memory for SQL w/exec>1:   75.49   79.90

 

Top 5 Wait Events

~~~~~~~~~~~~~~~~~                                             Wait     % Total

Event                                               Waits  Time (cs)   Wt Time

-------------------------------------------- ------------ ------------ -------

db file sequential read                         5,030,075      389,071   86.37

log file sync                                      17,470       21,187    4.70

log file parallel write                            17,640       18,611    4.13

db file parallel write                              1,853       14,930    3.31

db file scattered read                              3,149        2,297     .51

 

对报告分析后发现有一些不合理的初始化参数需要调整,建议如下调整:

1. 报告中发现全表扫描的语句特别多因此建议程序中尽量避免使用全表扫描

减少IO等待,从而加快语句的执行速度。

类似如下语句需要优化:

SQL>select count(*) as totalcount from LOCALUSAGE where se

rviceid=:"SYS_B_0" and starttime>=to_date(:"SYS_B_1",:"SYS_B_2")

 and starttime <=to_date(:"SYS_B_3",:"SYS_B_4") and ( LOCALROAMI

NGCHARGE >:"SYS_B_5"  or LocalCharge >:"SYS_B_6"  or UrbanCharge

 >:"SYS_B_7" or ruralcharge >:"SYS_B_8");

 

2.调整db_file_multiblock_read_count=16

这个参数指定一个完全连续扫描的一次I/O操作过程中读取的块的最大数量。它的增加对IO是有改善的,特别是在做full table scan的时候,可以减少IO的次数。

 

3.调整db_block_lru_latches=2

这个参数指定LRU 闩锁集数量的上限。LRU锁的数量是在Oracle数据库内部用来管理数据库缓冲的,它严重依赖于服务器上CPU的数量,这个值通常设置为服务器上cpu_count的一半,增大这个值有利于提高磁盘的I/O性能。

 

4.调整session_cached_cursors=200

这个参数指定要高速缓存的会话游标的数量,对同一SQL语句进行多次语法分析后,它的会话游标将被移到该会话的游标高速缓存中。增大这个值可以缩短语法分析的时间,因为游标被高速缓存,无需被重新打开。

 

5.调整log_buffer=1048576

参数log_buffer指定在 LGWR 将重做日志缓冲区里的内容写入重做日志文件之前,用于缓存这些条目的内存量。这个参数以字节为单位,同时受cpu_count的影响, log_buffer如果被设置得太高(例如,大于1MB),这会引起性能问题,因为大容量的结果会使得写入同步进行(例如,日志同步等待事件非常高)。

 

6.调整db_block_buffers = 200000  shared_pool_size= 262144000

按照杭州的规划,Oracle最终运行起来占用近1/2的物理内存。其中最主要的两个参数为:

db_block_buffers:它的配置原则是,最终数据块缓存占据1/3的内存。

Shared_pool_size:它的配置原则是,基本控制在200-500M左右。

 

7.从报告中发现系统等待最严重的五个事件为:db file sequential read,log file sync,log file parallel write,db file parallel write和db file scattered read.

(1)对于db file sequential read等待事件,一般问题出现在读索引上,建议将wacos表空间和wacos索引表空间分开存储在不同的物理卷下,以提高磁盘的I/O性能。

(2)对于db file scattered read等待事件,建议程序中尽量避免使用全表扫描的语句,或者可以增大db_file_multiblock_read_count的值,提高全表扫描一次读取数据块的速度,减少磁盘I/O。

(3)对于db file parallel write等待事件,说明DBWR进程正等待把缓冲区的内容并行写入数据文件中去,等待将一直持续到所有的I/O全部完成。建议增大初始化参数中的db_writer_processes的值,可以增大到4
(4)
对于log file sync等待事件,说明任何时候一个事物提交时,它将通知LGWRLOG_BUFFER写入日志文件,如果此部分占用时间较长,应减少COMMIT的次数,建议将重做日志放到较快的磁盘上进行存储。
(5)
对于log file parallel write等待事件,和上面一样建议将重做日志放到较快的磁盘上进行存储。   

故障处理
调整initORCL.ora里不合理的参数,具体调整为:

process=200

log_buffer=1048576

session_cached_cursors=200 

db_block_lru_latches=2

shared_pool_size= 262144000

db_block_buffers = 200000

sort_area_size = 6553600

sort_area_retained_size = 6553600

db_file_multiblock_read_count = 16

 

处理结果
调整完重启DB后,发现查询一切正常,很快就返回了结果。

总结

数据库里初始化参数设置不合理, 内存富余太少, 导致数据库运行使用大量的swap空间,数据库性能很差,导致通过OSS界面查询话单很慢。这时需要通过调整数据库初始化参数解决该问题。从性能方面考虑,数据库服务器最好能富余300500M以上的内存。
版权声明:本文为博主原创文章,未经博主允许不得转载。

nginx性能改进一例

本文研究的是在海量静态文件下,如何提高nginx的处理能力。在静态文件数量不是很多的情况下,nginx处理静态文件的能力极快,但是在海量的静态文件下,nginx的处理能力就不那么乐观,怎么提高ngin...
  • sd4015700
  • sd4015700
  • 2014年11月13日 17:58
  • 444

数据库性能优化详解

一、数据库访问优化法则简介 要正确的优化SQL,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在哪里?而大多数情况性能最慢的设备会是瓶颈点,如下载时网络速度可能会是瓶颈点,本...
  • he90227
  • he90227
  • 2014年07月23日 13:21
  • 4037

mysql监控和性能分析工具

mysql监控和性能分析工具 Mysql作为广泛应用的数据库系统,平时运维工作中对她的监控必不可少,现在把我对Mysql数据库的监控体会写成下文,欢迎拍砖。 无论是DBA或是SA,监控的目标都...
  • k_scott
  • k_scott
  • 2013年03月16日 12:53
  • 8951

Oracle数据库的性能调整

原文链接:http://www.cnblogs.com/rootq/archive/2008/11/17/1335222.html 感谢作者:四月天 oracle是一个高性能数据库软件。用...
  • benglehan
  • benglehan
  • 2014年03月18日 14:01
  • 351

数据库性能需求分析及评估模型

数据库作为应用系统当中最重要的一块,也是性能测试非常关注的一块,根据我自己的项目经验,和以往对应用系统的性能需求分析和测试策略制定过程,总结一下如何开展数据库系统的性能需求分析,以及制定数据库能力评估...
  • smooth00
  • smooth00
  • 2017年11月22日 17:05
  • 395

mysql性能分析

mysql> show global status;   可以列出MySQL服务器运行各种状态值,另外,查询MySQL服务器配置信息语句:   mysql> show variables;   一、慢...
  • liunian02050328
  • liunian02050328
  • 2016年09月23日 16:24
  • 945

数据库调优教程(四)Explain性能分析命令

在发现慢查询后,要怎样才能知道语句的问题发生在哪里。本章将介绍怎样使用Mysql提供的数据库性能分析命令,对sql语句进行分析。...
  • hzy38324
  • hzy38324
  • 2015年04月07日 15:44
  • 2415

性能测试工具操作数据库(三)-Jmeter与Mysql

性能测试工具操作数据库连载三,本章节以jmeter操作mysql为例,可以实现jmeter批量插入、更新、查询、删除mysql数据库,以及说明注意事项,对初学者绝对有帮助。...
  • smooth00
  • smooth00
  • 2017年03月28日 09:57
  • 6020

数据库的架构设计与性能优化

本文首发于阿里云&《程序员》杂志联合出品的《凌云》专刊中。 作者: 杭州湖畔网络技术经理 王鑫鹏 杭州湖畔网络技术有限公司是一家专业提供SaaS化电商ERP服务的创业公司,主要用户群体为经...
  • liu870915
  • liu870915
  • 2016年07月05日 15:07
  • 1326

oracle 性能调优之数据库中的常见性能问题

oracle 性能调优之数据库中的常见性能问题 转自:http://blog.csdn.net/huzia/article/details/8807383     在Oracle数据库中找...
  • yongjiao124
  • yongjiao124
  • 2016年04月05日 19:36
  • 2356
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据库性能分析及调整一例
举报原因:
原因补充:

(最多只允许输入30个字)