Hello and welcome to the first installment of SQL Velocity!
Over the coming weeks and months I will be documenting best practices for high performance database installations. The focus of this material will be running SQL server on EMC Symmetrix arrays.
This first installment covers separating SQL Functions to optimize storage I/O. For the purposes of this installment we will separate databases into three basic types:
- Online transaction Processing (OLTP)
- Data Warehousing
- Analysis Services (OLAP Cubes)
These database types are separated because they generally access their data files in very different ways.
- Online Transaction Processing systems are assumed to issue random read/write I/O to their data devices.
- Data warehousing systems (often called Business Intelligence systems) often load data into the data warehouse using sequential writes, and access the data using Sequential read I/O’s
- SQL Services Analysis Services (SSAS) utilizes the NTFS file system directly. SSAS reads and writes these many files in a highly random pattern. Many times SSAS will randomly read many files while creating new files sequentially. SSAS can be an excellent candidate for high performance Enterprise Flash Drives.
Each database utilizes four distinct file types. Each file type has its own data access pattern:
- Data - Each different type of database performs either random or sequential I/O access patterns, as noted above.
- Log - Log access is mostly sequential. The database writes data to the log, and then writes the data into the database. Note that SSAS does not use Log files.
- Temporary database (TempDB) - TempDB is used when the system needs to perform temporary data base operations. TempDB access is assumed to be highly Random. SSAS does not use a TempDB device. As a side note, TempDB can often benefit from high performance Enterprise Flash Drives (EFDs).
- Backup - Database backup files should be stored on a separate backup volume. Backup devices are accessed sequentially.
Best practices:
- Aggregate Random I/O: Random workload disk drive performance is based on rotational latency and seek times. For example, a 10,000 RPM drive spins 166 times per second. It takes some time to physically move the drives read/write head (this is measured as seek time) therefore I conservatively use 100 I/O’s per second (IOPS) to calculate how many drives are needed to support a given work load. If I need to perform 1000 IOPS then I need 10 drives (Note: There are a significant number of variables in these calculations; that data is beyond the scope of this post). You will find that issuing I/O serially will not be able to achieve this performance threshold. In order to reach optimal performance many I/O’s should be issued to the RAID set (the number of I/O’s the system can sustain is based on many factors, again beyond our scope today).
- Separate Sequential I/O: A single physical hard disk can perform about 10 times the number of sequential IOPS as random IOPS. Sending two sequential data streams to the same drive becomes a random operation thus conceivably reducing performance by a factor of ten. By isolating sequential operations, fewer drives are needed. Using the same 10 drives listed above; sequential access will yield 10,000 IOPS vs. 1,000 random IOPS, based purely on performance requirements. Separating two sequential files onto 4 total drives will yield 4,000 IOPS vs. only 400 IOPS when the sequential operations are combined.
Based on these rules the log and backup devices should be isolated.
That sounds really easy. Dedicate a few drives to a small DB log file and a whole lot of drives to the backup drive, ensure that nothing else shares these drives and you are home free.
Note: There is one other really good reason to separate database functions and that is Business Continuance. If all data base functions are placed on the same physical disks then one disaster can render all data lost. If, on the other hand, the backup and log files are on separate physical media there is a better chance at recovery.
Imagine that someone manages to drive a forklift through your storage array. If you separate the backup and log files then there is an excellent chance of recovering all data. Since the SQL Server instance writes to the transaction log before updating the database, keeping the log files along with the backup files will enable a full data recovery. Shame on the fork lift driver – lucky for you – you planned for that. Planning for your datacenter falling into the ocean, catching fire, or losing power; well you should implement remote storage replication, but that is a topic for another time!
This is where things get a bit complicated.
One easy way to separate I/O should be to simply create different storage devices (called Logical Units or LUNS), and on each of those LUNs create a single separate NTFS volume, such as the following:
- E$ - Backup
- H$ - Data
- O$ - Log
- T$ - Temp DB
A Symmetrix array has two technologies that greatly enhance performance:
- Hyper Volumes
- Virtual Storage (often called virtual provisioning).
While these technologies will enhance performance they can make it more challenging for the average user to clearly identify where their storage actually resides.
The Symmetrix array splits each physical disk into pieces called Hyper Volumes. The hyper volume offers an excellent performance boost over using a single physical disk. Using 400GB physical disks a single volume of 1 terabyte would only utilize 8 physical disks (assuming that the disks are configured using RAID mirroring). Utilizing 50GB hyper volumes this same 1TB volume can be spread across 40 mirrored volumes. This offers 5 times the performance when compared to utilizing the basic 8 disks.
Figure 1: Hyper volumes
Understanding a specific volume layout is important. An administrator may separate data files onto unique volumes, only to later discover that the underlying storage is amalgamated.
Figure 2 shows an example of SQL server sharing all Physical Disks. This configuration will induce a condition known as excessive head seek. This causes the disk read write heads to move all over the disk surface whenever an operation is initiated. A worst case example would be running a production backup. The backup operation will cause the disk heads to move rapidly, back and forth, as data needs to be read from the Data and Log volumes and written to the Backup volume. This head seeking will cause I/O that is much slower than random I/O.
Figure 2: Poorly configured SQL layout
Whenever possible do not allow a SQL data file to “wrap” back onto the same physical disk. Figure 3 is an example of a configuration that will cause excessive head seeking. Ensure that enough physical disks are allocated to each volume so that it can support the necessary I/O load. Balance highly utilized hyper volumes with less frequently accessed data.
Figure 3: Data volume wrapping
Many Symmetrix deployments will take advantage of Virtual Provisioning, also known as Thin Provisioning. A thin data pool can greatly improve storage utilization and work significantly increase performance. Ideally the administrator may create one pool for data and temporary data base files, and a second pool for log and backup files.
Best Practices
· Utilize SQL best practices to separate data files. Visit www.SQLCAT.com for best practices from the Microsoft SQL Customer Advisory team and www.EMC.com/collateral/software/solution-overview/h2203-ms-sql-svr-symm-ldv.pdf for our EMC SQL server best practices tech book.
· Using Standard Hyper Volumes:
o Ensure that heavily used SQL volumes are paired with less frequently accessed data (such as an archive).
o This will dedicate maximum performance to the SQL workload, while ensuring storage capacity is well utilized.
· Using Virtual Provisioning:
o When possible create enough thin pools to ensure that backup and log data are separated from the database files. This should boost performance and add an extra layer of reliability to the design.
o Size the thin pool for performance and capacity. Add enough physical disk resources to a thin pool to absorb the entire peak IOPS load that will be generated. I like to use a worst case scenario, assuming all I/O is random, to determine the number of physical disks that are needed in every pool.
o Balance data files that have high performance requirements with less frequently accessed data.
· For the Symmetrix array:
o Ensure that workloads are balanced across
§ Physical Disks
§ Disk Groups
§ Front End Ports
Figure 4: Well-balanced configuration
