Share via


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.

 

clip_image002

clip_image004

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