PgFincore

PgFincore are a set of functions to handle low-level management of relations (tables and indexes).

Download

Lastest release is 0.4 : direct download

You can grab the latest code with git :

 git clone git://git.postgresql.org/git/pgfincore.git

Or browse it

And the project is on pgfoundry : PgFincore

Use Cases

Here are some examples of usage. If you want more details go to Documentation

Keep a table or an index in OS Page Buffer

You may want to try to keep a table or an index into the OS Page Buffer, to do so, just execute the following query frequently enough (perhaps using crontab):

SELECT * FROM pgfadv_willneed('pgbench_accounts');
...

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 Buffer as it was while you do the snapshot. For example if you have to reboot your server, then when PostgreSQL start up the first queries might be slow because nether PostgreSQL or the OS have cache pages about the relations involved in those first queries. Executing a snapshot and a restore is very simple :

-- Snapshot
SELECT * FROM pgmincore_snapshot('pgbench_accounts');
...
-- Restore
SELECT * FROM pgfadv_willneed_snapshot('pgbench_accounts');
...

Documentation

Several functions are actually provided :

  • pgsysconf
  • pgmincore
  • pgmincore_snapshot
  • pgfadv_dontneed
  • pgfadv_willneed
  • pgfadv_willneed_snapshot
  • pgfadv_normal
  • pgfadv_random
  • pgfadv_sequential

pgsysconf

This function output size of OS blocks, number of free page.

Example

cedric=# select * from pgsysconf();
 block_size | block_free
------------+------------
       4096 |     417534

pgmincore

This function provide information about the file system cache (buffer cache).

For the specified relation (can be call with tableoid too) it return :

  • relpath : the relation path
  • block_size : the size of one block disk
  • block_disk : the total number of file system blocks of the relation
  • block_mem : the total number of file system blocks of the relation in buffer cache. (not the shared buffers from PostgreSQL but the OS cache)
  • group_mem : the number of groups of adjacent block_mem

Example

cedric=# select * from pgmincore('test');
     relpath       | block_size | block_disk | block_mem | group_mem
-------------------+------------+------------+-----------+-----------
base/16987/26245   |       4096 |     262144 |         0 |         0
base/16987/26245.1 |       4096 |       8090 |         0 |         0

cedric=# select * from test limit 10000;

cedric=# select * from pgmincore('test');
      relpath       | block_size | block_disk | block_mem | group_mem
--------------------+------------+------------+-----------+-----------
 base/16987/26245   |       4096 |     262144 |       426 |         1
 base/16987/26245.1 |       4096 |       8090 |         0 |         0

pgmincore_snapshot

This function write a file with _mincore suffix for each segment of the relation. So it does a snapshot of on memory blocks per segments, allowing a reload with pgfadv_willneed_snapshot.

Example

cedric=# select * from pgmincore_snapshot('pgbench_accounts');
          relpath           | block_size | block_disk | block_mem | group_mem
----------------------------+------------+------------+-----------+-----------
 base/16385/49240_mincore   |       4096 |     262144 |         0 |         0
 base/16385/49240.1_mincore |       4096 |     262144 |    238180 |         2
 base/16385/49240.2_mincore |       4096 |     262144 |     56478 |         2
 base/16385/49240.3_mincore |       4096 |      46902 |         0 |         0

# ls -1 /var/lib/postgresql/8.4/main/base/16385/49240*
49240
49240.1
49240.1_mincore
49240.2
49240.2_mincore
49240.3
49240.3_mincore
49240_fsm
49240_mincore
49240_vm

pgfadv_*

All those function call fadvise with the prefix as the flag and return the same columns :

For the specified relation (can be call with tableoid too) it return :

  • relpath : the relation path
  • block_size : The size in bytes of the filesystem block.
  • block_disk : the total number of file system blocks of the relation
  • block_free :

pgfadv_WILLNEED

This function set WILLNEED flag on the current relation. It means that OS will try to load every blocks of the file when it can. Main idea is to preload files on server startup, perhaps using cache hit/miss ratio or most required relations/indexes.

Example


pgfadv_WILLNEED_snapshot

This function set WILLNEED flag on each block which were in memory when pgmincore_snapshot was called .

Example


pgfadv_DONTNEED

This function set DONTNEED flag on the current relation. It means that OS will first unload blocks of the file if it need to free so memory. Main idea is to unload files when they are not usefull anymore (instead of perhaps more interesting blocks)

Example


pgfadv_NORMAL

This function set NORMAL flag on the current relation.

Example


pgfadv_RANDOM

This function set RANDOM flag on the current relation.

Example


pgfadv_SEQUENTIAL

This function set SEQUENTIAL flag on the current relation.

Example


 
Back to top
projects/pgfincore.txt · Last modified: 2010/02/07 04:33 by Cédric Villemain
 
 
CC Attribution-Noncommercial-Share Alike 3.0 Unported Valid CSS Driven by DokuWiki Recent changes RSS feed Valid XHTML 1.0 Google Analytics