Several indexes of performance measurement of database-io system

Transfer from http://storage.it168.com/a2011/0323/1169/000001169755_all.shtml

As a database administrator, pay attention to the performance of the system is one of the most important tasks, and in all aspects of attention to performance can only IO performance is the most troublesome piece, facing a variety of jerky parameters and dazzling new terminology, coupled with the memory maker's flicker, always let us have a foggy feeling. This series of articles attempts to summarize the various concepts related to disk storage from the basic concept, so that we can have a more comprehensive understanding of the basic concepts related to IO performance and the monitoring and tuning of IO performance.

In this section we will first discard various complex storage systems and directly study the performance of a single disk to understand the various metrics and relationships that measure the power of the IO system. It is important to note that this article discusses only disk IO performance and network IO performance is not taken into account.

　　A few basic concepts

Before studying disk performance, we must first understand the structure of the disk and how it works. However, it is not repeated here that the relationship between the structure of the hard disk and the working principle of the information can refer to Wikipedia above the relevant terms--hard disk drive (English) and hard disk drives (Chinese).

　　Read-write Io (read/write io) operation

Disk is used to give us access to data, so when it comes to IO operation, there will be two corresponding operations, the data when the corresponding to the write IO operation, the data when the corresponding is read IO operation.

　　Single IO operation

When the controller of the control disk receives the read IO operation instruction of the operating system , the controller sends a read instruction to the disk, and the address of the block to be read is passed to the disk, and then the disk will pass the data read to the controller and returned by the controller to the operating system , completes a write IO operation, similarly, a write IO operation is similar, the controller receives writes the IO operation instruction and to write the data, and passes it to the disk, the disk after the data writes completes the operation result passes back to the controller, then the controller returns to the operating system, completes a write IO operation. A single IO operation refers to the completion of a write IO or read IO operation.

　　Random access with continuous access (sequential access)

Random access refers to the sector address given by this IO and the last IO-given sector address is relatively large, so that the head in the two IO operation between the need for a relatively large mobile action to restart reading/writing data. Conversely, if the sector address given by the secondary IO is consistent or close to the last IO end of the sector, the head can start the IO operation very quickly, and multiple IO operations are called continuous accesses. Therefore, although the adjacent two IO operations are emitted at the same time, they can only be called random access if their requested sector address is very different, rather than continuous access.

　　Sequential io mode (Queue mode)/concurrent IO mode (Burst mode)

The disk controller may issue a series of IO commands to the disk group at one time, which is called sequential IO if the disk group can only execute one IO command at a time, called concurrent IO when the disk group is able to execute multiple IO commands at the same time. Concurrent IO can only occur on a disk group consisting of multiple disks, and a single disk can only process one IO command at a time.

　　Size of single io (io Chunk size)

People familiar with the database will have this concept, that is, the database storage has a basic block size (block size), whether it is SQL Server or Oracle, the default block size is 8KB, that is, every time the database reads and writes are in 8k units. So what is the level of a single read and write disk for a fixed 8k size that is emitted by a database application, and what is the size of a single IO operation data for a read-write disk, or is it a fixed value?

The answer is not sure. First, the operating system introduces the file system cache to improve the performance of IO, the system will put multiple requests from IO in the cache according to the request data, and then commit to the disk again, that is to say, the read operation of multiple 8K blocks emitted by the database May be placed in a disk read IO to be processed.

There is also a cache for some storage systems, and after receiving an IO request from the operating system, the IO requests from multiple operating systems will be combined into one for processing. Whether the cache at the OS level or at the disk controller level, there is only one purpose, which improves the efficiency of data reading and writing. Therefore, each individual IO operation size is not the same, it depends mainly on the system of data read and write efficiency judgment.

When the size of an IO operation is small, we become small IO operations, such as 1k,4k,8k, and when the data volume of an IO operation is compared, it is called a large IO operation, such as 32k,64k or even larger.

When we talk about block size, we usually have access to a number of similar concepts, like the one we mentioned above in the database, the smallest management unit, Oralce called block, the size is generally 8k,sql server called page , the general size is also 8k.

In the file system we can also encounter a file system block, in the current many Linux systems are 4K (through/usr/bin/time-v can see), it is actually the role of the database inside the block/page is the same, all for the convenience of data management. But when it comes to the size of a single IO, it is not directly related to the size of these blocks, and the single IO size in English is often referred to as IO Chunk size, which is not said to be IO block size.

