Ask the Expert

Database data layout for optimized disk performance

I am a database administrator. We currently have only a few critical production servers in a small SAN environment. These databases are experiencing degradation in redo log-write times. Because of the heavy I/O of redo logs they should be typically put on fast devices.

I believe in our current SAN environment, all the disks are striped in 4 GB chunks and on RAID 10. The databases are OLTP type databases. Most are Oracle and some are Informix Dynamic Server.

Do you have any recommendations on how to layout the disks on the new SAN environment we are building so that performance will be improved? Should we not stripe the disks that redo logs are held on? Does it pose any problems to have only some of the disks striped in the same EMC storage unit?

Thanks for your help.

    Requires Free Membership to View

You should put your redo logs on fast devices but realize that the EMC cache is going to satisfy the write request first. Striping across front-end channels is usually more effective than a faster drive. Only if your writes are occurring faster than cache can de-stage to the back-end drive will drive speed result in a bottleneck.

I recommend striping at the OS level using a volume manager and RAID 10 is fine for a database application. Since Informix uses a 2K block for I/O, a stripe size of 32-64MB works well. If you are using Oracle, 8K block, I would suggest a larger 64-128MB stripe size. Your logical volumes on the EMC systems are probably 4GB.

There is no problem mixing RAID protection schemes or striping on EMC systems.

When laying out a database, sort your disks by activity from highest to lowest. Get a copy of the logical:physical layout from the SE. Map your disks to the logical disk number. If you are using multiple subsystems, place each disk on a separate subsystem before going to next logical disk number. For example, if you have two subsystems, disk0 would map to subsystem 1, logical disk 1; disk1 would map to subsystem 2, logical disk 1; disk2 would map to subsystem 1, logical disk 2, and so on. This gives you maximum spread across subsystems, front-end directors, back-end disk directors, and physical devices.

Also, you are going to want at least four channels per subsystem supporting your database. If this is all EMC, you should use the PowerPath product to provide channel protection and load balancing.

Jim

Editor's note: Do you agree with this expert's response? If you have more to share, post it in our .HcX6azlxeJg^0@.ee83ce2!viewtype=&skip=&expand=>Administrator Central discussion forum.

This was first published in January 2002

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: