一. 概述

 sql server作为关系型数据库,需要进行数据存储,
那在运行中就会不断的与硬盘进行读写交互。如果读写不能正确快速的完成,就会出现性能问题以及数据库损坏问题。下面讲讲引起I/O的产生,以及分析优化。

Zookeeper的I/O模式?

Zookeeper在启动过程中有一步很重要:初始化ServerCnxnFactory
其中的实现类有NettyServerCnxnFactory和NIOServerCnxnFactory

一.概述 

  与网络I/O相关的等待的主要是ASYNC_NETWORK_IO,是指当sql
server返回数据结果集给客户端的时候,会先将结果集填充到输出缓存里(ouput
cache),同时网络层会开始将输出缓存里的数据打包,由客户端接收。如果客户端接收数据包慢,sql
server没有地方存放新数据结果时,这时任务进入ASYNC_NETWORK_IO等待状态。

  1. 从实例级别查看ASYNC_NETWORK_IO

   图片 1

   平均耗时: 46366950.0/43014737.0=1.077ms, 最大等待时间:~40秒。

  2. 重现ASYNC_NETWORK_IO等待

     为了演示ASYNC_NETWORK_IO
现象,我们需要输出一个大结果集。当sql
server内存完全被使用后,大量的数据填充到缓存里,此时sql
server没有地方存放新数据结果,进入等待状态。

-- 一次查询100000条数据输出到客户端
SELECT TOP 100000 * FROM PUB_Stock WITH(nolock)

  监听到的会话如下:

  图片 2

  使用dbcc inputbuffer 查询64结果如下:

    图片 3

  3.分析与解决

    这个等待出现的问题强调以下几点:

    (1) 客户端没有把数据及时取走,调整sqlserver
的配置一般情况下是不是有什么大的帮助。

    (2) 网络层可能是问题的原因。 
解决:1是减少对客户端大量数据输出。 2是加大sqlserver 的network packe
size,从一定程度上优化网络转输的性能,但会增加内存的开销(建议小于设置小于8kb)。

    network packe
size是客户端与sqlserver通信的每个数据包大小有关系。network packe
size设置的数据包存放于内存功能组件的connection类别里。默认是4kb设置,输入输出缓存会放在buffer
pool里,如果改成了8kb 或更大,输入输出缓存会放在multi-page里
关于内存可查看sql server
内存初探。 设置network
packe size 可以由sp_configure控制。客户端应用程序可以覆盖此值如在.net
里配置如下。

Data Source=(local);Initial Catalog=AdventureWorks;"Integrated Security=SSPI;Packet Size=512

    演示将 net work packe size设置成6050字节

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'network packet size', 6500 ;  
GO  
RECONFIGURE;  
GO 

   也可以能过界面来配置

  图片 4

    (3) 应用程序端性能问题,也会导致sql
server里的ASYNC_NETWORK_IO等待。

      sqlserver
的网络层将结果集打包好发向客户端以后,要等到客户端确认收到,才会接着发下一个包。

    (4) 分布式锁

      如果长时间看到ASYNC_NETWORK_IO,同时在sqlserver内部又造成了阻塞,并且该等待持续了很久,就该怀疑是否是分布式的死锁。

  总结:当遇到ASYNC_NETWORK_IO等待,需要检查应用程序自己的健康状况,也要检查应用是否有必要向sql
server 申请这么大的结果集返回,一般来讲sqlserver 本身没有什么问题。

一.概述

  IO 内存是sql
server最重要的资源,数据从磁盘加载到内存,再从内存中缓存,输出到应用端,在sql
server
内存初探中有介绍。在明白了sqlserver内存原理后,就能更好的分析I/O开销,从而提升数据库的整体性能。
在生产环境下数据库的sqlserver服务启动后一个星期,就可以通过dmv来分析优化。在I/O分析这块可以从物理I/O和内存I/O二方面来分析,
重点分析应在内存I/O上,可能从多个维度来分析,比如从sql
server服务启动以来
历史I/O开销总量分析,自执行计划编译以来执行次数总量分析,平均I/0次数分析等。

  sys.dm_exec_query_stats:返回缓存的查询计划,缓存计划中的每个查询语句在该视图中对应一行。当sql
