Hello everyone,
I've been digging the Internet over and over for 3 weeks and couldn't find any reasonable answer, so in the end I decided to write post here. We're during virtualization of our servers. We've chosen NetApp FAS2020 (12x300GB SAS, iSCSI 1Gbit) and XenServer to work on top of 3xIBM x3550M3 servers. One of our goals is virtualization of SQL Server and here comes our problem. We get TPS (transactions per second) below our expectations.
Currently, our test setup is build to eliminate most problem-causing factors - FAS is connected directly to NIC (no switch), no bonds (we tested with them tho), no vifs (tested), no jumbo frames (tested), no VLANs (tested). While now SQL works inside VM, we've tested bare metal config with ~ the same results. We have separate LUNs for log and data, both connected with iSCSI Initiator, partitions are NTFS formatted (4k block) and aligned (strange that we don't see any perf difference when partition is misaligned). Our aggregate contains of 9 spindles - 8 RAID-DP + 1 spare to max out performace.
What was thought provoking, our sql benchmark script (~70k inserts, updated and deletes) executed as single transaction finished in 8s on our reference database server (x3650, 2xRAID1 - 15k RPM log, 10k RPM data) and 7s on virtualized SQL, while without transaction (every SQL query is single transaction then?!) in 21s on reference, and 40+s on virtualized. It turned out that most likely log writes are the bottle neck. We've benchmarked log LUN with sqlio (6GB file, 8 threads) and checked what sysstat -x 3 says. Here's what we've got (for full benchmark results see the attachment):
Current database server (log RAID):
| Write IOPS
| Write MB/s
| Read IOPS
| Read MB/s
|
|---|
| Sequential, 2k block | 17394.26 | 33.97 | 11684.93 | 22.82 |
| Sequential, 8k block | 10264.61 | 80.19 | 4412.93 | 34.47 |
| Sequential, 64k block | 1168.91 | 73.05 | 1283.50 | 80.21 |
| Random, 2k block | 328.37 | 0.64 | 668.33 | 1.30 |
| Random, 8k block | 323.24 | 2.52 | 616.62 | 4.81 |
| Random, 64k block | 254.78 | 15.92 | 510.80 | 31.92 |
FAS2020 LUN:
| Write IOPS
| Write MB/s
| Write processor %
| Write disk %
| Read IOPS
| Read MB/s
| Read processor %
| Read disk %
|
|---|
| Sequential, 2k block | 5235.93 | 10.22 | ~98% | ~13% | 7936.50 | 15.50 | ~65% | ~4% |
| Sequential, 8k block | 3378.50 | 26.39 | ~98% | ~25% | 6131.30 | 47.90 | ~83% | ~9% |
| Sequential, 64k block | 786.60 | 49.16 | ~100% | ~44% | 1483.43 | 92.71 | ~90% | ~16% |
| Random, 2k block | 1028.96 | 2.00 | ~55% | ~73% | 1037.59 | 2.02 | ~36% | ~70% |
| Random, 8k block | 785.73 | 6.13 | ~63% | ~75% | 1010.30 | 7.89 | ~37% | ~69% |
| Random, 64k block | 457.30 | 28.58 | ~84% | ~76% | 880.20 | 55.01 | ~63% | ~74% |
As you can see, random access performs much better, while the key factor for db performance - sequence log write - is a few times worse. While 8 threads write sequentially 10.2MB/s, single thread is at level of ~3MB/s. During my benchmark query I can see writes to log (and mostly to log) at 1.5MB/s.
My questions are: is this typical performance for my setup? What can I do to improve it? I'm a little bit worried about running more VMs on this filler, when only with SQL Server on board performance is at the lower leve of acceptance.
