One of the most common database bottlenecks nowadays is the I/O subsystem throughput and latency.Especially for OLTP databases, and with the fact that other resources such as memory and CPUs are growing dramatically, the storage system should be able to accommodate this increase.
As a database architect, one of your main responsibilities is to choose the best storage options in terms of cost, performance, space, RAID level and disk type.
For OLTP databases, latency in writing to the online redo log files is the most crucial part of a transaction. For large queries (reports) with sorting, the latency of reading from temp files should be as low as possible.
Why SSDs?
Solid State Disks (SSDs) are nowadays considered the fastest type of disks and started to replace the old spinning disks. SSDs uses NAND flash technology and that's why it's fast. It deals with the disk as a random memory so it doesn't have to seek the disk tracks to write or read data and therefore reading an writing are very fast compared to the legacy spinning disk. Great news for database admins, the I/O bottlenecks are now resolved with SSDs??? NO!!
Why NOT SSDs?
Oracle says NO, Don't place online redo logs on SSDs?? Why? Actually SSDs are good for random access files and not sequential, because of the fact that it doesn't have to seek the disk tracks. At the same time, it might be bad for writes (modification) because each time the disk is being written to, the disk controller has to read the whole block of data and then write it back again to apply the changes and this will take time.Since the redo logs are circular (sequential) in nature, they will be overwritten every while and another and hence the replacement overhead will be significant. One more bad thing about SSDs is that their lifetime might be shorter than spinning disks while the cost for them is higher.
Which RAID level to Choose?
RAID (Redundant array of inexpensive disks) is a striping and mirroring methodology used to achieve performance enhancement and disk failure protection. There are various RAID configurations available, but for oracle the most popular ones are RAID 5 and RAID (1+0). For oracle admins, it has been a well known story that RAID 5 is not recommend, especially for redo logs, because of it's high writing penalty. RAID (1+0) is much better but costs almost double the number of disks that RAID 5 requires.
Many storage vendors are trying to overcome the drawback of RAID 5 by using SAN caches in which writing latency will be as fast as writing to that cache and then the storage controller will take care of the rest.
Conclusion
Now, with all this in mind, where should i place my oracle files to achieve the best performance?
if you don't mind for the cost, the best scenario might be to place all your datafiles on SSDs and the redo logs on a separate array with RAID (1+0). Otherwise, you may think of moving only hot segments and temp files to SSDs and leaving all other files either on RAID (1+0) or on RAID 5 storage with a cache implemented.
References
- How to Reduce Waits on 'Log File Sync' (Doc ID 857576.1).
- https://asktom.oracle.com/pls/apex/fp=100:11:0::::P11_QUESTION_ID:597820900346656359.
As a database architect, one of your main responsibilities is to choose the best storage options in terms of cost, performance, space, RAID level and disk type.
For OLTP databases, latency in writing to the online redo log files is the most crucial part of a transaction. For large queries (reports) with sorting, the latency of reading from temp files should be as low as possible.
Why SSDs?
Solid State Disks (SSDs) are nowadays considered the fastest type of disks and started to replace the old spinning disks. SSDs uses NAND flash technology and that's why it's fast. It deals with the disk as a random memory so it doesn't have to seek the disk tracks to write or read data and therefore reading an writing are very fast compared to the legacy spinning disk. Great news for database admins, the I/O bottlenecks are now resolved with SSDs??? NO!!
Why NOT SSDs?
Oracle says NO, Don't place online redo logs on SSDs?? Why? Actually SSDs are good for random access files and not sequential, because of the fact that it doesn't have to seek the disk tracks. At the same time, it might be bad for writes (modification) because each time the disk is being written to, the disk controller has to read the whole block of data and then write it back again to apply the changes and this will take time.Since the redo logs are circular (sequential) in nature, they will be overwritten every while and another and hence the replacement overhead will be significant. One more bad thing about SSDs is that their lifetime might be shorter than spinning disks while the cost for them is higher.
Which RAID level to Choose?
RAID (Redundant array of inexpensive disks) is a striping and mirroring methodology used to achieve performance enhancement and disk failure protection. There are various RAID configurations available, but for oracle the most popular ones are RAID 5 and RAID (1+0). For oracle admins, it has been a well known story that RAID 5 is not recommend, especially for redo logs, because of it's high writing penalty. RAID (1+0) is much better but costs almost double the number of disks that RAID 5 requires.
Many storage vendors are trying to overcome the drawback of RAID 5 by using SAN caches in which writing latency will be as fast as writing to that cache and then the storage controller will take care of the rest.
Conclusion
Now, with all this in mind, where should i place my oracle files to achieve the best performance?
if you don't mind for the cost, the best scenario might be to place all your datafiles on SSDs and the redo logs on a separate array with RAID (1+0). Otherwise, you may think of moving only hot segments and temp files to SSDs and leaving all other files either on RAID (1+0) or on RAID 5 storage with a cache implemented.
References
- How to Reduce Waits on 'Log File Sync' (Doc ID 857576.1).
- https://asktom.oracle.com/pls/apex/fp=100:11:0::::P11_QUESTION_ID:597820900346656359.
- http://en.wikipedia.org/wiki/Flash_memory#NAND_flash
- http://www.ssdperformanceblog.com/
