资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划，从策略到执行的一站式服务

网站建设 >

查看其它板块

PostgreSQLDBA(83)-Extension(pg_buffercache)

使用pg_buffercache插件可查看shared buffer中的内容.

10年积累的网站设计、网站建设经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先网站设计后付款的网站建设流程，更有望花免费网站建设让你可以放心的选择与我们合作。

安装pg_buffercache

[pg12@localhost pg_buffercache]$ make
make -C ../../src/backend generated-headers
make[1]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend/utils'
make[1]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend'
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I. -I. -I../../src/include  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pg_buffercache_pages.o pg_buffercache_pages.c -MMD -MP -MF .deps/pg_buffercache_pages.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -shared -o pg_buffercache.so pg_buffercache_pages.o  -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/appdb/pg12/pg12beta3/lib',--enable-new-dtags  
[pg12@localhost pg_buffercache]$ sudo make install
[sudo] password for pg12: 
make -C ../../src/backend generated-headers
make[1]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend/utils'
make[1]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend'
/bin/mkdir -p '/appdb/pg12/pg12beta3/lib/postgresql'
/bin/mkdir -p '/appdb/pg12/pg12beta3/share/postgresql/extension'
/bin/mkdir -p '/appdb/pg12/pg12beta3/share/postgresql/extension'
/bin/install -c -m 755  pg_buffercache.so '/appdb/pg12/pg12beta3/lib/postgresql/pg_buffercache.so'
/bin/install -c -m 644 ./pg_buffercache.control '/appdb/pg12/pg12beta3/share/postgresql/extension/'
/bin/install -c -m 644 ./pg_buffercache--1.2.sql ./pg_buffercache--1.2--1.3.sql ./pg_buffercache--1.1--1.2.sql ./pg_buffercache--1.0--1.1.sql ./pg_buffercache--unpackaged--1.0.sql  '/appdb/pg12/pg12beta3/share/postgresql/extension/'
[pg12@localhost pg_buffercache]$

简单使用

[local]:5432 pg12@testdb=# create extension pg_buffercache
pg12@testdb-# ;
CREATE EXTENSION
Time: 149.794 ms
[local]:5432 pg12@testdb=# \d pg_buffercache
                 View "public.pg_buffercache"
      Column      |   Type   | Collation | Nullable | Default 
------------------+----------+-----------+----------+---------
 bufferid         | integer  |           |          | 
 relfilenode      | oid      |           |          | 
 reltablespace    | oid      |           |          | 
 reldatabase      | oid      |           |          | 
 relforknumber    | smallint |           |          | 
 relblocknumber   | bigint   |           |          | 
 isdirty          | boolean  |           |          | 
 usagecount       | smallint |           |          | 
 pinning_backends | integer  |           |          | 
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# select * from pg_buffercache;
-[ RECORD 1 ]----+------
bufferid         | 1
relfilenode      | 33029
reltablespace    | 1664
reldatabase      | 0
relforknumber    | 0
relblocknumber   | 0
isdirty          | f
usagecount       | 5
pinning_backends | 0
-[ RECORD 2 ]----+------
bufferid         | 2
relfilenode      | 32825
reltablespace    | 1664
reldatabase      | 0
relforknumber    | 0
relblocknumber   | 0
isdirty          | f
usagecount       | 4
pinning_backends | 0
...

直接查询pg_buffercache可获得shared buffer的相关信息.
创建统计视图 

create or replace view vw_buffercache_hogs as
select case
       when pg_buffercache.reldatabase = 0
            then '- global'
       when pg_buffercache.reldatabase <> (select pg_database.oid from pg_database where pg_database.datname = current_database())
            then '- database ' || quote_literal(pg_database.datname)
       when pg_namespace.nspname = 'pg_catalog'
            then '- system catalogues'
       when pg_class.oid is null and pg_buffercache.relfilenode > 0
            then '- unknown file ' || pg_buffercache.relfilenode
       when pg_namespace.nspname = 'pg_toast' and pg_class.relname ~ '^pg_toast_[0-9]+$'
            then (substring(pg_class.relname, 10)::oid)::regclass || ' TOAST'::text
       when pg_namespace.nspname = 'pg_toast' and pg_class.relname ~ '^pg_toast_[0-9]+_index$'
            then ((rtrim(substring(pg_class.relname, 10), '_index'))::oid)::regclass || ' TOAST index'
       else pg_class.oid::regclass::text
       end as key,
       count(*) as buffers, sum(case when pg_buffercache.isdirty then 1 else 0 end) as dirty_buffers,
       round(count(*) / (SELECT pg_settings.setting FROM pg_settings WHERE pg_settings.name = 'shared_buffers')::numeric, 4) as hog_factor
