The Database Management System (DBMS) is a memory buffer which stores copies of portions of the database that the DBMS is currently using. Reading from memory is much faster than reading from the disk. The DBMS therefore returns a record more quickly if it is already stored in cache. As long as the required data is stored in cache, the data is immediately available. When the required data is not stored in cache, it must be copied from the disk and then stored in cache.
DBMS Cache Transparency
The DBMS cache is transparent to the user. For example, when a user requests data, the data is automatically copied into the cache and stored there. If the data is modified, it is automatically copied back to the physical disk. These data transfers take place automatically. The user does not need to know about the cache.
For example, three users send requests to the DBMS. When user 2 sends a request to read data from the database, the request handler determines whether the desired data can be fetched directly from the cache or whether it must be fetched from a disk.
At the same time, another user can modify a record in a table in the database. The modified data will be written to the DBMS cache, and not to the disk. When this user completes the write transaction (that is, commits the changes), the data in the cache that was modified during the transaction is written to the disk. The cache is then said to be flushed.
The DBMS cache always contains the most recently used data. The cache is continually updated with the relevant data from the database.
The size of the cache greatly affects performance. When you set the size of the cache, you must remember two simple rules:
The more memory you assign to the cache, the more efficient it becomes. (Of course, there is no reason to assign more memory to the cache than the total size of your database.)
The size of the cache must not exceed the amount of physical memory available on your system. This is because the operating system may swap the cache memory in and out of the disk. This will considerably slow down overall performance.
Note You must remember to specify the commitcache=yes server parameter in the command line to enable the caching of write transactions.