Cache and Buffer

The cache and buffer are two different concepts, simply put, the cache is accelerated "read", and buffer is buffered "write", the former solve the problem of reading, save the data read from the disk, the latter is to solve the write problem, save the data that will be written to disk. In many cases, these two nouns are not strictly differentiated, often the read-write mixed type is called buffer cache, in Oracle instance also has a region as the database buffer && cache. The main content of this paper is from "MySQL High performance book ", "oracle9i Database Administration StudentGuide I".

Oracle Overview

MySQL Memory Overview

Database design generally has the features of insert buffering, double Wirte , which bring better performance and higher reliability to the database.

Double Write

redo Log Buffer in Oracle and the log buffers in the MySQL InnoDB engine is to solve the redo write problem, and the database buffer cache and the insert buffer address the read and write problems of data block.

For Oracle, if IO is not hit in the SGA, physical io,oracle are not concerned with the type of underlying storage, possibly a set of storage systems, possibly a local disk, possibly raid 10, or RAID 5, possibly a file system, or a bare device, or ASM. In summary, Oracle calls the underlying storage system a storage subsystem.

Storage System

In the storage system, buffer is almost ubiquitous, the file system has buffer, the buffer isstored , theRAID controller has buffer, There is also a buffer on the disk . To improve performance, one of Oracle's write operations is likely to be written back on the stored buffer , and if there is a problem with the storage system, how can oracle ensure data consistency.

The most important feature of the Oracle database is the write ahead logging, before the data block is written, it must be guaranteed to write redo log first, and at the time of transaction commit, the redo log must be guaranteed to be written. To ensure data consistency, Oracle uses Direct io,direct IO for redo log to skip the cache layer of the filesystem on the OS. However, the OS cannot store this layer, although it skips the cache of the file system, but it may still be written on the stored cache.

redolog is to The flush minimizes the disk's random writes by delaying Dirtypage. (Redolog will merge the TRX changes to a page over time)

General Storage has buffer, in order to improve performance, the write operation is done on the buffer to return to the OS, we call this write operation is write back, To ensure that the contents of the cache are not lost during power-down, the storage is battery-protected, and the batteries can be stored for a certain amount of time after power-down, ensuring that the data in the cache is brushed into the disk and is not lost. Unlike UPS, the battery can be supported for a short time, typically within 30 minutes, as long as The data in the buffer is guaranteed to be written. The store can shut down the write cache, when all writes must be written to the disk to return, we call this write Throuogh, and when the store finds some parts are unhealthy, the storage automatically shuts down write buffer, when write performance degrades.

Raid

The RAID card also has buffer, generally 256M, is also protected by the battery, unlike the storage, the battery does not guarantee that the data can be written to the disk, but the buffer Power supply to protect data is not lost, generally can support a few days of time. Some RAID cards have flash buffer, the contents of the cache can be written to flash buffer , to ensure that the data is not lost. If your database is not stored, but on the local hard disk of the normal PC, make sure that the RAID card in the host has a battery, and many hardware providers do not configure the battery by default. Of course, the buffer on the RAID card can also be selected to close.

Disk

buffer , generally 16m-64m, many storage vendors have made it clear that the is disabled, which is understandable, in order to ensure data reliability, and the storage itself provides a very large , in comparison, the > It's not that important anymore. The SCSI directive has a FUA (Force Unit Access) parameter, when setting this parameter, the write operation must be completed on disk to return, equivalent to disabling the disk's write buffer

At this point, we can see that one of Oracle's physical IO has gone through a series of buffer and is eventually written to disk. while buffer can improve performance, it also has to consider the issue of power-down protection. The consistency of the data is ensured by the Oracle database, the operating system, and the storage subsystem.

From for notes (Wiz)

Cache and Buffer