PostgreSQL DBA(83) - Extension(pg_buffercache)
发表于:2024-10-04 作者:千家信息网编辑
千家信息网最后更新 2024年10月04日,使用pg_buffercache插件可查看shared buffer中的内容.安装pg_buffercache[pg12@localhost pg_buffercache]$ makemake -C
千家信息网最后更新 2024年10月04日PostgreSQL DBA(83) - Extension(pg_buffercache)
使用pg_buffercache插件可查看shared buffer中的内容.
安装pg_buffercache
[pg12@localhost pg_buffercache]$ makemake -C ../../src/backend generated-headersmake[1]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend'make -C catalog distprep generated-header-symlinksmake[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-symlinksmake[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.Pogcc -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-headersmake[1]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend'make -C catalog distprep generated-header-symlinksmake[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-symlinksmake[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_buffercachepg12@testdb-# ;CREATE EXTENSIONTime: 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 | 1relfilenode | 33029reltablespace | 1664reldatabase | 0relforknumber | 0relblocknumber | 0isdirty | fusagecount | 5pinning_backends | 0-[ RECORD 2 ]----+------bufferid | 2relfilenode | 32825reltablespace | 1664reldatabase | 0relforknumber | 0relblocknumber | 0isdirty | fusagecount | 4pinning_backends | 0...
直接查询pg_buffercache可获得shared buffer的相关信息.
创建统计视图
create or replace view vw_buffercache_hogs asselect 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_factorfrom 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.relnamespacegroup by 1order by 2 desc;
查询该视图
[local]:5432 pg12@testdb=# create or replace view vw_buffercache_hogs aspg12@testdb-# select casepg12@testdb-# when pg_buffercache.reldatabase = 0pg12@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 > 0pg12@testdb-# then '- unknown file ' || pg_buffercache.relfilenodepg12@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'::textpg12@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::textpg12@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_factorpg12@testdb-# from pg_buffercachepg12@testdb-# left join pg_database on pg_database.oid = pg_buffercache.reldatabasepg12@testdb-# left join pg_class on pg_class.relfilenode = pg_buffercache.relfilenodepg12@testdb-# left join pg_namespace on pg_namespace.oid = pg_class.relnamespacepg12@testdb-# group by 1pg12@testdb-# order by 2 desc;CREATE VIEWTime: 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 INDEXTime: 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
索引
视图
查询
信息
内存
内容
参考资料
插件
数据
答案
缓存
资料
参考
统计
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
外国大学生互联网科技创新
dota2 寻找服务器中
抢单服务器
二年级网络安全主题班会内容
java可以上传其他服务器吗
金融科技移动互联网内在动力
计算机网络技术英语重要吗
荣胜网络技术支持
远程访问服务器上禁止使用
中国移动网络技术员
横峰县网络安全
快手小游戏红包挂机服务器
2018网络安全考试试卷6
虚拟币钱包服务器
外国人看中国lol服务器数量
宁波余姚市金蝶服务器
中国大学专业软件开发
买了代理服务器怎么用
柳州ARM服务器
个人网络服务器
坦克世界服务器设置
access用什么数据库
扇贝单词软件开发
互联网安全科技
用户身份认证属于网络安全的
数据库设计的三大范式通俗解释
服务器串号显示什么原因
阿里云的git服务器
卫宁互联网科技刘自豪
临空港网络安全展示