近期，试用pgfincore，对此做一个实验记录。pgfincore是将数据库对象提前加载到OS cache中，加快操作效率。当有一顶的物理更改时，pgfincore也会将其加载到缓存中。主要是提高查询效率的，当有频繁的缓存交换时，有可能会被挤出缓存。
环境介绍：
OS：CentOS 6.4 64bit
Database：PostgreSQL9.4
Memory：2G
CPU：1核
下载安装：
在下载pgfincore-v1.1.1.tar.gz，，将源码解压到数据库源码下的contrib下。不要在其github上下载，目前应该有一些bug，最新版本为1.1.1，1.1.2在我试用的时候发现大部分函数无法使用。
其次在我编译时一直出现如下错误：
[postgres@localhost pgfincore-1.1.1]$ make
/bin/sh: pg_config: command not found
make: pg_config: Command not found
cp pgfincore.sql pgfincore--1.1.1.sql
cp: cannot create regular file `pgfincore--1.1.1.sql': Permission denied
make: *** [pgfincore--1.1.1.sql] Error 1
所以在此我修改了一下Makefile文件，内容如下：
# contrib/pgfincore/Makefile
MODULE_big = pgfincore
OBJS = pgfincore.o
EXTENSION = pgfincore
DATA = pgfincore--1.1.1.sql pgfincore--unpackaged--1.1.1.sql
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/pgfincore
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
现在进行make，即可：
[postgres@localhost pgfincore-1.1.1]$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -c -o pgfincore.o pgfincore.c
pgfincore.c: In function ‘pgsysconf’:
pgfincore.c:227: warning: implicit declaration of function ‘heap_form_tuple’
pgfincore.c:227: warning: assignment makes pointer from integer without a cast
pgfincore.c: In function ‘pgfadvise’:
pgfincore.c:477: warning: assignment makes pointer from integer without a cast
pgfincore.c: In function ‘pgfadvise_loader’:
pgfincore.c:710: warning: assignment makes pointer from integer without a cast
pgfincore.c: In function ‘pgfincore’:
pgfincore.c:1016: warning: assignment makes pointer from integer without a cast
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -shared -o pgfincore.so pgfincore.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/hg3.0/lib',--enable-new-dtags
[postgres@localhost pgfincore-1.1.1]$ make install
/bin/mkdir -p '/opt/hg3.0/lib/postgresql'
/bin/mkdir -p '/opt/hg3.0/share/postgresql/extension'
/bin/mkdir -p '/opt/hg3.0/share/postgresql/extension'
/usr/bin/install -c -m 755 pgfincore.so '/opt/hg3.0/lib/postgresql/pgfincore.so'
/usr/bin/install -c -m 644 pgfincore.control '/opt/hg3.0/share/postgresql/extension/'
/usr/bin/install -c -m 644 pgfincore--1.1.1.sql pgfincore--unpackaged--1.1.1.sql '/opt/hg3.0/share/postgresql/extension/'
[postgres@localhost pgfincore-1.1.1]$
实验记录：
1、安装：
[postgres@localhost bin]$ ./psql
psql (9.4.5)
Type "help" for help.
postgres=# create extension pgfincore ;
CREATE EXTENSION
postgres=# select * from pg_extension ;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig |
extcondition
-----------+----------+--------------+----------------+------------+-----------+
--------------
plpgsql | 10 | 11 | f | 1.0 | |
pgfincore | 10 | 2200 | t | 1.1.1 | |
(2 rows)
postgres=#
2、准备工作：记录比较使用pgfincore前后的性能差，使用插件pg_stat_statments记录每条sql的使用时间。
其次将shared_buffer改为16mb，减少其对pgfincore的影响。
postgres=# create extension pg_stat_statements ;
CREATE EXTENSION
postgres=# select * from pg_extension ;
extname | extowner | extnamespace | extrelocatable | extversion | ex
tconfig | extcondition
--------------------+----------+--------------+----------------+------------+---
--------+--------------
plpgsql | 10 | 11 | f | 1.0 |
|
pgfincore | 10 | 2200 | t | 1.1.1 |
|
pg_stat_statements | 10 | 2200 | t | 1.2 |
|
(3 rows)