server工作负载过重时,该dmv也有可以统计不正确。如果sql
server服务重启缓存的数据将会清掉。这个dmv包括了太多的信息像内存扫描数,内存空间数,cpu耗时等,具体查看msdn文档。

  sys.dm_exec_sql_text:返回的 SQL
文本批处理,它是由指定sql_handle,其中的text列是查询的文本。

1.1 按照物理读的页面数排序 前50名

SELECT TOP 50
 qs.total_physical_reads,qs.execution_count,
 qs.total_physical_reads/qs.execution_count AS [avg I/O],
 qs. creation_time,
 qs.max_elapsed_time,
 qs.min_elapsed_time,
 SUBSTRING(qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset=-1
 THEN LEN(CONVERT(NVARCHAR(max),qt.text))*2
 ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) AS query_text,
 qt.dbid,dbname=DB_NAME(qt.dbid),
 qt.objectid,
 qs.sql_handle,
 qs.plan_handle
 from sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 ORDER BY qs.total_physical_reads DESC

  如下图所示:

  total_physical_reads:计划自编译后在执行期间所执行的物理读取总次数。

  execution_count :计划自上次编译以来所执行的次数。

  [avg I/O]:    平均读取的物理次数(页数)。

  creation_time:编译计划的时间。 

        query_text:执行计划对应的sql脚本

       后面来包括所在的数据库ID:dbid,数据库名称:dbname

图片 5

 1.2 按照逻辑读的页面数排序 前50名

SELECT TOP 50
 qs.total_logical_reads,
 qs.execution_count,
  qs.max_elapsed_time,
 qs.min_elapsed_time,
 qs.total_logical_reads/qs.execution_count AS [AVG IO],
 SUBSTRING(qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset=-1 
 THEN LEN(CONVERT(NVARCHAR(max),qt.text)) *2
  ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) 
  AS query_text,
 qt.dbid,
 dbname=DB_NAME(qt.dbid),
 qt.objectid,
 qs.sql_handle,
  creation_time,
 qs.plan_handle
 from sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 ORDER BY qs.total_logical_reads DESC

如下图所示:

图片 6

  通过上面的逻辑内存截图来简要分析下:

  从内存扫描总量上看最多的是8311268次页扫描,自执行编译后运行t-sql脚本358次,这里的耗时是毫秒为单位包括最大耗时和最小耗时,平均I/O是23215次(页),该语句文本是一个update
修改,该表数据量大没有完全走索引(权衡后不对该语句做索引覆盖),但执行次数少,且每次执行时间是非工作时间,虽然扫描开销大,但没有影响白天客户使用。

  从执行次数是有一个43188次, 内存扫描总量排名39位。该语句虽然只有815条,但执行次数很多,如里服务器有压力可以优化,一般是该语句没有走索引。把文本拿出来如下

SELECT  Count(*)  AS TotalCount FROM [MEM_FlagshipApply]
 WITH(NOLOCK) Where (((([Status] = 2) AND ([IsDeleted] = 1)) AND ([MemType] = 0)) AND ([MEMID] <> 6))

下面两图一个是分析该语句的执行计划,sqlserver提示缺少索引,另一个是i/o统计扫描了80次。

图片 7

图片 8

 新建索引后在来看看

 CREATE NONCLUSTERED INDEX ix_1
ON [dbo].[MEM_FlagshipApply] ([Status],[IsDeleted],[MemType],[MEMID])

  图片 9

   
  图片 10

 

Cacti是我常用的监控网络软件,有时候我拿来监控我的硬盘I/O
,来看看具体是怎么做的!

二.sql server  主要磁盘读写的行为

  2.1 
从数据文件(.mdf)里, 读入新数据页到内存。前页讲述内存时我们知道,如果想要的数据不在内存中时,就会从硬盘的数据文件里以页面为最小单位,读取到内存中,还包括预读的数据。
当内存中存在,就不会去磁盘读取数据。足够的内存可以最小化磁盘I/O,因为磁盘的速度远慢于内存。

  2.2  预写日志系统(WAL),向日志文件(.ldf)写入增删改的日志记录。
