客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
开源软件名称：PgFincore开源软件地址：https://gitee.com/mirrors/PgFincore开源软件介绍：
PgFincoreA set of functions to manage pages in memory from PostgreSQLA set of functions to handle low-level management of relations using mincore toexplore cache memory. DESCRIPTIONWith PostgreSQL, each Table or Index is splitted in segments of (usually) 1GB,and each segment is splitted in pages in memory then in blocks for thefilesystem. Those functions let you know which and how many disk block from a relation arein the page cache of the operating system. It can provide the result as a VarBitand can be stored in a table. Then using this table, it is possible to restorethe page cache state for each block of the relation, even in another server,thanks to Streaming Replication. Other functions are used to set a POSIX_FADVISE flag on the entire relation(each segment). The more usefull are probably WILLNEED and DONTNEED whichpush and pop blocks of each segments of a relation from page cache,respectively. Each functions are call with at least a table name or an index name (or oid)as a parameter and walk each segment of the relation. DOWNLOADYou can grab the latest code with git: git clone git://git.postgresql.org/git/pgfincore.gitorgit://github.com/klando/pgfincore.gitAnd the project is on pgfoundry : http://pgfoundry.org/projects/pgfincore INSTALLFrom source code: make cleanmakesumake installFor PostgreSQL >= 9.1, log in your database and: mydb=# CREATE EXTENSION pgfincore;For other release, create the functions from the sql script (it should be inyour contrib directory): psql mydb -f pgfincore.sqlPgFincore is also shipped with Debian scripts to build your own package: aptitude install debhelper postgresql-server-dev-all postgresql-server-dev-9.1# or postgresql-server-dev-8.4|postgresql-server-dev-9.0make debdpkg -i ../postgresql-9.1-pgfincore_1.1.1-1_amd64.debPgFincore is packaged for RPM at http://yum.postgresql.org/PgFincore is packaged for debian at http://pgapt.debian.net/ EXAMPLESHere are some examples of usage. If you want more details go to Documentation_ Get current state of a relationMay be useful: cedric=# select * from pgfincore('pgbench_accounts'); relpath | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit | pages_dirty | group_dirty --------------------+---------+--------------+--------------+-----------+-----------+---------------+---------+-------------+------------- base/11874/16447 | 0 | 4096 | 262144 | 262144 | 1 | 81016 | | 0 | 0 base/11874/16447.1 | 1 | 4096 | 65726 | 65726 | 1 | 81016 | | 0 | 0(2 rows)Time: 31.563 msLoad a table or an index in OS Page BufferYou may want to try to keep a table or an index into the OS Page Cache, orpreload a table before your well know big query is executed (reducing the querytime). To do so, just execute the following query: cedric=# select * from pgfadvise_willneed('pgbench_accounts'); relpath | os_page_size | rel_os_pages | os_pages_free --------------------+--------------+--------------+--------------- base/11874/16447 | 4096 | 262144 | 169138 base/11874/16447.1 | 4096 | 65726 | 103352(2 rows) Time: 4462,936 ms
Snapshot and Restore the OS Page Buffer state of a table or an index (or more)You may want to restore a table or an index into the OS Page Cache as it waswhile you did the snapshot. For example if you have to reboot your server, thenwhen PostgreSQL start up the first queries might be slower because neitherPostgreSQL or the OS have pages in their respective cache about the relationsinvolved in those first queries. Executing a snapshot and a restore is very simple: -- Snapshotcedric=# create table pgfincore_snapshot ascedric-# select 'pgbench_accounts'::text as relname,*,now() as date_snapshotcedric-# from pgfincore('pgbench_accounts',true);-- Restorecedric=# select * from pgfadvise_loader('pgbench_accounts', 0, true, true, (select databit from pgfincore_snapshot where relname='pgbench_accounts' and segment = 0)); relpath | os_page_size | os_pages_free | pages_loaded | pages_unloaded ------------------+--------------+---------------+--------------+---------------- base/11874/16447 | 4096 | 80867 | 262144 | 0(1 row)Time: 35.349 ms
SYNOPSISpgsysconf(OUT os_page_size bigint, OUT os_pages_free bigint, OUT os_total_pages bigint) RETURNS recordpgsysconf_pretty(OUT os_page_size text, OUT os_pages_free text, OUT os_total_pages text) RETURNS recordpgfadvise(IN relname regclass, IN fork text, IN action int, OUT relpath text, OUT os_page_size bigint, OUT rel_os_pages bigint, OUT os_pages_free bigint) RETURNS setof recordpgfadvise_willneed(IN relname regclass, OUT relpath text, OUT os_page_size bigint, OUT rel_os_pages bigint, OUT os_pages_free bigint) RETURNS setof recordpgfadvise_dontneed(IN relname regclass, OUT relpath text, OUT os_page_size bigint, OUT rel_os_pages bigint, OUT os_pages_free bigint) RETURNS setof recordpgfadvise_normal(IN relname regclass, OUT relpath text, OUT os_page_size bigint, OUT rel_os_pages bigint, OUT os_pages_free bigint) RETURNS setof recordpgfadvise_sequential(IN relname regclass, OUT relpath text, OUT os_page_size bigint, OUT rel_os_pages bigint, OUT os_pages_free bigint) RETURNS setof recordpgfadvise_random(IN relname regclass, OUT relpath text, OUT os_page_size bigint, OUT rel_os_pages bigint, OUT os_pages_free bigint) RETURNS setof recordpgfadvise_loader(IN relname regclass, IN fork text, IN segment int, IN load bool, IN unload bool, IN databit varbit, OUT relpath text, OUT os_page_size bigint, OUT os_pages_free bigint, OUT pages_loaded bigint, OUT pages_unloaded bigint) RETURNS setof recordpgfadvise_loader(IN relname regclass, IN segment int, IN load bool, IN unload bool, IN databit varbit, OUT relpath text, OUT os_page_size bigint, OUT os_pages_free bigint, OUT pages_loaded bigint, OUT pages_unloaded bigint) RETURNS setof recordpgfincore(IN relname regclass, IN fork text, IN getdatabit bool, OUT relpath text, OUT segment int, OUT os_page_size bigint, OUT rel_os_pages bigint, OUT pages_mem bigint, OUT group_mem bigint, OUT os_pages_free bigint, OUT databit varbit, OUT pages_dirty bigint, OUT group_dirty bigint) RETURNS setof recordpgfincore(IN relname regclass, IN getdatabit bool, OUT relpath text, OUT segment int, OUT os_page_size bigint, OUT rel_os_pages bigint, OUT pages_mem bigint, OUT group_mem bigint, OUT os_pages_free bigint, OUT databit varbit, OUT pages_dirty bigint, OUT group_dirty bigint) RETURNS setof recordpgfincore(IN relname regclass, OUT relpath text, OUT segment int, OUT os_page_size bigint, OUT rel_os_pages bigint, OUT pages_mem bigint, OUT group_mem bigint, OUT os_pages_free bigint, OUT databit varbit, OUT pages_dirty bigint, OUT group_dirty bigint) RETURNS setof recordDOCUMENTATIONpgsysconfThis function output size of OS blocks, number of free page in the OS Page Buffer. cedric=# select * from pgsysconf(); os_page_size | os_pages_free | os_total_pages --------------+---------------+---------------- 4096 | 80431 | 4094174pgsysconf_prettyThe same as above, but with pretty output. cedric=# select * from pgsysconf_pretty(); os_page_size | os_pages_free | os_total_pages --------------+---------------+----------------4096 bytes | 314 MB | 16 GBpgfadvise_WILLNEEDThis function set WILLNEED flag on the current relation. It means that theOperating Sytem will try to load as much pages as possible of the relation.Main idea is to preload files on server startup, perhaps using cache hit/missratio or most required relations/indexes. cedric=# select * from pgfadvise_willneed('pgbench_accounts'); relpath | os_page_size | rel_os_pages | os_pages_free --------------------+--------------+--------------+--------------- base/11874/16447 | 4096 | 262144 | 80650 base/11874/16447.1 | 4096 | 65726 | 80650pgfadvise_DONTNEEDThis function set DONTNEED flag on the current relation. It means that theOperating System will first unload pages of the file if it need to free somememory. Main idea is to unload files when they are not usefull anymore (insteadof perhaps more interesting pages) cedric=# select * from pgfadvise_dontneed('pgbench_accounts'); relpath | os_page_size | rel_os_pages | os_pages_free--------------------+--------------+--------------+--------------- base/11874/16447 | 4096 | 262144 | 342071 base/11874/16447.1 | 4096 | 65726 | 408103pgfadvise_NORMALThis function set NORMAL flag on the current relation. pgfadvise_SEQUENTIALThis function set SEQUENTIAL flag on the current relation. pgfadvise_RANDOMThis function set RANDOM flag on the current relation. pgfadvise_loaderThis function allow to interact directly with the Page Cache.It can be used to load and/or unload page from memory based on a varbitrepresenting the map of the pages to load/unload accordingly. Work with relation pgbench_accounts, segment 0, arbitrary varbit map: -- Loading and Unloadingcedric=# select * from pgfadvise_loader('pgbench_accounts', 0, true, true, B'111000'); relpath | os_page_size | os_pages_free | pages_loaded | pages_unloaded ------------------+--------------+---------------+--------------+---------------- base/11874/16447 | 4096 | 408376 | 3 | 3-- Loadingcedric=# select * from pgfadvise_loader('pgbench_accounts', 0, true, false, B'111000'); relpath | os_page_size | os_pages_free | pages_loaded | pages_unloaded ------------------+--------------+---------------+--------------+---------------- base/11874/16447 | 4096 | 408370 | 3 | 0-- Unloadingcedric=# select * from pgfadvise_loader('pgbench_accounts', 0, false, true, B'111000'); relpath | os_page_size | os_pages_free | pages_loaded | pages_unloaded ------------------+--------------+---------------+--------------+---------------- base/11874/16447 | 4096 | 408370 | 0 | 3pgfincoreThis function provide information about the file system cache (page cache). cedric=# select * from pgfincore('pgbench_accounts'); relpath | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit | pages_dirty | group_dirty --------------------+---------+--------------+--------------+-----------+-----------+---------------+---------+-------------+------------- base/11874/16447 | 0 | 4096 | 262144 | 3 | 1 | 408444 | | 0 | 0 base/11874/16447.1 | 1 | 4096 | 65726 | 0 | 0 | 408444 | | 0 | 0For the specified relation it returns:
DEBUGYou can debug the PgFincore with the following error level: DEBUG1 andDEBUG5. For example: set client_min_messages TO debug1; -- debug5 is only usefull to trace each blockREQUIREMENTS
LIMITATIONS
SEE ALSOData Bene, PostgreSQL Expertise, Technical Support and Assistance, Trainings:  |
请发表评论