PostgreSQL DBA (83)-Extension (pg_buffercache)

Shulou Source: shulou.com Published: 2022-06-01 05:05:45 10月25日 Update

Use the pg_buffercache plug-in to view the contents of shared buffer.

Install 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-Wendif-labels-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] $

Easy to use

Information about pg_buffercache can be obtained by directly querying shared buffer.

Create a statistical view

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

Query the view

[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 | | 0.0027 | 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 |

In addition, about whether the index data has been cached in shared buffer after index creation, the answer is no.

[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

It won't be in memory until it's preheated.

[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

references

Postgresql cache (memory) performance + how to warm up the cache

PostgreSQL DBA (83)-Extension (pg_buffercache)

Related Articles

Oracle 10g rac modifies public ip and vip

Simple misoperation recovery

2018 OCP 052 up-to-date question bank and answers-35

Ocp latest question bank 052 new question tape answer collation-36 questions

Analysis of 052 new questions in OCP latest question bank-with answers-question 37

CentOS 7.5 1804 yum install Postgre