Skip to main content

Understanding database [2] : Buffer Manager

Understanding database 2 : Buffer manager

Continuing from the previous chapter, we will now talk more about how the buffer would work when a request to the database is executed.

We consider there are frames of data which consists of records that are being stored in the buffer pool, some filled and some empty. The database management system would send a message to the buffer manager when it needs to request a page from disk, and the buffer may or may not have the requested page within the buffer. The page consists of records, and is being stored as frames in the pool.

If our requested page is not in the pool, we will first look for an empty frame. If we could grab an empty one, great, but if not, we will have to replace one of the frames for the current execution. The frame must not be pinned, namely it must not be in use at the moment. The pinning of a frame arrives when the page is in the pool(may exist already before, or it may have just been pulled in to the pool). Once we have a frame allocated for the current execution, the buffer manager will read the page from the disk. The point where the page is in the buffer, we pin it, and return the address of the page in memory to the database management system. The requestor of the page has to unpin the page and indicate whether or not the page had became dirty or not. Note that the pages that have been modified, it will be marked as a dirty page. When we look for replacements, dirty pages will be written back to the disk.

As a page in the pool may be requested many times, we will need a pin count to keep track of the status of the pages. For a page with a pin count of 0, it becomes a candidate for being replaced when looking to free some space for the new page.


Popular posts from this blog

Understanding database [9] : Choosing indexes

Understanding database 9 : Choosing indexes When choosing indexes, we choose the best plan that suits for the queries, and look for additional indexes that may potentially upgrade upon that. Before creating, we must also consider the impact on updates in the workload, such that indexes take disk space. For a query, the WHERE clause are the main focus point to make indexes on, where exact matches suggest a hash index and range queries suggest a tree index. Clustering is extremely helpful when it comes to range queries, and may also help with equality queries if there are duplicates. Search keys with multiple attribute should be considered if a WHERE clause contains multiple conditions, and the order of attributes is important for range queries. Searching may become ‘index-only’ with such indexes.

Understanding database [6] : Clustered Index

A clustered index is what is good for a range search over a range of search key values, and the index entries and the rows are ordered the same way, which is different from unclustered indexes (secondary indexes). To use a clustered index, we use the index to locate the first index entry at the start of the range, which where the first row is located at. If the index is clustered, subsequent rows will be stored in successive locations with the ordering , therefore it may minimize the page transfers and maximizes cache hits. For one table, there may only be one clustered index, whereas there can be as many unclustered indexes as you may want  to create. Unclustered indexes aren ’ t as good as clustered, but they might become necessary when it comes to finding for other attributes apart from the primary key. Smaller topics : Dense index & Sparse index When we say sparse index, we mean that there is an index entry for each page of the data file. With this structuring