Optimize database storage

How we place data on our arrays

Requires Free Membership to View

EMC 8830, 4 channels, 12 73GB drives free, mirrored

LSI 4400, 2 channels, 4 36GB drives free, RAID-5

In our example, we've physically divided the database components between the EMC and LSI arrays. The arrays are connected to a cluster consisting of two Sun 420 servers, Solaris 8, Oracle 91, Veritas Manager and Cluster server, with a block size of 8Kb.

Pulling it all together
After knowing the data objects and available storage platforms, we're ready to determine how the data should be placed on the physical drives. To maximize parallelism, data should be spread across as many physical spindles as possible by striping. Striping is most effective at the host level because you can not only stripe across the disk drives, but also stripe across controllers.

To complete our environment, I added a cluster of Sun servers with a volume manager consisting of two Sun 420 servers Solaris 8, Oracle 9i, Veritas Volume Manager and Cluster Server, with a block size of 8Kb.

Data placement
The first step is to isolate the redo logs. One log each will go on separate physical 73GB drives on our high-end array (EMC in our case). Because we'll only be using about 1GB of space, use the smallest hyper available on the drive to avoid wasted space. Additionally, because the redo logs are on the EMC, they are mirrored using hardware.

Secondly, the indexes are placed on separate physical drives to isolate the random I/O from other database elements. Ideally, we want to isolate these further by placing the indexes on separate back-end buses, but in this example we would have to reduce the number of redo logs or add more back-end adapters (which in most cases would mean a larger array).

The next performance-sensitive data are the data tablespaces. One of these tablespaces requires high performance, so we'll deal with that first. On the host, the tablespace will be striped across all four channels and reside on eight physical drives using a chuck size of 64Kb. The 64Kb chunk will map nicely to the cache slots on the EMC. The idea is two keep two I/O blocks in the queue for each channel, but not saturate the channels by moving large blocks since this is an OLTP application. We are also taking advantage of dedicated back-end directors by striping across drives that sit behind unique disk adapters. The remaining tablespace will be striped across the two channels and four drives on the LSI system.

Our sequential table could go on either the midrange on the LSI or EMC, but because I'm going to assume mostly read, RAID-5 will work just fine. We'll stripe these tables with a chuck size of 256MB to keep our channels full. Lastly, we'll fill the empty space on the LSI drives with the archive logs.

All our database components now have a home. We've analyzed the database objects and placed them appropriately based on their unique characteristics. The objects are grouped according to I/O access pattern and on the appropriate storage systems for performance and availability.

It's important to note that this example was in a clean environment. That isn't always going to be the case. It's equally important to know data that already exists as well as the new data being added. A comprehensive analysis should be done of the existing environment before placing additional data.

This was first published in February 2003

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: