(最初发布在 DBA.StackExchange.com 但已关闭,希望在这里更相关。)
亚历山大和可怕的,可怕的,不好的,非常糟糕的......备份。
设置:
我有一个在VMWare的虚拟机上运行的本地SQL Server 2016 标准版实例。
@@版本:
Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) 2021 年 3 月 19 日 19:41:38 版权所有 (c) Windows Server 2016 Datacenter 10.0(内部版本 14393)上的 Microsoft Corporation 标准版(64 位): )(管理程序)
服务器本身目前分配有8 个虚拟处理器,具有32 GB 内存,所有磁盘都是 NVMes ,其I/O 速度约为1 GB/秒。数据库本身位于 G: 驱动器上,备份单独存储在 P: 驱动器上。所有数据库的总大小约为 500 GB(在压缩到备份文件本身之前)。
维护计划每晚运行一次(大约晚上 10:30),以对服务器上的每个数据库进行完整备份。服务器上没有运行其他任何异常,特别是在那个时候也没有运行其他任何东西。关闭服务器的电源计划设置为“平衡”(并且“之后关闭硬盘”设置为 0 分钟,即永不关闭)。
发生了什么:
在过去一年左右的时间里,维护计划作业的总运行时间总共需要大约 15分钟才能完成。自上周以来,它已飙升至大约 40 倍的时间,大约需要 15小时才能完成。
在维护计划放缓的同一天,我唯一知道的更改是在维护计划运行之前在计算机上安装了以下 Windows 更新:
我们还在另一个 VM 上拥有另一个类似配置的 SQL Server 实例,该实例经历了相同的 Windows 更新,随后也经历了较慢的备份。考虑到 Windows 更新是直接原因,我们将它们完全回滚,并且备份维护计划仍然运行得非常缓慢。奇怪的是,为给定数据库恢复备份的速度非常快,并且几乎使用了 NVMes 上 1 GB/秒的全部 I/O。
我尝试过的事情:
在使用 Adam Mechanic 的 sp_whoisactive 时,我发现备份过程的最后等待类型始终表明存在磁盘性能问题。我总是看到BACKUPBUFFER
并BACKUPIO
等待类型,除了ASYNC_IO_COMPLETION
:
查看服务器本身的资源监视器时,在备份期间,磁盘 I/O 部分显示正在使用的总 I/O 仅为大约 14 MB/秒(自此问题发生以来我见过的最多的是30 MB/秒):
在偶然发现这篇关于使用 DiskSpd 的有用Brent Ozar 文章后,我尝试在类似的参数下自己运行它(仅将线程数降低到 8,因为我在服务器上有 8 个虚拟处理器并将写入设置为 50%)。这是确切的命令diskspd.exe -b2M -d60 -o32 -h -L -t8 -W -w50 "C:\Users\...\Desktop\Microsoft DiskSpd\Test\LargeFile.txt"
。我使用了一个手动生成的文本文件,它不到 1 GB 大。我相信它测量的 I/O 看起来还不错,但是磁盘延迟显示了一些可笑的数字:
DiskSpd 结果看起来简直令人难以置信。在进一步阅读之后,我偶然发现了 Paul Randall 的一个查询,该查询返回每个数据库的磁盘延迟指标。结果如下:
最差的写入延迟为 63 毫秒,最差的读取延迟为 6 毫秒,因此这似乎与 DiskSpd 有很大差异,而且似乎还不足以成为我问题的根本原因。进一步交叉检查,根据这篇 Microsoft 文章,我在服务器本身上运行了几个 PerfMon 计数器,结果如下:
这里没什么特别的,我测量的所有计数器的最大值是 0.007(我相信是毫秒?)。最后,我让我的基础架构团队检查了 VMWare 在备份作业期间记录的磁盘延迟指标,结果如下:
似乎在最坏的情况下,午夜时分出现大约 200 毫秒的延迟峰值,最高 I/O 为 600 KB/秒(我不太明白,因为资源监视器显示备份至少正在使用大约 14 MB/秒的 I/O)。
我尝试过的其他事情:
我刚刚尝试恢复一个较大的数据库(大约 250 GB),总共只需要大约 8 分钟即可恢复。然后我尝试DBCC CHECKDB
在它上面运行,总共运行了 16 分钟(不确定这是否正常),但资源监视器显示了类似的 I/O 问题(它使用的最多 I/O 是 100 MB/s),没有其他运行:
这是我第一次运行时的 sp_whoisactive 结果DBCC CHECKDB
,然后在完成 5% 后,请注意,即使已经完成 5%,估计剩余时间也增加了大约 5 分钟。
我猜这是正常的,它只是一个估计值,对于 250 GB 的数据库来说,16 分钟似乎并不算太糟糕(虽然我不确定这是否正常),但 I/O 再次达到最大值大约 10% 的驱动器功能,在服务器或 SQL 实例上没有运行其他任何东西。
这些是 的结果,DBCC CHECKDB
没有报告错误。
我也遇到了奇怪的SHRINK
命令缓慢问题。我刚刚尝试SHRINK
了释放 5% 空间(大约 14 GB)的数据库。它只用了大约 1 分钟就完成了 90% 的SHRINK
:
大约 5 分钟后,它仍然停留在相同的完成百分比,我的事务日志备份(通常在 1-2 秒内完成)已经争用了大约 30 秒:
15 分钟后SHRINK
刚刚完成,而事务日志备份现在仍在争用大约 6 分钟,仅完成 50%。我相信他们在完成后立即SHRINK
完成。资源监视器一直显示 I/O 仍然很糟糕:
SHRINK
然后,当它完成时,我收到了命令错误:
我SHRINK
再次重试,结果与上述完全相同。
然后我尝试手动将 T-SQL 备份脚本编写到 P: 驱动器上的文件中,并且运行速度很慢,就像维护计划备份作业一样:
大约 3 分钟后我最终取消了它,它立即回滚。
概括:
巧合的是,在安装 Windows 更新后,备份维护计划作业每晚都会慢 40 倍(从 15 分钟到 15 小时)。回滚这些 Windows 更新并不能解决问题。SQL Server 等待类型、资源监视器和 Microsoft DiskSpd 表明存在磁盘问题(特别是 I/O),但来自 Paul Randall 查询、PerfMon 和 VMWare 日志的所有其他测量结果均未报告磁盘的任何问题。恢复特定数据库的备份很快,并且几乎使用了完整的 1 GB/秒 I/O。我在挠头……