January 13th, 2014 by Kevin OBrien
OCZ ZD-XL SQL Accelerator Review
The OCZ ZD-XL SQL Accelerator is a duty-specific combination of hardware and software designed explicitly, as the name implies, to accelerate SQL databases. ZD-XL leverages an OCZ Z-Drive R4 RM series PCIe application accelerator as the flash storage layer combined with a host application to either accelerate a SQL volume on hard drives or accelerate a SQL database by placing it in flash. Its biggest differentiator though is a third option; if the situation calls for it ZD-XL can do both - accelerate a SQL volume on the flash card and cache large databases that reside on HDDs.
To better understand ZD-XL, let's break the solution into the core components of hardware and software. On the hardware side, as noted, the ZD-XL leverages OCZ's current line of R4 PCIe application accelerators that have the built-in capacitors to prevent loss of data in an unexpected power loss. Structurally there are a few modest design improvements, but in total the card is more or less the same as it's sold bare and in the ZD-XL configuration. On the software side, the card has a specialized firmware that works with a host system driver and ZD-XL software to accelerate SQL workloads. The net benefit is an easy to install and deploy solution that works into an existing SQL deployment with no need to replace what's currently operating. ZD-XL may also be deployed in an HA configuration across multiple hosts, should a more comprehensive solution be required.
In terms of configuring the solution for use, ZD-XL is one of the most flexible acceleration tools in the market. To speed the data path of SQL transactions, ZD-XL does a few things. First off, flash volumes can be created on the card to house tables like tempDB and log files to get the highest IO performance boost from the flash. This is a novel feature that is generally not available in other caching solutions. Most caching applications require consuming the entire cache pool available. Of course ZD-XL's bread and butter is caching hot spots present in databases that are either too large to fit within flash in their entirety, or there are other issues which make placing the databases entirely in flash impractical. The cache algorithm has been optimized for both OLTP and OLAP use cases and is designed to maximize hit ratio for these very different IO patterns. ZD-XL can be proactive as well, storage admins get the benefit of the option to pre-warm the cache when a high-demand workload can be anticipated. In total, the solution is fluid and can be deployed to meet the needs of a variety of SQL-centric environments.
ZD-XL is available in three capacities including a half height, half length form factor in 600GB and full height, 3/4 length 800GB and 1.6TB capacities. The solution comes with a three year warranty and dedicated FAE/FSE support. Our review unit is the 1.6TB capacity.
OCZ ZD-XL Specifications
- ZD-XL Half Height, Half Length
- ZD-XL Full Height, 3/4 Length
- ZD-XL Half Height, Half Length
- NAND: Multi-Level Cell (MLC)
- Interface: PCI Express Gen. 2 x 8
- Power Consumption
- Idle: 14.5W
- Active: 16W
- FH 3/4L
- Idle: 23W
- Active: 26W
- Operating Temperature: 0°C ~ 70°C
- Storage Temperature: -45°C ~ 85°C
- Airflow Requirement: 300 Linear Feet/Minute (LFM)
- Power Fail Protection: DataWrite Assurance Technology (capacitance power back-up to ensure data hardening)
- Data Fail Recovery: Recovers data from up to one NAND flash block per NAND controller
- Data Path Protection: ECC: Up to 55 bits correctable per 512-byte sector; Data path parity protection
- Data Reliability: Read Unrecoverable Bit Error Rate (UBER) 10e-17
- Data Encryption: 128-bit AES-compliant
- Power Requirement: PCI Express 12V & 3.3V
- Services & Support: 3-Year Warranty; Dedicated FAE/FSE support (includes PM/engineer support through validation cycle)
Design and Build
The OCZ ZD-XL Accelerator utilizes industry-proven Z-Drive R4 with a design overhaul to make it stronger and improve cooling. Both the ZD-XL and Z-Drive R4 use the same twin-board design, although the ZD-XL now includes a sturdy metal cover to improve rigidity as well as shed heat from the board into the server's airflow path. It also gives the product a huge facelift, showing a greater attention to detail as the PCIe Application Accelerator matured since it first launched.
The internal design of the OCZ ZD-XL incorporates eight LSI SandForce SF-2500 controllers, MLC NAND offering up to 40PB of endurance, and power-fail capacitors to flush data in-flight to NAND in the event of a power disruption. The 1.6TB card we're reviewing is a full-height, 3/4-length design, while 300GB and 600GB versions are offered which are a more compact HHHL design.
After installing the software, the card is immediately identified and users are driven through a wizard to configure ZD-XL. The configuration process is designed to be deployed by DBAs that understand SQL and how it's used in their environment. Terminology is therefore customized for this audience and SQL DBAs can be more concerned about performance acceleration than being experts in flash storage and cache tuning.
Users are guided through partitioning the card into part flash volume and part cache volume. This process is very unique in the PCIe Application Accelerator, which have traditionally been an all or nothing affair where the SSD can't be used for both purposes at the same time.
After partitioning the card users are taken through a step where you select the disk volume you want to cache and to select the database activity profile to hone its caching algorithm between OLTP and OLAP.
ZD-XL offers a pre-warm feature that lets the admin create an analysis file for each volume that can track activity over a period of time. When the pre-warm routine is run, the cache gets heated up with prospective hot data before the data is actually hot, giving the volume a little boost in performance immediately rather than waiting for the background algorithms to take effect.
While the pre-warm feature is nice it falls a little bit short on the management side, there's no scheduling option and it's not possible to run more than one warmup routine against a volume. While not official yet, word from OCZ is that they are aware of these feature requests and that we'll likely see an updated pre-warm tool that is more robust in upcoming revisions. Users can also select a "warm volume" option which is a more broad approach to cache the entire volume without having to wait to analyze database activity.
The StorageReview Enterprise Test Lab allows us to provide relevant and unbiased benchmarks of enterprise storage devices by establishing a testing environment comparable to what IT administrators encounter in real datacenter and field deployments. The Enterprise Test Lab incorporates a variety of servers, networking, power conditioning, and other infrastructure to design real-world configurations that accurately reflect the performance of storage devices during reviews.
We incorporate details about our lab environment and testing protocols into reviews so that storage administrators and those responsible for equipment acquisition can fairly gauge the conditions under which we have achieved the published results. None of our reviews are paid for or overseen by the manufacturer of equipment we are testing. Additional details about the StorageReview Enterprise Test Lab and an overview of its networking capabilities are available on those respective pages.
- 2 x Intel Xeon E5-2690 (2.9GHz, 20MB Cache, 2-cores)
- Intel C602 Chipset
- Memory - 256GB (16 x 16GB) 1333Mhz Micron DDR3 Registered RDIMMs
- Windows Server 2012 Standard
- 100GB Micron RealSSD P400e Boot SSD
- LSI 9211-4i SAS/SATA 6.0Gb/s HBA (For boot SSDs)
- Mellanox ConnectX-3 InfiniBand PCIe 3.0 Adapter
Application Performance Analysis
In the enterprise market there is a huge difference between how products claim to perform on paper and how they perform in a production environment. We understand the importance of evaluating storage as a component of larger systems, most importantly how responsive storage is when interacting with key enterprise applications. To this end, we've rolled out application tests; in this review we've included our new SQL Server OLTP Benchmark.
To measure the caching and raw flash performance of the OCZ ZD-XL, we created a SQL Server test that leveraged a 1.1TB (5,000 scale) SQL Server database and measured the performance from a light load of 250 Virtual Users (VU) up to a heavy load of 20,000 VU. The baseline number in this test is an array of 16 900GB WD Xe 10K SAS drives in RAID6, which scaled from 51.8TPS at 250VU up to 719TPS at 15,000VU before dipping down to 592TPS at 20,000VU. With ZD-XL introduced to the environment with 1.2TB of the drive allocated to caching, performance scaled from 51.8TPS at 250VU up to 1,244TPS at 20,000VU. The caching really started to show an impact at a load of 2,500VU where it increased performance by 13% and further increased to an improvement of 109% at a load of 20,000VU. Measuring the performance of the database sitting completely in flash, we measured performance scaling from 52.7TPS at 250VU up to 4,205TPS at 20,000VU.
Switching our focus to average latency our baseline hard drive array scaled from 9.4ms at 250VU and spiked to 29 seconds at 20,000VU. After introducing the OCZ ZD-XL, that average latency dropped to 9.1ms at 250VU and scaled up to 11.3 seconds at 20,000 VU. With the database sitting completely inside of flash, things remained much, much quicker on the scale of 0.3ms at 250VU and increasing to just 1.4ms at 20,000VU.
Enterprise Synthetic Benchmarks
Prior to initiating each of our enterprise synthetic benchmarks, StorageReview’s Enterprise Test Lab preconditions the device into steady-state under a heavy load of 16 threads with an outstanding queue of 16 per thread. Then, the devices are tested in set intervals with multiple thread/queue depth profiles to show performance under light and heavy usage.
Preconditioning and Primary Steady-State Tests:
- Throughput (Read+Write IOPS Aggregate)
- Average Latency (Read+Write Latency Averaged Together)
- Max Latency (Peak Read or Write Latency)
- Latency Standard Deviation (Read+Write Standard Deviation Averaged Together)
Our analysis of the OCZ ZD-XL SQL Accelerator as a flash device includes three profiles comparable to our past enterprise storage benchmarks.
- 4k Random
- 100% Read and 100% Write
- 8k Sequential
- 100% Read and 100% Write
- 128k Sequential
- 100% Read and 100% Write
The OCZ ZD-XL SQL Accelerator offers some unique benefits compared to other caching products on the market, which have only let users configure an entire PCIe SSD as a flash device or a caching device, not both at the same time. OCZ was able to design that dual-purpose capability into the ZD-XL, allowing administrators to use some of it to caching certain databases, while allowing the user to still allocate some of it to a flash volume. To show how well the ZD-XL performs in that regard, we measured the performance of a 400GB section of the drive using our synthetic workload generator.
After a lengthy preconditioning period, we measured a random 4K read speed of 171,984 IOPS and a write speed of 140,778 IOPS with a heavy load of 16T/16Q. Average latency during this test measured just 1.5ms read and 1.8ms write. With a sequential 8k workload, we measured 161,070 IOPS read and 92,962 IOPS write. In a large-block 128k sequential workload we saw transfer speeds in excess of 2.1GB/s read and 1.1GB/s write.
OCZ's ZD-XL SQL Accelerator offers a unique take when it comes to accelerating a very specific workload. In this case it's SQL, but long-term the software could be adapted to accelerate a variety of applications. ZD-XL has two components, the PCIe flash card and the software. While the R4 flash card is very capable on its own, laying in the ZD-XL software is the key differentiator. The software is flexible enough to let storage admins create volumes directly on the card for acceleration at the same time as accelerating SQL databases that are located on higher capacity, but much slower hard drives.
The GUI lets admins quickly decide how to configure ZD-XL for their specific needs; in our environment we set up the software a few times and managed to move along pretty quickly without needing to ask for support. Once ZD-XL is configured, there's little need to revisit the GUI regularly, unless to execute advanced features like the pre-warming of a database. Overall the GUI is functional but a little bit clunky, as these things tend to be, until the user has logged some time on the system. For their part OCZ recognizes the shortcomings and has shown us a preview of what's to come in terms of new functionality and features. Many of these updates address UI and enhancing existing features like the pre-warm option.
Turning to SQL performance, which is clearly the key with ZD-XL, in our OLTP environment we saw an improvement over a WD 10K SAS array by more than 100%. Given going from box to operational is under 30 minutes, there's reason for SQL admins to be enthusiastic about what ZD-XL can offer. Our testing showed the gains scaled under load, which highlights the benefits of injecting just a little bit of flash where HDDs start showing signs of bogging down under the weight of SQL transactions. Of course for those cases where a doubling in performance isn't good enough, ZD-XL allows for a database to be moved entirely within the flash on the PCIe card where performance gains are exponentially higher than just a HDD array.
- Easy to configure
- Can both cache a database and store a database entirely in flash
- SQL performance doubled with ZD-XL
- UI is a little bit clunky
- Pre-warm feature needs more flexibility
The OCZ ZD-XL SQL accelerator gives businesses a big bump in SQL performance with modest changes to their existing environment. Performance gains for SQL doubled over 10K HDDs and databases that need more performance can be pinned to the PCIe flash. Given the ease of deployment and flexibility in configuration, ZD-XL is a worthy companion for those looking to get more out of their HDD-based SQL environments.