SQL IO 和 SAN 问题

SQL IO 和 SAN 问题

我们正在运行两台具有相同软件设置但不同硬件的服务器。

第一个是位于普通塔式服务器上 VMWare 上的 VM,具有双核 Xeon、16 GB RAM 和 7200 RPM 驱动器。

第二个是功能强大的全新机架服务器上 XenServer 上的 VM,具有 4 核 Xeon 和共享存储。

我们正在运行 Dynamics AX 2012 和 SQL Server 2008 R2。当我在慢速塔式服务器上向表中插入 15,000 条记录时(作为测试),它需要 13 秒。在快速服务器上则需要 33 秒。我多次重新运行这些测试,结果相同。

我感觉这是一种 IO 瓶颈,所以我在两者上都运行了 SQLIO。

以下是慢速塔式服务器的结果:

C:\Program Files (x86)\SQLIO>test.bat

C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS C:\Tes
tFile.dat
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
8 threads writing for 120 secs to file C:\TestFile.dat
        using 8KB random IOs
        enabling multiple I/Os per thread with 8 outstanding
        buffering set to use hardware disk cache (but not file cache)
using current size: 5120 MB for file: C:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec:   226.97
MBs/sec:     1.77
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 281
Max_Latency(ms): 467
histogram:
ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 99

C:\Program Files (x86)\SQLIO>sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS C:\Tes
tFile.dat
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
8 threads reading for 120 secs from file C:\TestFile.dat
        using 8KB random IOs
        enabling multiple I/Os per thread with 8 outstanding
        buffering set to use hardware disk cache (but not file cache)
using current size: 5120 MB for file: C:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec:    91.34
MBs/sec:     0.71
latency metrics:
Min_Latency(ms): 14
Avg_Latency(ms): 699
Max_Latency(ms): 1124
histogram:
ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 100

C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS C
:\TestFile.dat
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
8 threads writing for 120 secs to file C:\TestFile.dat
        using 64KB sequential IOs
        enabling multiple I/Os per thread with 8 outstanding
        buffering set to use hardware disk cache (but not file cache)
using current size: 5120 MB for file: C:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec:  1094.50
MBs/sec:    68.40
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 58
Max_Latency(ms): 467
histogram:
ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 100

C:\Program Files (x86)\SQLIO>sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS C
:\TestFile.dat
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
8 threads reading for 120 secs from file C:\TestFile.dat
        using 64KB sequential IOs
        enabling multiple I/Os per thread with 8 outstanding
        buffering set to use hardware disk cache (but not file cache)
using current size: 5120 MB for file: C:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec:  1155.31
MBs/sec:    72.20
latency metrics:
Min_Latency(ms): 17
Avg_Latency(ms): 55
Max_Latency(ms): 205
histogram:
ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 100

以下是快速机架服务器的结果:

C:\Program Files (x86)\SQLIO>test.bat

C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS E:\Tes
tFile.dat
sqlio v1.5.SG
using system counter for latency timings, 62500000 counts per second
8 threads writing for 120 secs to file E:\TestFile.dat
        using 8KB random IOs
        enabling multiple I/Os per thread with 8 outstanding
        buffering set to use hardware disk cache (but not file cache)
open_file: CreateFile (E:\TestFile.dat for write): The system cannot find the pa
th specified.
exiting

C:\Program Files (x86)\SQLIO>sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS E:\Tes
tFile.dat
sqlio v1.5.SG
using system counter for latency timings, 62500000 counts per second
8 threads reading for 120 secs from file E:\TestFile.dat
        using 8KB random IOs
        enabling multiple I/Os per thread with 8 outstanding
        buffering set to use hardware disk cache (but not file cache)
open_file: CreateFile (E:\TestFile.dat for read): The system cannot find the pat
h specified.
exiting

C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS E
:\TestFile.dat
sqlio v1.5.SG
using system counter for latency timings, 62500000 counts per second
8 threads writing for 120 secs to file E:\TestFile.dat
        using 64KB sequential IOs
        enabling multiple I/Os per thread with 8 outstanding
        buffering set to use hardware disk cache (but not file cache)
open_file: CreateFile (E:\TestFile.dat for write): The system cannot find the pa
th specified.
exiting

C:\Program Files (x86)\SQLIO>sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS E
:\TestFile.dat
sqlio v1.5.SG
using system counter for latency timings, 62500000 counts per second
8 threads reading for 120 secs from file E:\TestFile.dat
        using 64KB sequential IOs
        enabling multiple I/Os per thread with 8 outstanding
        buffering set to use hardware disk cache (but not file cache)
open_file: CreateFile (E:\TestFile.dat for read): The system cannot find the pat
h specified.
exiting

C:\Program Files (x86)\SQLIO>test.bat

C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS c:\Tes
tFile.dat
sqlio v1.5.SG
using system counter for latency timings, 62500000 counts per second
8 threads writing for 120 secs to file c:\TestFile.dat
        using 8KB random IOs
        enabling multiple I/Os per thread with 8 outstanding
        buffering set to use hardware disk cache (but not file cache)
using current size: 5120 MB for file: c:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec:  2575.77
MBs/sec:    20.12
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 24
Max_Latency(ms): 655
histogram:
ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%:  0  0  0  5  8  9  9  9  8  5  3  1  1  1  1  0  0  0  0  0  0  0  0  0 37

C:\Program Files (x86)\SQLIO>sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS c:\Tes
tFile.dat
sqlio v1.5.SG
using system counter for latency timings, 62500000 counts per second
8 threads reading for 120 secs from file c:\TestFile.dat
        using 8KB random IOs
        enabling multiple I/Os per thread with 8 outstanding
        buffering set to use hardware disk cache (but not file cache)
using current size: 5120 MB for file: c:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec:  1141.39
MBs/sec:     8.91
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 55
Max_Latency(ms): 652
histogram:
ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  1  1  1  1  1  1  1  1 91

C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS c
:\TestFile.dat
sqlio v1.5.SG
using system counter for latency timings, 62500000 counts per second
8 threads writing for 120 secs to file c:\TestFile.dat
        using 64KB sequential IOs
        enabling multiple I/Os per thread with 8 outstanding
        buffering set to use hardware disk cache (but not file cache)
using current size: 5120 MB for file: c:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec:   341.37
MBs/sec:    21.33
latency metrics:
Min_Latency(ms): 5
Avg_Latency(ms): 186
Max_Latency(ms): 120037
histogram:
ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 100

C:\Program Files (x86)\SQLIO>sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS c
:\TestFile.dat
sqlio v1.5.SG
using system counter for latency timings, 62500000 counts per second
8 threads reading for 120 secs from file c:\TestFile.dat
        using 64KB sequential IOs
        enabling multiple I/Os per thread with 8 outstanding
        buffering set to use hardware disk cache (but not file cache)
using current size: 5120 MB for file: c:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec:  1024.07
MBs/sec:    64.00
latency metrics:
Min_Latency(ms): 5
Avg_Latency(ms): 61
Max_Latency(ms): 81632
histogram:
ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 100

在我看来,四项测试中有三项在机架服务器的合理参数范围内。然而,64 个写入测试难以置信在机架服务器上速度很慢。(在较慢的塔式服务器上为 68 mb/秒,而在机架服务器上为 21 mb/秒)。64k 的读取速度似乎也很慢。

这是否足以说明共享存储存在某种瓶颈?我想知道我是否可以采纳这些证据并说我们需要对此展开调查。

任何帮助都将受到赞赏。

答案1

请使用 Citrix Performance VM 测试您的 xenserver 性能:关联

它将作为虚拟机运行并测试磁盘和网络 IO

相关内容