用来维护数据事务的ACID。

  2.3  Checkpoint 检查点发生时,将脏页数据写入到数据文件
,在sp_configure的recovery interval 控制着sql
server多长时间进行一次Checkpoint,
如果经常做Checkpoint,那每次产生的硬盘写就不会太多,对硬盘冲击不会太大。如果隔长时间一次Checkpoint,不做Checkpoint时性能可能会比较快,但累积了大量的修改,可能要产生大量的写,这时性能会受影响。在绝大多数据情况下,默认设置是比较好的,没必要去修改。

  2.4   内存不足时,Lazy
Write发生,会将缓冲区中修改过的数据页面同步到硬盘的数据文件中。由于内存的空间不足触发了Lazy
Write, 主动将内存中很久没有使用过的数据页和执行计划清空。Lazy
Write一般不被经常调用。

  2.5   CheckDB, 
索引维护,全文索引,统计信息,备份数据,高可用同步日志等。

如何配置?

QuorumPeerConfig#parseProperties中,可以看到,直接设置serverCnxnFactory的classname就行。

二. 其它网络I/O等待

  这里还有其它几个NET_WAITFOR_PACKET,PROXY_NETWORK_IO,EXTERNAL_SCRIPT_NETWORK_IOF。
  2.1 NET_WAITFOR_PACKET: 在msdn中解释是
网络读取过程中,连接正在等待网络数据包时出现。

    实际级等待如下图所示:
    图片 11
  
2.2
后面二个proxy_network_io,external_script_network_iof。在生产环境下没有数据。在msdn中也没有找到相应解释。只能通过字面含义去解释。

cacti监控硬盘I/O的方法:

三. 磁盘读写的相关分析

  3.1 sys.dm_io_virtual_file_stats  获取数据文件和日志文件的I/O
统计信息。该函数从sql server
2008开始,替换动态管理视图fn_virtualfilestats函数。
哪些文件经常要做读num_of_reads,哪些经常要做写num_of_writes,哪些读写经常要等待io_stall_*。为了获取有意义的数据,需要在短时间内对这些数据进行快照,然后将它们同基线数据相比较。

SELECT  DB_NAME(database_id) AS 'Database Name',
        file_id,
        io_stall_read_ms / num_of_reads AS 'Avg Read Transfer/ms',
        io_stall_write_ms / num_of_writes AS 'Avg Write Transfer/ms'
FROM    sys.dm_io_virtual_file_stats(null, null)
WHERE   num_of_reads > 0 AND num_of_writes > 0 

  io_stall_read_ms:用户等待文件,发出读取所用的总时间(毫秒)。

  io_stall_write: 用户等待在该文件中完成写入所用的总时间毫秒。

  图片 12

  3.2  windows 性能计数器:  Avg. Disk Sec/Read
这个计数器是指每秒从磁盘读取数据的平均值

< 10 ms – 非常好
 10 ~ 20 ms 之间- 还可以
 20 ~50 ms 之间- 慢,需要关注
> 50 ms –严重的 I/O 瓶颈

  3.4  I/O  物理内存读取次数最多的前50条

 SELECT TOP 50
 qs.total_physical_reads,qs.execution_count,
 qs.total_physical_reads/qs.execution_count AS [avg I/O],
 qs. creation_time,
 qs.max_elapsed_time,
 qs.min_elapsed_time,
 SUBSTRING(qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset=-1
 THEN LEN(CONVERT(NVARCHAR(max),qt.text))*2
 ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) AS query_text,
 qt.dbid,dbname=DB_NAME(qt.dbid),
 qt.objectid,
 qs.sql_handle,
 qs.plan_handle
 from sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 ORDER BY qs.total_physical_reads DESC

 3.5 使用sp_spaceused查看表的磁盘空间

  exec sp_spaceused 'table_xx'

图片 13

