Quantifying the benefits of using EMC Enterprise Flash Drives in DB2 9.7 for Linux, UNIX, and Windows OLTP environments

How OLTP workloads perform on EMC Enterprise Flash Drives compared to Fibre Channel hard disk drives

Flash drive technology, also referred to as solid state drive (SSD) technology, was introduced more than three decades ago. However, until recently, the high cost-per-gigabyte and limited capacity of these drives restricted deployment to niche markets or military applications. But recent advances in Flash drive technology and economies of scale have driven down the cost, and have made Flash drives a viable storage option for many I/O-intensive enterprise applications.

With EMC's addition of Flash drives to Symmetrix storage arrays, organizations can now take advantage of ultra-high performance, optimized for the highest-level enterprise requirements. Flash storage is ideally suited for applications with high transaction rates and those requiring the fastest possible retrieval and storage of data, such as on-line transaction processing (OLTP) applications.

This article examines the performance of an OLTP workload with an IBM® DB2® 9.7 for Linux®, UNIX®, and Windows® database on Flash drives versus conventional hard disk drives. It also provides information on how you can integrate Flash drives into your storage infrastructure so you can immediately take advantage of this technology to improve database performance and increase productivity.

Roger E. Sanders (sanders_roger@emc.com), Consultant Corporate Systems Engineer, EMC Corporation

Roger E. Sanders is a Consultant Corporate Systems Engineer with the Integrated Customer Operations team at EMC Corporation. He has eight years of experience in the storage industry and he has been working with DB2 for Linux, UNIX, and Windows (LUW) since it was first introduced on the IBM PC. Roger has written articles for IDUG Solutions Journal and Certification Magazine, authored DB2 tutorials for IBM's developerWorks Web site, presented at several International DB2 User's Group (IDUG) and regional DB2 User's Group (RUG) conferences, taught classes on DB2 Family Fundamentals and DB2 LUW Database Administration, and is the author of 19 books on DB2 LUW and one book on ODBC. Roger is also a member of the DB2 Certification Exam development team and the author of a regular column (Distributed DBA) in IBM Data Management Magazine. In October 2008 and again in April 2010, Roger received an IBM Information Champion award for his exceptional contributions to the Information Management community; in April 2010 he was also recognized as an IBM developerWorks Contributing Author.


developerWorks Master author level
        2

29 April 2010

Introduction

Database performance has long been constrained by the I/O capability of hard disk drives (HDDs). The reason is that HDD performance is limited by mechanical constraints such as head movement (seek time) and rotational latency. And while the capacity of HDDs has grown exponentially, the speed of these devices has not significantly increased. In fact, over the past 25 years, the rotational speeds of HDDs has only increased from 3,600 revolutions per minute (RPM) to 15,000 RPM, yielding a meager 4x improvement in input/output operations per second (IOPS)

Enterprise Flash Drives (EFDs), also known as solid state drives (SSDs), contain no moving parts and therefore are not constrained by seek time or rotational latency. This lack of moving parts dramatically improves the ability of ESDs to deliver a significant amount of IOPS with very low response times. In fact, Symmetrix storage arrays populated with EFDs can deliver single-millisecond application response times and up to 30 times more IOPS than arrays populated with traditional Fibre Channel (FC) HDDs. And, because there are no mechanical components, EFDs consume up to 98 percent less energy per I/O than FC HDDs.

With the release of DB2 9.7, the Symmetrix VMAX™, and EFDs, both EMC and IBM saw an opportunity to work together to determine how a DB2 9.7 for Linux, UNIX, and Windows (LUW) database would perform when deployed on EFDs. This article describes the testing that was done to make this determination and presents the results that were obtained. The article also discusses database environments where EFDs might prove beneficial.

Characteristics that affect HDD performance

Fibre Channel HDDs have been the backbone of enterprise storage arrays for many years. And while these drives are growing very quickly from a capacity standpoint, they are not getting much faster in terms of RPMs. In fact, the fastest HDD speed has been 15,000 RPM for many years and there does not appear to be a faster speed on the horizon.

