Managing data storage

Manage data storage to ensure optimal database performance.

Introduction

DB2® includes Self-Tuning Memory Management (STMM) and so makes efficient use of the available system RAM. Typically, DB2 uses up to 80% of available RAM. Extra RAM is a relatively inexpensive way to mitigate against suboptimal storage.

The DB2 product documentation provides detailed guidelines on how to manage storage to ensure good database performance. For more information, see Database storage.

Managing the number of physical disks per CPU core

Data storage communication improved significantly in recent years. These improvements include increased disk capacities and spindle speed, the introduction of storage area networks (SANs) and network-attached storage (NAS), and the caching of data on storage controllers by using dynamic random access memory (DRAM) and non-volatile random access memory (NVRAM). However, these enhancements cannot compete with the significant increases in CPU processing power in the same period. As a result, disk I/O service times are now much slower relative to CPU processing power. This difference means that, despite substantially increased disk capacity, an increasingly large number of physical disks are required per CPU core to ensure that the system is not I/O bound.
You can use the following guidelines to determine the numbers of physical disks that are required per CPU core:
  • For online transaction processing (OLTP), ensure that there are 15 - 20 dedicated physical disks per CPU core.
  • For warehousing, ensure that there are 8 - 10 disks per CPU core.

Optimizing storage

The available hardware determines the approach to take to optimize storage for best database performance.
  • If the server has a small number of locally attached disks, isolate the operating system files, the individual database table space data, and the transaction logs on to separate physical disks. Typically, 8 -16 disks is considered to be a small number in this context. If solid-state drives (SSDs) are available, these are most effective for storing table space data and transaction logs. For example, in IBM® Intelligent Operations Center the table space data is mounted on /db2data and the transaction logs are mounted on /db2logs.
  • If a large array of locally attached storage devices is available, then a Redundant Array of Independent Disks (RAID) configuration is probably the best option. Typically, 15 or more disks per CPU core is considered to be a large array in this context.
  • For a SAN, use the maximum I/O throughput capacity available. The guideline is to use 3000 - 5000 input/output operations per second (IOPS) for a medium to large IBM Intelligent Operations Center deployment that includes more than 100 active data sources.
  • For IBM Cloud™ environments, if surface capacity requirements are less than 300 GB, select the Local Storage option to maximize throughput at low cost. If surface capacity requirements are greater than 300 GB, then you might specify the more expensive Block storage to get the required throughput capacity of 3000 - 5000 IOPS.