Faster DB2 Performance with IBM FlashSystem

Abstract

This IBM® Redbooks® Solution Guide provides an overview of IBM FlashSystem™ running in a DB2® environment with a focus on performance and the benefits gained from running DB2 with Flash storage.

I/O performance is critical to DB2 operations. Although you can improve performance by using faster processors, faster systems, and advanced DB2 capability, data must still be read from and written to storage. Traditional hard disk drive (HDD) storage cannot provide the speed that is required by many applications. Many solid-state device (SSD) solutions add latency because software is required to manage access to flash storage and access can take several milliseconds (ms).

IBM FlashSystem products provide extremely fast external storage, with low latency of approximately 90 μs write time and 135 μs read time. You can move your most I/O-intensive operations to FlashSystem storage for an immediate increase in performance, while reducing costs for power, cooling, and administration.

Contents


This IBM® Redbooks® Solution Guide provides an overview of IBM FlashSystem running in a DB2® environment with a focus on performance and the benefits gained from running DB2 with Flash storage.

I/O performance is critical to DB2 operations. Although you can improve performance by using faster processors, faster systems, and advanced DB2 capabilities, data must still be read from and written to storage. Traditional hard disk drive (HDD) storage cannot provide the speed that is required by many applications. Many solid-state device (SSD) solutions add latency because software is required to manage access to flash storage and access can take several milliseconds (ms). FlashSystem products provide a hardware-only data path; field-programmable gate array (FPGA), that provides much faster access than any of these other solutions.

IBM FlashSystem products provide extremely fast external storage, with low latency of approximately 90 μs write time and 135 μs read time. You can move your most I/O-intensive operations to FlashSystem storage for an immediate increase in performance, while reducing costs for power, cooling, and administration. The following figure shows some of the advantages of replacing slower HDD storage and SSD storage devices with FlashSystem storage.


Figure 1. Possible savings when FlashSystem storage products replace disk storage


Did you know?

IBM FlashSystem storage products offer many benefits:
  • The extreme performance of FlashSystem storage helps reduce I/O bottlenecks and boost the speed and frequency of database transactions.
  • FlashSystem products have low-power consumption, a small footprint, and can be easily integrated into your existing environment.
  • A hardware-only data path makes FlashSystem products much faster than many other flash and SSD storage devices.


Business value

IBM FlashSystem storage products have extremely low latency and can be integrated into your existing environment to minimize I/O wait time and increase database performance. The following figure shows the key benefits of FlashSystem products.


Figure 2. Key advantages of FlashSystem products

FlashSystem products offer these advantages:
  • Extreme Performance. Enables the business to unleash the power of performance, scale, and insight to drive services and products to market faster
  • IBM MicroLatency. Delivers response time in microseconds to accelerate critical applications to achieve competitive advantages
  • Macro Efficiency. Driven by the consolidation of hardware and software, deployment speed, efficient use of IT staff, and power and cooling savings
  • Enterprise reliability. Durable and reliable designs that use enterprise-class flash and patented data protection technology

Use FlashSystem products to improve response time, increase the number of transactions that can be processed, and save time when making copies of the database. You can reduce the total cost of ownership (TCO) with FlashSystem products by consolidating storage to reduce costs for administration and software licenses. FlashSystem products have low-power consumption, efficient cooling, and provide up to 48 TB of storage in a small, 2U design.

Decreasing application performance under heavy user loads is not a new story for most enterprises. In the last three years, there have been dramatic changes in demands placed upon database servers. While the number of users of database systems has increased, so has the average amount of data stored in databases. Additionally, the demand for more complex business analysis has increased the complexity of the work done by database servers. The combination of more users, a greater volume of data, and more complex queries has frequently resulted in slower database response. Administrators often consider two sources for database performance problems:
  • Server and processor performance. One of the first things that most IT shops do when performance decreases is to add processors and memory to servers or add servers to server farms. Adding servers and processors has a minimal impact on database performance and compounds the resources that are wasted as even more processing power waits on the same slow storage.
  • Tuning. Enterprises invest millions of dollars squeezing every bit of efficiency out of database programming. The software tools that assist programmers with the assessment of their code can cost tens of thousands of dollars. The personnel required for evaluating and iterating the code costs much more. In the last decade, many consulting firms have appeared that specialize in system tuning. In many cases, applications cannot support features that rely heavily on disk I/O. For example, developers often remove features that cause large queries and return large data sets in order to protect application performance.