from pg_buffercache
     left join pg_database on pg_database.oid = pg_buffercache.reldatabase
     left join pg_class on pg_class.relfilenode = pg_buffercache.relfilenode
     left join pg_namespace on pg_namespace.oid = pg_class.relnamespace
group by 1
order by 2 desc;

查询该视图

[local]:5432 pg12@testdb=# create or replace view vw_buffercache_hogs as
pg12@testdb-# select case
pg12@testdb-#        when pg_buffercache.reldatabase = 0
pg12@testdb-#             then '- global'
pg12@testdb-#        when pg_buffercache.reldatabase <> (select pg_database.oid from pg_database where pg_database.datname = current_database())
pg12@testdb-#             then '- database ' || quote_literal(pg_database.datname)
pg12@testdb-#        when pg_namespace.nspname = 'pg_catalog'
pg12@testdb-#             then '- system catalogues'
pg12@testdb-#        when pg_class.oid is null and pg_buffercache.relfilenode > 0
pg12@testdb-#             then '- unknown file ' || pg_buffercache.relfilenode
pg12@testdb-#        when pg_namespace.nspname = 'pg_toast' and pg_class.relname ~ '^pg_toast_[0-9]+$'
pg12@testdb-#             then (substring(pg_class.relname, 10)::oid)::regclass || ' TOAST'::text
pg12@testdb-#        when pg_namespace.nspname = 'pg_toast' and pg_class.relname ~ '^pg_toast_[0-9]+_index$'
pg12@testdb-#             then ((rtrim(substring(pg_class.relname, 10), '_index'))::oid)::regclass || ' TOAST index'
pg12@testdb-#        else pg_class.oid::regclass::text
pg12@testdb-#        end as key,
pg12@testdb-#        count(*) as buffers, sum(case when pg_buffercache.isdirty then 1 else 0 end) as dirty_buffers,
pg12@testdb-#        round(count(*) / (SELECT pg_settings.setting FROM pg_settings WHERE pg_settings.name = 'shared_buffers')::numeric, 4) as hog_factor
pg12@testdb-# from pg_buffercache
pg12@testdb-#      left join pg_database on pg_database.oid = pg_buffercache.reldatabase
pg12@testdb-#      left join pg_class on pg_class.relfilenode = pg_buffercache.relfilenode
pg12@testdb-#      left join pg_namespace on pg_namespace.oid = pg_class.relnamespace
pg12@testdb-# group by 1
pg12@testdb-# order by 2 desc;
CREATE VIEW
Time: 69.892 ms
[local]:5432 pg12@testdb=# select * from vw_buffercache_hogs;
           key            | buffers | dirty_buffers | hog_factor 
--------------------------+---------+---------------+------------
                          |   65187 |             0 |     0.9947
 - system catalogues      |     174 |            22 |     0.0027
 - unknown file 32856     |      32 |             1 |     0.0005
 - unknown file 32861     |      28 |             2 |     0.0004
 - global                 |      19 |             0 |     0.0003
 - unknown file 32869     |      15 |             4 |     0.0002
 - unknown file 32868     |      11 |             1 |     0.0002
 t_copy                   |       8 |             0 |     0.0001
 - unknown file 32867     |       8 |             1 |     0.0001
 - unknown file 32860     |       8 |             1 |     0.0001
 - unknown file 32873     |       7 |             2 |     0.0001
 - unknown file 32809     |       7 |             1 |     0.0001
 - unknown file 32816     |       6 |             3 |     0.0001
 - unknown file 32872     |       5 |             1 |     0.0001
 pg_rewrite TOAST         |       4 |             3 |     0.0001
 - unknown file 32815     |       4 |             1 |     0.0001
 - unknown file 32874     |       4 |             1 |     0.0001
 - unknown file 32859     |       3 |             1 |     0.0000
 pg_rewrite TOAST index   |       2 |             1 |     0.0000
 pg_statistic TOAST index |       2 |             0 |     0.0000
 t_import                 |       1 |             0 |     0.0000
 pg_statistic TOAST       |       1 |             0 |     0.0000
(22 rows)
Time: 201.894 ms