Aside from RPMs, there are a number of characteristics that can affect HDD performance. Specifically, these characteristics are:

  • Actuator Positioning (Seek Time). This is the time it takes the actuating mechanism to move the heads from their present position to a new position. Seek time typically averages a few milliseconds and is dependent upon the drive type. For example, a 15,000 RPM drive has an average seek time of approximately 3.5 milliseconds for reads and 4 milliseconds for writes. (The full disk seek time is 7.4 milliseconds for reads and 7.9 milliseconds for writes.)
  • Rotational latency. This is the time it takes to correctly position the platter underneath the head so that the desired data can be accessed. (The average rotational latency is one-half of a revolution of the disk platter.) In the case of a 15,000 RPM drive, this is approximately 2 milliseconds.
  • Areal density. This is a measure of the number of bits of data that can be stored on a given surface area of the disk. The greater the density, the greater the amount of data that can be read from the platter as it passes under the head.
  • Interface speed. On a Symmetrix array, this is the time it takes to transfer data from a physical HDD to the Symmetrix cache. Delay caused by this is typically very small — on the order of a fraction of a millisecond.
  • Drive cache capacity and algorithms. This is the amount of cache available on the drive, as well as the set of algorithms that are used to read and write data. Together, drive cache and algorithms work to improve the transfer of data in and out of the drive and to make parity calculations for RAID 5 implementations.

To summarize, delay caused by the movement of the disk head across the platter surface is referred to as seek time and the time associated with a data track rotating to the required location under the disk head is referred to as rotational latency or delay. When combined, the cache capacity on the drive, the disk algorithms used, the interface speed, and the areal density, produce a disk transfer time. The time it takes to complete an I/O request (referred to as disk latency) is a combination of seek time, rotational latency, and disk transfer time.

Data transfer times are typically fractions of a millisecond, so rotational latency and seek time are the primary sources of disk latency on a physical HDD. Even though rotational speeds of disk drives have increased over time from top speeds of 3,600 RPM, to 7,200 RPM, to 10,000 RPM, and finally to 15,000 RPM, rotational delay still averages a few milliseconds. Therefore, seek time continues to be the largest source of latency in HDDs.


EMC's Enterprise Flash Drives

EMC's Enterprise Flash Drives (EFDs) are constructed with nonvolatile semiconductor NAND Flash memory and are packaged in the standard 3.5-inch disk drive form factor that is used in Symmetrix DMX-4 and VMAX drive shelves. Unlike FC and Serial ATA (SATA) HDDs, which use spinning magnetic media to store digital information, EFDs are semiconductor-based block storage devices that behave as virtual HDDs that can be accessed via a traditional storage interface such as Fibre Channel. Because EFDs have no moving parts, they are not constrained by seek time or rotational latency in the way that FC and SATA HDDs are. As a result, EFDs provide greater performance and efficiency than similarly sized FC and SATA HDDs. In fact, in a Symmetrix storage array, one EFD can deliver IOPS equivalent to thirty 15,000 RPM hard disk drives with a response time of approximately 1 millisecond. EFDs are also able to handle burst writes better than HDDs and can sustain lower response times — even under heavy workloads. Table 1 compares several aspects of an HDD RAID group to an EFD RAID group.

Table 1. HDD RAID groups versus EFD RAID groups
HDD RAID groups — RAID 5 (7+1)EFD RAID groups — RAID 5 (7+1)
Average effective application response time is approximately 5-10 msConsistent effective application response times of 1 ms
Performance varies based on data location and spindle contentionLittle performance variance based upon data location
Fragmentation affects performance (additional seeks required for random access)Fragmentation-agnostic (random and sequential accesses are the same)
Highly dependent on workload "cache-friendliness"Effective regardless of workload "cache-friendliness"

The enterprise-class EFDs used in Symmetrix storage arrays differ significantly from the solid state technology used in consumer electronics, particularly in terms of performance and reliability. Standard Flash-based SSDs, such as those found in laptops, have not been optimized for writes. This means that while the read speed is much faster than a standard HDD, the write speed is about the same. Using innovative techniques and components, EMC's EFDs have been optimized for writes and longevity, The result is a significant improvement in EFDs write times, as compared to HDDs. With EMC EFDs, writes are buffered in their internal DDR SDRAM cache before they are destaged to the NAND Flash. This minimizes the number of small writes that are presented to the device, which in turn optimizes performance and helps extend the usable life of the drives.

