Choosing the best suitable storage disk for your Oracle database is very important when you architect the environment for better performance. Not just considering database files (Data files, Control files, Redo logs, Archive logs etc…) it is also vital to look at the type of application and the data flow in and out of the database before opting a storage disk. We have seen how important are kernel parameters for better database performance similarly, right storage type will also aid in improving database performance. There are multiple types of applications categorized based on data flow like Online Transaction Processing (OLTP), Online Analytical Processing (OLAP), Loading(ETL), decision support systems (DSS) and so on. Choosing a disk type for each such environments will be completely different.
Example – A storage disk for your data files on OLTP environment will not perform well for data files on OLAP environment.
In this blog, we will discuss on: What RAID storage will be the best option for database files on OLTP and OLAP systems.
Widely used RAID types:
| Features/RAID | RAID 0 | RAID 10 | RAID 50 | RAID 5 |
|---|---|---|---|---|
|
Write performance |
High |
Medium |
Medium |
Low |
|
Read performance |
High |
High |
High |
High |
|
Data protection |
No |
Yes |
Yes |
Yes |
|
Disk usage |
100% |
50% |
67% to 94% |
67% to 94% |
|
Cost |
Cheap |
High |
Very high |
High |
There are technical and non-technical factors to decide the appropriate storage for database files. Technical includes performance, data protection, Usage levels, reliability and so on. Non-technical includes vendor, disk costs, disk wastage, maintenance costs and so on.
When we merge all these factors, then choosing a storage disk might be different from what we are about to discuss. However, in this blog we consider that storage should have the best performance and data protection and see how to pick from the available RAID groups.
Environmental comparison:
| Database files | OLTP environment | OLAP environment |
|---|---|---|
|
Data files |
Read and Write operations |
More Read than Write |
|
Redo log files |
More Write than Read |
Very less Write/Read operations |
|
Archive log files |
More Write than Read |
Very less Write/Read operations |
|
Control files |
More Write than Read |
Very less Write/Read operations |
The above comparison is for business hours with respect to the storage disk I/O operations. We are not considering the behavior in non-business hours at the moment which includes data loading, backups, running scheduled jobs in the database, data maintenance etc…
Storage disks:
| Database files | OLTP environment | OLAP environment |
|---|---|---|
|
Data files |
RAID 10 |
RAID 5 |
|
Redo log files |
RAID 50 |
RAID 10 |
|
Archive log files |
RAID 50 |
RAID 10 |
|
Control files |
RAID 50 |
RAID 10 |
Key points:
- Disk performance will vary from vendor to vendor like EMC2, IBM, DELL etc… We have discussed our scenario of choosing RAID groups taking any one vendor provided disks in the storage array.
- Choosing storage disks are subject to change with non-technical factors as well.
- We have not considered the behavior of database in non-business hours in this blog, so add it as a part of your analysis when you need.
- There are complex RAID groups with E and EE extensions like RAID 5E, RAID 5EE that can be considered with proper assistance from storage administrators.
- Focus on the block size of RAID group as well for better database performance especially for data files.
For more details on RAID levels – https://en.wikipedia.org/wiki/Standard_RAID_levels
I love to see your comments!
Feel free to give additional information in the comments, that I can validate and include in blog posts or update this as well.
Navakanth
Just want to add something related to redolog storage ..
With the enhancement with storage..now-a-days redo logs are being placed on to flash disks /SSD’s …for fast petformance …..
Pawan Kumar Yaddanapudi
That’s interesting one Navakanth. Thanks for sharing.
Jim Czuprynski
You +do+ realize that due to the nature of redo log writes, flash storage is the absolute +worst+ place to place them, correct? Large serial writes are the worst type of physical I/O for an SSD – it wears it out faster. That’s why Oracle’s enterprise platforms like Exadata reserves just a very small amount of flash memory for redo logging (called Smart Flash Logging) to alleviate the typical bottleneck that redo writes create.
Kiran Kaki
Excellent article Pawan. It definitely helps to understand the storage concepts while adding/choosing disk for database storage. Thanks for sharing it.
Pawan Kumar Yaddanapudi
Thank you Kiran.
Jim Czuprynski
Of course, enterprise-level platforms like Exadata completely negate the need for worrying about all this RAID b/s and make management of storage systems much simpler. That’s why so many organizations are turning their attention to leveraging their own in-house X5-2 based systems (or ZFS or something else that Oracle makes) to speed physical I/O. And remember … the Oracle Cloud already uses X5-2 technology “under the covers.” It’s inevitable that in just a very few years, these types of discussions about RAID will become moot.
Pawan Kumar Yaddanapudi
Yes, RAID is a traditional way of providing disks to the servers which are still in place to many customers across the globe. Oracle cloud and big customers are already with Exadata and again because of administration limitations on Exadata not every customer is ready to go for it. But as years go on!!! things might definitely change 🙂
Robin Chatterjee
Maybe what I see is atypical but generally I see huge redo log volumes written in OLAP environments. The only differnce is there is rarely permannet archiving. Even if many operations are done nologging which is often not the best design because it can cause recoverability and repeatability issues there is still a much higher volume of information especially during batch loads. I see lots of truncate and loads ( I’m looking at you OBIEE) index drop and creation which doesn’t Gel with your statement that in OLAP “Very less Write/Read operations” for redo logs or control files ( because the scn s advance like crazy during refreshes). so can you elaborate on that ?
Pawan Kumar Yaddanapudi
Robin, That’s correct when you look at OBIEE environment in a non-business hours.. In this blog i have clearly mentioned that “vey less write/read operations” for redo logs in OLAP environment in business hours.
naresh
Thankyou sir nice explanation can u post videos related to backups and recovery with real time senarios .
Pawan Kumar Yaddanapudi
Will do !!!