Problem solve Get help with specific problems with your technologies, process and projects.

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?

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 Database storage

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.