EMC EFDs also employ a technique that ensures that all cells in the Flash memory are used evenly. This minimizes the risk of "wear-out" that is common to less advanced Flash devices. Using advanced algorithms and block management, combined with a pool of cells that are never exposed to the host, EMC EFDs also balance erase and rewrite operations to sustain maximum bandwidth and reliability. (This too helps extend the usable life of the drive.) Additionally, EMC EFDs safeguard data with full round-trip error correcting code (ECC) data integrity protection and destage power backup. This ensures that, once stored, data is either returned unmodified, or is identified as being "suspect" — much like data on HDDs is safeguarded.


Database workloads best suited for EFDs

Because of the gap between HDD and CPU speeds, DB2 LUW experts at IBM conservatively estimate that, when processing database workloads, between eight and 20 HDDs are needed per CPU (or core) to avoid heavy I/O wait. (The type of CPU used, IOPS needed, and throughput desired are the factors that determine exactly how many HDDs are required.) For most database workloads, 15,000 RPM FC HDDs deliver random I/O performance of approximately 150 - 180 IOPS with a latency of about 5 -7 milliseconds and a sequential scan bandwidth of about 30 - 60 MB/sec. Therefore, in many customer environments, to maintain the high IOPS rate needed to service database workloads and to provide reasonable response times, less data is physically placed on each HDD used. As a result, a significant amount of space on HDDs (greater than 50% in many cases) is never used or under-utilized. And this type situation has only worsened as the density and capacity of HDDs have increased.

On Symmetrix storage systems, all I/O requests from a host are serviced from global cache. Under normal circumstances, a write request is written directly to cache and incurs no delay because physical disk access is not required. Similarly, if a read request is received and the data requested is already in global cache (either because of a recent read or write, or as a result of a sequential prefetch), the request is serviced immediately without requiring disk I/O. (Any read that gets serviced directly from cache without any additional disk I/O is referred to as a "read hit.") If the data requested is not in global cache, it must be retrieved from disk and placed into cache before the Symmetrix will respond to the request. (This is referred to as a "read miss.") When HDDs are used, a read miss will almost always result in longer response times due to the fact that additional disk I/O is required to service the request.

Because workloads with high Symmetrix cache read-hit rates are serviced at memory access speed, storing the data needed on EFDs may not result in a significant increase in performance. On the other hand, workloads with low Symmetrix cache read-hit rates that exhibit random I/O patterns and consist of small I/O requests (of up to 16 KB), and that require high transaction throughput, will benefit most from the low latency of EFDs. Therefore, from a database perspective, on-line transaction processing (OLTP) environments are ideal candidates for EFD data storage.

An OLTP environment is a database environment that facilitates and supports transaction-oriented workloads, which typically consist of numerous data entry and retrieval operations. Examples of OLTP environments include banking applications, order processing systems, and airline reservation systems. OLTP environments typically have a significant amount of random I/O. As a result, a high number of physical disk reads are usually required to ensure consistently low transaction response times. Therefore, OLTP transactions often spend a great deal of time waiting on disk I/O. And the wait time is considerably longer for HDDs than for EFDs due to the mechanical delays that are inherent to HDDs.

When used in OLTP environments, EFDs offer the following benefits over traditional HDDs:

  • Substantially fewer physical drives required
  • Better I/O and throughput performance
  • Substantial reduction in response time
  • Less energy consumption
  • Reduced amount of lab space needed

Quantifying the benefits of using EFDs in OLTP environments

To characterize the benefits of using EMC EFDs in an OLTP environment, a database workload consisting of 60% random reads and 40% random writes was ran against two identical DB2 9.7 LUW databases. One of the databases had its data stored on EFDs; the other had its data stored on similarly configured FC HDDs. As the workloads were run, performance data was collected, and eventually, the results of the collected data were compared.

Test environment

