digoal
2019-09-15
PostgreSQL , pg_buffercache , pin , backend , count
通过pg_buffercache可以查询shared buffer的统计信息
https://www.postgresql.org/docs/12/pgbuffercache.html
字段如下
| Name | Type | References | Description |
|---|---|---|---|
| bufferid | integer | - | ID, in the range 1..shared_buffers |
| relfilenode | oid | pg_class.relfilenode | Filenode number of the relation |
| reltablespace | oid | pg_tablespace.oid | Tablespace OID of the relation |
| reldatabase | oid | pg_database.oid | Database OID of the relation |
| relforknumber | smallint | - | Fork number within the relation; see include/common/relpath.h |
| relblocknumber | bigint | - | Page number within the relation |
| isdirty | boolean | - | Is the page dirty? |
| usagecount | smallint | - | Clock-sweep access count |
| pinning_backends | integer | - | Number of backends pinning this buffer |
包括bufferid，buffer属于哪个数据库，哪个对象，什么表空间，哪个文件，block id等。
同时还包括这个buffer是否为脏页，被多少backend pin过，被pin过多少次。
解释：
- relforknumber (数据、vm、fsm) Fork number within the relation; see include/common/relpath.h
- usagecount 此bufferid被pin的次数,最大只记录16次(4bit) Clock-sweep access count
- pinning_backends 此bufferid被多少个backend pin过,最大18bit Number of backends pinning this buffer
regression=# SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;
relname | buffers
---------------------------------+---------
tenk2 | 345
tenk1 | 141
pg_proc | 46
pg_class | 45
pg_attribute | 43
pg_class_relname_nsp_index | 30
pg_proc_proname_args_nsp_index | 28
pg_attribute_relid_attnam_index | 26
pg_depend | 22
pg_depend_reference_index | 20
(10 rows)
1、https://www.postgresql.org/docs/12/pgbuffercache.html
2、contrib/pg_buffercache/pg_buffercache_pages.c
fctx->record[i].usagecount = BUF_STATE_GET_USAGECOUNT(buf_state);
fctx->record[i].pinning_backends = BUF_STATE_GET_REFCOUNT(buf_state);
3、src/include/common/relpath.h
/*
* Stuff for fork names.
*
* The physical storage of a relation consists of one or more forks.
* The main fork is always created, but in addition to that there can be
* additional forks for storing various metadata. ForkNumber is used when
* we need to refer to a specific fork in a relation.
*/
typedef enum ForkNumber
{
InvalidForkNumber = -1,
MAIN_FORKNUM = 0,
FSM_FORKNUM,
VISIBILITYMAP_FORKNUM,
INIT_FORKNUM
/*
* NOTE: if you add a new fork, change MAX_FORKNUM and possibly
* FORKNAMECHARS below, and update the forkNames array in
* src/common/relpath.c
*/
} ForkNumber;
4、src/include/storage/buf_internals.h
/*
* Buffer state is a single 32-bit variable where following data is combined.
*
* - 18 bits refcount
* - 4 bits usage count
* - 10 bits of flags
*
* Combining these values allows to perform some operations without locking
* the buffer header, by modifying them together with a CAS loop.
*
* The definition of buffer state components is below.
*/
#define BUF_REFCOUNT_ONE 1
#define BUF_REFCOUNT_MASK ((1U << 18) - 1)
#define BUF_USAGECOUNT_MASK 0x003C0000U
#define BUF_USAGECOUNT_ONE (1U << 18)
#define BUF_USAGECOUNT_SHIFT 18
#define BUF_FLAG_MASK 0xFFC00000U
/* Get refcount and usagecount from buffer state */
#define BUF_STATE_GET_REFCOUNT(state) ((state) & BUF_REFCOUNT_MASK)
#define BUF_STATE_GET_USAGECOUNT(state) (((state) & BUF_USAGECOUNT_MASK) >> BUF_USAGECOUNT_SHIFT)
5、src/backend/storage/buffer/bufmgr.c
/*
* PinBuffer -- make buffer unavailable for replacement.
*
* For the default access strategy, the buffer's usage_count is incremented
* when we first pin it; for other strategies we just make sure the usage_count
* isn't zero. (The idea of the latter is that we don't want synchronized
* heap scans to inflate the count, but we need it to not be zero to discourage
* other backends from stealing buffers from our ring. As long as we cycle
* through the ring faster than the global clock-sweep cycles, buffers in
* our ring won't be chosen as victims for replacement by other backends.)
*
* This should be applied only to shared buffers, never local ones.
*
* Since buffers are pinned/unpinned very frequently, pin buffers without
* taking the buffer header lock; instead update the state variable in loop of
* CAS operations. Hopefully it's just a single CAS.
*
* Note that ResourceOwnerEnlargeBuffers must have been done already.
*
* Returns true if buffer is BM_VALID, else false. This provision allows
* some callers to avoid an extra spinlock cycle.
*/
static bool
PinBuffer(BufferDesc *buf, BufferAccessStrategy strategy)
{
....
if (strategy == NULL)
{
/* Default case: increase usagecount unless already max. */
if (BUF_STATE_GET_USAGECOUNT(buf_state) < BM_MAX_USAGE_COUNT)
buf_state += BUF_USAGECOUNT_ONE;
}
else
{
/*
* Ring buffers shouldn't evict others from pool. Thus we
* don't make usagecount more than 1.
*/
if (BUF_STATE_GET_USAGECOUNT(buf_state) == 0)
buf_state += BUF_USAGECOUNT_ONE;
}
......
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