In many cases, the money spent in these two pursuits can be significant; however, the return is often disappointing. Server performance and tuning alone do not often detect the true cause of poor database performance: the gap between processor performance and storage performance. Adding servers and processors will have minimal impact on database performance and will compound the resources wasted, as more processing power waits on the same slow storage. Tuning can result in performance improvements, but even the best code cannot make up for poor storage I/O. Frequently, features that rely heavily on disk I/O cannot be supported by applications. In particular, programs that result in large queries and those that return large data sets are often removed from applications in order to protect application performance.

When system administrators look to storage to resolve performance problems, they frequently try several approaches:
  • Increase the number of disks. Adding disks to just a bunch of disks (JBOD) or RAID configurations is one way to improve storage performance. By increasing the number of disks, the I/O from a database can be spread across more physical devices. This approach will increase the I/O capacity as I/O growth is a linear function when increasing the drive count, yet will have a trivial impact on the bottleneck if the performance issue is related to latency. You can scale I/O and bandwidth with disks if you have the space and budget to do so, but you cannot gain the benefit of microsecond latency no matter how many disks you add.
  • Move the most frequently accessed files to their own disk. This approach will deliver the best I/O available from a single disk drive. The I/O capability of a single HDD is extremely limited. At best, a single disk drive can provide hundreds of IOPS versus thousands of IOPS for FlashSystem storage. Latency is the measure of how long it takes for a single I/O request to be requested and completed from an application's point of view. FlashSystem combines the low latency of writes at 90 μs and reads at 135 μs, with over a million IOPS for FlashSystem 840, to provide extreme performance and MicroLatency for critical business needs. Moving frequently accessed files to their own disk is not always inexpensive or easy to implement because it requires tiering and analyzing workloads, tuning applications, and validating results. Depending on where the segregation happens (disk, SSD, or Flash), the results will vary on IOPS, bandwidth, and latency.

Another solution to the performance gap is to implement SSDs for the most frequently accessed database components. Many administrators use SSD storage with servers and might implement in-server Flash PCI Express (PCIe) cards. It is important to consider the performance and availability of various types of flash storage. It is also important to distinguish between FlashSystem products and the other types of flash storage available. For more information, see Flash or SSD? Why and When to Use IBM FlashSystem, REDP-5020, IBM Redpaper publication:
http://www.redbooks.ibm.com/redpieces/abstracts/redp5020.html?Open

While other components in the data chain move in terms of computation times and the raw speed of electricity through a circuit, hard drives move mechanically, relying on physical movement around a magnetic platter to access information. Form factor SSD technologies are limited by the speed of SCSI controllers, and many PCIe solutions require additional software to manage operations. FlashSystem products provide a hardware-only data path that provides much faster access than any of these other solutions.

When servers wait on storage, users wait on servers. This is I/O wait time. FlashSystem storage systems are designed to minimize I/O wait time by providing a fast, hardware-only data path. Although multiple HDDs can be stacked to obtain thousands of IOPS, this type of solution soon reaches a point of diminishing returns where the costs of power, floor space, and air conditioning become prohibitive. The following figure shows latency for flash products compared to other types of storage technologies.


Figure 3. Latencies seen in storage technologies


Solution overview

When I/O latency affects the speed of your applications, it is important to find and identify the causes of the I/O wait time.

