Db2

 View Only

Best Practices for Database storage

By Shanavi Pawar posted Tue December 06, 2022 06:30 AM

  

Executive summary

In a world with networked and highly virtualized storage, database storage design can seem like a dauntingly complex task for a DBA or system architect to get right.

Poor database storage design can have a significant negative impact on a database server. CPUs are so much faster than physical disks that it is not uncommon to find poorly performing database servers that are significantly I/O bound and underperforming by many times their potential.

The good news is that it is not necessary to get database storage design perfectly right. Understanding the innards of the storage stack and manually tuning the location of database tables and indexes on particular parts of different physical disks is neither generally achievable nor generally maintainable by the average DBA in today’s virtualized storage world.

Simplicity is the key to ensuring good database storage design. The basics involve ensuring an adequate number of physical disks to keep the system from becoming I/O bound.

This document provides basic initial suggestions for a healthy database server through easy-to-follow best practices in database storage, including guidelines and recommendations in each of the following areas:

  • Physical disks and logical unit numbers (LUNs)

  • Stripe and striping

  • Transaction logs and data

  • File systems versus raw devices

  • Redundant Array of Independent Disks (RAID) devices

  • Registry variable and configuration parameter settings

  • Automatic storage

Note: This paper focuses on best practices for deployments of DB2 for Linux, UNIX, and Windows in typical OLTP environments. Unless specifically mentioned, the recommendations in this paper do not necessarily apply in data warehousing environments or in environments where a DB2 database is used as the underlying database for third-party software.

Introduction

Storage area networks (SANs) and network-attached storage (NAS) have fundamentally changed the database storage world. A decade or so ago, the word disk referred to physical disks with heads and platters. In today’s storage world, a disk is often a completely virtual entity that is somewhere on the storage network and that can be a single physical disk, a portion of a physical disk, a RAID array, a part of a RAID array, or part of several RAID arrays. Recent enhancements in file system options, such as direct and concurrent I/O, have almost eliminated any performance advantage that using raw devices had over using file systems.

Although Moore’s Law has held for CPU processing power, it does not apply to disk drives. Despite changes in storage communication that were introduced by SAN and NAS, the underlying infrastructure for storing bits remains fundamentally the same. A mechanical spindle rotates a platter of magnetic material, upon which bits of information are encoded using a read/write head on the end of an actuator. Although spindle speeds have increased and caching of data on storage controllers using dynamic random access memory (DRAM) and non-volatile random access memory (NVRAM) has helped, neither of these advancements have kept up with the sheer magnitude of processing power increases over the past decade or so. The result is that relative to CPU processing speeds, disk I/O service times have become much slower. This difference requires an increasingly larger number of physical disks per CPU core to ensure that the system is not I/O bound. Because disk capacities have increased substantially, achieving an appropriate ratio of physical disks per CPU core is often overlooked, therefore leading to disk bottlenecks.

Given the changes in storage, file systems, and CPU processing speeds, the best practices for database storage provisioning and management have also evolved. In the past, a DBA might have been advised to determine which physical disk and which part of each physical disk on which to place individual tables and indexes. In today’s virtualized storage world, for the average DBA, yesterday’s best practices are impractical.

The best practices presented in this document have been developed with the reality of today’s storage systems in mind. For related information about database performance and speed of database operations, refer to the “Best Practices for Physical Database Design” paper. This paper and others are available at the DB2 Best Practices website at http://www.ibm.com/developerworks/data/bestpractices/db2luw/


Download the report to get started!
#Db2
3 comments
33 views

Permalink

Comments

Wed March 22, 2023 02:57 PM

I clicked on the first link provided and it redirect me to an old article from 2012 on Database Storage best practices (link is bellow).


https://community.ibm.com/community/user/datamanagement/viewdocument/best-practices-for-database-storage?CommunityKey=ea909850-39ea-4ac4-9512-8e2eb37ea09a&tab=librarydocuments


The pdf link is broken, I get an error message.

<Error>
<Code>AccessDenied</Code>
<Message>Request has expired</Message>
<Expires>2022-12-15T03:00:08Z</Expires>
<ServerTime>2023-03-22T19:00:42Z</ServerTime>
<RequestId>B8DW0G2KZQKJPW3H</RequestId>
<HostId>njM74TCWpvfiMT9erIDee59v838EOuf8WJMOrkdtb4IXVyzWiDlSc3gJ2X1H/MY1uLi6OzdbP6M=</HostId>
</Error>


Can some one please help me find the more recent Database Storage Best practices document ?

Wed December 14, 2022 10:26 AM

I wasn't aware of the best practices website. But when I follow your link, I am taken to the main page of developerworks, it doesn't seem to lead where I would expect (a listing of LUW specific best practices). Is this link correct?