另外,关于索引创建后,索引数据是否已缓存在shared buffer,答案是否定的.

[local]:5432 pg12@testdb=# create index idx_t_prewarm_id on t_prewarm(id);
CREATE INDEX
Time: 578.582 ms
[local]:5432 pg12@testdb=# select * from vw_buffercache_hogs;
          key           | buffers | dirty_buffers | hog_factor 
------------------------+---------+---------------+------------
                        |   59920 |             0 |     0.9143
 t_prewarm              |    5406 |             0 |     0.0825
 - system catalogues    |      82 |             6 |     0.0013
 - unknown file 32856   |      32 |             2 |     0.0005
 - unknown file 32861   |      19 |             0 |     0.0003
 - unknown file 32869   |      14 |             1 |     0.0002
 - global               |      14 |             0 |     0.0002
 - unknown file 32867   |       8 |             0 |     0.0001
 - unknown file 32860   |       8 |             1 |     0.0001
 - unknown file 32872   |       5 |             1 |     0.0001
 - unknown file 32873   |       4 |             1 |     0.0001
 - unknown file 32816   |       4 |             0 |     0.0001
 - unknown file 32868   |       4 |             0 |     0.0001
 - unknown file 32859   |       3 |             1 |     0.0000
 - unknown file 32809   |       3 |             0 |     0.0000
 - unknown file 32815   |       3 |             0 |     0.0000
 - unknown file 32874   |       3 |             1 |     0.0000
 pg_rewrite TOAST index |       2 |             0 |     0.0000
 pg_rewrite TOAST       |       2 |             0 |     0.0000
(19 rows)
Time: 221.542 ms

在预热后才会在内存中

[local]:5432 pg12@testdb=# select pg_prewarm('idx_t_prewarm_id');
 pg_prewarm 
------------
       2745
(1 row)
Time: 51.211 ms
[local]:5432 pg12@testdb=# select * from vw_buffercache_hogs;
          key           | buffers | dirty_buffers | hog_factor 
------------------------+---------+---------------+------------
                        |   62601 |             0 |     0.9552
 idx_t_prewarm_id       |    2745 |             0 |     0.0419
 - system catalogues    |      69 |             0 |     0.0011
 - unknown file 32856   |      31 |             0 |     0.0005
 - unknown file 32861   |      18 |             0 |     0.0003
 - global               |      14 |             0 |     0.0002
 - unknown file 32869   |      11 |             0 |     0.0002
 - unknown file 32860   |       8 |             0 |     0.0001
 - unknown file 32867   |       8 |             0 |     0.0001
 - unknown file 32872   |       5 |             0 |     0.0001
 - unknown file 32816   |       4 |             0 |     0.0001
 t_prewarm              |       4 |             0 |     0.0001
 - unknown file 32873   |       4 |             0 |     0.0001
 - unknown file 32815   |       3 |             0 |     0.0000
 - unknown file 32868   |       3 |             0 |     0.0000
 - unknown file 32809   |       3 |             0 |     0.0000
 pg_rewrite TOAST index |       2 |             0 |     0.0000
 pg_rewrite TOAST       |       2 |             0 |     0.0000
 - unknown file 32874   |       1 |             0 |     0.0000
(19 rows)
Time: 131.575 ms

参考资料
Postgresql cache (memory) performance + how to warm up the cache


本文名称：PostgreSQLDBA(83)-Extension(pg_buffercache)
网页链接：http://cdkjz.cn/article/pcghsp.html
返回首页 了解更多建站资讯

相关资讯

多年建站经验

多一份参考，总有益处

联系快上网，免费获得专属《策划方案》及报价

咨询相关问题或预约面谈，可以通过以下方式与我们联系

大客户专线   成都：13518219792   座机：028-86922220

在线咨询 提交需求
友情链接 交换友情链接
雅安电信机房软文营销成都移动托管手机网站开发东电技服成都网站设计小谭建站达州托管服务器app软件定制开发VI设计

成都网站建设公司地址：成都市青羊区太升南路288号锦天国际A座10层 建设咨询028-86922220

成都快上网科技有限公司-四川网站建设设计公司 | 蜀ICP备19037934号 Copyright 2020,ALL Rights Reserved cdkjz.cn | 成都网站建设 | © Copyright 2020版权所有.

专家团队为您提供成都网站建设,成都网站设计,成都品牌网站设计,成都营销型网站制作等服务,成都建网站就找快上网！ | 成都网站建设哪家好？ | 网站建设地图