[SQL]数据库系统的维护与优化(二)

二、数据库系统的性能监视与优化

1.、性能监视:

       1)增长

       测量并预测增长,需要收集四种主要的信息:处理器,网络,存储,内存。

      

对像类型

 

要收集的值

Processor

 

使用率百分比

Network

 

流量,总字节数

Storage

 

总传输数,以操作或块为单位

Memory

 

使用中的MB数或GB

Database

 

每个数据库的大小

 

       如果没有以前管理人员留下来的数据库性能基准线,则可以自己制做自己的数据库性能监视基准线。

       方法:

       以使用一个较小的时间间隔(5-10)进行一周,每天24小时,在之后24小时的测量改为每周一次,持续一个月,最后每月一次,并持续两个月。

有了这些数字之后,首先检查每天的范围。按照时间排列这些数字并为它们创建一个图表。这些数字将显示一种趋势,如果数字显示出一个增长趋势的

可预测模式,则要注意周测试的最后一次测量,并根据它预测后三个星期的增长模式。如果预测正确,它们应该与你实际采集的周数据接近。采用相同

的方法对月度数据进行测试。如果度量值初始显示出一个平稳的曲线,你应当进行更长时间的测试,每周一次,至少进行三个月以上。如果测量值呈下

降趋势,则延迟一周后再进行每天测量。

 

2、活动和性能

      

对像类型

 

要收集的值

Processor

 

使用率百分比,特别是应用程序相关的进程

Network

 

读和写的字节数

Storage

 

读和写操作

Memory

 

SQL server需要使用的值,以及SQL server正在使用的值

Database

 

数据库活动,连接和锁

       得到这些值之后,按时间顺序组织它们,计算出最小值,最大值和平均值。如果使得平均有意义,还要计算出集合的标准差,标准差越接近0,平均值越可信。

 

3、性能监视

对像

计数器

含义

说明

Processor

%Processor Time

显示在监视时间内处理器的使用百分比。

平均低于75%(低于50%更佳)

 

Memory

Available Mbytes

显示还剩多少内存。从总内存数量中减去这个值就可以算出正在使用的数量

应该保持在50MB

 

 

Page/sec 

 

平均低于20(低于15更佳)

 

LogicalDisk   

%Disk Time

显示在监视时间内的读,写百分比

 

Network Interface

Bytes Total/sec

显示发送和接收的字节数

用于规划网络带宽的大小

 

SQL Server:Databases

Active Transactions

       显示实例范围内数据库的所有活动事务

 

 

SQLServer:General      

User Connections      

显示被统计的连接到服务器的用户数量                        

用于规划内存

 

                                                                                                                                                                                                                                                                  

       我通常会使用系统监视器的日志功能,并把结果发送一个制表符分隔的文件。如果只监视一天,我会把收集时间间隔设置为5秒一次,如果要监视更长的时间

,则设置为5分钟一次。

       然后我创建了一个三张表的Excel文件:监视,评估,建议。我会把系统监视器的结果读到“监视”表中,然后更改列的格式,例如去掉服务器名称。对于处理

器内存,磁盘,网络和Sql server计数器,我会使用不同的颜色标记。

       在“评估”表中我会创建引用“监视”表中数据的公式,这些公式会计算出最大值,最小值,平均值,标准偏差等。然后我会对这些列中的数据进行评估并使用

不同的颜色标注,例如红色表示非常正常,绿色表示好,蓝色表示差。在这些数字下面我会解释为什么觉得它好或是不好,以及其他影响它的因素。我还会对测试环

境进行简单描述。

       在“建议”表中我会解释使用什么方法来解决问题。例如如果连接数以某种频率增长,并且同时内存使用量多于服务器上安装的数量,那么服务器正在进行换页

操作,那么它需要更多内存。

 

 

       磁盘增长方式

