Buffer pool

from Wikipedia, the free encyclopedia

The buffer pool is the buffer storage of a database management system (DBMS). Frequently used parts of a database are temporarily stored in the main memory. This can reduce the number of accesses to the hard disk and thus the time required for this.

Concept of the buffer pool

The buffer pool is a cache that temporarily stores parts of a database in memory . The locality properties of the processes that access the database are used. The speed can be increased significantly compared to direct hard disk access.

functionality

A DBMS manages the information in the database in data units of a fixed size. These are called sites or pages referred to. In one side could e.g. B. contain several rows of a table. Data is stored in blocks on the permanent memory. The size of a page is usually a multiple of the size of a data block on the hard disk.

The displacement strategy decides which page in the buffer pool will be replaced if a page is to be loaded from disk into the buffer pool. Usually LRU (Least Recently Used) is used.

Requirements for the buffer pool

The four ACID properties define the requirements for transactions . In particular, the two properties of atomicity and durability have an impact on the buffer pool. Isolation is not the job of the buffer pool. This must be achieved using locking algorithms.

When operating a DBMS, many problems can arise which lead to the loss of the main memory. For example power failures, defective computer components, software errors, etc. If the main memory is lost, the buffer pool is lost and, in particular, all those pages that have been changed but not yet written back to the hard disk.

If this data belonged to a successfully completed transaction, the ACID requirement of durability is not met. In addition, the ACID property of atomicity must not be violated. Changes to the database of transactions that were not successfully completed at the time of the failure must be fully reversible.

Various buffer pool implementations

The way in which a buffer pool is implemented can differ greatly from DBMS to DBMS. For some methods and strategies, special terms are used in connection with the buffer pool.

Force / No-Force

The Force method is a simple way to ensure the durability of transactions . For this purpose, at the end of a transaction (during the commit process), all pages that were changed during the transaction are written to the hard disk. The use of the force method therefore means that (relatively slow) write operations are necessary for every changing transaction.

Accordingly, there is the no-force method. No pages need to be written to the hard disk at the end of a transaction. If several transactions change the same page, it does not need to be saved each time. The performance of the DBMS can thus be increased. However, no-force does not offer guaranteed durability; this must be ensured otherwise.

Steal / no-steal

The no-steal method is a simple way of ensuring that transactions are atomic . As long as the transaction is still active, the changed pages of the transaction are not transferred to the hard disk. All changes occur exclusively in the buffer pool. In the event of a failure, the buffer pool and the changes to all ongoing transactions with it are lost. This amounts to rolling back these transactions. There are only pages of complete transactions on the hard drive. The atomicity is thus guaranteed.

The disadvantage of the no-steal method is that many pages are blocked in the buffer pool. Because if the changes cannot be transferred to the hard disk, the page cannot be removed from the buffer pool to make room for another page. The space in the buffer pool can quickly reach its limits. The amount of data that can be changed in a transaction is limited by the size of the buffer pool.

Most changes will have to be written to secondary storage later anyway, since transaction aborts are the exception. Keeping these pages in the buffer pool is therefore mostly unnecessary. Accordingly, the steal method allows pages of ongoing transactions to be written to the hard disk. You steal a place in the buffer pool, so to speak, from an ongoing transaction. The buffer pool can thus be used more efficiently. However, the atomicity is no longer guaranteed and must be implemented differently.

Combinations

In practice, the combinations of force and no-steal or no-force and steal are usually used. Force and no-steal is easy to implement, but does not achieve the performance of no-force and steal. In particular, no two transactions can work on the same page in the buffer pool with force and no-steal. For this reason, no-force and steal are usually used in large DBMS systems, even if this is more complex to develop. A well-known algorithm for maintaining the ACID properties despite no-force and steal is ARIES (Algorithms for Recovery and Isolation Exploiting Semantics) . This is based on a protocol that enables both the restoration and the undoing of changes in order to obtain a status that corresponds to the ACID properties after a failure.

configuration

Most DBMS offer options to adapt the buffer pool to your own needs. The most important parameter is the size of the buffer pool. The more pages it can hold, the faster the database can be used, but the need for memory also increases.

With some DBMS, the methods for maintaining the ACID properties can also be manipulated. In particular, systems that use the no-force and steal methods usually offer a possibility to enable data to be backed up during operation. The ACID properties are also retained if a copy is made from the permanent storage while the DBMS is running. This is known as hot backup.

Individual evidence

  1. Theo Härder and Erhard Rahm: Database systems, concepts and techniques of implementation , 2nd edition (2001)