溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
使用pg_buffercache插件可查看shared buffer中的內容.
安裝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
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
