后面检查错误日志,监控物理Disk的IO延迟

2019-08-23 作者:数据库   |   浏览(100)

mysql的字符集和字符序:
    字符序:字符序(Collation)是指在同一字符集内字符之间的比较规则
    一个字符序唯一对应一种字符集,但一个字符集可以对应多种字符序,其中有一个是默认字符序(Default Collation)

1. 索引重建和重组有什么用?

当修改表(UPDATE、INSERT、DELETE等)中数据,数据库引擎自动维护索引的数据和结构。但是随着修改次数的累积,可能会现:

  • 索引中记录的数据顺序(逻辑顺序)和数据的实际顺序不一致(物理顺序),这也称之为外部碎片
  • 索引页的数据填充度变小(页密度),也称之为内部碎片
    有索引碎片是正常的,但是有大量的碎片,会降低查询性能,可以通过重建和重组索引来减少或消除碎片。

最近遇到一起关于"I/O is frozen on database xxx. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."的案例。

SQL Server的IO性能受到物理Disk的IO延迟和SQL Server内部执行的IO操作的影响。在监控Disk性能时,最主要的度量值(metric)是IO延迟,IO延迟是指从Application创建IO请求,到Disk完成IO请求的时间延迟。如果物理Disk不能及时完成IO请求,跟不上请求负载的速度,那么SQL Server就容易出现性能问题。SQL Server内部在执行一些特定的操作时,会和Disk做读写交互,这也会影响物理硬盘响应SQL Server的IO请求的性能,使查询进程处于PageIOLatch或WriteLog等待。

    mysql的字符集和字符序有四个级别的默认设置:服务器级,数据库级,数据表级,字段级

2. 索引重建和重组有什么区别?

  • 重建是删除索引并重新创建。通过这种方式移除碎片、回收磁盘空间(根据现有的或指定的填充因子压缩(Compact)页数据)、对相邻页中的索引进行重新排列。重组索引使用的系统资源最少。它在叶级层从左至右,重新排列叶级页使之于索引的逻辑顺序一致。同时也会对页按填充因子进行压缩。由此可知重建对于消除碎片和空间回收上的程度更高。
  • 重建索引是单个事务,如果指定了ALL关键字,则所有的索引重建做为一个事务。重组索引(包括指定了ALL),在内部会分解为多个较小的事务执行。重建事务回滚,需要回滚所有已经发生的修改。重组可以在任意时间点停止并且只回滚当前的某个较小的事务,已经发生的修改不会回滚(这个有点像DBCC SHRINKFILE)。
  • 重组只能在ONLINE模式下,重建可以指定为ONLINE或者OFFLINE。

出现问题的时候,我去执行一个非常简单的SQL语句,执行时间非常长,检查没有阻塞。正常情况下,应该是几秒就OK。后面检查错误日志,发现有大量这类消息.而这个点,我们没有备份数据库的作业。后面搜索,了解了一下这个消息出现的原因:

一,在系统级别监控物理Disk的IO性能

    mysql中的字符序的命名按照规范,以字符序对应的字符集名称开头.以_ci(大小写不敏感),_cs(大小写敏感)或者_bin(按编码值比较)
        例如:在字符序“utf8_general_ci”下,字符“a”和“A”是等价的

3. 索引重建时的ONLINE和OFFLINE选项是什么意思?

顾名思义,表示重建索引的模式。

  • OFFLINE时,会在表上获取Sch-M锁来阻止所有用户的访问,然后将旧索引的数据复制到新索引中,完成重建后才会释放表锁。
  • ONLINE时,也是复制旧索引数据到新索引中,同时旧索引是可以读写的。重建过程中旧索引的修改操作同时会被应用到新索中,还有一个中间数据结构实现新旧数据的映射和修改冲突。在重建完成后,会使用Sch-M锁定表非常短的时间,然后使用新索引替代旧索引,并释放Sch-M。详情参考:How Online Index Operations Work
  • 本地临时表的索引不能使用ONLINE模式。
  • 相对来说,ONLINE要比OFFLINE使用更多的资源,但提供并发支持。

 

1,监控物理Disk的IO延迟

    mysql字符集设置:
        系统变量:
            – character_set_server:默认的内部操作字符集
            – character_set_client:客户端来源数据使用的字符集
            – character_set_connection:连接层字符集
            – character_set_results:查询结果字符集
            – character_set_database:当前选中数据库的默认字符集
            – character_set_system:系统元数据(字段名等)字符集
            – 还有以collation_开头的同上面对应的变量,用来描述字符序