reserved:保留的空间总量
data:数据使用的空间总量
index_size:索引使用空间
Unused: 未用的空间量

 3.6  监测I/0运行状态 STATISTICS IO ON;

**Nio? **

public void run() {
        while (!ss.socket().isClosed()) {
            try {
                selector.select(1000);
                Set<SelectionKey> selected;
                synchronized (this) {
                    selected = selector.selectedKeys();
                }
                ArrayList<SelectionKey> selectedList = new ArrayList<SelectionKey>(
                        selected);
                Collections.shuffle(selectedList);
                for (SelectionKey k : selectedList) {
                    if ((k.readyOps() & SelectionKey.OP_ACCEPT) != 0) {
                        SocketChannel sc = ((ServerSocketChannel) k
                                .channel()).accept();
                        InetAddress ia = sc.socket().getInetAddress();
                        int cnxncount = getClientCnxnCount(ia);
                        if (maxClientCnxns > 0 && cnxncount >= maxClientCnxns){
                            LOG.warn("Too many connections from " + ia
                                     + " - max is " + maxClientCnxns );
                            sc.close();
                        } else {
                            LOG.info("Accepted socket connection from "
                                     + sc.socket().getRemoteSocketAddress());
                            sc.configureBlocking(false);
                            SelectionKey sk = sc.register(selector,
                                    SelectionKey.OP_READ);
                            NIOServerCnxn cnxn = createConnection(sc, sk);
                            sk.attach(cnxn);
                            addCnxn(cnxn);
                        }
                    } else if ((k.readyOps() & (SelectionKey.OP_READ | SelectionKey.OP_WRITE)) != 0) {
                        NIOServerCnxn c = (NIOServerCnxn) k.attachment();
                        c.doIO(k);
                    } else {
                        if (LOG.isDebugEnabled()) {
                            LOG.debug("Unexpected ops in select "
                                      + k.readyOps());
                        }
                    }
                }
                selected.clear();
            } catch (RuntimeException e) {
                LOG.warn("Ignoring unexpected runtime exception", e);
            } catch (Exception e) {
                LOG.warn("Ignoring exception", e);
            }
        }
        closeAll();
        LOG.info("NIOServerCnxn factory exited run method");
    }

cacti本身的模板只可以监控硬盘的使用大小,而不能监控使用百分率,所以我们可以自定义cdef来监控硬盘使用率,并借助thold插件实现报警功能。网上找的资料都是在cacti.0.8.6版上实现的,而我用的是cacti.0.8.7版。因此,根据实际情况做如下改动:

 四  磁盘读写瓶颈的症状

  4.1  errorlog里报告错误 833

  4.2  sys.dm_os_wait_stats 视图里有大量等待状态PAGEIOLATCH_* 或
WriteLog。当数据在缓冲区里没有找到,连接的等待状态就是PAGEIOLACTH_EX(写)
PAGEIOLATCH_SH(读),然后发起异步操作,将页面读入缓冲区中。像
waiting_tasks_count和wait_time_ms比较高的时候,经常要等待I/O,除在反映在数据文件上以外,还有writelog的日志文件上。想要获得有意义数据,需要做基线数据,查看感兴趣的时间间隔。

select wait_type,
waiting_tasks_count,
wait_time_ms ,
max_wait_time_ms,
signal_wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%' 
order by wait_type

  wait_type:等待类型
  waiting_tasks_count:该等待类型的等待数
  wait_time_ms:该等待类型的总等待时间(包括一个进程悬挂状态(Suspend)和可运行状态(Runnable)花费的总时间)
  max_wait_time_ms:该等待类型的最长等待时间
  signal_wait_time_ms:正在等待的线程从收到信号通知到其开始运行之间的时差(一个进程可运行状态Runnable花费的总时间)
  i/o等待时间==wait_time_ms – signal_wait_time_ms

在cacti目录下,vi global_arrays.php

   五  优化磁盘I/O

   5.1
数据文件里页面碎片整理。 当表发生增删改操作时索引都会产生碎片(索引叶级的页拆分),碎片是指索引上的页不再具有物理连续性时,就会产生碎片。比如你查询10条数据,碎片少时,可能只扫描2个页,但碎片多时可能要扫描更多页(后面讲索引时在细说)。

   5.2
