Analyzing buffer cache contents

In this recipe, we will be discussing how to analyze the content that resides in PostgreSQL shared buffers.

Getting ready

To analyze the shared buffer contents, PostgreSQL provides an extension called pg_buffercache, and we also use the CREATE EXTENSION command. This extension reports the buffer details such as which relation holds the number of buffers in the memory, what buffers are dirty at this moment, and what is a specific buffer's usage count.

In PostgreSQL, shared buffers are not specific to any database as they are managed at cluster level. Hence, while querying the pg_buffercache we may get relation details that are from other databases too. Also, it is not recommended to query the pg_buffercache too frequently as it needs to handle buffer management locks, to get the buffer's current status.

How to do it...

Let's install the pg_buffercache extension and then query the view as follows:

postgres=# CREATE EXTENSION pg_buffercache;
CREATE EXTENSION
postgres=# SELECT * FROM pg_buffercache LIMIT 1;
-[ RECORD 1 ]----+-----
bufferid | 1
relfilenode | 1262
reltablespace | 1664
reldatabase | 0
relforknumber | 0
relblocknumber | 0
isdirty | f
usagecount | 5
pinning_backends | 0

From the preceding output, the reldatabase is 0, which indicates that the bufferid 1 belongs to a table/view of the shared system catalogs, such as pg_database. Also, it displays the usagecount of that buffer as 5, which will be helpful during the buffer cache eviction policy.

How it works...

PostgreSQL has a built-in buffer manager that maintains the shared buffers for the cluster. Whenever a backend requests to read a block from the disk, then the buffer manager allocates a block for it from the available shared buffer memory. If the shared buffers don't have any available buffers, then the buffer manager needs to choose a certain number of blocks to evict from the shared buffers for the newly requested blocks.

PostgreSQL's buffer manager follows an approach called clock sweep for the buffer eviction policy. When a block is loaded from disk into the shared buffers, then its usage count will begin with 1. Whenever this buffer hits from the other backend processes, its usage count will be increased and its value will stay as 5 when its usage count exceeds 5. This usage count value is reduced by 1 during the eviction policy. When the buffer manager needs to evict the buffers for the incoming requests, then it scans all the buffers including dirty buffers, and will reduce all the buffer usage counts by 1. After the end of the scan, if it finds the buffers whose usage count is 0, then it treats those buffers as if they should be evicted from the shared buffers. If the chosen buffer is a dirty buffer, then the backend process request will be in waiting state, until that dirty buffer is flushed to the respective physical file storage. From the preceding output, the usage count of the bufferid is 5, which defines that it is not much closer to the eviction policy.