AnalyticDB:pg_buffercache

Introduction

AnalyticDB for PostgreSQL uses the pg_buffercache extension to provide observable views of different pages cached in the shared buffer. You can monitor the observable views to achieve the following purposes:

• Identify hotspot data. Tables or indexes that frequently appear in the shared buffer represent access hotspots in the system. You can optimize queries on the tables or indexes to significantly improve database performance.

• Optimize cache configurations. You can adjust the size of the shared buffer based on the actual cache usage to ensure that critical data is fully cached and reduce disk I/O operations.

• Prevent performance bottlenecks. You can analyze the observable views and take precautions, such as adding cache resources and adjusting data access policies, to ensure the high performance of databases.

You can preload the tables or indexes that may be frequently accessed in the future to the shared buffer by using the pg_prewarm extension. This significantly reduces the latency of the first access due to data cache misses and ensures stable database response when the system starts and predicts an access mode change.

Limits

The following instance versions and minor versions of AnalyticDB for PostgreSQL support the pg_buffercache extension:

• AnalyticDB for PostgreSQL V6.0 instances of V6.6.2.5 or later.

• AnalyticDB for PostgreSQL V7.0 instances of V7.1.0.0 or later.

• AnalyticDB for PostgreSQL instances in Serverless mode do not support the pg_buffercache extension.

Installation and uninstallation

Install the extension

Before you use the pg_buffercache extension on an AnalyticDB for PostgreSQL instance, you must install the extension on the Extensions page of the instance. For more information, see Install, update, and uninstall extensions.

Uninstall the extension

If you no longer need to use the pg_buffercache extension on an AnalyticDB for PostgreSQL instance, you can uninstall the extension on the Extensions page of the instance. For more information, see Install, update, and uninstall extensions.

Examples

Query the cache usage statistics of tables or indexes on the coordinator node

SELECT * FROM shared_buffer_stat_master_lockfree;

Sample result:

           relname         | percentage_in_shared_buffer | pages_in_shared_buffer
---------------------------+-----------------------------+------------------------
 others                    |                     0.53125 |                     85
 pg_depend_reference_index |                     0.09375 |                     15
 pg_depend                 |                     0.05625 |                      9
 ...

• relname: the name of the table or the index that occupies space in the shared buffer. This metric helps analyze the database objects that are frequently accessed to affect cache efficiency and query performance.

• percentage_in_shared_buffer: the percentage of the shared buffer occupied by the table or the index. This metric helps obtain the tables or indexes that occupy the majority of the shared buffer and determine whether to modify the cache allocation policy or optimize the data access mode to balance resource usage.

• pages_in_shared_buffer: the number of pages of the table or the index cached in the shared buffer. This metric helps evaluate the data access locality and cache hit rate.

Query the cache usage statistics of tables or indexes on compute nodes

SELECT * FROM shared_buffer_stat_segments_lockfree;

Sample result:

        relname         | percentage_in_shared_buffer
------------------------+-----------------------------
 gp_distribution_policy |                     0.00625
 pg_namespace_oid_index |                      0.0125
 pg_rewrite             |                     0.00625
...

The query result on compute nodes is similar to the query result on the coordinator node. The difference is that the percentage_in_shared_buffer metric indicates the average percentage of the shared buffer occupied by each table or index across compute nodes.

Query the overall cache usage statistics on the coordinator node

SELECT * FROM shared_buffer_summary_master_lockfree;

Sample result:

 pages_in_use | pages_marked_dirty | pages_total
--------------+--------------------+-------------
          594 |                594 |       16000

• pages_in_use: the number of pages that are being used in the shared buffer.

• pages_marked_dirty: the number of pages that are marked as dirty in the shared buffer. The pages are modified in the shared buffer but are not synchronized back to disks. This metric indicates the activity of write operations and potential I/O write-back workloads.

• pages_total: the maximum number of pages that can be used in the shared buffer. This metric indicates the total cache size that can be used.

Query the overall cache usage statistics on compute nodes

SELECT * FROM shared_buffer_summary_segments_lockfree;

Sample result:

 pages_in_use | pages_marked_dirty | pages_total
--------------+--------------------+-------------
        11328 |              11328 |       98304
...

The query result on compute nodes is similar to the query result on the coordinator node. The difference is that the query result indicates the overall statistics of all compute nodes.

Query the cache hit statistics of a table or index

Syntax

SELECT * FROM relation_shared_buffer_stat_lockfree('<Table name or index name>'::regclass);

Example

SELECT * FROM relation_shared_buffer_stat_lockfree('adbpg_autoanalyze_test_table'::regclass);

Sample result:

           relname            | total_pages | pages_in_shared_buffer
------------------------------+-------------+------------------------
 adbpg_autoanalyze_test_table |       20055 |                   3327
(1 row)

• relname: the name of the table or the index.

• total_pages: the total number of pages of the table or the index in the database.

• pages_in_shared_buffer: the number of pages of the table or the index cached in the shared buffer. You can compare the values of the total_pages and pages_in_shared_buffer metrics to obtain the cache hit potential and the actual cache efficiency of the shared buffer. This helps determine whether to modify the cache allocation policy or optimize the data access mode to improve cache usage and query performance.

Query the cache hit statistics of a table and the related index and TOAST table

Syntax

SELECT * FROM relation_detail_shared_buffer_stat_lockfree('<Table name or index name>'::regclass);

Parameters

• If you specify the name of a table, the cache hit statistics of the table, the table index, and the TOAST table that depends on the table is returned.

• If you specify the name of an index, only the cache hit statistics of the index is returned.

Example

SELECT * FROM relation_detail_shared_buffer_stat_lockfree('adbpg_autoanalyze_test_table'::regclass);

Sample result:

           relname            | total_pages | pages_in_shared_buffer
------------------------------+-------------+------------------------
 adbpg_autoanalyze_test_table |       20055 |                   3327
(1 row)

The query allows you to obtain the cache statistics of the specified table and the related index or TOAST table in the shared buffer. This helps fully analyze and optimize database performance.

In this example, a large number of pages (3327/20055) of the adbpg_autoanalyze_test_table table are cached in the shared buffer to improve query efficiency. If no pages of the related adbpg_autoanalyze_test_table_i_idx index are cached, you may fail to achieve high performance when you use the index to perform queries.