Would you please let us know the design considerations and the dos and don'ts of an OLTP application?
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.)
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
Dig Deeper on RAID
Related Q&A from Christopher Poelker
RAID can allow for better storage performance and higher availability, and there are many different RAID types. Read a comparison of RAID levels, as ...continue reading
SAN expert Chris Poelker discusses how to change the size of a LUN in a Microsoft cluster server environment.continue reading
SAN expert Chris Poelker compares connecting a SAN with wavelength cabling and dark fiber and discusses the pros and cons of each.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.