Introduction to Database IO

There are four types of IO: continuous read, random read, random write and continuous write, the IO size of continuous reading and writing is usually larger (128KB-1MB), the main measure of throughput, and random read and write IO size is smaller (less than 8KB), mainly measure IOPS and response time. The full table scan in the database is sequential read IO, the index access is the typical random read Io, the log file is the sequential write Io, and the data file is the random write Io.

Database system based on the traditional disk access features to design, the most important feature is that the log file using sequential logging, the log file in the database, the request must be written to disk when the transaction commits, the response time requirements are very high, so the design is sequential write way, It can effectively reduce the time spent on disk seek and reduce the delay time. Log files are written sequentially, although the physical location is continuous, but unlike traditional sequential write types, the log file IO size is small (usually less than 4K), each IO is independent (the head must be lifted up and re-sought, and the disk is rotated to the appropriate position), and the interval is very short, The database is increased in size by using log buffer (cache) and group commit (bulk commit), and the number of IO is reduced, resulting in a smaller response delay, so sequential writes of log files can be considered "random writes of sequential locations". Pay more attention to IOPS than throughput.

The data file is in the place uddate, meaning that the data file changes are written to the original location, the data file is different from the log file, and does not write the data file when the transaction commits, only when the database discovers dirty Buffer too much or need to do checkpoint action, will refresh these dirty buffer to the corresponding position, this is an asynchronous process, usually, the data file random write to the IO requirements are not particularly high, as long as the checkpoint and dirty are satisfied The request for buffer is ready.

Introduction to Database IO