The configuration used to characterize the benefits of using EMC EFDs in a DB2 9.7 LUW OLTP environment is summarized in Table 2.

Table 2. Test environment
ComponentDescription
Server:One (1) IBM p520 running AIX 6.1 (64 bit) and DB2 9.7 for Linux, UNIX, and Windows.
Storage:One (1) Symmetrix VMAX containing two engines and 128 GB of global cache (mirrored), running Enginuity™ 5874.This Symmetrix VMAX was configured as follows:

  • Tier 0 — Enterprise Flash Drives (EFDs):
    • Eight (8) 400 GB drives in a RAID 5 7+1 configuration; 1 spare
    • Thirty-two (32) devices (presented to the host as 32 individual LUNs) — 16 to house the test database and 16 to serve as a repository for a backup image (resulting in approximately 1.4 TB of space available for the database and 1.4 TB of space for a backup image)
  • Tier 1 — Fibre Channel Hard Disk Drives (FC HDDs):
    • Eight (8) 450GB 15K RPM drives in a RAID 5 7+1 configuration
    • Thirty-two (32) devices (presented to the host as 32 individual LUNs) — 16 to house the test database and 16 to serve as a repository for a backup image
Note: Each device was approximately 80,625 MB in size.

In addition, Dynamic Cache Partitioning (DCP) was enabled and set to 10%. This was done to ensure that the entire database would not fit in cache, thereby forcing disk I/O to occur.
Connectivity:Connectivity between the IBM p520 and the Symmetrix VMAX was provided via four (4) host initiators that were connected to four (4) 4 GB front-end FA ports. PowerPath® version 5.3 HF1 was used to provide multipathing between the server and the storage.

Test scenario

Once the test environment was established, I/O testing was conducted and some basic system tuning was performed by an EMC Performance Engineer to ensure that the hardware had been configured to deliver optimal I/O performance. Then, a 1.2 TB DB2 9.7 LUW database was created on the Tier 0 storage (EFDs). The database was populated, and then the DB2 instance and database were configured for optimum performance by a member of the DB2 LUW Performance Engineering team at the IBM Toronto Lab. It is important to note that file system caching was disabled by specifying the NO FILE SYSTEM CACHE clause when each table space in the database was created. In addition, Deep Compression was used to compress both table and index data.

After being built, populated, and tuned, the database was backed up so that the testing environment could be returned to the same "baseline" state prior to each test run.

Once the testing environment was ready and a backup image of the database had been created, IBM's DB2 Transaction Workload (DTW) benchmarking tool was used to generate an OLTP workload that was evenly distributed across all available system resources. (The workload of the DTW benchmark is characterized by having very high I/O transaction rates and a large degree of concurrent users. For our tests, we simulated 400 concurrent users running queries that were a mixture of select, insert, update, and delete operations. As mentioned earlier, the actual ratio of read to write operations performed was 60/40 in favor of reads.) Each test began with a 20-minute "ramp-up" time and continued with a 30-minute "run time." During the 30-minute run time, monitoring was performed and data was collected on both the server and the Symmetrix VMAX. Once Symmetrix monitoring showed 100% utilization on the EFDs and DA processors used, the database backup image was used to duplicate the test environment on Tier 1 storage (HDDs), and testing/monitoring was conducted again.

After monitoring data was collected for both the EFD and HDD configurations, the results were compiled to produce the data in the Test results section below.

Test results

Figure 1 shows a screenshot of a utilization map that was captured while the DTW benchmark was run against the DB2 9.7 LUW database that was stored on EFDs. The screenshot was taken after the 20-minute ramp-up time and about halfway through the 30-minute run time.

Figure 1. Map of EFD drive utilization during IBM DTW benchmark testing
Screensot of a utilization map captured while the DTW benchmark was run against the DB2 9.7 LUW database that was stored on EFDs

The map shown in Figure 1 was generated using an EMC internal performance analysis tool that shows a "heat map" of the drive utilization at the Symmetrix back end. Each physical disk drive in the array is represented by a single rectangle and that rectangle corresponds with the physical location of the drive in the array. The rectangle is color-filled to represent the utilization of the physical drive as shown in the color legend on the left side of the figure. Disks that are unused, or that are used very little, are shown as shades of blue; disks that are utilized in the 40-60% range are presented as shades of green; and highly utilized disks are shown in shades of yellow, orange, and red.

