Cache Information Functions
In DWS 9.1.1.100 and later, you can use cache information functions. Such functions provide an interface for querying shared buffer usage, helping you learn about the cache usage and then optimize cache policies to improve system performance and stability.
|
Query Object |
Description |
Cache Usage Statistics |
Cache Usage Details |
|---|---|---|---|
|
Instances |
Queries the summary information about the shared buffers of instances and the usage of each buffer (not associated with a specific table). |
||
|
Tables |
Displays the shared buffer usage of each table. Buffers are aggregated by table. |
- |
|
|
Single table |
Quickly queries the buffer usage of a specified table. |
When you query the cache information of a single table, different parameter values need to be specified based on the table type. For details, see Table 2. For details about the usage examples, see pg_buffercache_rel(nspname, relname, partname, tempnspname).
|
Table Type |
nspname |
relname |
partname |
tempnspname |
|---|---|---|---|---|
|
Ordinary table |
Specifies the table schema name. |
Specifies the table name. |
Must be set to NULL. |
Must be set to NULL. |
|
Partitioned table |
Specifies the table schema name. |
Specifies the table name. |
Specifies the partition name or can be set to NULL to query all partitions. |
Must be set to NULL. |
|
Global temporary table |
Specifies the table schema name. |
Specifies the table name. |
Must be set to NULL. |
Specifies the temporary schema name or can be set to NULL to query all temporary schemas. |
pg_buffercache_summary()
Description: Displays the shared buffer usage of the current instance.
Return columns:
|
Column |
Type |
Description |
|---|---|---|
|
buffers_used |
integer |
Number of used buffers. |
|
buffers_unused |
integer |
Number of unused buffers. |
|
buffers_dirty |
integer |
Number of dirty buffers. |
|
buffers_pinned |
integer |
Number of pinned buffers. |
|
usagecount_avg |
real |
How many times buffers are used averagely. |
Example:
1 2 3 4 5 |
SELECT * FROM pg_catalog.pg_buffercache_summary(); buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg --------------+----------------+---------------+----------------+---------------- 1761 | 129311 | 725 | 0 | 4.40829 (1 row) |
pgxc_buffercache_summary()
Description: Displays the shared buffer usage of all instances.
Return columns: The return columns are the same as those of pg_buffercache_summary() except that the column nodename is added to identify the instance name.
Example:
1 2 3 4 5 6 7 8 9 10 |
SELECT * FROM pg_catalog.pgxc_buffercache_summary(); nodename | buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg --------------+--------------+----------------+---------------+----------------+---------------- cn_5001 | 1237 | 31531 | 13 | 0 | 3.89248 cn_5002 | 1047 | 31721 | 9 | 0 | 4.01146 cn_5003 | 1473 | 31295 | 15 | 0 | 3.93143 dn_6001_6002 | 1768 | 129304 | 371 | 0 | 4.41572 dn_6003_6004 | 1549 | 129523 | 180 | 0 | 3.74758 dn_6005_6006 | 1488 | 129584 | 176 | 0 | 4.0578 (6 rows) |
pg_buffercache_usage_counts()
Description: Displays shared buffer usage of the current instance based on how many times buffers are accessed.
Return columns:
|
Column |
Type |
Description |
|---|---|---|
|
usage_count |
integer |
How many times buffers are used. |
|
buffers |
integer |
Total number of buffers that are accessed. |
|
dirty |
integer |
Number of dirty buffers in all buffers that are accessed. |
|
pinned |
integer |
Number of pinned buffers in all buffers that are accessed. |
Example:
1 2 3 4 5 6 7 8 9 10 |
SELECT * FROM pg_catalog.pg_buffercache_usage_counts(); usage_count | buffers | dirty | pinned -------------+---------+-------+-------- 0 | 129302 | 0 | 0 1 | 141 | 0 | 0 2 | 82 | 2 | 0 3 | 59 | 2 | 0 4 | 109 | 0 | 0 5 | 1379 | 169 | 0 (6 rows) |
pgxc_buffercache_usage_counts()
Description: Displays shared buffer usage of all instances based on how many times buffers are used.
Return columns: The return columns are the same as those of pg_buffercache_usage_counts() except that the column nodename is added to identify the instance name.
Example:
1 2 3 4 5 6 7 8 9 10 |
SELECT * FROM pg_catalog.pgxc_buffercache_usage_counts() WHERE usage_count = 5; nodename | usage_count | buffers | dirty | pinned --------------+-------------+---------+-------+-------- cn_5001 | 5 | 804 | 55 | 0 cn_5002 | 5 | 694 | 32 | 0 cn_5003 | 5 | 987 | 61 | 0 dn_6001_6002 | 5 | 1380 | 124 | 0 dn_6003_6004 | 5 | 871 | 87 | 0 dn_6005_6006 | 5 | 975 | 93 | 0 (6 rows) |
pg_buffercache_rel()
Description: Queries the shared buffer usage statistics of each table on the current instance.
Return columns:
|
Column |
Type |
Description |
|---|---|---|
|
nspname |
name |
Schema name. |
|
relname |
name |
Table name. |
|
partname |
name |
Partition name. If the table is not a partitioned table, the value is NULL. |
|
tempnspname |
name |
Temporary schema name of the global temporary table. If the table is not a global temporary table, the value is NULL. |
|
buffers |
bigint |
Number of buffers. |
|
dirty |
bigint |
Number of dirty buffers. |
|
usage_count |
bigint |
How many times buffers are used. |
|
pinned |
bigint |
Number of pinned buffers. |
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT * FROM pg_catalog.pg_buffercache_rel() ORDER BY usage_count DESC LIMIT 10; nspname | relname | partname | tempnspname | buffers | dirty | usage_count | pinned ------------+--------------------------------------+----------+-------------+---------+-------+-------------+-------- cstore | pg_hstore_delta_part_17696 | | | 539 | 142 | 2694 | 0 pg_catalog | pg_attribute | | | 114 | 1 | 563 | 0 pg_catalog | pg_class | | | 53 | 6 | 265 | 1 pg_catalog | pg_statistic | | | 46 | 28 | 230 | 0 cstore | pg_hstore_delta_part_17696_tid_index | | | 44 | 11 | 216 | 0 pg_catalog | pg_attribute_relid_attnum_index | | | 29 | 1 | 139 | 0 pg_toast | pg_toast_2619 | | | 23 | 3 | 109 | 0 scheduler | workload_collection | | | 34 | 2 | 94 | 0 pg_catalog | pg_partition | | | 18 | 0 | 84 | 0 pg_catalog | pg_type | | | 33 | 1 | 77 | 0 (10 rows) |
pgxc_buffercache_rel()
Description: Queries the shared buffer usage statistics of each table on all instances.
Return columns: The return columns are the same as those of pg_buffercache_rel() except that the column nodename is added to identify the instance name.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT * FROM pg_catalog.pgxc_buffercache_rel() ORDER BY usage_count DESC LIMIT 10; nodename | nspname | relname | partname | tempnspname | buffers | dirty | usage_count | pinned --------------+------------+----------------------------+----------+-------------+---------+-------+-------------+-------- dn_6001_6002 | cstore | pg_hstore_delta_part_17696 | | | 539 | 187 | 2694 | 0 dn_6003_6004 | cstore | pg_hstore_delta_part_17704 | | | 486 | 266 | 1702 | 0 dn_6005_6006 | cstore | pg_hstore_delta_part_17696 | | | 445 | 181 | 1559 | 0 cn_5003 | pg_catalog | pg_attribute | | | 143 | 3 | 711 | 0 cn_5001 | pg_catalog | pg_attribute | | | 150 | 1 | 671 | 0 dn_6005_6006 | pg_catalog | pg_attribute | | | 125 | 1 | 574 | 0 dn_6003_6004 | pg_catalog | pg_attribute | | | 114 | 1 | 563 | 0 dn_6001_6002 | pg_catalog | pg_attribute | | | 114 | 1 | 563 | 0 cn_5002 | pg_catalog | pg_attribute | | | 99 | 1 | 483 | 0 dn_6001_6002 | pg_catalog | pg_class | | | 53 | 6 | 265 | 1 (10 rows) |
pg_buffercache_rel(nspname, relname, partname, tempnspname)
Description: Queries the statistics on the shared buffer usage of a specified table on the current instance.
Parameter description: Different parameter values need to be specified based on the table type. For details, see Table 2.
Return columns: The return columns are same as those in pg_buffercache_rel().
Example 1: Query an ordinary table.
1 2 3 4 5 |
SELECT * FROM pg_catalog.pg_buffercache_rel('public', 't1', NULL, NULL); nspname | relname | partname | tempnspname | buffers | dirty | usage_count | pinned ---------+---------+----------+-------------+---------+-------+-------------+-------- public | t1 | | | 4 | 2 | 12 | 0 (1 row) |
Example 2: Query the specified partition of a partitioned table.
1 2 3 4 5 |
SELECT * FROM pg_catalog.pg_buffercache_rel('public', 'part1', 'p1', NULL); nspname | relname | partname | tempnspname | buffers | dirty | usage_count | pinned ---------+---------+----------+-------------+---------+-------+-------------+-------- public | part1 | p1 | | 1 | 1 | 5 | 0 (1 row) |
Example 3: Query all partitions of a partitioned table.
1 2 3 4 5 6 |
SELECT * FROM pg_catalog.pg_buffercache_rel('public', 'part1', NULL, NULL) ORDER BY partname; nspname | relname | partname | tempnspname | buffers | dirty | usage_count | pinned ---------+---------+----------+-------------+---------+-------+-------------+-------- public | part1 | p1 | | 1 | 1 | 5 | 0 public | part1 | p2 | | 1 | 1 | 5 | 0 (2 rows) |
Example 4: Query the specified session of a global temporary table.
1 2 3 4 5 |
SELECT * FROM pg_catalog.pg_buffercache_rel('public', 'gtt1', NULL, 'pg_temp_cn_5001_4_1_139815154281712') ORDER BY tempnspname; nspname | relname | partname | tempnspname | buffers | dirty | usage_count | pinned ---------+---------+----------+-------------------------------------+---------+-------+-------------+-------- public | gtt1 | | pg_temp_cn_5001_4_1_139815154281712 | 4 | 2 | 12 | 0 (1 row) |
Example 5: Query all sessions of a global temporary table.
1 2 3 4 5 6 |
SELECT * FROM pg_catalog.pg_buffercache_rel('public', 'gtt1', NULL, NULL) ORDER BY tempnspname; nspname | relname | partname | tempnspname | buffers | dirty | usage_count | pinned ---------+---------+----------+-------------------------------------+---------+-------+-------------+-------- public | gtt1 | | pg_temp_cn_5001_4_1_139815154281712 | 4 | 2 | 12 | 0 public | gtt1 | | pg_temp_cn_5001_4_2_139815154283080 | 4 | 2 | 12 | 0 (2 rows) |
pgxc_buffercache_rel(nspname, relname, partname, tempnspname)
Description: Queries the statistics on the shared buffer usage of a specified table on all instances.
Parameter description: Different parameter values need to be specified based on the table type. For details, see Table 2.
Return columns: The return columns are same as those in pgxc_buffercache_rel().
Example:
1 2 3 4 5 6 7 |
SELECT * FROM pg_catalog.pgxc_buffercache_rel('public', 't1', NULL, NULL) ORDER BY nodename; nodename | nspname | relname | partname | tempnspname | buffers | dirty | usage_count | pinned --------------+---------+---------+----------+-------------+---------+-------+-------------+-------- dn_6001_6002 | public | t1 | | | 4 | 2 | 12 | 0 dn_6003_6004 | public | t1 | | | 4 | 2 | 12 | 0 dn_6005_6006 | public | t1 | | | 4 | 2 | 12 | 0 (3 rows) |
pg_buffercache_pages()
Description: Queries details about the shared buffer of the current instance.
Return columns:
|
Column |
Type |
Description |
|---|---|---|
|
bufferid |
integer |
Buffer ID. Value range: 1 to shared_buffers. |
|
relfilenode |
oid |
ID of the physical file corresponding to the buffer. |
|
reltablespace |
oid |
OID of the tablespace that the physical file corresponding to the buffer is located within. |
|
reldatabase |
oid |
OID of the database that the physical file corresponding to the buffer is located within. If a system catalog is shared across databases, its value is 0. |
|
relforknumber |
smallint |
File type of the physical file corresponding to the buffer. |
|
relblocknumber |
bigint |
Page number of the buffer in the corresponding physical file. |
|
isdirty |
boolean |
Whether the buffer is a dirty buffer. |
|
usage_count |
smallint |
How many times the buffer is used. |
|
pinning_backends |
integer |
Number of backend threads that pin the buffer. |
- There is one row for each buffer in the shared cache. Unused buffers are shown with all columns NULL except bufferid.
- This function lists the usage of all buffers. Setting shared_buffers too large may return too many results.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT * FROM pg_catalog.pg_buffercache_pages() LIMIT 10; bufferid | relfilenode | reltablespace | reldatabase | relforknumber | relblocknumber | isdirty | usage_count | pinning_backends ----------+-------------+---------------+-------------+---------------+----------------+---------+-------------+------------------ 1 | 16637 | 1664 | 0 | 0 | 0 | f | 5 | 0 2 | 17183 | 1664 | 0 | 0 | 0 | f | 5 | 0 3 | 16750 | 1663 | 17187 | 0 | 0 | f | 5 | 0 4 | 16621 | 1663 | 17187 | 0 | 0 | f | 5 | 0 5 | 16785 | 1663 | 17187 | 0 | 0 | f | 5 | 0 6 | 16801 | 1663 | 17187 | 0 | 0 | f | 5 | 0 7 | 16797 | 1663 | 17187 | 0 | 0 | f | 5 | 0 8 | 16810 | 1663 | 17187 | 0 | 0 | f | 5 | 0 9 | 16810 | 1663 | 17187 | 0 | 1 | f | 2 | 0 10 | 16810 | 1663 | 17187 | 0 | 2 | f | 2 | 0 (10 rows) |
pgxc_buffercache_pages()
Description: Queries details about the shared buffers of all instances.
Return columns: The return columns are the same as those of pg_buffercache_pages() except that the column nodename is added to identify the instance name.
- There is one row for each buffer in the shared cache. Unused buffers are shown with all columns NULL except bufferid.
- This function lists the usage of all buffers in all instances. Setting shared_buffers too large may return too many results.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT * FROM pg_catalog.pgxc_buffercache_pages() WHERE isdirty LIMIT 10; nodename | bufferid | relfilenode | reltablespace | reldatabase | relforknumber | relblocknumber | isdirty | usage_count | pinning_backends ----------+----------+-------------+---------------+-------------+---------------+----------------+---------+-------------+------------------ cn_5001 | 7 | 17047 | 1664 | 0 | 0 | 1 | t | 5 | 0 cn_5001 | 15 | 16926 | 1663 | 17187 | 0 | 0 | t | 5 | 0 cn_5001 | 16 | 16931 | 1663 | 17187 | 0 | 1 | t | 5 | 0 cn_5001 | 19 | 16865 | 1663 | 17187 | 0 | 53 | t | 5 | 0 cn_5001 | 21 | 17022 | 1663 | 17187 | 0 | 0 | t | 5 | 0 cn_5001 | 22 | 17024 | 1663 | 17187 | 0 | 1 | t | 5 | 0 cn_5001 | 32 | 17050 | 1664 | 0 | 0 | 2 | t | 5 | 0 cn_5001 | 40 | 16617 | 1663 | 17187 | 1 | 2 | t | 5 | 0 cn_5001 | 41 | 16620 | 1663 | 17187 | 0 | 1 | t | 5 | 0 cn_5001 | 44 | 16750 | 1663 | 17187 | 0 | 1 | t | 5 | 0 (10 rows) |
pg_buffercache_pages(nspname, relname, partname, tempnspname)
Description: Queries details about the shared buffer used by a specified table on the current instance.
Parameter description: Different parameter values need to be specified based on the table type. For details, see Table 2.
Return columns: The return columns are same as those in pg_buffercache_pages().
Example:
1 2 3 4 5 6 7 8 |
SELECT * FROM pg_catalog.pg_buffercache_pages('public', 't1', NULL, NULL); nspname | relname | partname | tempnspname | bufferid | relforknumber | relblocknumber | isdirty | usage_count | pinning_backends ---------+---------+----------+-------------+----------+---------------+----------------+---------+-------------+------------------ public | t1 | | | 1786 | 0 | 0 | t | 5 | 0 public | t1 | | | 1787 | 1 | 2 | f | 1 | 0 public | t1 | | | 1788 | 1 | 0 | f | 1 | 0 public | t1 | | | 1789 | 0 | 1 | t | 5 | 0 (4 rows) |
pgxc_buffercache_pages(nspname, relname, partname, tempnspname)
Description: Queries details about the shared buffers occupied by a specified table on all instances.
Parameter description: Different parameter values need to be specified based on the table type. For details, see Table 2.
Return columns: The return columns are same as those in pgxc_buffercache_pages().
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT * FROM pg_catalog.pgxc_buffercache_pages('public', 't1', NULL, NULL) ORDER BY nodename, bufferid; nodename | nspname | relname | partname | tempnspname | bufferid | relforknumber | relblocknumber | isdirty | usage_count | pinning_backends --------------+---------+---------+----------+-------------+----------+---------------+----------------+---------+-------------+------------------ dn_6001_6002 | public | t1 | | | 1786 | 0 | 0 | t | 5 | 0 dn_6001_6002 | public | t1 | | | 1787 | 1 | 2 | f | 1 | 0 dn_6001_6002 | public | t1 | | | 1788 | 1 | 0 | f | 1 | 0 dn_6001_6002 | public | t1 | | | 1789 | 0 | 1 | t | 5 | 0 dn_6003_6004 | public | t1 | | | 1740 | 0 | 0 | t | 5 | 0 dn_6003_6004 | public | t1 | | | 1741 | 1 | 2 | f | 1 | 0 dn_6003_6004 | public | t1 | | | 1742 | 1 | 0 | f | 1 | 0 dn_6003_6004 | public | t1 | | | 1743 | 0 | 1 | t | 5 | 0 dn_6005_6006 | public | t1 | | | 1196 | 0 | 1 | t | 5 | 0 dn_6005_6006 | public | t1 | | | 1197 | 1 | 0 | f | 1 | 0 dn_6005_6006 | public | t1 | | | 1198 | 1 | 2 | f | 1 | 0 dn_6005_6006 | public | t1 | | | 1199 | 0 | 0 | t | 5 | 0 (12 rows) |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