线性增长:

       未来使用率=当前使用率+(增长数量*周期个数)

       例如:如果数据库当前每分钟处理40个事务,并且每年每分钟增长10个事务,就可以通过这些数值放置到公式中计算出未来3年内数据库使用的情况

       未来3年内使用率=40+(10*3)

 

几何增长:

       未来使用率=当前使用率+(+增长率)^周期个数

       例如:如果数据库当前是600GB,并且增长率每个月为2%,则未来三年内数据库大小=600(1+0.02)^36

 

4、磁盘系统的优化

 

 

对于磁盘子系统规划监视

对像

计数器

含义

说明

System

Processor Queue      

每个处理器平均低于2

例如,在一个双处理器的机器上,应该保持在低于4的状态

Physical Disk

%Disk Time  

显示在监视时间内的读,写百分比   

 

平均低于50%

 

Avg.Disk QueueLength       

平均每个磁盘应该低于2

例如,对于一个5磁盘的阵列,此值应该低于10

 

      

Avg.Disk Reads/sec

用户规划磁盘和CPU

该低于磁盘容量的85%

 

 

vg.Disk Writes/sec

用户规划磁盘和CPU

应该低于磁盘容量的85%

SQL Server:Buffer Manager

Buffer Cache HitrATIO

应该超过90%(理想状态下接近99%          

 

      

Page Life Expectancy    

用于规划内存(应该保持在300秒以上)

                    

用于规划内存

 

 

Transactions/sec

用户规划磁盘和CPU       

 

 

 

Data Files Size KB

用于规划磁盘子系统

 

 

 

Percent Log Used

用于规划磁盘子系统

 

 

 

 

 

 

 

 假设:数据库的工作负载在峰值时段内为每秒600个读事务,200个写事务, 每个磁盘的标准是300IOPS及最大255 IOPS

 1.使用RAID1R的配置中,写事务的数量是双倍,因此它将此吞吐量的级别调整为600个读事务,400个写事务.或者1000 IOPS.如果这些I/O负载分摊到两个磁盘,

那么每个磁盘就是500IOPS,远远超出了每个磁盘的标准值.

所以RAID1不满足这个数据库的负载.

 

 2.RAID 5配置中,由于增加了4倍的写事务个数,所以提高了总体的I/O.由此I/O吞吐量级别上升到了每秒1400(600+200*4).不像RAID1,RAID5配置固定磁盘个数,

所以在RAID5 配置中,你可以使用多个磁盘来分担吞吐量,以符合每个磁盘最大255IOPS的负载.

       计算出磁盘数量:1400/255=5.49.由于5.49不是一个整数值,所以应该将其上升到下一个整数值,所以应该是6.

 3.RAID 10配置中,RAID1配置一样的写事务的数量翻一倍.

       计算出磁盘数量:1000/255=3.9 .所以最少要4个物理磁盘才能满足.

 

4.内存:

 并发是指300秒持续时间内的执行数量.

 1.连接上下文指的是需要支持用户连接的数据库结构.连接到sql server的每个用户大约需要500KB.确定并发用户的最大数量之后,500*最大并发连接数,就可以确定用户的内在需要.

 2.存储过程缓冲内存:

    1) 首先收集在数据库应用程序中包含所有的查询类型.然后计算每个查询文本在内在中需要的存储空间数量.请记住,执行此计算时,文本中每个字符都是一个字节.

 :

 如果一个特定的存储过程中包含了4000个字符,则此查询的文本在内存中大概占据4KB.

     2) 对每个查询评估最大的并发执行数量.

     :

       你可能估算出相同的存储过程的并发执行的最大的数量为500.则需要的缓存为4*501 (一个用于共享的查询计划,500用于执行上下文)2004KB.

 3.缓冲区内存🙁这个是最大的内存用户)

    :

       查询A:30个用户并发执行,每个查询有400KB数据输出, 120MB

       查询B:20个用户并发执行,每个查询有300KB数据输出,60MB

       查询C: 50个用户并发执行,每个查询有100KB数据输出,50MB

       三个种查询总共需要230MB内存,做为缓冲区.

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