3

In below screenshot, I benchmarked 3 machines, 4 Drives. my main production based on DL580, HPE 1.92TB SATA 6G Mixed Use SFF SSD RAID 1.

MySQL insert time of 1 million rows is ~ 30 seconds in the SSD, while its ~ 15 seconds in the ImageServer 20TB 7200 RPM RAID 5!! How the hell this is possible?

The SSD cost me ~ USD 2300 , I am really shocked, all firmwares and drivers up to date, and OS is Windows 2012 R2.

Is this known or expected?! if not, then share your insight.

Details about the 3 machines:

  1. HP ProLiant DL380 Gen9, Smart Array P840, 20TB RAID 5 volume: 12 X 2 TB 7200 RPM (Drive Type: SAS HDD,model: HP MB2000FCWDF, Firmware Version: HPD9, Transfer Speed: PHY 1: 6 Gbps ).

  2. HP ProLiant DL380 Gen9, Smart Array P440ar, ~1TB RAID 5 volume: 4 X 300 GB 10500 RPM (Drive Type: SAS HDD,model: HP EG0300FCVBF, Firmware Version: HPD9, Transfer Speed: PHY 1: 6 Gbps ).

  3. HP ProLiant DL580 Gen9, Smart Array P830i, 1.9TB RAID 1 volume: 2 X HPE 1.92TB SATA 6G Mixed Use SFF SSD (Drive Type: SATA SSD, model: ATA MK1920GFDKU , Firmware Version: HPG0 , Transfer Speed: 6 Gbps ). This is the machine in Question, the machine also contain second disk: 1 TB RAID 0 10500 RPM "the fourth in the picture".

disks benchmark

Update 1:

re-benchmark SSD after enabling write cache in the smart array didn't do anything as below screenshots highlight! do i need to reboot after enabling write cache?

enable write cache

re-benchmark SSD

Update 2:

Further Digging showed that My SSD BenchMarked Write Performance seems to meet the Specs of 28500 IOP/S as mentioned in HPE link: http://ssd.hpe.com/recommendations see below screenshot (please correct me if I understood wrong)

Max Seq WRITE Throughput (MiB/s): 475

Also all the 3 HP smart arrays official specs mentioned in performance section:

  • 12Gb/s SAS (1200 MB/s theoretical bandwidth per physical lane)
  • 6Gb/s SATA (600 MB/s theoretical bandwidth per physical lane)

Finally: All Smart Arrays GUI, In Controller Hardware Information Section showed:

Negotiated PCIe Data Rate: PCIe 3.0 x8 (7880 MB/s)

Putting all that together, it seems SAS HDD out performed the SSD with the help of the smart array cache and RAID 5. I couldn't find Max Seq WRITE Throughput for the SAS HDD, Does anyone know?

Maybe it can be calculated using the benchmark results:

Example: in case of the 4 X 300 GB SAS HDD:

Seq WRITE Throughput (MiB/s) Benchmarked as: 5782

Divide 5782 By 4 (number of disks) = 1445.5 MiB/s

But Since smart array Max is 1200 MB/S, I guess my calcs missing something!

That's All, Please share insight/feedback/corrections.

Max Seq WRITE Throughput

Jawad Al Shaikh
  • 254
  • 1
  • 3
  • 15

1 Answers1

7

Your HDD performance are greatly boosted by the RAID card's DRAM write back cache, which basically transforms random writes in sequential ones. I suppose that your P840 has at least 2 GB of write back cache, which totally absorbs CrystalDisk's workload and cached a good chunk of the MySQL one.

While is true that even your SSDs should benefit from the controller DRAM cache, SSDs are much more dependent on their private DRAM cache, which are often disabled by RAID controllers. Moreover, I suppose that your P830i has smaller cache (1 GB?)

To enhance the performance of your SSDs, you can enable their internal cache (via the RAID controller's utility), but be sure to completely understand the possible data loss ramification of this choice. Give a look here for more information.

Finally, consider that you are comparing a 2-disk RAID1 with a 12-disks RAID5 array...

shodanshok
  • 44,038
  • 6
  • 98
  • 162
  • what's the problem of comparing 2-disk RAID1 with a 12-disks RAID5? according to Windows 2012 Performance Tuning guide, RAID 5 is the slowest for writing performance. based on that, if same disks used in RAID 5 put in RAID 1, the writing performance should be even better. – Jawad Al Shaikh Jun 19 '17 at 14:47
  • 1
    Yeah, but 12 spindles give quite high sequential read/writes, and the controller DRAM cache transform random writes into sequential ones. – shodanshok Jun 19 '17 at 14:53
  • P830i: Controller Memory Module Size 4096 MiB . same as P840. but P440ar only 2 GB yet it out performed other 2 machines with its 300 GB disks! – Jawad Al Shaikh Jun 19 '17 at 14:57
  • In MySQL docs, its mentioned that rotational disks usually perform 100-200 IOPS, while SSD can do at least 1000 IOPS, now hpe 1.92 SSD specs: Read: 61,500 IOP/s, Write: 28,500 IOP/s. So How can 100-200 Compete & win over 25000 or even 15000 "assuming specs 28,500 represent max values"? – Jawad Al Shaikh Jun 19 '17 at 15:09
  • 1
    You continue to not consider the effect of the RAID controller's DRAM cache, which dramatically increase the random write IOPS of a mechanic disk – shodanshok Jun 19 '17 at 16:11
  • This is not my point, I apperciate all your input, and I am considering everything shared. my point is, does My 1.92 TB SSD with 28500 write IOP/S fast enough to insert 1 million rows in MySQL in 5 seconds or less instead of 30 seconds? otherwise please recommend solution (tweak or SSD or HDD specs) for such requirements, the DL580 server cost us over 80K USD and now the only bottleneck is the storage performance. – Jawad Al Shaikh Jun 19 '17 at 16:46
  • As stated above, your SSDs are probably handicapped by the RAID controller disabling their internal, private caches. With these caches disabled, SSDs speed is dramatically lower than published specifications. Try to reenable them and to rebenchmark your setup. Anyway, a 80K USD solution with only two RAID1 disks and an high IO load probably is an unbalanced solution. – shodanshok Jun 19 '17 at 16:53
  • Ok, I will do that and report my findings, but hope you aware that the smart array P830i specs mentions its designed for SSD. – Jawad Al Shaikh Jun 19 '17 at 16:55
  • 1
    This only means that the P830i will not be a bottleneck when used with flash disks, and that it can understand some specific SMART values (ie: SSD lifetime) and act accordingly. Please consider than enterprise SSDs drives typically have powerloss protected private caches, and are expected to run with these caches enabled. – shodanshok Jun 19 '17 at 17:00
  • check update 1. – Jawad Al Shaikh Jun 19 '17 at 17:19
  • Update 2 Added. – Jawad Al Shaikh Jun 19 '17 at 19:52