What is the general guidance for choosing suitable storage for a Db2 pureScale workload?

Two aspects of performance are considered in storage for Db2® pureScale®: recovery performance and runtime performance. Recovery performance is minimizing the time to recover from a software or hardware failure, while runtime performance is maximizing transaction rates.

For recovery performance, storage where Spectrum Scale supports Fast IO Fencing is considered first. See What type of file system or storage is supported? for more details.

For runtime performance, Db2 pureScale storage requirements are typically measured by using input/output operations per second (IOPS) and the time for the I/O request to complete (also known as latency). Different workload characteristics (read/write ratio) generally dictates the requirements of the mentioned metrics, which provide the guidance of the storage type choices. For most users, the most performance critical storage is for the transaction log file system.

Guidance for transaction log storage

Given that Db2 pureScale is designed for high-performance, mission-critical OLTP applications with read/write ratios that range from mostly read scenarios (for example, 90% read and 10% write) to heavy write scenarios (for example, 50% read and 50% write), the IOPS requirement is linked to the number of transactions log writes that the storage can provide. As an example, for a workload that demands a maximum of 100,000 transactions per second with 50% insert/update/delete (IUD), the storage for the transaction log must provide at least 50,000 IOPS. If the IUD is 30%, the guidance for IOPS requirement is calculated at 100,000 x 0.3 = 30,000 IOPS.
Note: These IOPS rates are the ones that the SAN controller can sustain.

For latency, submillisecond is the goal for transaction log writes. Since Db2 pureScale requires a SAN storage controller, which adds to the overall latency, low to mid hundreds of microseconds is deemed an acceptable range for moderate to high workload demands (based on in-house performance tests).

In summary, the following considerations can be used for deciding on the type of storage for Db2 pureScale for on-premises and cloud deployments:
  • IOPS is 5,000 - 25,000 or higher (the higher the better for demanding workload)
  • Latency is submillisecond for transaction log writes (the lower the better)

Guidance for database container storage

For some users who have larger databases (for example, databases with hundreds of terabytes or higher), more care is needed in choosing the storage where the database containers are stored. Larger databases require more reads from physical disk, as the available system memory that is used for buffer pools cannot hold all the active data at once. A similar consideration applies to database workloads: if the workload needs to read 10,000 4k pages from the table space containers per second, the SAN storage must be able to maintain this IOPS rate. SAN storage with low-latency is beneficial in reducing the time that it takes to read the pages and can reduce contention in other parts of the database, such as page and lock contention. As with the transaction log SAN, submillisecond latency is highly recommended.