While an SSD can speed up almost any database, it is most needed in installations where servers are experiencing I/O wait time. A number of elements are involved in server I/O. The PCI (or other) bus, host bus adapter, interface, storage network switch, RAID controller, and HDDs are all involved in every I/O operation between server and storage. Theoretically, any one of these points can cause an I/O bottleneck. In practice, however, the HDDs are the most likely culprit. Simply put, every component in the I/O process is solid state except for the HDDs. Therefore, when you identify I/O wait time, HDDs are the most likely cause. Disk latency, which is defined as a measure of the time delay from the time a disk I/O request is created until the time the disk I/O is completed, affects DB2 performance. Adding an SSD can significantly reduce I/O wait time.

Adding FlashSystem storage can reduce wait time even more, because there is no software to create latency in the data path.

Improving database performance

If you determine that your system has I/O subsystem problems, the next step is to identify which components of your IBM DB2 Server database are experiencing the highest I/O and, in turn, causing I/O wait time. Consider moving the following components to FlashSystem storage.

Entire database

There are some databases that should have all of their files moved to flash storage. These databases tend to have at least one of the following characteristics:
  • High concurrent access. Consider moving all data to FlashSystem for databases that are used by many concurrent users. This ensures that storage is not a bottleneck for the application and maximizes the utilization of servers and networks. I/O wait time will be minimized and servers and bandwidth will be fully utilized.
  • Frequent random accesses to all tables. For some databases, it is impossible to identify a subset of files that are frequently accessed. Many times, these databases are effectively large indexes.
  • Small to medium-sized databases. Given the fixed costs associated with buying RAID systems, it is often economical to buy FlashSystem storage to store small to medium-sized databases.
  • Large read-intensive databases. Given the fixed costs associated with architecting a RAID system for performance, or buying a large cache and buying a lot of spindles for striping, it is economical and much faster to buy a FlashSystem all-flash solution to accelerate large read-intensive databases. A single FlashSystem array can scale up to 48 TB of capacity in a 2U form factor.
  • Business critical applications, such as order entry, online banking, and other applications that require sub-second transaction rates among a high volume of concurrent users, can benefit from the improved read and write performance of IBM FlashSystem, which can meet the stringent I/O performance requirements these types of applications demand.

Preferred read

To accelerate larger portions of the database, you can use a technique that involves mirroring of the entire database, or portions of the database (indexes and some table spaces) and using the copy that resides on flash storage to be the primary or preferred read copy. Preferred read works this way:
  • One copy of the database volumes resides on traditional storage, and one copy resides on FlashSystem storage.
  • A mirroring system with synchronization is used to ensure that both copies are identical.
  • The data is read from FlashSystem storage.
  • Writes are done simultaneously to both copies of the mirror.

There are several ways to implement preferred read: IBM System Storage® SAN Volume Controller mirroring and Logical Volume Manager (LVM) mirroring.

IBM System Storage SAN Volume Controller is one way to easily and quickly introduce FlashSystem storage as a preferred read mirrored copy of the database. In this way, the operating system and the database do not know that there is an existing mirrored copy of the database. Only the SAN Volume Controller product knows and manages that relationship. All resources for managing the mirror and for enforcing the preferred read policy are handled at the SAN Volume Controller layer. No operating system resources are required for the additional workload. The SAN Volume Controller has the added benefit of completely virtualizing the storage environment, so that any changes to the underlying storage are completely hidden from the operating system and database. The SAN Volume Controller adds very little to the read and write latency profile, so you still benefit from ultra high-speed I/O from FlashSystem storage. The SAN Volume Controller mirroring is completely transparent to the database with no downtime.

IBM FlashSystem V840 Enterprise Performance Solution (FlashSystem V840) also provides all the capabilities and functionality noted above for the SAN Volume Controller and therefore can be used in the same way to configure a preferred read storage solution. For more details, see the Redbooks IBM FlashSystem V840 Product Guide at: http://www.redbooks.ibm.com/abstracts/tips1158.html?Open.