As illustrated by the utilization map screenshot in Figure 1, monitoring showed 100% utilization on the EFDs and DA processors. (Other monitoring showed that the FA channels were reaching 70% utilization.) Figure 2 shows the IOPS that were measured on both the front end and back end during the same test run.

Figure 2. EFD IOPS measured during IBM DTW benchmark testing
Screensot showing EFD IOPS measured during IBM DTW benchmark testing

As Figure 2 illustrates, approximately 30,000 IOPS were measured on the front end, which is an excellent number considering only eight (8) EFDs were used. Data captured by the DTW benchmarking tool during this test run showed that the database was processing an average of 302.93 transactions per second.

Figure 3 shows a screenshot of a utilization map that was captured while the DTW benchmark was run against the same database after it was moved to FC HDDs. Once again, this screenshot was taken after the 20-minute ramp-up time and about halfway through the 30-minute run time.

Figure 3. Map of HDD drive utilization during IBM DTW benchmark testing
Screensot of a utilization map captured while the DTW benchmark was run against the DB2 9.7 LUW database that was stored on FC HDDs

As shown in Figure 3, monitoring showed 100% utilization on the FC HDDs and DA processors. Figure 4 shows the IOPS that were measured on both the front end and back end during this test run.

Figure 4. FC HDD IOPS measured during IBM DTW benchmark testing
Screensot showing FC HDD IOPS measured during IBM DTW benchmark testing

As Figure 4 illustrates, this time approximately 4,000 IOPS were measured on the front end. And data captured by the DTW benchmarking tool during this test run indicated that the database was processing an average of 36.04 transactions per second.

With the only difference being the storage devices that were used to house the database, measured I/O rates on the Symmetrix VMAX went from 3,974 IOPS (which resulted in 36.04 transactions per second) on Tier 1 (FC HDDs) to 30,047 IOPS (which resulted in 302.93 transactions per second) on Tier 2 (EFDs) —a 9X improvement in performance! In both cases, Symmetrix monitoring showed 100% utilization on the drives and DA processors. Therefore, testing demonstrated that, when used in conjunction with DB2 9.7 LUW in OLTP environments, EMC Enterprise Flash Drives offer all of the benefits that were described above in the section titled Database workloads best suited for EFDs.


Summary

CPU speeds have outpaced the performance capabilities of traditional hard disk drives, and latencies associated with spinning platters and moving heads limit the speed at which data stored on traditional HDDs can be accessed. EMC EFDs' near instantaneous data access overcomes these limitations, and greatly improves I/O performance. With EFDs, magnetic disk drive technology no longer defines the performance boundaries for mission-critical storage environments. In addition, the costly approach of spreading workloads over dozens or hundreds of underutilized disk drives is no longer necessary with EFDs.

This article provided detailed analysis of a DB2 9.7 LUW OLTP workload running on a Symmetrix VMAX using EFD technology. The testing that is described demonstrated that EFDs provide a substantial improvement (approximately 9X) in I/O performance when used in an OLTP database environment. This performance increase translates to increased business output and results in the need for fewer drives. This, in turn, results in reduced energy consumption, reduced floor space requirements, and overall cost savings.

Acknowledgements

The author would like to thank Bill Brown, Don Fried-Tanzer, and John Adams for their help in configuring, setting up, and tuning the hardware that was used for testing; Sunil Kamath and John Tran for their help with designing, building, populating, and tuning the DB2 9.7 DB2 database used, as well as installing, configuring, and running the DTW test kit; Richard Otte for monitoring the Symmetrix VMAX during testing and providing screenshots and data on front-end, drive, and back-end performance; and Berni Schiefer for reviewing and providing feedback on this article.

Resources

Learn

Get products and technologies

  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=485760
ArticleTitle=Quantifying the benefits of using EMC Enterprise Flash Drives in DB2 9.7 for Linux, UNIX, and Windows OLTP environments
publish-date=04292010