Discuss serious iowait problems caused by intensive transactions

This article discusses the serious problem of iowait caused by intensive transactions. 1. Overview in linux, a database server with dual-sata disks and RAID 1 is installed. I ran a stored procedure and inserted tens of millions of records into a table. This is an intensive small transaction operation. In iostat detection, a large number of I/O waits are found, and the read/write speed is always low, which is around 10 MB per second. $ Sudo iostat Linux 2.6.30-2-amd64 (lukahn) 12/12/2009 _ x86_64 _ (2 CPU) avg-cpu: % user % nice % system % iowait % steal % idle 7.13 0.00 1.94 27.96 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 144.02 198.41 11139.44 9322418 523399320 sdb 143.78 165.59 11137.39 7780516 523303000 hda 0.01 0.05 0.00 2492 0 md0 0.66 6.44 0.71 302538 md1 33496 7.32 67.02 7.51 3148938 352960 md2 24, 6.08 0.02 18.95 11277610 890584 md3 1389.80 46.85 11106.55 2201410 521853640 md4 0.41 3.03 0.21 142322 from iostat, the cpu consumes a lot of resources on iowait. The tps value is always low, and the read/write speed per second is not high. However, when a large file is created for testing, the speed can still be up to MB per second. Therefore, the customer said that our disk speed is not slow, and the problem lies in your application. 2. I have learned a concept here. I am also familiar with storage. Here I am talking about what I understand, not necessarily. We know that when purchasing a disk, we say 7200 to every minute, and 15000 to no minute. This metric is for mechanical hard disks. In this case, the conversion rate from 7200 to 120 is 15000, and the conversion rate from 250 to is. Each transition is the disk track addressing. Here, disk track access involves Sequential operations and Random operations. The former is used for large-sized data transmission such as kb, and the latter is small-sized such as 4 kb. This is easy to understand. For example, if you create a tablespace with a size of 10 GB at a time, it is a Sequential operation and a large data transfer operation. If multiple small transactions insert intensive records to a table in the database, it is a Random operation. However, the number of operations supported by a disk per second is certain, for example, 15000 to 250, that is, per second. If the disk is too busy, the CPU will consume resources on iowait. I didn't talk about the Major I mentioned above. Generally, the major term is IOPS, that is, the input output per second (or the number of reads and writes ). This is one of the main indicators to measure disk performance. Another important metric is Throughput, which refers to the number of data records that can be successfully transferred per unit time. 3. Technical statement: the following content is from the Internet, which is a technical term. Here is an excerpt. A traditional disk is essentially a type of mechanical device, such as FC, SAS, and SATA disks, with a speed of 5400/7200/10 K/15 K rpm. The key factor affecting the disk is the disk service time, that is, the time it takes for the disk to complete an I/O request. It consists of three parts: Seeking time, rotation delay, and data transmission time. (1) Seek time Tseek refers to the time required to move the read/write head to the correct track. The shorter the tracing time, the faster I/O operations are. Currently, the average tracing time of a disk is generally 3-15 ms. (2) The rotation delay Trotation refers to the time required to rotate the disk to move the sector where the request data is located to the bottom of the read/write head. The rotation delay depends on the disk speed, which is usually expressed by 1/2 of the time required for disk rotation for one week. For example, the average rotation latency of a 7200 rpm disk is about 60*1000/7200/2 = 4.17 ms, while the average rotation latency of a 15000 rpm disk is about 2 ms. (3) data transmission time Ttransfer refers to the time required to complete the data requested for transmission. It depends on the data transmission rate, and its value is equal to the data size divided by the data transmission rate. At present, IDE/ATA can reach 133 MB/s (MBPS), and sata ii can reach 300 MB/s interface data transmission rate. The data transmission time is usually far earlier than the first two parts. IOPS (I/O times per second) = 1 s/(seek time + rotation delay + data transmission time). Therefore, the maximum IOPS of the disk can be calculated theoretically, that is, IOPS = 1000 ms/(Tseek + Troatation), ignoring the data transmission time. Assuming that the average physical tracing time of a disk is 3 ms, and the disk speed is 1000, 10 K, and 15 Krpm, the theoretical maximum IOPS of the disk is: IOPS = 60000/7200/(3 +/2) = 140 IOPS = 1000/(3 + 60000/10000/2) = 167 IOPS = 1000/(3 + 60000/15000/2) = 200 SSD is an electronic device, this avoids the time spent on searching and rotating traditional disks, and greatly reduces the overhead of storage unit addressing. Therefore, IOPS can be very high and reach tens of thousands or even hundreds of thousands. In actual measurement, IOPS values are affected by many factors, including I/O load characteristics (read/write ratio, order and random, number of working threads, queue depth, and data record size), system configuration, operating system, disk drive, and so on. Therefore, when comparing disk IOPS measurement, it must be performed under the same test benchmark, even in case of random uncertainty. In general, IOPS can be subdivided into the following indicators: Toatal IOPS: disk IOPS under mixed read/write and sequential random I/O load, which is the most consistent with the actual I/O situation, most applications focus on this metric. Random Read IOPS: IOPS in the case of 100% Random Read loads. Random WriteIOPS: IOPS under 100% Random write load. Sequential ReadIOPS: IOPS in the case of 100% Sequential load reads. Sequential WriteIOPS: 100% IOPS under Sequential write load. 4. Conclusion In the linux iostat detection tool, tps is understood as disk iops, and Blk_read/s Blk_wrtn/s is understood as Disk Throughput. When a SATA disk is used to read and write small transactions, that is, a random read/write operation can only reach about 200 transactions per second. In terms of throughput, that is, the amount of 10 MB per second. Therefore, this result is normal for this disk. The read/write size of a disk per second can only be the same. A large amount of CPU resources are wasted on IOWAIT. For the current intensive transaction processing, the disk is the bottleneck of the entire system, and more memory and CPU are useless. Even so, the more transactions processed by the CPU per second, the more I/O will be busy.