Logical Volume Manager mirroring uses the LVM in the IBM AIX® and Linux operating systems. When LVM mirroring is used, there is no requirement for the database to know or care about the relationship between the FlashSystem storage system and the traditional storage system. You can introduce mirroring through LVM completely transparently to the database with no downtime. There are additional operating system resources required to manage the increased CPU load and I/O load that are introduced, but the load can be managed. The synchronization process takes time, but once done, the preferred read system is in place automatically and without incurring downtime.

Transaction logs

The DB2 active log directory contains the log files required for data integrity and data recovery. Both committed transactions that need to be reapplied after a failure and partial transactions that need to be rolled back are supported by the DB2 transaction logs.

Because the transaction logs are a source of constant I/O during database operation, the transaction logs should be stored on the fastest storage possible. Writing a transaction log to FlashSystem storage is a natural way to improve overall database performance.

Temporary table spaces

DB2 uses system and user temporary table spaces to create intermediate tables for multi-table joins, to contain sorts that are too large to fit within the DB2 sort memory, to create temporary tables for database application use, and to perform other functions where the overhead of a permanent table is not wanted. Because these temporary data structures can be accessed frequently during their limited life spans, it is beneficial to locate the temporary table spaces on the fastest storage possible and IBM FlashSystem meets this requirement.

Frequently accessed tables

Sometimes, just 5% - 10% of tables account for a large percentage of all database activity, and therefore, I/O to storage. When a large number of users hit a table, they are likely going after different records and different attributes. As a result, the activity on that table is random. Disk drives are notoriously bad at servicing random requests for data. In fact, the peak performance of a disk drive drops as much as 95% when servicing random transactions. When a table experiences frequent access, transaction queues develop where other transactions are literally waiting on the disk to service the next request. These queues are another sign that the system is experiencing I/O wait time.

Moving frequently accessed tables to FlashSystem storage makes sense. Unlike traditional hard drives, random transactions do not affect flash performance. Additionally, flash offers faster access times than disk drives. Therefore, moving frequently accessed tables to FlashSystem can improve application performance up to 10 times.

The best way to identify I/O wait time is to look at operating system performance. The tools to evaluate operating system performance vary by operating system.

Monitoring latency and I/O wait time in DB2 for Linux, Microsoft Windows, and UNIX

IBM DB2 includes several tools to monitor I/O latency and wait time. Use these tools to determine the speed and frequency of reads and writes.

MONREPORT module

The DB2 MONREPORT module provides procedures that can be used to retrieve monitoring data and generate text reports.

You can use the DBSUMMARY procedure to generate a text-formatted report that summarizes system and application performance data for the entire database, as well as key performance indicators for each connection, workload, service class, and database member. The report breaks the TOTAL_WAIT_TIME statistic into detailed information about I/O wait time and network wait time.

There are a number of DB2 monitoring data elements, which include either snapshot monitoring, event monitoring, or DB2 table functions, that you can use to decide if I/O performance is an issue in DB2. For storage, you can monitor the amount of synchronous I/O (physical I/O that results in a DB2 wait) versus asynchronous I/O (I/O that occurs in the background and does not result in a DB2 wait).

For more information, see the product documentation links on the following website:
http://www.ibm.com/support/docview.wss?rs=71&uid=swg27009474

Snapshot monitoring

Snapshots provide information about the database and connected applications at a specific point in time. To determine which tables should be placed on FlashSystem storage, use snapshot monitoring at the table level to determine the most active tables within the database to assist with this decision.

For more information, see the product documentation links on the following website:
http://www.ibm.com/support/docview.wss?rs=71&uid=swg27009474

Db2top utility

The db2top utility monitors DB2 snapshot information for all partitions and can be used to calculate the delta values for those snapshot entries in real time. This tool provides a GUI under a command-line mode, so that users can get a better understanding while reading each entry. Db2top also integrates multiple types of DB2 snapshots, categorizes them, and presents them in different windows for the GUI environment. Db2top lists the top application users of database resources, not specific to I/O. To decide which tables should be placed on flash storage, snapshot monitoring can be used at the table level to determine the most active tables within the database.