4. 在重组(或重建)大表的索引时,日志文件变得很大,怎么办?

说明一下,小表的索引整理问题没有太多意义。

数据库的所有有损操作都需要记录到日志,这个跟哪种恢复模式没有关系。也就是说从数据库的角度来看,这些日志都是它必须要写的。我们要做的是:引导它少写点日志和提高写日志的性能。下面是一些考虑点:

  • 最重要考虑点:我整理索引的目的是什么?消除碎片,回收空间,迁移数据等等?只有重建/重组索引才能达到我的目的吗?

  • 我们知道重组始终是ONLINE模式,它提供了并发支持,却会使用更多资源。这些资源中就包括日志。这很好验证,构建两个库,创两个同样的表和同样的索引,分别导入足够多的会产生碎片的数据,截断日志后分别执行重组和重建,你会发现重组产生的日志量要远多于重建。

  • 重建索引时的ONLINE和OFFLINE的选择,要结合前一点和实际系统应用情况考虑。我们可以做一些准备工作,比如:重建前先截断日志,对日志文件做一次手动增长来避免自动增长。
  • 事务在提交或者回滚后才能被截断,从前面的问题的,我们也知道重建的事务是原子性的,而重组被分成了多个小事务。也就说,在重建过程中,我们不能截断它的日志,而重组时可以截断。同理,不要在显式事务中使用ONLINE,这会导致显式事务提交后,才能截断日志。
  • 考虑使用 SORT_IN_TEMPDB选项。这个选项使得索引整理的事务日志写到tempdb,而不是用户数据库。这样就减少了用户数据库事务日志量,当然tempdb的空间要足够。如果tempdb位于独立的磁盘,就可以进一步的减少与用户数据库的存储空间和性能的竞争。
  • 如果可能,可以考虑切换到simple和bulk_logged恢复模式,索引的重建和重组可以利用最小化日志减少日志量。最小化日志,它不对每一行数据记录日志,而是对页和区的改变写日志。但是它不支持时间点还原。
  • 如果需要预留日志空间,索引大小的2~3倍会比较安全

图片 1

在Windows级别上对Physical Disk的IO延迟进行分析,主要依赖于Performance Monitor的计数器,衡量物理Disk的IO延迟的计数器主要有三个:

        MySQL中的字符集转换过程:
            1.MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection
            2.进行内部操作前将请求数据从character_set_connection转换为内部操作字符集,其确定方法如下
                   - 使用每个数据字段的CHARACTER SET设定值
                   - 若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展,非SQL标准)
                   - 若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值
                   - 若上述值不存在,则使用character_set_server设定值
            3.将操作结果从内部操作字符集转换为character_set_results

5. 在重建大表的索引时,数据文件也增长到很大了,怎么办?

索引重建过程中,旧索引结构和新索引结构是并存的,如果是ONLINE模式下,还有一个中间数据结构存在。如果涉及到数据排序操作,数据排序的临时数据结构也是需要占用空间的。跟日志的问题一样,我们能做的是减弱,不可能杜绝

  • 合理配置MAXDOP选项。在SQL Server 2012/2014/2016 Enterprise上,可以使用多个处理器来执行与索引语句关联的扫描、排序和索引操作。默认是0,由SQL Server引擎决定并行度。并不是越大越好,要根据系统和负载合理设置。
  • 对于临时的排序空间,它一次只能被一个索引操作使用,所以如果执行多个索引操作,只需要保证临时排序空间与最大的那个索引一样大即可。例如重建聚集索引,会同时重建相关的非聚集索引,只需要保证预留的空间与其中最大那个索引一样大即可。
  • 当SORT_IN_TEMPDB=ON时,临时排序空间则位于tempdb(重建索引的事务日志也在tempdb)。如=OFF,则排序空间位于当前用户数据库中。
  • 对于ONLINE模式重建的中间数据结构的位置,由SORT_IN_TEMPDB决定,跟上一点一样。
  • ONLINE操作使用行版本控制,这样读取行时不需要S锁,避免了并发的数据修改事务对索引操作的影响。使用了行版本,对于并发的数据修改操作,在tempdb中存储相关的行版本数据也需要一些空间。

 

  • Avg. Disk sec/Transfer:Disk每一次读写操作所用的平均时间
  • Avg. Disk sec/Read:Disk每一次读操作所用的平均时间 
  • Avg. Disk sec/Write:Disk每一次写操作所用的平均时间

        检测字符集问题的命令;
                SHOW CHARACTER SET;
                SHOW COLLATION;
                SHOW VARIABLES LIKE ‘character%’;
                SHOW VARIABLES LIKE ‘collation%’;
                SQL函数HEX、LENGTH、CHAR_LENGTH
                SQL函数CHARSET、COLLATION