　　IOPS (IO per Second)

The number of IO operations performed by the Iops,io system per second is an important parameter to measure the system IO capability. For an IO system consisting of a single disk, calculating its iops is not a very difficult task, as long as we know the time it takes the system to complete an IO, we can calculate the system ioPS.

Now we're going to calculate the ioPS of the disk, assuming that the disk's RPM (rotational speed) is 15K rpm, the average seek time is 5ms, and the maximum transfer rate is 40mb/s (read and write speed as the same, the actual difference is larger).

A complete IO operation for the disk is done: When the controller sends an IO command to the disk, the drive arm (actuator arm) of the disk takes the read-write head (head) out of the landing area (Landing zone, which is located in an area with no data in the inner ring), Moving to the front of the track where the initial block of data is to be manipulated is called addressing (seeking), and the time spent is referred to as the addressing time (Seek times), but finding the corresponding track does not immediately read the data, and the head waits for the disk disc (Platter) rotates to the sector where the initial data block (Sector) falls directly above the read-write head before the data is read, and the time spent in the waiting disk to rotate to the operable sector is called the rotational delay (rotational Delay), followed by the disk rotation, the head constantly read/write the corresponding data block, until the completion of the IO to complete the operation of all the data, this process is called Data Transfer, the corresponding time is called the transfer Time (Transfer). Once the three steps have been completed, an IO operation is completed.

When we look at the drive manufacturer's flyer, we often see 3 parameters, namely average addressing time, disc rotation speed, and maximum transfer speed, and these three parameters provide us with the time to calculate the three steps above.

The first addressing time, taking into account that the data being read and written may be on any track of the disk, both possible on the disk's most inner ring (the shortest addressing time), may also be in the disk's most outer ring (the longest addressing time), so in the calculation we only consider the average addressing time, that is, the average addressing time indicated in the This uses 5ms of the current maximum 10KRMP HDD.

The second rotation delay, and the same as addressing, when the head is positioned after the track may be good to read and write on the sector, this time is no need for additional delay to read and write to the data immediately, but the worst case is really to rotate the disk after a full circle of the head to read the data, so here we also consider the average rotation delay, For a 10krpm disk is (60s/15k) * () = 2ms.

The third transfer time, the disk parameters to provide our maximum transmission speed, of course, to achieve this speed is very difficult, but this speed is disk pure read and write disk speed, so as long as given the size of a single IO, we know how much time the disk will take on the data transfer, this time is IO Chunk Size/max Transfer rate.

　　IOPS Calculation formula

Now we can draw a formula that calculates the single IO time:

　Io time = Seek time + sec/rotational SPEED/2 + IO Chunk size/transfer rate

So we can calculate the IOPS like this.

　　IOPS = 1/io time = 1/(Seek time + sec/rotational SPEED/2 + IO Chunk size/transfer rate)

For a given different IO size we can draw the following series of data

4K (1/7.1 ms = IOPS)
5ms + (60SEC/15000RPM/2) + 4K/40MB = 5 + 2 + 0.1 = 7.1
8k (1/7.2 ms = 139 IOPS)
5ms + (60SEC/15000RPM/2) + 8K/40MB = 5 + 2 + 0.2 = 7.2
16K (1/7.4 ms = 135 IOPS)
5ms + (60SEC/15000RPM/2) + 16K/40MB = 5 + 2 + 0.4 = 7.4
32K (1/7.8 ms = IOPS)
5ms + (60SEC/15000RPM/2) + 32K/40MB = 5 + 2 + 0.8 = 7.8
64K (1/8.6 ms = IOPS)
5ms + (60SEC/15000RPM/2) + 64K/40MB = 5 + 2 + 1.6 = 8.6

As you can see from the data above, the smaller the single IO, the less time is spent on a single IO, and the greater the corresponding IOPS.

Above our data are in a more ideal hypothesis, the ideal situation here is that the disk to spend the average size of the address time and the average rotation delay, this hypothesis is actually more in line with our actual situation of random read and write, in random Read and write, The addressing time and rotation delay of each IO operation are not negligible, and the presence of these two times limits the size of the IOPS. Now we consider a relatively extreme sequence of read and write operations, such as reading a very large storage of files continuously distributed on the disk, because the distribution of the file storage is continuous, the head after the completion of a read IO operation, do not need to new addressing, nor need to rotate the delay, In this case we can get to a very large IOPS value, as follows