Note that the db2top utility is supported for UNIX (AIX, Solaris, and HP) or Linux only and does not work for DB2 in Windows environments.

Monitoring latency and I/O wait time in the Windows operating system

For Microsoft Windows operating systems, the best tool for system performance analysis is the Performance Monitor (Perfmon) program. This tool uses counters to monitor the speed of disk transfers and the I/O operations that are generated by the server.

The Performance Monitor program does not provide the actual I/O Wait Time statistic, but it does include actual processor performance levels. The “Processor: % Processor Time” statistic shows the actual work that is being done by the processor. If transactions are hitting your system hard and yet the “% Processor Time” statistic is well under 100%, it is possible to infer an I/O wait problem. Systems that implement flash show high “% Processor Time” numbers.

For example, Figure 4 and Figure 5 show screen captures from the Windows Performance Monitor program. Figure 4 shows the “Processor: % Processor Time” statistic for a Windows system that is running the Iometer software performing 100% random writes to an HDD. In this exhibit, the processor utilization is less than 1%. Running additional applications on this system would only increase the processor utilization marginally, because the processor is waiting on I/O from the HDD. In the following example, the Iometer software shows that on average there were 150 writes per second (150 IOPS) to the disk drive.

Processor performance when writing to hard disk
Figure 4. Processor performance when writing to hard disk

Figure 5 shows the same system with the same access specifications in the Iometer software running against a FlashSystem array. In this example, the processor averages 68% utilization. The Iometer software shows that 138,000 writes per second are going to the FlashSystem product (138,000 IOPS). As it turns out, this IOPS number is a limitation of the host bus adapter. Nonetheless, this example clearly illustrates how an FlashSystem can improve processor utilization for a Windows based system.

Processor performance when writing to an SSD
Figure 5. Processor performance when writing to FlashSystem

In addition to processor indicators, you can look at the “Physical Disk: Average Queue Length” and “Physical Disk: Disk Bytes Per Second” indicators to detect bottlenecks in the disk subsystem. If the “Physical Disk: Average Disk Queue Length” is consistently high, consider moving files that are located on that disk to FlashSystem. A “Disk Queue Length” greater than 3 (per disk in the RAID set) indicates a problem.

The “Physical Disk: Disk Bytes Per Second” indicator helps you visibly determine the level of performance you are getting when your disk array levels off. The point at which disk bytes per second level off and physical disk queue lengths increase is the point when your system is demonstrating an I/O bottleneck. In addition, if you simultaneously graph Processor Utilization, you will notice that processor time tends to decrease when your system experiences an I/O bottleneck. One you have narrowed down the time range causing I/O problems, further refine your search for the problem by using Perfmon’s ability to narrow details by reads/writes and by drive arrays. In this way, you can quickly determine which array is causing I/O problems.


Solution architecture

FlashSystem products can easily be integrated into your existing environment. FlashSystem products are a storage area network (SAN) solution with Fibre Channel (FC), FC over Ethernet (FCoE), iSCSI, or InfiniBand connectivity. They have small 2 - 8U form factors, depending on the model/configuration, and the FlashSystem V840 Enterprise Performance Solutions add many storage services features to performance and low latency.


Usage scenarios

The following usage scenarios are ideal for FlashSystem products.

Online transaction processing (OLTP)

FlashSystem products can be ideal for OLTP applications. OLTP handles many short transactions in quick, random bursts. Use FlashSystem products to accelerate OLTP for a variety of business needs:
  • Analytics
  • Financial applications
  • Gaming
  • Real-time billing

For more information, see the IBM FlashSystem in OLTP Database Environments Redbooks Solution Guide:
http://www.redbooks.ibm.com/abstracts/tips0973.html?Open

Online analytical processing (OLAP)

