Performance tuning has always been a challenge for System administrators and Database administrators for a long time. As virtualization continues to grow in every aspect of the IT infrastructure, tuning the OS, DB or storage tends to become even more complex.
Apart from CPU power and memory size, disk subsystem handles the movement of data on the computer system and has a powerful influence on its overall response. Also performance disk layout must also be designed to provide appropriate data protection with overall cost in mind.
Planning ahead is the most effective practice to avoid performance issues later on while also providing the flexibility to make adjustments before committing the changes into production.
Some fundamental disk terminology:
Alignment –Data block addresses compared to RAID stripe addresses
Coalesce – To bunch together multiple smaller IOs in one larger one
Concurrency – Multiple threads writing to disk simultaneously
Flush – Data in cache written to disk
Multi-pathing – Concurrent paths to same disk storage
How to choose a RAID type:
The concept of RAID is comprehensible to most in the storage industry. To extract the best disk performance, choosing the right RAID type based on IO patterns is very important. Most commonly observed IO patterns are listed later in the article. Since RAID 5 and RAID 1/0 are the most commonly used RAID types in the industry lets focus on these two for now.
RAID 1/0 – This RAID type works best for random IOs pattern especially for write-intensive applications. If the writes are above 20% go and RAID 1/0
RAID 5 – Compared to RAID1/0, for same number of physical spindles, the performance of the two is very close in a read-heavy environment. For instance, a 2+2 RAID 1/0 (total 4 disks) will perform similar to a RAID 5 3+1 (total 4 disks).
On the other hand, if one can afford to ignore the number of physical spindles and consider only the usable capacity (RAID 10 3+3 v/s RAID 5 3+1), RAID 1/0 is the way to go.
RAID1/0 has higher cost associated with it while RAID 5 provides a more efficient use of disk space. The only con associated with RAID 5 is the re-sync time after a disk replacement.
Number of Operations per RAID type:
RAID 1 and 1/0 require that two disks to be written for each host initiated write.
Total IO=host reads + 2* host writes
RAID5 (4+1) requires 4 operations per host write. If the data is sequential, do one large stripe write
A r5 write requires 2 reads and 2 writes
Total IO=host reads + 4*host writes
To re-cap,
. Parity RAID operations increase the disk load. For similar capacity, go for RAID1/0
. RAID5 is better than RAID1/0 for large sequential IO
. RAID 3 is much effective as you can now use cache with RAID 3
. RAID1/0 is best for mixed IO types.
Commonly noticed Database IO patterns:
OLTP Log – sequential
OLTP – Data – random
Bulk Insert - sequential
Backup – sequential read/write
Restore – sequential read/write
Re-index - sequential read/write
Create Database - Sequential read
Knowing your IO personality is important to choose which RAID type to consider. Using the above notes and application IO patterns, one can make a choice on the disk layout. Some characteristics of the IOs to consider are:
- IO size
- IO Read/Write ratio
- Type of IO - Random v/s Sequential
- Snapshots / Clones
- Application type – OLTP ?
- Bandwidth requirements
- Estimated Growth
Once you decide the type of RAID to use, you can fine tune the disk system by following vendor recommended practices like:
-- Lun distribution
-- Distribute the IO load evenly across available disk drives
-- Avoid using Primary and BCv/Snapshots luns on same physical spindles. (The best way to avoid this is to have separate disk groups for primary data disks and BCVs/Clones/Snapshots)
-- Consider using Meta or host stripping
Cache:
Disk writes are more costly and thus must be given bigger share of cache
Match cache page size to IO size to prevent multiple Ios
Stripe Size:
Retain default stripe size of 64KB
Larger the stripe size, more cache size required and longer it takes to rebuild
FC or iSCSI:
FC Best for large IO and high bandwidth
FC More expensive
iSCSI involves lowest cost
iSCSI works best for OLTP, small block IO
Some Best Practice recommendations from Microsoft:
Microsoft has laid a few guidelines for designing the SQL database
Use RAID1+0 for log files
Isolate log from data at physical disk level
Use RAID 1+0 for tempdb
Revisiting the above recommendations periodically to stay on track, will go a long way in extracting the best out of disks
--Contributed by Suraj Kawlekar
Posts