表格上的索引。比如:建议每个表都包含聚集索引,这是因为数据存储分为堆和B-Tree,
按B-Tree空间占用率更高。 充分使用索引减少对I/0的需求。

   5.3
数据文件,日志文件,TempDB文件建议存放不同物理磁盘,日志文件放写入速度比较快的磁盘上,例如
RAID 10的分区

        5.4
文件空间管理,设置数据库增长时要按固定大小增长,而不能按比例,这样避免一次增长太多或太少所带来的不必要麻烦。建议对比较小的数据库设置一次增长50MB到100MB。下图显示如果按5%来增长近10G, 如果有一个应用程序在尝试插入一行,但是没有空间可用。那么数据库可能会开始增长一个近10G,
文件的增长可能会耗用太长的时间,以至于客户端程序插入查询失败。

  图片 14

       5.5 避免自动收缩文件,如果设置了此功能,sql
server会每隔半小时检查文件的使用,如果空闲空间>25%,会自动运行dbcc
shrinkfile 动作。自动收缩线程的会话ID
SPID总是6(以后可能有变) 如下显示自动收缩为False。

   
 图片 15

     图片 16

   5.6 如果数据库的恢复模式是:完整。
就需要定期做日志备份,避免日志文件无限的增长,用于磁盘空间。

    

     

搜索custom_data_source_types,修改这一段如下:

$custom_data_source_types = array(   "CURRENT_DATA_SOURCE" => "Current Graph Item Data Source",   "ALL_DATA_SOURCES_NODUPS" => "All Data Sources (Don't Include Duplicates)",   "ALL_DATA_SOURCES_DUPS" => "All Data Sources (Include Duplicates)",   "SIMILAR_DATA_SOURCES_NODUPS" => "All Similar Data Sources (Don't Include Duplicates)",   "SIMILAR_DATA_SOURCES_DUPS" => "All Similar Data Sources (Include Duplicates)",   "CURRENT_DS_MINIMUM_VALUE" => "Current Data Source Item: Minimum Value",   "CURRENT_DS_MAXIMUM_VALUE" => "Current Data Source Item: Maximum Value",   "CURRENT_GRAPH_MINIMUM_VALUE" => "Graph: Lower Limit",   

//添加以下两行

"CURRENT_GRAPH_MAXIMUM_VALUE" => "Graph: Upper Limit",   "VALUE_OF_HDD_TOTAL" => "Value of hdd_total data source");   

//中文版汉化为:

“CURRENT_GRAPH_MAXIMUM_VALUE” => “图像: 上限”,

“VALUE_OF_HDD_TOTAL” => “所有硬盘数据的值”);

在cacti界面的Graph
Management-cdefs新建cdef模块,名字自己起,添加字段如下:

Item #1 Special Data Source: CURRENT_DATA_SOURCE   Item #2 Custom String: 100   Item #3 Operator: *   Item #4 Special Data Source: VALUE_OF_HDD_TOTAL   Item #5 Operator: /   

也就是说

cdef=CURRENT_DATA_SOURCE,100,*,VALUE_OF_HDD_TOTAL,/   

这样就可以在Threshold Templates里面添加监控硬盘的模块了,我添加的是Host
MIB – Hard Drive Space ,最后在Threshold
CDEF里面选择刚自定义的cdef,这样就可以正常监控硬盘使用率了。

通过阅读完整的文章,想必大家都学会了Cacti监控硬盘I/O的方法了吧!祝你们成功!

  • CACTI的客户端SNMP设置
  • Cacti配置Threshold(Thold)并e-mail报警
  • Cacti配置和安装具体介绍
  • 利用cacti监控memcache
  • Windows下安装Cacti
  • UbuntuLinux系统架设cacti监控服务

是我常用的监控网络软件,有时候我拿来 监控
我的 硬盘 I/O ,来看看具体是怎么做的! cacti监控硬盘I/O的方法:
cacti本身的模板只可…

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图