Data warehouses are commonly used with OLAP workloads in decision support systems, such as financial analysis. OLAP queries are typically complex, and they process large volumes of data from multiple sources. Accurate, real-time operational data is critical for analytics. Delays can significantly increase your business and financial risks.

Many servers have adequate RAM and processor power to process massive amounts of data (frequently referred to as big data). However, the I/O that is required for reading data from storage for processing in the OLAP database server can frequently reduce performance. Delays come primarily from batch data loads and performance issues due to handling heavy, complex queries that use I/O resources.

IBM FlashSystem storage solutions help address these challenges in the following ways:
  • Dramatically boosting the performance of OLAP workloads with distributed scale-out architecture, providing almost linear and virtually unlimited performance and capacity scalability
  • Significantly improving response time for better and more timely decision making
IBM DB2 pureScale environments

The IBM DB2 pureScale® feature provides a clustered database environment with support for high availability. This scale-out architecture allows servers to be dynamically added to provide capacity for growth, peak workloads, or maintenance.

DB2 pureScale functionality is particularly useful for SAP and OLTP applications, which require a high number of transactions.

For more information, see the Continuous Availability with the IBM DB2 pureScale Feature IBM Redbooks Solution Guide:
http://www.redbooks.ibm.com/abstracts/tips0926.html?Open

For a more detailed explanation of pureScale and SAP applications, see the DB2 pureScale Feature on SUSE Linux Enterprise Server and IBM System x best practices paper:
http://ibm.co/121wuID

BLU Acceleration in IBM DB2 10.5

IBM DB2 10.5 with BLU Acceleration offers a new, column-based interface that provides compression through the use of Huffman encoding algorithms. This compression reduces data volumes by up to 10x for tables that are column-organized by up to 10X. This means a BLU table needs less storage (20 TBs can be stored in 2 TBs), which means that IBM FlashSystem can be even more cost-effective in this type of database environment.

FlashSystem storage products can be used to maximize storage performance and to accelerate analytics.

For more information, see the following website:
http://ibmdatamag.com/2013/04/super-analytics-super-easy/


Integration

FlashSystem products can easily be integrated into your existing storage environment. You can also deploy FlashSystem V840 Enterprise Performance Solutions to add many storage services and even manage your entire storage system. FlashSystem V840 includes the following features:

  • Thin provisioning to allocate storage "just in time"
  • Improved utilization to harvest all SAN capacity
  • Disaster avoidance with location-proof data availability
  • IBM Easy Tier® for storage efficiency
  • IBM FlashCopy® for point-in-time copies
  • Mirroring/copy services for data replication and protection
  • Real-time compression to place up to five times more data in the same physical space

You can order FlashSystem individually or an enhanced version of FlashSystem bundled with the IBM SAN Volume Controller, which adds the advantages of the above functionality and storage hypervising.


Supported platforms

For supported hardware and platforms for the FlashSystem portfolio of products, see the following link:
http://www.ibm.com/systems/support/storage/ssic


Ordering information

FlashSystem storage products can be ordered individually (FlashSystem 840) or the IBM FlashSystem V840 Enterprise Performance Solution (FlashSystem V840) may be ordered to provide advanced functionality, such as thin provisioning, IBM Easy Tier, FlashCopy, and Real-time Compression.

For FlashSystem ordering information, see the following IBM Redbooks® Product Guides:
http://www.redbooks.ibm.com/abstracts/tips1158.html


Related information

For more information, see the following documents:
http://www.redbooks.ibm.com/abstracts/tips1204.html?Open

Special Notices

This material has not been submitted to any formal IBM test and is published AS IS. It has not been the subject of rigorous review. IBM assumes no responsibility for its accuracy or completeness. The use of this information or the implementation of any of these techniques is a client responsibility and depends upon the client's ability to evaluate and integrate them into the client's operational environment. publib-b.boulder.ibm.com

Profile

Publish Date
18 July 2013

Last Update
21 July 2014


Rating:
(based on 3 reviews)


Author(s)

IBM Form Number
TIPS1041