Ask the Expert

Want to know the dos and don'ts of an OLTP?

Would you please let us know the design considerations and the dos and don'ts of an OLTP application?

Requires Free Membership to View

I will approach this from an I/O perspective since schema design and indexing is a subject all by itself.

Since OLTP data types are high IOPS (I/O per Second) rather than high throughput (MB per second), bandwidth is not as much as an issue. The preferred method of back-end storage design for high IOP applications is to try and use many high performance spindles for both the database and its associated log files. The log files should be based on RAID 0+1 stripped mirror sets for the best performance. The database can be kept on RAID 5 or also on RAID 0+1 if the budget allows. ALWAYS put the log files and database files on separate PHYSICAL disks to eliminate hot spots. If you figure as a general rule that a spinning disk will give you a range (depending on spindle speed and latency) of approx. 120-180 IOps, then use as many spindles as you need to reach your performance numbers. Front-end cache in a RAID array can help performance dramatically, and help you to use less spindles to get the same performance numbers.

One good method of finding out what design would be best for your environment would be to go to Transaction Performance Council and look at the details of the benchmarks performed with the database you are using. This will show you details of how the back-end storage was designed to get the performance numbers achieved.

You also need to look at what file system you are using and whether you should be doing software striping on the host subsystem to spread I/O load across HBAs. Other good sources of information are on the database vendors and server vendors web sites. There are many white papers available from the server vendors in conjunction with database vendors on how to set up the servers for best performance (memory, CPU, etc.)

Chris

Editor's note: Do you agree with this expert's response? If you have more to share, post it in our Storage Networking discussion forum at --> --> .8DmraOhxbkd^1@.ee83ce4!viewtype=convDate>http://searchstorage.discussions.techtarget.com/WebX?replyToMessage@156.8DmraOhxbkd^1@.ee83ce4!viewtype=convDate


This was first published in December 2001

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: