) Talking About the cache in the Database System

Cache and buffer are two different concepts. Simply put, cache accelerates "read" while buffer is "write". The former solves the read problem, save the data read from the disk. The latter solves the write problem and stores the data to be written to the disk. In many cases, these two terms are not strictly differentiated. The read/write hybrid type is often referred to as buffer cache, which will be called cache in subsequent discussions.

The log buffer in Oracle solves the redo writing problem, while the data buffer cache solves the data block read/write problem. For Oracle, physical Io occurs if I/O is not hit in SGA. Oracle does not care about the type of underlying storage. It may be a storage system or a local disk, it may be raid 10, RAID 5, file system, bare device, or ASM. In short, Oracle calls the underlying storage system a storage subsystem.

In the storage system, cache is almost everywhere (In the subsequent discussion, we collectively referred to as cache), the file system has cache, the storage has cache, And the RAID Controller has cache, cache is also available on the disk. To improve performance, it is very likely that a write operation in Oracle will be returned after it is written in the storage cache. If there is a problem in the storage system, how can Oracle ensure data consistency.

The most important feature of Oracle Database is write Ahead Logging. before writing data blocks, ensure that the redo log is written first. When the transaction is committed, ensure that the redo log is written. Oracle uses direct Io for redo logs to ensure data consistency. Direct Io skips the cache layer of the file system on the OS. However, the operating system cannot manage the storage layer. Although the file system cache is skipped, it may still be written on the storage cache.

Generally, cache is used for storage. To improve performance, write operations are completed and returned to the OS. We call this write operation write back, to ensure that the content in the cache will not be lost when the power is down, the storage is protected by batteries. These batteries can be stored for a certain period of time after the power is down. This ensures that the cache data is flushed into the disk and will not be lost. Unlike ups, battery can support a short period of time, generally within 30 minutes, as long as the data in the cache is written. Storage can disable write cache. In this case, all write operations must be written to the disk before they are returned. We call this write operation write throuogh. When the storage finds that some parts are abnormal, the storage will automatically disable the write cache, And the write performance will decrease.

The RAID card also has a cache, usually 256 m, which is also protected by a battery. Unlike the storage, this battery does not guarantee that data can be written to the disk, instead, it supplies power to the cache to protect data from loss. Generally, it can support several days. Some raid cards also have flash cache. After power loss, you can write the content in the cache to the Flash cache to ensure that data is not lost. If your database is not stored but placed on the local hard disk of a common PC, make sure that the raid card in the host has a battery. Many hardware providers do not configure the battery by default. Of course, you can also disable the cache on the raid card.

The cache on the disk is generally 16 m-64 m. Many storage vendors have clearly stated that the cache on the disk in the storage is disabled, which is understandable. To ensure data reliability, the storage itself provides a very large cache. In comparison, the cache on the disk is no longer so important. The SCSI command has a FUA (Force Unit access) parameter. When this parameter is set, write operations must be completed on the disk to return results, which is equivalent to disabling disk write cache. I personally think that writing cache will be disabled once the disk is connected to the RAID Controller. This is also for the sake of data reliability, I believe that storage vendors should consider this issue.

So far, we can see that a physical Io of Oracle is written to the disk after a series of caches. Although the cache can improve the performance, the power loss protection should also be considered. Data Consistency is ensured by the Oracle database, operating system, and storage subsystem.