Differences
This shows you the differences between two versions of the page.
|
projects:pgfincore [2010/02/07 04:33] Cédric Villemain |
projects:pgfincore [2010/05/23 07:15] (current) Cédric Villemain |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | A talk about PgFincore is scheduled at [[http://www.pgcon.org/2010/|PgCon10]]. | ||
| + | |||
| ====== PgFincore ====== | ====== PgFincore ====== | ||
| - | PgFincore are a set of functions to handle low-level management of relations (tables and indexes). | + | PgFincore is a set of functions to manage blocks in memory. |
| + | |||
| + | Each Table or Index is truncated in segment of (usually) 1GB, and each segment is truncated in block in filesystem. | ||
| + | |||
| + | Those functions let you know which and how many disk block from a relation are in the page cache of the operating system, and eventually write the result to a file. Then using this file, it is possible to restore the page cache state for each block of the relation. | ||
| + | |||
| + | Other functions are used to set a //POSIX_FADVISE// flag on the entire relation (each segment). The more usefull are probably //WILLNEED// and //DONTNEED// which push 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. | ||
| ===== Download ===== | ===== Download ===== | ||
| - | Lastest release is 0.4 : [[http://pgfoundry.org/frs/download.php/2541/pgfincore-v0.4.0.tar.gz|direct download ]] | + | Lastest release is 0.4.1 : [[http://pgfoundry.org/frs/download.php/2670/pgfincore-v0.4.1.tar.gz|direct download ]] |
| You can grab the latest code with git : | You can grab the latest code with git : | ||
| Line 14: | Line 25: | ||
| And the project is on pgfoundry : [[http://pgfoundry.org/projects/pgfincore|PgFincore]] | And the project is on pgfoundry : [[http://pgfoundry.org/projects/pgfincore|PgFincore]] | ||
| + | |||
| + | ===== Install ===== | ||
| + | |||
| + | First, make stage : | ||
| + | |||
| + | USE_PGXS=1 make clean | ||
| + | USE_PGXS=1 make | ||
| + | su | ||
| + | USE_PGXS=1 make install | ||
| + | |||
| + | Then, create the functions from the sql script (it should be in your contrib directory) : | ||
| + | |||
| + | psql mydb -f pgfincore.sql | ||
| ===== Use Cases ===== | ===== Use Cases ===== | ||
| Line 19: | Line 43: | ||
| Here are some examples of usage. If you want more details go to [[#Documentation]] | Here are some examples of usage. If you want more details go to [[#Documentation]] | ||
| - | ==== Keep a table or an index in OS Page Buffer ==== | + | ==== Load a table or an index in OS Page Cache ==== |
| + | |||
| + | You may want to try to keep a table or an index into the OS Page Cache, or preload a table before your well know big query is executed (reducing the query time). | ||
| + | |||
| + | To do so, just execute the following query: | ||
| - | 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): | ||
| <code sql> | <code sql> | ||
| - | select * from pgfadv_willneed('pgbench_accounts'); | + | cedric=# select * from pgfadv_willneed('pgbench_accounts'); |
| - | ... | + | relpath | block_size | block_disk | block_free |
| + | --------------------+------------+------------+------------ | ||
| + | base/16384/24598 | 4096 | 262144 | 111882 | ||
| + | base/16384/24598.1 | 4096 | 55318 | 56764 | ||
| + | (2 rows) | ||
| + | |||
| + | Time: 39309,294 ms | ||
| </code> | </code> | ||
| - | ==== Snapshot and Restore the OS Page Buffer state of a table or an index (or more) ==== | + | //The column "block_size" report that block size of the filesystem is 4KB.// |
| + | //The column "block_disk" is the number of blocks of the specified file.// | ||
| + | //The column "block_free" is the number of free blocks in memory (for caching).// | ||
| - | 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. | + | ==== Snapshot and Restore the OS Page Cache 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 was while you did 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 : | Executing a snapshot and a restore is very simple : | ||
| <code sql> | <code sql> | ||
| -- Snapshot | -- Snapshot | ||
| - | select * from pgmincore_snapshot('pgbench_accounts'); | + | cedric=# select * from pgmincore_snapshot('pgbench_accounts'); |
| - | ... | + | relpath | block_size | block_disk | block_mem | group_mem |
| + | --------------------+------------+------------+-----------+----------- | ||
| + | base/16384/24598 | 4096 | 262144 | 131745 | 1 | ||
| + | base/16384/24598.1 | 4096 | 55318 | 55318 | 1 | ||
| -- Restore | -- Restore | ||
| - | select * from pgfadv_willneed_snapshot('pgbench_accounts'); | + | cedric=# select * from pgfadv_willneed_snapshot('pgbench_accounts'); |
| - | ... | + | relpath | block_size | block_disk | block_free |
| + | --------------------+------------+------------+------------ | ||
| + | base/16384/24598 | 4096 | 262144 | 105335 | ||
| + | base/16384/24598.1 | 4096 | 55318 | 50217 | ||
| + | (2 rows) | ||
| + | |||
| + | Time: 38745,140 ms | ||
| </code> | </code> | ||
| + | |||
| + | //The column "block_mem" report how many blocks of the file are in memory.// | ||
| + | //The column "group_mem" report that all the bloks in memory are contigous (only one group).// | ||
| + | |||
| + | A more complete example is at [[./postgres_stuff|my postgres stuff]]. | ||
| ===== Documentation ===== | ===== Documentation ===== | ||
| Line 57: | Line 109: | ||
| ==== pgsysconf ==== | ==== pgsysconf ==== | ||
| - | This function output size of OS blocks, number of free page. | + | This function output size of OS blocks, number of free page in the OS Page Cache. |
| === Example === | === Example === | ||
| Line 70: | Line 122: | ||
| ==== pgmincore ==== | ==== pgmincore ==== | ||
| - | This function provide information about the file system cache (buffer cache). | + | This function provide information about the file system cache (page cache). |
| For the specified relation (can be call with tableoid too) it return : | For the specified relation (can be call with tableoid too) it return : | ||
| Line 77: | Line 129: | ||
| * block_size : the size of one block disk | * block_size : the size of one block disk | ||
| * block_disk : the total number of file system blocks of the relation | * 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) | + | * block_mem : the total number of file system blocks of the relation in page cache. (not the shared buffers from PostgreSQL but the OS cache) |
| * group_mem : the number of groups of adjacent block_mem | * group_mem : the number of groups of adjacent block_mem | ||
| Line 83: | Line 135: | ||
| <code> | <code> | ||
| - | 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('pgbench_accounts'); |
| + | relpath | block_size | block_disk | block_mem | group_mem | ||
| + | --------------------+------------+------------+-----------+----------- | ||
| + | base/16384/16603 | 4096 | 262144 | 0 | 0 | ||
| + | base/16384/16603.1 | 4096 | 65726 | 0 | 0 | ||
| - | cedric=# select * from pgmincore('test'); | + | cedric=# select * from pgbench_accounts limit 10000; |
| + | |||
| + | cedric=# select * from pgmincore('pgbench_accounts'); | ||
| relpath | block_size | block_disk | block_mem | group_mem | relpath | block_size | block_disk | block_mem | group_mem | ||
| --------------------+------------+------------+-----------+----------- | --------------------+------------+------------+-----------+----------- | ||
| - | base/16987/26245 | 4096 | 262144 | 426 | 1 | + | base/16384/16603 | 4096 | 262144 | 414 | 1 |
| - | base/16987/26245.1 | 4096 | 8090 | 0 | 0 | + | base/16384/16603.1 | 4096 | 65726 | 0 | 0 |
| </code> | </code> | ||
| Line 135: | Line 189: | ||
| * block_size : The size in bytes of the filesystem block. | * block_size : The size in bytes of the filesystem block. | ||
| * block_disk : the total number of file system blocks of the relation | * block_disk : the total number of file system blocks of the relation | ||
| - | * block_free : | + | * block_free : the number of free blocks in memory (for caching). |
| === pgfadv_WILLNEED === | === 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. | + | This function set //WILLNEED// flag on the current relation. It means that OS will try to load as much blocks as possible of the relation. Main idea is to preload files on server startup, perhaps using cache hit/miss ratio or most required relations/indexes. |
| == Example == | == Example == | ||
| <code> | <code> | ||
| + | cedric=# select * from pgfadv_willneed('pgbench_accounts'); | ||
| + | relpath | block_size | block_disk | block_free | ||
| + | --------------------+------------+------------+------------ | ||
| + | base/16384/16603 | 4096 | 262144 | 3744 | ||
| + | base/16384/16603.1 | 4096 | 65726 | 4236 | ||
| </code> | </code> | ||
| Line 153: | Line 212: | ||
| <code> | <code> | ||
| + | cedric=# select * from pgfadv_willneed_snapshot('pgbench_accounts'); | ||
| + | relpath | block_size | block_disk | block_free | ||
| + | --------------------+------------+------------+------------ | ||
| + | base/16384/24598 | 4096 | 262144 | 105335 | ||
| + | base/16384/24598.1 | 4096 | 55318 | 50217 | ||
| </code> | </code> | ||
| Line 162: | Line 226: | ||
| <code> | <code> | ||
| + | cedric=# select * from pgfadv_dontneed('pgbench_accounts'); | ||
| + | relpath | block_size | block_disk | block_free | ||
| + | --------------------+------------+------------+------------ | ||
| + | base/16384/24598 | 4096 | 262144 | 178743 | ||
| + | base/16384/24598.1 | 4096 | 55318 | 234078 | ||
| </code> | </code> | ||
| Line 171: | Line 240: | ||
| <code> | <code> | ||
| + | cedric=# select * from pgfadv_NORMAL('pgbench_accounts'); | ||
| + | relpath | block_size | block_disk | block_free | ||
| + | --------------------+------------+------------+------------ | ||
| + | base/16384/24598 | 4096 | 262144 | 233954 | ||
| + | base/16384/24598.1 | 4096 | 55318 | 233954 | ||
| </code> | </code> | ||
| Line 180: | Line 254: | ||
| <code> | <code> | ||
| + | cedric=# select * from pgfadv_RANDOM('pgbench_accounts'); | ||
| + | relpath | block_size | block_disk | block_free | ||
| + | --------------------+------------+------------+------------ | ||
| + | base/16384/24598 | 4096 | 262144 | 234233 | ||
| + | base/16384/24598.1 | 4096 | 55318 | 234233 | ||
| </code> | </code> | ||
| Line 189: | Line 268: | ||
| <code> | <code> | ||
| + | cedric=# select * from pgfadv_SEQUENTIAL('pgbench_accounts'); | ||
| + | relpath | block_size | block_disk | block_free | ||
| + | --------------------+------------+------------+------------ | ||
| + | base/16384/24598 | 4096 | 262144 | 233985 | ||
| + | base/16384/24598.1 | 4096 | 55318 | 233985 | ||
| </code> | </code> | ||
| + | ===== Debug ===== | ||
| + | |||
| + | You can debug the function with the following error level : //DEBUG1// and //DEBUG5// | ||
| + | set client_min_messages TO debug1; -- debug5 is only usefull to trace each block | ||
| + | ===== Notes ===== | ||
| + | * linux 2.6.34 : POSIX_FADV_RANDOM now does not deactivate readahead 100% . see http://git.kernel.org/?p=linux/kernel/git/torvalds/linux-2.6.git;a=commitdiff;h=0141450f66c3c12a3aaa869748caa64241885cdf | ||
| + | --> impact O_DIRECT | ||
| + | --> improv | ||
