5. Setting and planning the I / O subsystem
DV system characteristics
Disk array overview
Concept of I / O subsystem
Common RAID level overview
RAID level performance comparison
I / O delay with SQL Server
Planning SQL Server Disk Architecture
Tips and suggestions for I / O subsystem
Summary of this chapter
When you set the system, you should pay special attention to the design and setting of the I / O subsystem. The planning of the I / O subsystem does not improve your system, it is to reduce the overall performance. If you can understand the limitations of the I / O subsystem, your system can provide ideal performance performance on the server on the server.
We will explore the I / O subsystem in this chapter. From the basic principles of disk operation and its performance limit, then introduce the canable disk array scheme and its own characteristics. In addition, you can learn about the I / O functional issues and further resolve these issues. This chapter also provides some 诀窍 and suggestions for I / O subsystems. Finally, we will tell you how to properly set Microsoft SQL Server 2000, and use the I / O subsystem.
The characteristic disk (or hard disk) of the disk system is a basic component in the computer system. Surprisingly, there have been almost no changes in the disk system in the past 20 years. Compared with the past, today's disk may be more stable faster, but basically they are all the same. From the viewpoint of performance, the tuning of the disk system is the most important key to the hardware component. More specifically, you cannot adjust your disk; however, if you know the principles and restrictions of the disk system operation, you will pay your system for these restrictions, you are taking your I / O subsystem. The construction of the disk system is used to store the data element to be called a disk or disk. A magnetic substance that can store data can be applied to the disc. The data is stored on the magnetic track, the so-called magnetic track, it seems like a black collide (or CD, if you don't remember what the black paste is long, what kind of words are on the track. Several magnetic regions are sequentially divided on each track, the farther the center of the disk, the more the magnetic regions accommodated by the magnetic rail. Figure 5-1 is a typical magnetic disc.
Figure 5-1 Disk
Most of the disks have a number of disk discs and is a stacked, as shown in Figure 5-2. Read the part of the write information to be "read the writing". Due to the number of disk, the writing head often does not stop one. The part of the read writing is called "read and write arm", which allows the read writing to move back and forth on the magnetic area, a bit like the rocker arm connected to the singer. The read writing and the read-write arm is fixed, resulting in the result of all the actions on the disk on the disc will be in the same. In other words, all the action read or written on the disk is synchronized, and the information is not read simultaneously simultaneously. Based on this mode of operation, the read writing is simultaneously passing through the same magnetic rails per disk, and these magnetic rails are stacked, such as cylinders, so we say that the information is stored on "magnetic column", just as shown in Figure 5- 2 shown. The number of disks in the magnetic disc can be a piece, or more than six pieces, and the density and quantity of the disc can determine the capacity size of the disk. Most of the magnetic discs are similar, and different parts are only in the number of disks. More common is a 9 GB of disks with a three-piece disc (18 GB of magnetic disc of six-piece disks.
Figure 5-2 Magnetic column
Disk features now you probably understand the structure of the disk, then let's take a look at how it works. First, you must understand the characteristics of the magnetic disc rotation, thereby understanding the symptoms of the disk search performance. Recycling Today Most high-order disks have been able to reach 10,000 speeds per minute (10000 rpm). When there is a data request, the disk needs to be rotated to the information position before reading this information, which is about 6 milliseconds (MS), or 0.006 seconds. A 10,000 rpm of disk is rotated 166.7 copies per second, i.e., 1 / 166.7 seconds per lap, or 6 ms. The magnetic area where the disk is read, and the magnetic area of the storage data must be under the read writing. Because the disk seems to be rotated forever, the writing of the writing should look like a position waiting for the magnetic area to its position. This time waiting for the magnetic area to rotate to the write-headed position, we call "Rotational Latency". If the disk is completely rotated in a circle, the length of the loop delay is 6 ms, but the average is about 3 ms. Recycling latency increases the response time of the disk access. Therefore, when you select a disk for the system, it is very important to take care of the length of the loop delay from the viewpoint of the performance. For example, a magnetic disc of 10,000 revolutions per minute is approximately 3 ms. Generally, the older disk is 7200 per minute, a circle is approximately 8.3 ms, and the average recipient delay is approximately 4.15 ms. It seems that there is no difference, but in fact, this time is approximately 38% more than 10,000 rpm. After reading this chapter, you will find that this 38% will be a big burden on the system I / O time. Download Search When we want to retrieve the information from the disk, do not only need to rotate the magnetic district to the reading of the magnetic district, let it read the information, and read the writing head must also move to the magnetic rail of the information. Row. The read writing is moved back and forth in the sandwich space of the disk, and the time to move to the correct magnetic column is called "SEEK TIME". Figure 5-3 shows the latency latency and search time. Figure 5-3 Relay latency and search time
The length of search time is mainly due to the distance required to move by the writing. When the disk access action is step-by-step, the read writing head only needs to move a little bit, the search time will be shorter. Relative, if the memory is spread throughout the disk, the search time will naturally become quite long. Search time and back ring delays will increase the time of I / O jobs, and will therefore deteriorate the performance of the disk. The loop delay on a 100,000 rpm disk is usually about 3 ms. Search time, depending on the size, speed, and time of searching. TRACK-TO-TRACK Search "Track-TO-TRACK Search Time" refers to the time required to read the writing head in adjacent magnetic rails. Most of this type of search is when performing a sequential I / O job. Taking a typical 10,000 rpm, 9GB of disk, Track-to-track search time is about 0.8 ms. You can see that 0.8 ms TRACK-TO-TRACK search time and approximately 3 MS loop delay is the biggest factor affecting the performance of the disc. If the I / O job requires a speed to be transmitted to a disk, the action of the disk between the adjacent magnetic rails (whether reading or writing) is almost simultaneously. However, it is more than that. Some situations are that the I / O action is not fast enough, and the disk is rotated between each step-by-step access. This problem will not occur, to see the design and speed of the disk control card. The average search time "Average Search Time" refers to the time average of the reads and writing to search between random tracks. According to the disk specification sheet, the average search time of a 100,000 rpm disk is about 6 ms. Since SQL Server's I / O job is randomly generated, your disk has many opportunities to perform random I / O. The maximum search time of this disk can be long to 13 ms. The so-called maximum search time refers to the time that is taken from the magnetic rail from the magnetic disc of the magnetic disk to the magnetic rail of the outermost ring. This is also known as Full-Disk search. However, in general, most of the search moves will not be Full-Disk search, especially when the disk is not full. Disk specifications In this section, you can see how fast the disk can reach how fast it is performing different I / O homework. To calculate these things, you must have more information on your hand. Most of the information can be found from the disk specification sheet provided by the manufacturer. The example raised by this chapter is a 100,000 rpm, 9.1 GB of disk specifications. This type of disk can be found in Table 5-1. Table 5-1 Disk Specifications
Specifications Description Disk Capacity 9.1 GB The Rotation Speed Transmission Rate of 10,000 RPM Disks The Speed Average Search Time 5.2 MS (WRITE) In Random I / O , Disk-to-track search time 0.6 ms (READ) 0.9 ms (WRITE) in sequential I / O operation, SVR FULL -disk search time 12 ms (read) 13 ms (write) from disk the most innermost ring to the outermost ring 2.99 MS ring lazy time mean an average mean an average error occurs Interval 1000,000 Hours A magnetic The average life of the disc
As you can see, the data stoveted on the disk specification table helps to understand the true performance of a disk. The amount of time required for disk performance I / O operations comes from the following factors: the necessary search time (let the magnetic head can be moved to the magnetic track where the data) must be returned (let the information can be transferred to the head below)
The data from the data from the disk to the disk control card is therefore, the time required for the I / O job is actually the time to complete the above steps, plus the time spent between the disk and the operation system. Remember, the total time of I / O operations is a great relationship with the operation method. The sequential I / O is quasi-TRACK-TO-TRACK search time, and the random I / O is based on average search time. The sequential I / O sequential I / O is mainly data from adjacent to the disk. Because Track-TO-TRACK search time is much faster than the random search time, this type of work can make the disk reach a high productivity. To learn more about the sequential I / O, let us see the following examples. With the previously mentioned disk, its TRACK-TO-TRACK search time is almost 0.8 ms, plus the loop delay 2.99 ms, you can calculate an I / O action to spend about 3.79 ms. In other words, 264 sequential I / O operations (1000 / 3.79 = 263.8522) can be performed within a second. However, in the actual situation, sequential I / O will be affected by other factors, such as 40MB of the SCSI interface, as well as the limitations of the operating system - such as archive systems, drivers, and more. Consolidation of these factors, the first step-by-step I / O maximum speed of the disk is about 250 times per second (it is necessary to see how much this job). In Chapter 6, you will see that the I / O amount of the disk will generate bottlenecks, so it is recommended that the maximum I / O rate estimate is 225 times per second. Randomness I / O Randomness I / O occurs when the magnetic head must be read in different parts of the disk. The result of this job is to reduce the effectiveness of I / O. Similarly, let's take a look at the example mentioned before. The 0.8 MS TRACK-TO-TRACK search time is now replaced by 7.5 times 6 ms average search time. Plus 2.99 ms loop delay, a random I / O consumption of 8.99 ms, that is, 111 random I / O (1000 / 8.99 = 111.2347 ...) per second. The law mentioned before the application, the maximum I / O rate estimate must not exceed 85%, which is 94 times per second I / O operation. If you add a test of the control unit, your disk will not exceed 85 I / O jobs per second. When the disk is executing random I / O, the normal delay time (executing a single I / O job) is 8.99 ms. If the disk access action exceeds the speed it can master, the bottleneck will begin, and the delay time will increase. As can be seen in Figure 5-4, the closer the number of operations per second, the more serious the delay will be. Figure 5-4 Comparison of I / O Operations and Delays per second
In fact, if you make the operation of the disk reach the full file, it will definitely produce bottlenecks and the performance will drop. You will learn later, SQL Server (Other Relationgerative Database is also quite sensitive to I / O delays. When the completion of the I / O job must take a considerable time, SQL Server's performance will be greatly reduced, and the case where the block or dead junction may occur - because the resources are locked, how long does the job are completed? How long does the resource are locked, so it is prone to such problems. Solution of disk performance restrictions so we do to solve the problem of disk efficiency limit? In fact, the answer is not difficult. Follow the principles under the bottom, you should allow the I / O system to have an ideal performance performance: Isolated sequential I / O jobs have a sequential component in the magnetic disc area to maintain them itself. Sequential. The transaction record file is a file with a sequential access. If you put more step-by-step access files in the same disk area, the I / O operation will turn random because the disk must first find the correct object in different steps. Dispersion random I / O operations are randomly in this I / O operation mode, you can increase the number of disks to reduce the load. If you have enough magnets to master the load of random I / O, you will not encounter what bad problems will be encountered. As for how many magnets do it, how to plan them, we will explain later in this chapter. Disk arrays Overview You can imagine that the system has increased more and more disks, and the management of the system will become more difficult. In addition to it dozens of individual discs, many users prefer to improve their systems in a way that the RAID, Redundant Array Of Independnt Disks is selected. You can use software to plan a disk array with an existing I / O component, or purchase a hard-driven disk array device. In this section, you can learn what is RAID and its way of operation. As the name suggests, RAID is two or more disks consisted in an array. Although this array contains several solid discs, it is only a "logical disk" in the operating system, which is a "Disk Volume". This is true for users, applications and even Windows 2000 (if used hardware RAID). In most cases, this single logical disk is much larger than any of the discs you can buy. RAID not only allows you to have a large-capacity logical disk, but many different kinds of RAID hierarchy (RAID planning) provide disk fault tolerance, even if a disk in the array is accidentally damaged, RAID still can continue to operate . In a few sections later, you will learn about this fault tolerance and some of the features of different RAID hierarchies. As mentioned earlier, RAID can use software to plan, in fact, Windows 2000 can do it. However, it will be discussed in this chapter that the hardware foundation RAID level is discussed because they provide additional additional features. In the next two chapters, you can learn the features and features provided by different RAID levels. The idea of the I / O subsystem has the basic ability of the RAID in the hardware RAID control card. Most common hardware RAIDs have a control card. In this section, you can learn about the knowledge of the control card, the magnetic disc of the magnetic disc, and the difference between the internal RAID and the external RAID. In addition, you can also understand the nearest I / O technology: Storage Area Network. This section covers a wide variety of issues related to the RAID control card and the disk, and also includes the discussion of the bandwidth. Quickly control In order to improve the effectiveness of I / O, many vendors have caches in the control card. The "Control Card Express" refers to the RAM in which the device is controlled.
There are two purposes to get the memory: write quickly because there is a memory on the control card, when the data is written, the control card can inform the operating system (and the subsequent SQL Server) I / O action has been completed. In this way, it increases the performance of the write. Another use of the pre-read to get the control card is to read more information exceeded. This is expected that additional information may be available soon. As such, the response time will be daily shortened. In the next chapter, you will find that write efficiency is fairly decisive factors, especially using RAID 5 levels. In most cases, the control card will be a great advantage. However, you should still pay attention to the following two points: Do not use the write to use it without alternate electricity. Most of the control card build a battery or provide a similar function option. This battery can save data in a quick time when the power failure occurs. Without this battery, the information that will be taken will be lost, and the database is destroyed. The more rare situation is when RAID is full, it will seriously affect the performance of the read. This is because the previous write programs may have been taken by a time. The control card is really possible to enhance the effectiveness of the I / O subsystem in a reliable situation. When you understand different RAID hierarchies and their performance characteristics, you can set up the control card to perform the best performance of specific applications and requirements. Digital disgables are mostly built into memory. This quick tap is much smaller than the control card. It can master some requirements at a time, let the disk sorted it on a time. However, because this fast tap is really small (usually only a few KB), there is no need to read a lot of information. Many RAID control cards or SCSI control card manufacturers do not allow you to modify the status of disgovation of disks, however, some RAID manufacturers let you decide whether to close this cour. There are two basic types of in-line RAID RAID systems: in-line RAID and external RAID. Introduction and external interface, mainly according to the design of the RAID logic resident. Most of the system's RAID logic is resident in the control card, which is on the RAID control card installed in the host. This is the innermost RAID. The RAID logic of external RAID is in a storage unit that is always on disk, and Figures 5-5 show these two different systems. Each method has its own properties and features, but the difference between it is not the focus of this chapter. Figure 5-5 Introduction and External RAID
In the next section, you can learn about different RAID levels. These levels further distinguish between the difference between RAID control. Storage Area NetWorks "Regional Storage Network" (SAN) is one of the latest technologies on the market. SAN is basically a large external RAID that can share storage spaces between several systems. This is why there is NetWork this word in the name. When you manage and support the system from the control center, SAN can centralize space and reduce cost. The concept of SAN is quite forward-looking. A external RAID system is mainly to connect a host bus control card (HBA) to the RAID subsystem. As seen in Figure 5-6, SAN connects multiple HBAs through a switch (SWICH), oriented (at least) an external RAID system. This design allows the various systems on the SAN to access the RAID subsystem.
Figure 5-6 SAN system
When writing this book, multiple different systems can also share a logical disk through SAN. San's software segments the storage unit, and the logical disk machine is assigned to the specified system. In any case, SAN provides several advantages: Cluster SAN can be simply collected because it is an extensive RAID controller. Servo can share the benefits of all RAIDs. The core stored with a storage feature can reduce the problem generated when maintaining the storage system. Reducing space Wasting You can place excess spaces in several systems more productive use, rather than reserving additional disks alone to each system. All systems that are fault tolerant accessed on SAN can share online boryed spaces, so they can be replaced immediately when the disk machine is faulty. The frequency width problem of the control card and the bus bar is the disclosure of which is the other problem that you are still considered is the frequency width size of the I / O bushill (usually SCSI or Fiber Channel). The bus row has a certain clock speed and data width (32BITS, 64BITS, etc.), so the maximum flow is fixed. Your needs may just meet or exceed the bandwidth of the control card (PCI interface or other I / O bushill control card). To avoid this problem, you can separate the control card to different PCI bushors in the system. Most new types of computer systems have more than three PCI bush rows. High-order I / O subsystems for companies that require maximum performance (system execution time to more than 99.99%), will usually give storage issues such as EMC such storage vendors. These storage systems provide a precise I / O subsystem, including quick-taking spaces in a billion-gigabytes, multiple data paths (transmission pipelines) between the computer to disk. Multi-transmission pipeline provides a number of guarantees - if a component (probably I / O pipeline, control card or quick approach) has problems, the subsystem can continue to operate. If you have a pound of these problems, this type of subsystem that provides high-order performance is quite trustworthy. Elevator Sort "Elevator Sorting is a more efficient method that allows random I / O operations. When the random I / O requires transferred to a disk, the read writing must be randomly moved from the disk, as mentioned earlier, this random job can delay. Many RAID control cards support the elevator sort to let random search more efficient. In this type of RAID, these jobs can be sorted to reduce the amount of movement of the read writing head when multiple I / O jobs stay in the control card. The advantages of elevator sorting are similar to people from one floor from one floor to another. Imagine in a elevator building, people press the situation in the elevator. The elevator can get better than some floor, and people can effectively choose the floor they want to go. A real elevator will be more efficient because it can stay in a place that truly needs. The elevator sorting algorithm is also the same thing. If there is more than one I / O job request in the column, the control card can find the best way to empty the column, as shown in Figure 5-7. Figure 5-7 Elevator Sort
As you can see, the elevator sort can improve the efficiency of the disk search. The overall search time can be reduced - may sacrifice some individual search time. However, let the disk start the elevator type, you must have several or more I / O jobs that have not been resolved in the control card or disk. This situation is quite common when the I / O subsystem is overloaded. Generally speaking, the ideal disk operation is only one or two I / O homework that has not been resolved, so that the elevator is sorted unused. A disk reliability disk machine is one of a few components having some mechanical structures in a computer system. The disk rotates under high speed and high temperatures, and the parts of the motor and the bearing sooner or later have the day of life. The life of the disk is indicated by "Mean Time Between Failures, MTBF)" in the magnetic disc unit. This persistent time is an estimate of this. In other words, even if everyone's MTBF is the same, some dischargers can still have been used longer. MTBF of a typical disk machine may be 1,000,000 hours, which is 114 years. This can be a quite long time, but some disk opportunities will have a longer life, and some will be short-lived. The focus is that there is a mechanical device in the disk machine, which will be worn and will eventually stop. Common RAID hierarchical Overview RAID The most important feature is that two solid disks merged into a logical disk, which looks just a disk on Windows 2000 (and terminal displays). Logical disks can have hundreds of GB capacity, even if the 100GB hard disc is still in the world. The RAID hierarchy here will be applied to the technology of "Data Striping). Data segmentation can split the data to each of the magnets in the RAID logical disk, and then combine them. Each part of the divided is like "data band" or "data block". The size of the data belt is determined by the control card. Some control cards allow you to customize the size of the data, some are fixed values. The data fragment present on each of the disks can be regarded as a strip or block, but it is still collectively referred to after combining, as seen in Figure 5-8. Therefore, "Information Belt" can be used to describe the data fragment on a particular disk, as a "magnetic disc belt" or means a collection of information, such as "array band". Please pay attention to this when you read this chapter or other files related to RAID. The inner and external RAID logic, the RAID hierarchy can more show their respective features of different RAID array plans. One of the most important features is "Fault Tolerance). The fault tolerance function allows the RAID system to continue to operate in a disk failure. Fault tolerance is also the main purpose of the RAID control card. Because your information is quite precious, you must prevent the loss of the misunderstanding of the disc. In this section, you will learn some common RAID hierarchies: how they work, support which level of fault tolerance, and how fast they can have much. Some other RAID hierarchies are currently not often used, and they will only introduce several of the most commonly used. Figure 5-8 RAID data
RAID 0 RAID 0 is the most basic RAID hierarchy, only functionality of disk data segmentation. The data block can be created on each disk, the size is defined by the control card. As shown in Figure 5-9, RAID 0 disperse data into each block on each disk with a loop method, thereby establishing a large logical disk.
Figure 5-9 RAID 0
Although RAID 0 can be said to be a RAID, there is no meaning in this level ("Redundance") on this level. Because there is no repetition, there is no fault. If there is a disk failure in the RAID 0 array, the information will be lost. A fault of a disk, the destructiveness of the information will be lost in every sentence in this book, there is a fourth word. The result of this part of the information will make the array useless. RAID 0 recommends that it is generally not recommended to store SQL Server's information file with RAID 0. The information in the database is so important for your business, and the loss may cause serious damage. RAID 0 cannot protect you from the threat of disk failure, so you should not use it on critical system components, such as operating systems, transaction records, or database files. Note that the disk operates is a mechanical component and operates and works at high speed, high temperature. In other words, it will eventually be faulty. Therefore, it is quite important to establish a fault-tolerant system to protect the SQL Server profile.
RAID 1 RAID 1 is the most basic fault-tolerant RAID level. RAID 1, also known as "mirroring", can establish a copy of the data disk. As shown in Figure 5-10, this copy contains all information existing in the original disk. When a disk is erroneous, the mirror backup will completely replace the faulty disk; so, you will not lose any information. All information is saved in a hard disk (and its mirror backup), so there is no problem with data segmentation. RAID 1 copies the first disk in a second disk, in other words, the RAID 1 disk area space will only be equal to the space of one disk. It can be seen that RAID 1 is quite expensive - you must have twice the number of hard discs to meet the needs, and there will be no extra disk space after the system reply; however, you can have an excellent fault tolerance.
Figure 5-10 RAID 1
For a RAID 1 disk area, I / O jobs do not have the data before the data is fully written to the original disk and backup disk, the system does not think that this I / O is executed, so that the system is unlikely The goal of achieving the information is not lost - only the data has been completely written to two disk, in order to fail on a disk, the same information from another disk can compensate for this error. This means means that if a disk write information is longer than another disk, the delay time of the I / O job will be delayed in two disks. Calculate. This situation that must be written to two disks, reducing the performance of the entire logical disk. When we are calculating how many I / O jobs reach each disk in the array, you must multiply the number of writes in two. Read only on one disk. Since the read and write head of a disk machine is currently in which magnetic tracks may be different from the readwritten of another disk, the disk can operate at different rates. In other words, the search will take a long time. The disk opener is different from the writing of the writing head position, mainly because RAID 1 has an execution feature called "Split Seeks). Separation Search Allows the discs in the RAID 1 disk area to be individually, independent reading materials. This separation search can be performed because the read action only occurs on a disk machine in the disk area at a time. Most of the control card manufacturers support the separation search. Since the I / O amount can be dispersed to two magnets rather than by a single disk machine, it will help the search for the output of the array. However, it is because the read and write head of the disk can operate independently, but it is necessary to write the same write actions when writing, and the overall write delay is longer. That delay time is main. RAID 1 suggests RAID 1 to provide excellent fault tolerance and performance performance. When we can master all the information as long as we use a disk machine, RAID 1 is the best solution. The use of RAID 1 is as follows: RAID 1 is used on a disk containing the job system because the reconstruction job system is a quite time-consuming effort. The reason why RAID 1 is a good choice because the homework system usually uses a disk machine to fully install it. Use RAID 1 to store transaction records. Similarly, the transaction record of SQL Server is also a disk that can be accommodated. In addition, many transaction records perform sequential write actions, only the situation reads when replying is the job. Therefore, you can independent transaction records in their respective RAID 1 disks, to achieve high-rate execution performance. Use write quick get in the RAID 1 disk area. Since the write action of RAID 1 must be completed twice to complete, the use of the use can be used to ensure the efficiency of its write. When using a write-hour, pay attention to whether the alternate power is properly placed. You will see later this chapter, if you exceed a disk, you can use other fault-tolerant schemes. RAID 1 is the best way only when the fault is required and a disk is required. RAID 5 RAID 5 is a fault-tolerant RAID hierarchy that protects the information using "Parity". Each array information will create a partner in a disk on the data tape. This assignment can operate together with other disks on the array data tape to reconstruct information on any other disk machine on the data. As a result, RAID 5 can allow information to be lost in an array. The same bit is stored on a different disk machine in the array in a loop, as shown in Figure 5-11. Figure 5-11 RAID 5
The advantage of RAID 5 is that the disk space available in this RAID level is the total combination of the N-1 disk machine, and n is the total number of disks in the array. As a result, the RAID 5 disk array composed of 10 dischargers will have a capacity of 9 dischargers, which can be said to be quite economical tolerance. Unfortunately, RAID 5 has a disadvantage that RAID 5 is not possible. Maintaining the same information requires additional system time. When the data is written to the RAID 5 array, you must first read the target disk data with the same data belt, calculate the same information, and then write the data to the two data. Therefore, a RAID 5 writes is actually four I / O jobs. RAID 5 is in RAID 5, and the same bit element is based on each of the data belts of all disks. The same bit element is a portion of the data to be added - when a crime element is generated, the same bit may determine why other bit elements. The same bit is mainly established in accordance with the overall part of the other bit, and the value of the same bit is (0 or 1) is determined by the same bit reference as an even number or odd. When a bit is lost, the value of the lost bit element can be obtained by calculating the same bits and the remaining bit. Let us look at an example of operational operations. In this example, we assume that there is a RAID 5 system composed of 5 deals. Each of the discs has been built with a number of bits, starting from the first portion of the data belt on the disk and ends the last portion of the data belt on the disk. The same bit is based on the bit dollar on each disk. In this example, we assume that the same reference is even, in other words, the total generals of all bits must be 0. If the first bit element of the first disk is 0, the first bit element of the second disk is 1, the first bit of the third disk is 1, the fourth disk One bit is 1, then the same bit element must be 1 to make the bit element aimed as an even number, as shown in Table 5-2. Table 5-2 RAID Symnial Section Examples
Map 1-bit 1-disc 2-bit 1 magnetic disc 3-bit yuan 1 magnetic disc 4 bits 1 disk 5 hi-position element total 011114 (even)
Therefore, thinking about the meaning of establishing the same bite check at a single bit. Although there may be no number of packets on disk data tape, you only need to reply to the original appearance as long as you build homology in a single bit. As you can see in Table 5-2, the homing is actually only built on a separate bit in the data tape. Even if the data block or data tape of the disk damage may be 64kb or more than 64kb, as shown here, we have established its homologous at the stage stage. In fact, the same bit check is calculated in a more sophisticated algorithm. Now, it is assumed that the disk 3 is faulty. In this case, since the headquarters must be an even number, the bodies can be replied to the bit elevation that is lost on the disk 3 as long as the bits can be used in the same manner. Building homogeneity is as seen, RAID 5 generates homogeneous methods, first to find the total sum of the bit elements of all the disk players in the array, and then establish the synonym element to make the total result Even the number. You can imagine getting, if each I / O job, the control card must go to all disks to read all materials, which is quite unrealistic. The effectiveness is never, and the speed is also slow. When RAID 5 is established, the data is zero, and the same bit is beginning to be established. Your RAID 5 disk collection will have no information but have a complete integrated bits. A focus is that when the information is written to the disk, the data disk and the homologous disk must be read first. The new information is compared to the old data. If a particular bit is changed, the bore will change the bit. This process is done in a mutective or xor operation. As a result, just read the data disc and the boreal disc, not all disks need to be read. This operation is completed, and the data disk and the hosae can have a write action because the same job will affect the entire data. Therefore, each data is written to the RAID 5 disk area, four entities I / O jobs can cause two entities (read data, reading the same information at a time), two writes (write back information and Write back to the same information). However, in the RAID 5 array, the same information is dispersed, so the I / O amount can be equipped with each disk to the array. RAID 5 recommends that due to the write actions in RAID 5 will cause additional I / O jobs, this RAID level is recommended to use the disk area that performs read actions in most. Because of the same information dispersion in different disks in the array, all disks can be used to read actions. Based on this feature, our recommendations are as follows: Use RAID 5 in the only missed disk area. RAID 5 should not be used when any disk area has more than 10% write demand. Use write quick get in the RAID 5 disk area. Because the write action of RAID 5 is completed when two reads are completed, it can improve the response time of writing through writing. (When you use write quick break, you must determine if the reserve power is normal.) However, writing is not allowed to make your hard drive can drive long-term speed, you must still pay attention to the capacity of these disks. As you can see, although RAID 5 is equivalent to the economy, it must pay the cost of effectiveness. This chapter will see this price later. RAID 10 RAID 10 is a complex of RAID 0 and RAID 1. The mirrored backup containing the disk data belt in RAID 10. Each disk has its own replica, but only part of the data is included in each disk, as shown in Figure 5-12. This level can provide RAID 1's fault tolerance and RAID 0 advantage in convenience and efficiency. Like RAID 1, each RAID 10 writes to cause two entity I / O jobs - once is a disk that is written to the mirror backup. Therefore, when calculating the number of I / O jobs of each disk, you must multiply the write action by 2.
Another feature of RAID 1 is that the I / O job of RAID 10 is completed twice to complete, and therefore, the write delay will increase. However, as RAID 1, most control cards support separation search in RAID 10. RAID 10 provides high-order fault tolerance. In fact, even if the failed hard disk exceeds more than one, the array can still survive. Of course, if the mirror backup is lost, the information will not be able to make up. If the mirror backup is stored separately on all the disk cabinets, even if a disk cabinet is full, it is still possible to play its role. Figure 5-12 RAID 10
RAID 10 recommends that RAID 10 provides high-order performance and fault tolerance. When we need a large disk area and the I / O operation writes more than 10%, RAID 10 should be used. The relevant recommendations of RAID 10 are as follows: Use RAID 10 when the write job of the array has more than 10%. For a large number of write actions, the efficacy of RAID 5 will not be able to compare with RAID 10. Use RAID 10 when the performance problem becomes a system key. RAID 10 supports separation search and you will get an excellent performance performance. Write is used in the RAID 10 disk area. Because the write action of RAID 10 must be completed twice to complete, it can improve its write performance through writing. Writing is only safe when you reserve electricity is properly connected. From the viewpoint of protection and efficiency, RAID 10 is the best fault tolerance scheme, but it is quite expensive. You must purchase the number of discs required for RAID 0. If your disk area is much more, RAID 5 may be more acceptable. RAID hierarchical performance is relatively correct planning and tuning your RAID system, you must understand different RAID hierarchical differences. As long as you understand how the RAID system operates and its implementation in different situations, you can make the I / O subsystem to make better. You see different performance features in the previous section, this section will compare more detailed. Reading Effectiveness The RAID level of the RAID will not have a significant impact on reading efficiency. When the RAID disk area performs reading job, each disk is welcoming the performance of the disk area. Since random I / O homework is the crux of most performance issues, this type of trouble will be resolved here. You can separate the sequential I / O jobs independently in your disk disks, so that step-by-step execution can have the greatest play. The following is a random read performance description in different RAID hierarchies: RAID 0 disk area divides the data average in all disks in the array. Therefore, random I / O homework should also be averaged all disks in the system. If we estimate that a disk can be grasped by 85 random I / O homework per second, then a RAID 0 array consisting of 10 disks should be able to grasp 850 random I / O jobs per second. RAID 1 Disk area supports separation search, so there are two magnetic discs to perform read jobs simultaneously. In other words, RAID 1 can support two times the number of readings that can be achieved, that is, 170 I / O homework per second. If the read action is more frequent than this, the performance will become very bad. The RAID 5 array divides the data average in all disks in the array. A disk is treated as an inherent in each split. Because I / O action is arbitrary, all disks will be used. The RAID 5 array read data efficiency is 85% of the RAID 0 array, and how much will affect the efficiency of SQL Server. RAID 10 arrays, just like RAID 1 arrays, support the functions of separating search. Therefore, its maximum effectiveness is equal to the total number of disks multiplied by 85 I / O operations per second. You may put more frequent I / O homework on initial planning, but they will not complete the entire I / O action as you vision. As you can see, the read capacity of a RAID array is quite easy to calculate. Just add enough disks to deal with your I / O demand, and pay attention to these restrictions, you will allow system smoothly to optimize. Writing Effectiveness The RAID control card type you use will have dramatic impact on write efficiency. Similarly, random I / O homework is the crux of most performance issues, which will also be discussed here. You can separate the sequential I / O jobs independently in their single or multiple disk disks, so that sequential execution can have the greatest play. The following is a RAID hierarchy that is randomly written in different RAID hierarchies: RAID 0 is the RAID hierarchy that is best to write jobs without causing performance reduced, but it cannot have fault tolerance.
Since RAID 0 is neither a mirror backup, it is not used in boreal inspection, and its efficiency is simple from each of each disk. That is, a RAID 0 array consisting of 10 disk can operate 850 random writes per second. The RAID 1 array must work for all written arrays to mirror backup. Therefore, a single write array works, the disk will produce two I / O jobs. Therefore, a RAID 1 array has only one single disk capacity, namely 85 I / O jobs per second. RAID 5 is slower in the performance of the write job. The write job of a RAID 5 actually generates two actions read from the disk and two operations of the two write discs. That is, a job written to the RAID 5 array will result in four entity I / O homses on the disk. Therefore, the write capacity of the RAID 5 array is equal to one quarter of the disk writer in the array. RAID 10 has the same write feature as the RAID 1 array. Each job writes to the RAID 10 generates a write action for two entities. Therefore, the write capacity of the RAID 10 array is equal to one-half of the write capacity of the disk opener in the array. If you see, calculating a RAID array is quite complicated. Just add enough disks to deal with your I / O demand, and pay attention to these restrictions, you will allow system smoothly to optimize. In the next section, you will learn how to calculate the number of I / O jobs per disk in different situations. The disk calculation To determine the load of each disk in the system, you have to do a little calculation. If you are using a hard body RAID control card, your amount of I / O jobs you read on the Vigification Monitor panel refers to the I / O job of arrival of the array. The amount of additional I / O generated by the control card is not displayed. In fact, Windows 2000 has not been able to understand the interface of this data, but you must realize that these additional parts do exist, and must decide how many disks that need to be achieved according to these data can achieve ideal performance. Some of the calculations listed below can help you determine how much I / O job actually reach each disk in your array: RAID 0 RAID 0 calculation method per disk I / O operation rate per disk is first array The number of reads is summarized from the number of writes, and then in addition to the total number of disks in the array. RAID 0 only requires the following simple formulas to calculate its I / O operation rate: the number of jobs per disk = (read quantity write quantity) ÷ disk number
The calculation of RAID 1 RAID 1 is slightly more complicated. Since the write quantity needs to be doubled, the number of job per disk is equal to the number of writes multiplied by the second plus the number of reads, and then divided by the number of disks in the array (RAID 1 array is 2 magnets). The formula is as follows:
The number of jobs per disk is = (number of reads (2 × writing)) ÷ disk number
RAID 1 is slower in writing but can provide high-order fault tolerance. RAID 5 RAID 5 provides fault tolerance, but writes will take a lot of time. The read of RAID 5 is also dispersed to different disks in the array, but the writing job is actually four entity I / O jobs. To calculate the number of individual I / O jobs per disk, you must multiply the number of writes to add the number of reads and then divide the disk in the array. Therefore, the formula of RAID 5 is as follows:
The number of jobs per disk = (number of reads (4 × write quantity)) ÷ disk number
RAID 10 RAID 10 is like RAID 1, slower, but can provide high-order fault tolerance. The calculation method of RAID 10 is the same as RAID 1. Since the write quantity is doubled, the number of jobs per disk I / O equal to the number of writes multiplied by the second plus the number of reads, and then divided by the number of disks in the array. The formula is as follows:
The number of jobs per disk = (the number of reads (2 × write)) ÷ The number of disks is compared to make us more comparisons directly in the various RAID levels. This way may help you decide which RAID level is the best choice for your system. When you compare these RAID level I / O performance, one of the most important factors is the ratio of reading and writing number. These different RAID hierarchies are mostly equal in the execution of the read, only different in writing efficiency. You should also consider whether your system needs to be fault tolerant. Finally, you should consider the ratio of "Price / Space". Table 5-3 summarizes these different RAID levels.
Table 5-3 Comparison of RAID hierarchies
RAID level performance fault tolerant price RAID 0 Best non-fault function economy RAID 1 Jiajia expensive RAID 5 read fast, write slowly, the most economical fault tolerance RAID 10 Jiajia expensive
You will find that your best choice comes from your needs. To understand the difference between the RAID 5 and RAID 10 in different "read / write" ratios, refer to the following table. Table 5-4 Take 10 magnetas, 500 I / O work per second as an example, showing the differences between the two "read / write" ratios:
Table 5-4 RAID 5 and RAID 10 comparison table
"Read / write" ratio RAID 5 I / O job (read number (4 × write quantity)) ÷ Disk number RAID 10 I / O job (read number (2 × write quantity)) ÷ ÷ 数 0% write (500 0) / 10 per disk I / O operation number 50 (500 0) / 10 per disk I / O job number 5090% read 10% write入 (450 200) / 10 per disk I / O operation number 65 (450 100) / 10 per disk I / O operation number 5575% read 25% write (375 500) / 10 per disk I / O operation number 87.5 (375 250) / 10 per disk I / O job number 62.550% read 50% write (250 1000) / 10 per disk I / O operation number 125 (250 500) / 10 Each disk I / O job 750% read 100% write (0 2000) / 10 per disk I / O operation number 200 (0 1000) / 10 per disk I / O job number 100
You will find that before the ratio is 90%, the use efficiency of the disk before the 10% write is written; however, as the write ratio is gradually increased, RAID 5 takes longer. I / O Delay and SQL Server Since the SQL Server engine often performs multiple transactions simultaneously, SQL Server is quite sensitive to I / O delays. In general, a SQL Server library often needs to face dozens or hundreds of positive implementation applications. To support this simultaneousity, SQL Server has a complex system for locking information columns, paging, scope, and data sheets. When some data or SQL Server resources are locked, other programs must wait until they are locked to use them before they can use them. If an I / O job consumption is too long, these resources are longer than the normal situation, which will more seriously delay other programs in the system. In addition, this also enables the system to form a deadline. When I / O takes a long time to complete, the longer the resource lock is, the higher the problem. As a result, individual delays can accumulate multiple "Snowball Effect", causing system paralysis. Furthermore, the query program will be particularly slow. For example, if your system is scanning a long information sheet, you usually have to read hundreds of thousands or even millions of data columns to complete this task. When the I / O job requires millions, even a slight change in efficiency will cause serious consequences. One million homework is about 2.8 hours to complete. If your I / O subsystem has been overloaded, and each I / O job will take 40ms, the time to complete this query will increase to 11 hours. If you see, short-range space or bad I / O subsystem planning may cause SQL Server to decline seriously. Design your I / O subsystem within the capacity range of individual components to optimize your system performance. Planning SQL Server Disk Architecture This chapter has been mentioned before, you should apply for your I / O system to avoid overloading of the system. Once the I / O subsystem exceeds overload, the I / O delay will increase dramatically, and the effectiveness of SQL Server will also drop. In this section, you will learn how to construct a SQL Server system that can work smoothly within the subsystem limit. The first part of the course will tell you how to determine the I / O demand for the system. Then you will plan your system and create your system in the end. Determine I / O demand for a system that is still in a conceptual phase, determines its I / O demand is quite difficult. However, if your hand is less than letting you think this is an impossible task, it is recommended that you try to collect enough information, at least a professional prediction. In any case, you must pay special attention to the establishment of a system that cannot be expanded is never a good idea. Always reserved some capacity and efficacy to prepare from time to time, because you may need them soon. In principle, you should design a system that meets at least the minimum demand based on the required storage space and the effectiveness. In the next section, you will see how to decide the number of disks in accordance with these factors. Space To determine how much space is needed in your database, it is not a very complicated thing. The amount of space is equal to the sum of the following requirements: the required space required for the required spatial specification required space Requires the space required for the required space, the space required to accommodate the new information of the database. Your business and customers may make your database grow into a huge giant. To determine your system growth rate, you can regularly check the existing database and calculate the size of the spatial quantity of the data library for different periods. The calculation of growth rate should be at least few months to see its trend line. The result of the result may be shocked.
To estimate the growth amount of a system without past, you need to multiply the estimated data column size in addition to the number of product orders. Sustained this one period (probably a few months or years), you can get a probably a growth rate of information archives. However, you cannot know the growth amount of the index. The demand for each data column index space is calculated based on the structure and amount of data of the index. A complex index takes more information for each column space than a simple index. Next you can decide that your system will be two years, five years or longer. This should be made to determine the space needs of the I / O subsystem. Once you determine the amount of data, index size, data library temporary space requirements and growth rates, etc., you can calculate the required disk space. You must then take into account the part of the RAID fault tolerance. Remember, RAID 1 or RAID 10 (data mirror backup) takes up half of the solid disk. RAID 5 occupies a space of a disk in the array. In addition, remember that the data provided by the manufacturer refers to a disk space that has not been formified. A 9.1GB of hard drive is actually 8.6GB after formatting. When you calculate the quantity of the current needs and the amount of time to grow demand, you should move step to the next focus: efficiency. It is necessary to plan the ideal I / O subsystem, and the demand for space and performance is not biased. The performance light is simple to comply with the spatial demand, still not enough. If you see this chapter, the planning method of the I / O subsystem can be a fierce fierce effect, or it can be a promotion of performance. However, the performance demand for determining the I / O subsystem is not as simple as determining space needs. The best way to determine the performance demand is to observe a similar application or system. The resulting information can give you a starting point for speculating future demand. In Chapter 6, you can learn more about this. If you are lucky enough to find a similar system, you can use the information collected from this system and the information you need to provide you with this chapter. Remember which RAID hierarchy should be used to consider the I / O subsystem. The next step is to start planning the SQL Server disk architecture and then perform your solution. Planning the disk architecture planning disk architecture means that the location of the data is about to be stored, and the SQL command file for generating the database. The advantage of using the SQL command file is that the command file can also be modified by the command file not only reuse, if necessary, you can also modify it. The command file must take into account how much logical disk area has your system, and how many physical discs are in the disk area. Balancing your database is quite important, so the number of I / O jobs per second per disk should be consistent. An imbalanced system that will be dragged by the slowest disk area in the system. Through some means of optimization, you should determine that the transaction record and data files have been dispersed into all disks in the system. The process of recorded planning planning transaction records is quite simple. It is the most common practice using only one data file to place transaction records. If you must increase the record file into the database, you must make sure they are placed in the field of RAID 1 or RAID 10. It is also important to note whether this transaction record is independent of information or other transaction records. The planning of the profile to plan the I / O subsystem of the data file, the best way is to plan the size of each disk region as the size of the magnetic disc. Generally speaking, you don't need to split the I / O subsystem to a multiple disk area. In fact, if you use only a logical disk area across the overall control card, you will be quite satisfactory. However, you should not use Windows 2000 split technology to span multiple control cards because it increases unnecessary resource waste. Tips Tips For your information file, how much disk can be used to connect to how much disks that can be connected in series as much as possible. This allows the control card to disperse the data into a plurality of disks. Do not use Windows 2000 split technology to flow across multiple control cards. This will cause a significant burden of CPU.
If you use multiple control cards, you can make each control card on the number of disk numbers in the split, so that your plan is more simple. If you are unable to let each control card are connected in series to the same number of disks, at least the database should be stored in a certain ratio. For example, if you use two disk disks, a 20-sized disk and the other is 10 devices, you should build a file group consisting of two data files. (In Chapter 9 you will be able to learn more knowledge using the archives and archives group.) The first data file should be twice as large as the second data file, the first data file is put into 20 disk The disk area, the second data file is placed in the disk area of 10 disk. When the data is loaded into the file, SQL Server loads the data of the first data file, which will be used in the second data file. This can be maintained closely close to the I / O load of each disk. The execution of planning When you have developed the SQL command file you use to generate the database, the next is a good wait for the results of the execution. If you have an error, it is not necessary to correct the problem immediately as expected to build the database, instead of the information that the information has begun to load and the user has begun to access the system. The use of the SQL command file is that you can modify the command file when necessary, or repeatedly execute the command file. Under the way, it is an example of a command file. This command file has multiple file groups in multiple files to extend the database in several control cards:
- SQL Script to create A Database over Severage Files - SQL SCRIPT TOE CREAL FILES
- D:, E:, AND f: for data. E: AND f: Have TWICE THE NUMBER
- of Disk Drives As D: SO They Get Allocated TWICE THE
- Database size as d :. l: is buy for the log.
-
Create Database Demo
On
PRIMARY (Name = DEMO1,
Filename = `d: /data/demo_dat1.mdf ',
Size = 100MB,
MaxSize = 200,
FileGrowth = 20),
(Name = Demo2,
FileName = `E: /DATA/demo_dat2.ndf ',
Size = 200MB,
MaxSize = 200,
FileGrowth = 20),
(Name = Demo3,
Filename = `f: /data/demo_dat3.ndf ',
Size = 200MB,
MaxSize = 200,
FileGrowth = 20)
LOG ON
(Name = DEMOLOG1,
Filename = `l: /data/demo_log1.ldf ',
Size = 100MB,
MaxSize = 200,
FileGrowth = 20)
Go