4K (1/0.1 ms = 10000 IOPS)
0ms + 0ms + 4K/40MB = 0.1
8k (1/0.2 ms = IOPS)
0ms + 0ms + 8K/40MB = 0.2
16K (1/0.4 ms = 2500 IOPS)
0ms + 0ms + 16K/40MB = 0.4
32K (1/0.8 ms = 1250 IOPS)
0ms + 0ms + 32K/40MB = 0.8
64K (1/1.6 ms = 625 IOPS)
　　0ms + 0ms + 64K/40MB = 1.6

The gap is very large compared to the first set of data, so when we want to measure the performance of an IO system with IOPS, we have to be clear about what the ioPS are, that is, how to read and write, and the size of a single IO, of course, in practice, especially in OLTP systems, The reading and writing of random small io is the most persuasive.

　　Transfer speed (Transfer rate)/throughput (throughput)

Now we're going to say that the transfer speed (another common claim to throughput) is not the maximum transmission speed indicated on the disk, or the ideal transfer speed, but the amount of data that the disk has flowed from the disk system bus when it was actually used. With the IOPS data, it's easy to calculate the corresponding transmission speed.

　　Transfer rate = IOPS * IO Chunk Size

Or the first set of IOPS data on top of that, we can draw the corresponding transmission speed as follows

4k:140 * 4K = 560k/40m = 1.36%
8k:139 * 8K = 1112k/40m = 2.71%
16k:135 * 16K = 2160k/40m = 5.27%
32k:116 * 32K = 3712k/40m = 9.06%

It can be seen that the actual transmission speed is very small, the utilization of the bus is also very small.

It is important to make a clear concept here, that is, although we use IOPS to calculate the transfer speed, there is actually no direct relationship between the transfer speed and IOPS, and in the absence of caching, the common determinants are the way the disk system is accessed and the size of the individual IO. Random access to a disk allows us to measure the performance of a disk system using IOPS, where the transfer speed is not too large, but when continuous access to the disk, the IOPS at this time is no reference value, this time limit the actual transfer speed is the maximum transfer speed of the disk. Therefore, in practical applications, the performance of random read and write of small IO can only be measured with IOPS, and when it comes to measuring the performance of large IO continuous reading and writing, it is necessary to use transmission speed instead of IOPS.

　　IO response times (IO Response time)

Finally, look at the IO response time that can directly describe IO performance. IO response time is also known as IO latency (io Latency), IO response time is a read or write IO command from the operating system kernel to the operating system kernel to receive the IO response time, attention not to be confused with the individual IO time, A single IO time refers only to the amount of time that IO operations are processed inside the disk, and the IO response time also includes the wait time that the IO operation spends in the IO wait queue.

The calculation of the time consumed by the IO operation in the wait queue has a queueing model derived from the Little theorem (S law) M/M/1 model can be followed, because the queuing model algorithm is more complex, until now has not been very clear (if anyone who is proficient in the M/M/1 model is welcome to give guidance), Here's a list of the final results, or the IOPS data calculated above:

8K IO Chunk Size (135 IOPS, 7.2 ms)
135 = 240.0 ms
29.5 ms
15.7 ms
10.6 ms
64K IO Chunk Size (IOPS, 8.6 ms)
135 = didn't respond ...
88.6 ms
24.6 ms
14.6 ms

As can be seen from the above data, as the system actual IOPS closer to the theoretical maximum, the response time of IO will be non-linear growth, the closer to the maximum value, the response time becomes larger, and will exceed the expectations of a lot more. Generally in the actual application there is a 70% guideline value, that is, in the IO read-write queue, when the queue size is smaller than the maximum iops of 70%, the response time of the IO will be small, relatively acceptable, once more than 70%, the response time will dramatically increase, So when a system's IO pressure exceeds the maximum tolerable pressure of 70%, it is necessary to consider the adjustment or upgrade.

Adding that this 70% guideline value also applies to CPU response time, which is proven in practice, and once the CPU exceeds 70%, the system will become slow. It's a very interesting thing.

Several indexes of performance measurement of database-io system