Over 2 GBps SQL Server 2012 backup on 12-core server with 7 Fusion-IO Cards!
I managed to squeeze 2 GBps (2192.207 MBps to be exact) out on 12 cores using a HP Server DL370 with 12 x 2.66 (12 MB Cache) cores, 108GB RAM, and 7 Fusion-IO Cards backing up from a database stored on 5 cards to backup files spread across 2 other cards, 1 Intel SSD, and 2 Samsung SSDs,. The receiving end was saturated. I could probably get closer to 3GBps if I balanced the load better by getting more SATA SSDs (although my built-in HP SATA III controller won’t handle very many SSDs) along with moving some more output to the cards currently being used by the database. I am going to rerun the test as soon as I get around to updating the firmware on the one Fusion-IO cards that is extremely out of date (will have to put it into a separate staging server and go through 3 firmware upgrades to get it working, guessing this card was never even used). These cards were all purchased off of EBAY for about 20 – 30% of retail price. With the exception of one card, they are all at 100% reserve and most only have a fraction of their expected life used, especially the 4 SLC Cards. The breakdown is:
- 4 SLC 320GB Duos (8 drives)
- 1 MLC 640GB Duo (2 drives)
- 1 MLC 1.2 TB Duo (2 drives)
- 1 MLC 320GB IoDrive
- 1 SLC 320GB Duo awaiting firmware update.
The database is using 3 SLC Duos and 2 MLC Duos providing 8 drives mostly in RAID-0 with a RAID-1 for the log. The combined read capability is about 3.75 GBps. The SATA SSDs collectively seem to provide about 400MBps collectively (the 2 Samsungs are in a RAID 1) and the 3 drives from the MLC 320GB IODrive and 2 160GB drives from the 320GB SLC add another 2.0 GBPS write capability.
A couple of interesting things before getting to the images of the actual backup execution:
1) I had to turn off backup compression in SQL to get best results. The issue is that the 12 cores start to max out at about 1.2 GBPs throughput with compression. However, without compression, the throughput seems more limited by the actual storage device. I almost doubled throughput on the same backup not using compression. Caveat here is that this is only because the storage is providing the data faster than the CPUs can compress it, this is only an issue with very high throughput storage and limited CPU resources, so do not take this as a best practice. I would love to run this on a 96 core with compression – we might be looking at 4 GBps instead of 2 GBps with more powerful CPU resources for this storage using backup compression.
2) Tweaked the backup settings default values using more buffers, larger buffer size, etc. Also found that using more files was useful not only for ensuring granularity to balance the output across the devices correctly, but to maximize queue depth. I had to play with this some; Initial tests showed the backup stalling out because I was writing too much of the backup to the SATA SSDs. When I redistributed more to the IO cards, the backup utilized all of the devise all the way until the end rather than getting stuck on one. Something to keep in mind when doing striped backup, put enough files on the faster devices to even out the slower devices, it is striping based on data, not time and will happily keep writing to the slower device until getting all of the data on it even if the other files have all been buffered out to disk. Using mismatched media will also slow you down because I don’t think SQL will let the files get too far out of sync in the striping. My backup even with the less than optimal configuration still only took 2 minutes, so it was easy to see the lag at the end for the big files.
3) These cards are running without any auxiliary power connectors just using the PCIE bus power. At the bottom, I included the output of the FIO-STATUS –A so you can see details of the cards used. I am still trying to figure out who to talk to at Fusion-IO or HP to get some of these, this was of minimal effect because the HP server does a great job of keeping the cards cooled and most of the activity is read, but with putting more cards on the receiving end, I suspect that the aux power will help.
It is something to fathom – backing up a database of over 150GB in less than 1 1/2 minutes. This database contains billions of stock market transactions, not just a generated bunch of dummy data.
Without further ado, here are a couple of screen snapshots, I really should put this on Youtube as it is incredible to watch this actually happen and watch the Resource monitor spike to over 5GBps read throughput at times. I think this is quite an accomplishment and shows the value of these cards. I’ve not found anybody else achieve this level of throughput at this low of a hardware cost with such a small server. This type of performance usually takes at least 48 cores and a 4-way processor in addition to the high-speed storage. This is just a 2-way Nephalem Xeon configuration. I am hoping to get this up to 3.5 GBps with the balancing of the cards better for write and read. Of course if I had a couple of Duo-2s, who knows. The CPUs were only hitting about 20% with the compression turned off. With 4 newer Duos (the new cards require X8, the server has only 4 X8), this could potentially go to 6Gbps.
Found 15 ioMemory devices in this system with 7 ioDrive Duos
Fusion-io driver version: 3.1.1 build 181
Adapter: Single Adapter
HP 320GB MLC PCIe ioDrive for ProLiant Servers, Product Number:600279-B21, SN:XXXXX
Pseudo Low-Profile ioDIMM Adapter, PN:00119200000
External Power: NOT connected
PCIe Bus voltage: avg 11.71V min 11.65V max 11.73V
PCIe Bus current: avg 0.46A max 1.69A
PCIe Bus power: avg 5.40W max 19.73W
PCIe Power limit threshold: 24.75W
PCIe slot available power: unavailable
Connected ioMemory modules:
fct2: Product Number:600279-B21
fct2 Attached as 'fct2' (block device)
HP ioDrive 320GB, Product Number:600279-B21
HP ioDrive 320GB, PN:00214200302
Located in slot 0 Center of Pseudo Low-Profile ioDIMM Adapter SN: XXXXX
Powerloss protection: protected
Last Power Monitor Incident: 33533 sec
PCI:02:00.0, Slot Number:1
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178c
Firmware v6.0.0, rev 107006 Public
288.00 GBytes block device size
Format: v500, 562500000 sectors of 512 bytes
PCIe slot available power: unavailable
Internal temperature: 43.31 degC, max 44.30 degC
Internal voltage: avg 1.00V, max 1.02V
Aux voltage: avg 2.46V, max 2.46V
Reserve space status: Healthy; Reserves: 100.00%, warn at 10.00%
Lifetime data volumes:
Physical bytes written: 9,532,104,713,616
Physical bytes read : 9,598,909,699,136
RAM usage:
Current: 126,144,192 bytes
Peak : 126,951,232 bytes
Adapter: Dual Adapter
HP 320GB SLC PCIe ioDrive Duo for ProLiant Servers, Product Number:600281-B21, SN:59738
ioDrive Duo HL, PN:00190000107
External Power: NOT connected
PCIe Bus voltage: avg 11.99V min 11.92V max 12.00V
PCIe Bus current: avg 0.91A max 2.41A
PCIe Bus power: avg 10.92W max 19.87W
PCIe Power limit threshold: 24.75W
PCIe slot available power: unavailable
Connected ioMemory modules:
fct16: Product Number:600281-B21
fct17: Product Number:600281-B21
fct16 Attached as 'fct16' (block device)
HP ioDIMM 160GB, Product Number:600281-B21
HP ioDIMM 160GB, PN:00277100101
Located in slot 0 Upper of ioDrive Duo HL SN: XXXXX
Powerloss protection: protected
Last Power Monitor Incident: 33506 sec
PCI:10:00.0, Slot Number:3
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178d
Firmware v6.0.0, rev 107007 Public
128.00 GBytes block device size
Format: v500, 31250000 sectors of 4096 bytes
PCIe slot available power: unavailable
Internal temperature: 45.77 degC, max 46.26 degC
Internal voltage: avg 1.03V, max 1.03V
Aux voltage: avg 2.44V, max 2.44V
Reserve space status: Healthy; Reserves: 100.00%, warn at 10.00%
Lifetime data volumes:
Physical bytes written: 799,340,235,816
Physical bytes read : 1,393,505,907,368
RAM usage:
Current: 49,125,952 bytes
Peak : 49,125,952 bytes
fct17 Attached as 'fct17' (block device)
HP ioDIMM 160GB, Product Number:600281-B21
HP ioDIMM 160GB, PN:00277100101
Located in slot 1 Lower of ioDrive Duo HL SN: XXXXX
Powerloss protection: protected
Last Power Monitor Incident: 33507 sec
PCI:11:00.0, Slot Number:3
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178d
Firmware v6.0.0, rev 107007 Public
128.00 GBytes block device size
Format: v500, 31250000 sectors of 4096 bytes
PCIe slot available power: unavailable
Internal temperature: 39.87 degC, max 40.85 degC
Internal voltage: avg 1.01V, max 1.01V
Aux voltage: avg 2.43V, max 2.43V
Reserve space status: Healthy; Reserves: 93.90%, warn at 10.00%
Lifetime data volumes:
Physical bytes written: 497,387,016,664
Physical bytes read : 1,115,459,630,528
RAM usage:
Current: 49,051,072 bytes
Peak : 49,051,072 bytes
Adapter: Dual Adapter
HP 320GB SLC PCIe ioDrive Duo for ProLiant Servers, Product Number:600281-B21, SN: XXXXX
ioDrive Duo HL, PN:00190000107
External Power: NOT connected
PCIe Bus voltage: avg 11.99V min 11.92V max 12.01V
PCIe Bus current: avg 0.95A max 2.26A
PCIe Bus power: avg 11.45W max 27.08W
PCIe Power limit threshold: 24.75W
PCIe slot available power: unavailable
Connected ioMemory modules:
fct21: Product Number:600281-B21
fct22: Product Number:600281-B21
fct21 Attached as 'fct21' (block device)
HP ioDIMM 160GB, Product Number:600281-B21
HP ioDIMM 160GB, PN:00277100103
Located in slot 0 Upper of ioDrive Duo HL SN: XXXXX
Powerloss protection: protected
Last Power Monitor Incident: 33775 sec
PCI:15:00.0, Slot Number:4
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178d
Firmware v6.0.0, rev 107007 Public
128.00 GBytes block device size
Format: v500, 31250000 sectors of 4096 bytes
PCIe slot available power: unavailable
Internal temperature: 45.28 degC, max 45.77 degC
Internal voltage: avg 1.03V, max 1.03V
Aux voltage: avg 2.46V, max 2.46V
Reserve space status: Healthy; Reserves: 93.89%, warn at 10.00%
Lifetime data volumes:
Physical bytes written: 2,399,961,878,072
Physical bytes read : 3,486,203,149,520
RAM usage:
Current: 52,154,432 bytes
Peak : 52,154,432 bytes
fct22 Attached as 'fct22' (block device)
HP ioDIMM 160GB, Product Number:600281-B21
HP ioDIMM 160GB, PN:00277100103
Located in slot 1 Lower of ioDrive Duo HL SN: XXXXX
Powerloss protection: protected
PCI:16:00.0, Slot Number:4
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178d
Firmware v6.0.0, rev 107007 Public
128.00 GBytes block device size
Format: v500, 31250000 sectors of 4096 bytes
PCIe slot available power: unavailable
Internal temperature: 44.30 degC, max 45.77 degC
Internal voltage: avg 1.00V, max 1.01V
Aux voltage: avg 2.46V, max 2.46V
Reserve space status: Healthy; Reserves: 93.90%, warn at 10.00%
Lifetime data volumes:
Physical bytes written: 2,509,265,675,328
Physical bytes read : 3,186,755,344,040
RAM usage:
Current: 525,667,328 bytes
Peak : 525,667,328 bytes
Adapter: Dual Adapter
HP 320GB SLC PCIe ioDrive Duo for ProLiant Servers, Product Number:600281-B21, SN: XXXXX
ioDrive Duo HL, PN:00190000107
External Power: NOT connected
PCIe Bus voltage: min 7.88V max 12.21V
PCIe Bus current: max 0.95A
PCIe slot available power: unavailable
Connected ioMemory modules:
fct31: Product Number:600281-B21
fct32: Product Number:600281-B21
fct31 Status unknown: Driver is in MINIMAL MODE:
The firmware on this device is not compatible with the currently installed version of the driver
HP ioDIMM 160GB, Product Number:600281-B21
!! ---> There are active errors or warnings on this device! Read below for details.
HP ioDIMM 160GB, PN:00277100101
Located in slot 0 Upper of ioDrive Duo HL SN: XXXXX
Powerloss protection: not available
PCI:1f:00.0, Slot Number:5
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178d
Firmware v3.0.3, rev 43246 Public
Geometry and capacity information not available.
Format: not low-level formatted
PCIe slot available power: unavailable
Internal temperature: 43.80 degC, max 44.30 degC
Internal voltage: avg 1.03V, max 1.03V
Aux voltage: avg 2.46V, max 2.46V
Lifetime data volumes:
Physical bytes written: 0
Physical bytes read : 0
RAM usage:
Current: 0 bytes
Peak : 0 bytes
ACTIVE WARNINGS:
The ioMemory is currently running in a minimal state.
fct32 Status unknown: Driver is in MINIMAL MODE:
The firmware on this device is not compatible with the currently installed version of the driver
HP ioDIMM 160GB, Product Number:600281-B21
!! ---> There are active errors or warnings on this device! Read below for details.
HP ioDIMM 160GB, PN:00277100101
Located in slot 0 Upper of ioDrive Duo HL SN: XXXXX
Powerloss protection: not available
PCI:20:00.0, Slot Number:5
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178d
Firmware v3.0.3, rev 43246 Public
Geometry and capacity information not available.
Format: not low-level formatted
PCIe slot available power: unavailable
Internal temperature: 42.82 degC, max 43.31 degC
Internal voltage: avg 1.01V, max 1.01V
Aux voltage: avg 2.45V, max 2.45V
Lifetime data volumes:
Physical bytes written: 0
Physical bytes read : 0
RAM usage:
Current: 0 bytes
Peak : 0 bytes
ACTIVE WARNINGS:
The ioMemory is currently running in a minimal state.
Adapter: Dual Adapter
HP 640GB MLC PCIe ioDrive Duo for ProLiant Servers, Product Number:600282-B21, SN: XXXXX
ioDrive Duo HL, PN:00190000108
External Power: NOT connected
PCIe Bus voltage: avg 11.99V min 11.93V max 12.02V
PCIe Bus current: avg 0.96A max 2.21A
PCIe Bus power: avg 11.48W max 26.39W
PCIe Power limit threshold: 24.75W
PCIe slot available power: unavailable
Connected ioMemory modules:
fct36: Product Number:600282-B21
fct37: Product Number:600282-B21
fct36 Attached as 'fct36' (block device)
HP ioDIMM 320GB, Product Number:600282-B21
HP ioDIMM 320GB, PN:00277100201
Located in slot 0 Upper of ioDrive Duo HL SN: XXXXX
Powerloss protection: protected
Last Power Monitor Incident: 33595 sec
PCI:24:00.0, Slot Number:6
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178e
Firmware v6.0.0, rev 107007 Public
256.00 GBytes block device size
Format: v500, 62500000 sectors of 4096 bytes
PCIe slot available power: unavailable
Internal temperature: 45.28 degC, max 45.77 degC
Internal voltage: avg 1.03V, max 1.03V
Aux voltage: avg 2.47V, max 2.48V
Reserve space status: Healthy; Reserves: 100.00%, warn at 10.00%
Lifetime data volumes:
Physical bytes written: 3,071,101,924,128
Physical bytes read : 3,407,184,536,048
RAM usage:
Current: 32,577,472 bytes
Peak : 32,577,472 bytes
fct37 Attached as 'fct37' (block device)
HP ioDIMM 320GB, Product Number:600282-B21
HP ioDIMM 320GB, PN:00277100201
Located in slot 1 Lower of ioDrive Duo HL SN: XXXXX
Powerloss protection: protected
PCI:25:00.0, Slot Number:6
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178e
Firmware v6.0.0, rev 107007 Public
256.00 GBytes block device size
Format: v500, 62500000 sectors of 4096 bytes
PCIe slot available power: unavailable
Internal temperature: 39.87 degC, max 40.36 degC
Internal voltage: avg 1.01V, max 1.02V
Aux voltage: avg 2.46V, max 2.47V
Reserve space status: Healthy; Reserves: 100.00%, warn at 10.00%
Lifetime data volumes:
Physical bytes written: 2,380,119,569,280
Physical bytes read : 2,736,125,677,440
RAM usage:
Current: 32,544,192 bytes
Peak : 32,544,192 bytes
Adapter: Dual Adapter
HP 320GB SLC PCIe ioDrive Duo for ProLiant Servers, Product Number:600281-B21, SN: XXXXX
ioDrive Duo HL, PN:00190000107
External Power: NOT connected
PCIe Bus voltage: avg 11.99V min 11.93V max 12.02V
PCIe Bus current: avg 0.90A max 1.33A
PCIe Bus power: avg 10.74W max 15.92W
PCIe Power limit threshold: 24.75W
PCIe slot available power: unavailable
Connected ioMemory modules:
fct41: Product Number:600281-B21
fct42: Product Number:600281-B21
fct41 Detached
HP ioDIMM 160GB, Product Number:600281-B21
HP ioDIMM 160GB, PN:00277100101
Located in slot 0 Upper of ioDrive Duo HL SN: XXXXX
Powerloss protection: protected
PCI:29:00.0, Slot Number:7
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178d
Firmware v6.0.0, rev 107007 Public
144.00 GBytes block device size
Format: v500, 35156250 sectors of 4096 bytes
PCIe slot available power: unavailable
Internal temperature: 40.85 degC, max 41.34 degC
Internal voltage: avg 1.03V, max 1.03V
Aux voltage: avg 2.46V, max 2.46V
Reserve space status: Healthy; Reserves: 100.00%, warn at 10.00%
Lifetime data volumes:
Physical bytes written: 959,690,184
Physical bytes read : 6,919,113,776
RAM usage:
Current: 10,920,000 bytes
Peak : 10,920,000 bytes
fct42 Detached
HP ioDIMM 160GB, Product Number:600281-B21
HP ioDIMM 160GB, PN:00277100101
Located in slot 1 Lower of ioDrive Duo HL SN: XXXXX
Powerloss protection: protected
PCI:2a:00.0, Slot Number:7
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178d
Firmware v6.0.0, rev 107007 Public
144.00 GBytes block device size
Format: v500, 35156250 sectors of 4096 bytes
PCIe slot available power: unavailable
Internal temperature: 42.33 degC, max 42.82 degC
Internal voltage: avg 1.00V, max 1.00V
Aux voltage: avg 2.45V, max 2.45V
Reserve space status: Healthy; Reserves: 100.00%, warn at 10.00%
Lifetime data volumes:
Physical bytes written: 960,269,736
Physical bytes read : 6,932,504,752
RAM usage:
Current: 10,920,000 bytes
Peak : 10,920,000 bytes
Adapter: Dual Adapter
HP 320GB SLC PCIe ioDrive Duo for ProLiant Servers, Product Number:600281-B21, SN: XXXXX
ioDrive Duo HL, PN:00190000107
External Power: NOT connected
PCIe Bus voltage: avg 11.99V min 11.92V max 12.02V
PCIe Bus current: avg 0.97A max 2.31A
PCIe Bus power: avg 11.58W max 27.61W
PCIe Power limit threshold: 24.75W
PCIe slot available power: unavailable
Connected ioMemory modules:
fct46: Product Number:600281-B21
fct47: Product Number:600281-B21
fct46 Attached as 'fct46' (block device)
HP ioDIMM 160GB, Product Number:600281-B21
HP ioDIMM 160GB, PN:00277100101
Located in slot 0 Upper of ioDrive Duo HL SN: XXXXX
Powerloss protection: protected
Last Power Monitor Incident: 33713 sec
PCI:2e:00.0, Slot Number:8
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178d
Firmware v6.0.0, rev 107007 Public
128.00 GBytes block device size
Format: v500, 31250000 sectors of 4096 bytes
PCIe slot available power: unavailable
Internal temperature: 44.30 degC, max 44.79 degC
Internal voltage: avg 1.02V, max 1.03V
Aux voltage: avg 2.47V, max 2.47V
Reserve space status: Healthy; Reserves: 100.00%, warn at 10.00%
Lifetime data volumes:
Physical bytes written: 661,125,209,496
Physical bytes read : 1,182,061,604,688
RAM usage:
Current: 525,667,840 bytes
Peak : 525,667,840 bytes
fct47 Attached as 'fct47' (block device)
HP ioDIMM 160GB, Product Number:600281-B21
HP ioDIMM 160GB, PN:00277100101
Located in slot 1 Lower of ioDrive Duo HL SN:104395
Powerloss protection: protected
PCI:2f:00.0, Slot Number:8
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178d
Firmware v6.0.0, rev 107007 Public
128.00 GBytes block device size
Format: v500, 31250000 sectors of 4096 bytes
PCIe slot available power: unavailable
Internal temperature: 41.83 degC, max 42.33 degC
Internal voltage: avg 1.00V, max 1.01V
Aux voltage: avg 2.46V, max 2.47V
Reserve space status: Healthy; Reserves: 100.00%, warn at 10.00%
Lifetime data volumes:
Physical bytes written: 612,235,065,752
Physical bytes read : 1,107,146,274,888
RAM usage:
Current: 525,666,880 bytes
Peak : 525,666,880 bytes
Adapter: Dual Adapter
HP 1280GB MLC PCIe ioDrive Duo for ProLiant Servers, Product Number:641027-B21, SN: XXXXX
ioDrive Duo HL, PN:00190000108
External Power: NOT connected
PCIe Bus voltage: avg 12.00V min 11.93V max 12.02V
PCIe Bus current: avg 0.93A max 1.87A
PCIe Bus power: avg 11.17W max 22.36W
PCIe Power limit threshold: 24.75W
PCIe slot available power: unavailable
Connected ioMemory modules:
fct51: Product Number:641027-B21
fct52: Product Number:641027-B21
fct51 Attached as 'fct51' (block device)
ioDIMM 640, SN: XXXXX
ioDIMM 640, PN:00277100605
Located in slot 0 Upper of ioDrive Duo HL SN: XXXXX
Powerloss protection: protected
Last Power Monitor Incident: 33703 sec
PCI:33:00.0, Slot Number:9
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:176f
Firmware v6.0.0, rev 107007 Public
512.00 GBytes block device size
Format: v500, 125000000 sectors of 4096 bytes
PCIe slot available power: unavailable
Internal temperature: 41.34 degC, max 42.33 degC
Internal voltage: avg 1.02V, max 1.03V
Aux voltage: avg 2.47V, max 2.47V
Reserve space status: Healthy; Reserves: 100.00%, warn at 10.00%
Rated PBW: 10.00 PB, 98.96% remaining
Lifetime data volumes:
Physical bytes written: 103,651,963,293,456
Physical bytes read : 127,186,509,181,944
RAM usage:
Current: 53,448,192 bytes
Peak : 53,448,192 bytes
fct52 Attached as 'fct52' (block device)
ioDIMM 640, SN: XXXXX
ioDIMM 640, PN:00277100605
Located in slot 1 Lower of ioDrive Duo HL SN: XXXXX
Powerloss protection: protected
PCI:34:00.0, Slot Number:9
Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:176f
Firmware v6.0.0, rev 107007 Public
512.00 GBytes block device size
Format: v500, 125000000 sectors of 4096 bytes
PCIe slot available power: unavailable
Internal temperature: 38.39 degC, max 38.88 degC
Internal voltage: avg 1.00V, max 1.01V
Aux voltage: avg 2.46V, max 2.46V
Reserve space status: Healthy; Reserves: 100.00%, warn at 10.00%
Rated PBW: 10.00 PB, 98.98% remaining
Lifetime data volumes:
Physical bytes written: 102,299,237,920,520
Physical bytes read : 126,266,803,141,400
RAM usage:
Current: 53,489,792 bytes
Peak : 53,489,792 bytes