PostgreSQL DBA(83) - Extension(pg_buffercache)
发表于:2025-01-22 作者:千家信息网编辑
千家信息网最后更新 2025年01月22日,使用pg_buffercache插件可查看shared buffer中的内容.安装pg_buffercache[pg12@localhost pg_buffercache]$ makemake -C
千家信息网最后更新 2025年01月22日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安全错误
数据库的锁怎样保障安全
川达网络技术服务
it软件开发公司架构
软件开发者选项在哪里
正数网络技术有限公司公司章程
内蒙古网络安全审查政策
专科计算机网络技术文科
单片机的软件开发工具有哪些
湖南服务器硬盘质量怎么样
网吧机房的服务器有点烫手
网络安全发培训
linux用哪个软件开发
安全狗服务器规则
带数据库的触摸屏
信息发布系统软件开发
vba打印最后一行数据库
mysql数据库表清空恢复
网络安全跟软件技术的区别
汇金软件开发有限公司怎么样
局域网交换机的网络技术
拾柒网络技术北京有限公司官网
河南网络安全工作责任制
高中网络技术运用题
服务器空间阿里云
有关网络安全1500
上海良将网络技术怎么样
服务器防护设备哪个好
深圳嵌入式软件开发
今日头条系统数据库
mic并行服务器
大数据日志审计服务器