总结

  1. 索引整理优化,对tempdb的使用较多,而tempdb本身的配置也是需要优化的。如果可能,将索引和数据分开存储,于性能和管理也有一定帮助。
  2. 将平时的一些零散的记录整理汇总而成,如有疏谬,请轻拍。

参考网上资料,关于“I/O is frozen on database xxx. No user action is required”的介绍如下:

avg.Disk sec/(Transfer,Read,Write),能够很好的反映Disk的IO速度,推荐的衡量Disk的IO速度的基线(baseline):

        注意事项:
            1.my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集,不会对使用libmysqlclient库
            的应用程序产生任何作用
            2.对字段进行的SQL操作通常都是以内部操作字符集来进行的,不受连接字符集设置的影响

 

  • 很快:<10ms
  • 一般:10-20ms
  • 有点慢:20-50ms
  • 非常慢:>50ms

        总结:
            mysql的字符集可以细化到一个库,一张表,一列.但是一般是使用默认的设置
                1.编译mysql时,指定了一个默认的字符集,这个字符集是latin1
                2.安装mysql时,可以在配置文件中指定一个默认的字符集,如果没有指定,这个值继承编译时的字符集
                3.启动mysqld时,可以使用character_set_server来指定默认的字符集,如果没有指定就继承配置文件中的配置
                4.安装mysql时选择多语言支持,在程序安装时会自动将配置设置为UTF-8

This message is logged in the Error Log whenever any backup service making use of SQL Server Virtual Device Interface (VDI) tries to backup the database (with snapshot)/drive on which the database files reside. Microsoft Backup (ntbackup.exe), Volume Shadow Copy (VSS), Data Protection Manager (DPM) and third party tools like Symantec Business Continuance Volume (BCV) are some of the application which cause this message to logged in the SQL Server Error Log.

2,分析Data Collector收集的计数器数值

            默认情况下的mysql默认字符集是latin1

What does these messages mean? Let me explain this with an example. Suppose ntbackup.exe is configured to take the backup of D drive. This drive has some data files related to few databases on SQL Server. Since the data files are in use by SQL Server, if these files are copied as it is the files in the backup will be inconsistent. To ensure that the database files are consistent in the drive backup, this application internally issues a BACKUP DATABASE [databasename] WITH SNAPSHOT command against the database. When this command is issued, the I/O for that database is frozen and the backup application is informed to proceed with its operation. Until the BACKUP WITH SNAPSHOT command is complete, the I/O for the database is frozen and the I/O is resumed once it completes. The corresponding messages are logged in the SQL Server Error Log.

下图是产品环境中一台Server的计数器数值图表,将IO延迟的度量值按比例放大1000倍,这样图表显示的单位就是ms。

        修改默认字符集:
            1.最简单的修改方法:
                在mysql的配置文件中加入default-character-set = utf8
                                    character_set_server = utf8
                    修改完后重启服务器
            2.在线修改字符集
                     mysql> SET character_set_client = utf8;
                     mysql> SET character_set_connection = utf8;
                     mysql> SET character_set_database = utf8;
                     mysql> SET character_set_results = utf8;
                     mysql> SET character_set_server = utf8;
                     mysql> SET collation_connection = utf8;
                     mysql> SET collation_database = utf8;
                     mysql> SET collation_server = utf8;

 

  • %Idle Time:在60%左右浮动,说明Disk不是很忙碌
  • Avg.Disk sec/Write:大多数情况下都是10ms以下,偶尔波动,说明Disk的写延迟比较低
  • Avg.Disk sec/Read:读延迟大多数情况下都是在40ms以上,鲜有低于40ms,偶尔达到峰值,说明Disk的读延迟非常高
  • Avg.Disk sec/Transfer:读写延迟的均值在30ms左右,时有波动,在%Idle Time曲线不波动时,Disk的读写延迟也有波动,说明Disk的读写延迟不稳定

本文由小鱼儿玄机30码发布于数据库,转载请注明出处:后面检查错误日志,监控物理Disk的IO延迟

关键词: 小鱼儿玄机30码

  • 上一篇:没有了
  • 下一篇:没有了