Discover best practices for database storage including guidelines and recommendations for 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, and automatic storage.
In a world with networked and highly virtualized storage, database storage design can
appear 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 true potential.
The good news is that it is more important to not get database storage design wrong than it is
to get it perfectly right. Trying to understand the innards of the storage stack and hand
tuning which database tables and indexes should be stored on which part of what
physical disk is an exercise that is neither generally achievable nor 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 a recipe to a healthy database server through easy to follow best
practices in database storage, including guidelines and recommendations for 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
- Executive summary
- Introduction to database storage
- Goals of good database storage design
- Simplicity in database storage design
- Recipe for database storage success
- Think about real physical disks, not just storage space
- Have dedicated LUNs and file systems per non-DPF DB2 database
server / per DPF partition
- Stripe at most in two places
- Separate DB2 transaction logs and data
- Use file systems instead of raw devices—one file system per LUN
- Where possible, use RAID-10 for transaction logs, RAID-10 or RAID-5 for data
- Set
EXTENTSIZE
- Use the
NO FILE SYSTEM CACHING clause
- Use DB2 automatic storage to stripe everything everywhere
- Do not hand-tune the
NUM_IOCLEANERS, NUM_IOSERVERS, and
PREFETCHSIZE configuration parameters
- Best Practices
- Conclusion
- Further reading
- Notices
"
Best Practices:
Database Storage
"
(October 2009)
Discover best practices for database storage including guidelines and recommendations for 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, and automatic storage. (pdf; 494KB; 19 pages)
Comment, edit, or add your own insights to the Database Storage best practices
on the IBM Database Wiki.
Check out all the other Best Practices papers and see how you can improve your experience with DB2 for Linux, UNIX, and Windows.
| 
