利用系统缓存提高PostgreSQL操作效率

近期，试用pgfincore，对此做一个实验记录。pgfincore是将数据库对象提前加载到OS cache中，加快操作效率。当有一顶的物理更改时，pgfincore也会将其加载到缓存中。主要是提高查询效率的，当有频繁的缓存交换时，有可能会被挤出缓存。环境介绍：OS：CentOS 6.4 64bit
Database：PostgreSQL9.4
Memory：2G
CPU：1核下载安装：在pgfoundry下载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）3、建立表testmem，分别在使用和不适用pgfincore情况下进行select, update, delete的操作，并对比执行时间，在这里为了更好的去作比较，建立三张表testmem1，testmem2，testmem3，分别进行三种操作进行对比。并插入相同的数据。[postgres@localhost bin]$ ./psql
psql （9.4.5）
Type "help" for help.

postgres=# d
No relations found.
postgres=# create table testmem1（t1 int, t2 text, t3 text, t4 bigint, t5 text, t6 text, t7 varchar, t8 char（22）, t9 numeric）;CREATE TABLE
postgres=# create table testmem2（t1 int, t2 text, t3 text, t4 bigint, t5 text, t6 text, t7 varchar, t8 char（22）, t9 numeric）;
CREATE TABLE
postgres=# create table testmem3（t1 int, t2 text, t3 text, t4 bigint, t5 text, t6 text, t7 varchar, t8 char（22）, t9 numeric）;
CREATE TABLE
postgres=# insert into testmem1 select generate_series（1,999999）, "wangshuo"||generate_series（1,999999）::text||random（）::text, generate_series（1,999999）::text||"sure",generate_series（1,999999）, random（）::text,random（）::text,random（）::text,"liuyuanyuan", generate_series（1,999999）;
INSERT 0 999999
postgres=# select pg_relation_size（"testmem1"）/1024/1024||"MB";
？column？
----------
166MB
（1 row）

postgres=# insert into testmem2 select generate_series（1,999999）, "wangshuo"||generate_series（1,999999）::text||random（）::text, generate_series（1,999999）::text||"sure",generate_series（1,999999）, random（）::text,random（）::text,random（）::text,"liuyuanyuan", generate_series（1,999999）;
INSERT 0 999999
postgres=# insert into testmem3 select generate_series（1,999999）, "wangshuo"||generate_series（1,999999）::text||random（）::text, generate_series（1,999999）::text||"sure",generate_series（1,999999）, random（）::text,random（）::text,random（）::text,"liuyuanyuan", generate_series（1,999999）;
INSERT 0 999999
postgres=# select pg_relation_size（"testmem2"）/1024/1024||"MB"; ？column？
----------
166MB
（1 row）

postgres=# select pg_relation_size（"testmem3"）/1024/1024||"MB";
？column？
----------
166MB
（1 row）

postgres=#三种操作性能对比：postgres=# select * from testmem1 where t1 < 789432 and t1 > 1208;
t1 | t2 | t3 | t4 | t5
| t6 | t7 | t8
| t9
--------+------------------------------------+------------+--------+------------
----------+----------------------+----------------------+-----------------------
-+--------
1209 | wangshuo12090.964191045146435 | 1209sure | 1209 | 0.075409890
152514 | 0.468206159770489 | 0.692714618053287 | liuyuanyuan
| 1209
1210 | wangshuo12100.652063825167716 | 1210sure | 1210 | 0.026932121
720165 | 0.802233531605452 | 0.706556385848671 | liuyuanyuan
| 1210
1211 | wangshuo12110.724962076637894 | 1211sure | 1211 | 0.732285636
477172 | 0.816857317462564 | 0.868489125277847 | liuyuanyuan
| 1211
1212 | wangshuo12120.0478062951005995 | 1212sure | 1212 | 0.190716865
006834 | 0.898683389648795 | 0.537546805106103 | liuyuanyuan
| 1212
1213 | wangshuo12130.582098880317062 | 1213sure | 1213 | 0.161297460
086644 | 0.395338968373835 | 0.684920639265329 | liuyuanyuan
| 1213
1214 | wangshuo12140.53120110463351 | 1214sure | 1214 | 0.253457680
810243 | 0.428548218682408 | 0.671272001229227 | liuyuanyuan

postgres=# update testmem2 set t7 = random（）::text where t1 < 789432 and t1 > 1208;
UPDATE 788223
postgres=# delete from testmem3 where t1 < 789432 and t1 > 1208;
DELETE 788223
   使用pgfincore前：通过查询表pg_stat_statments查看三种操作时间：
？1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

postgres=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
nullif（shared_blks_hit + shared_blks_read, 0） AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC;
-[ RECORD 1 ]------------------------------------------------------
query | update testmem2 set t7 = random（）::text where t1 < ？ and t1 > ？;
calls | 1
total_time | 14303.53
rows | 788223
hit_percent | 97.7162513440240383
-[ RECORD 2 ]------------------------------------------------------
query | delete from testmem3 where t1 < ？ and t1 > ？;
calls | 1
total_time | 5256.305
rows | 788223
hit_percent | 97.4004941321803582
-[ RECORD 6 ]------------------------------------------------------
query | select * from testmem1 where t1 < ？ and t1 > ？;
calls | 1
total_time | 2397.866
rows | 788223
hit_percent | 0.15039714245429336843使用pgfincore后：通过查询表pg_stat_statments查看三种操作时间（重启机器后，重新建库，重新建表、插数据）：SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
nullif（shared_blks_hit + shared_blks_read, 0） AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC;
-[ RECORD 1 ]------------------------------------------------------
query | update testmem2 set t7 = random（）::text where t1 < ？ and t1 > ？;
calls | 1
total_time | 6800.237
rows | 788223
hit_percent | 97.6908892215405358
-[ RECORD 2 ]------------------------------------------------------
query | delete from testmem3 where t1 < ？ and t1 > ？;
calls | 1
total_time | 3295.312
rows | 788223
hit_percent | 97.3715873996294009
-[ RECORD 3 ]------------------------------------------------------
query | select * from testmem1 where t1 < ？ and t1 > ？;
calls | 1
total_time | 891.002
rows | 788223
hit_percent | 0.000000000000000000000000
   明显看出当执行select时速度提升明显，其他的时间提升并不明显，这是因为数据还需要进行IO操作，所以在这里速度提升应该仅仅是读入的时间节省掉了。postgres=# select pgfadvise_willneed（"testmem1"）;
-[ RECORD 1 ]------+-------------------------------------
pgfadvise_willneed | （base/13003/16384,4096,42554,279311）

postgres=# select * from pgfincore（"testmem1"）;
-[ RECORD 1 ]-+-----------------
relpath | base/13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42554
pages_mem | 42554
group_mem | 1
os_pages_free | 279317
databit |

postgres=# select count（*） from testmem1 where t1 < 99999 and t1 > 12;
-[ RECORD 1 ]
count | 99986

postgres=# insert into testmem1 select generate_series（1,9999）, "wangshuo"||generate_series（1,9999）::text||random（）::text, generate_series（1,9999）::text||"sure",generate_series（1,9999）, random（）::text,random（）::text,random（）::text,"liuyuanyuan", generate_series（1,9999）;
INSERT 0 9999
postgres=# select * from pgfincore（"testmem1"）;-[ RECORD 1 ]-+-----------------
relpath | base/13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42980
pages_mem | 42980
group_mem | 1
os_pages_free | 277433
databit |

postgres=# select pg_relation_size（"testmem1"）;
-[ RECORD 1 ]----+----------
pg_relation_size | 176046080

postgres=# select 42980*4096;
-[ RECORD 1 ]-------
？column？ | 176046080

postgres=# insert into testmem1 select generate_series（1,999999）, "wangshuo"||generate_series（1,999999）::text||random（）::text, generate_series（1,999999）::text||"sure",generate_series（1,999999）, random（）::text,random（）::text,random（）::text,"liuyuanyuan", generate_series（1,999999）;
INSERT 0 999999
postgres=# select * from pgfincore（"testmem1"）;
-[ RECORD 1 ]-+-----------------
relpath | base/13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 85532
pages_mem | 6188
group_mem | 22
os_pages_free | 403050
databit |

postgres=#

上面这个实验验证了willneed会将小部分新加入的数据加载到cache中，能够快速的定位查找，但是当插入数据量较大时，就不会及时的写入到cache中了。
总结：pgfincore目前适用于数据量非常大、更新不频繁、更新量较小的表去加载到缓存中，有助于提高应用效率。插件函数介绍：这里会对函数进行介绍，并对所有函数进行操作实验。 1、pgsysconf：
这个函数输出OS block的大小（os_page_size），OS中剩余的page数（os_pages_free）和OS拥有的page总数（os_total_pages）。postgres=# select * from pgsysconf（）；
os_page_size | os_pages_free | os_total_pages
--------------+---------------+----------------
4096 | 167445 | 476688
（1 row）

2、pgsysconf_pretty：
这个函数和上面的一样，仅仅是输出更易懂些。postgres=# select * from pgsysconf_pretty（）;
os_page_size | os_pages_free | os_total_pages
--------------+---------------+----------------
4096 bytes | 654 MB | 1862 MB
（1 row）
   3、pgfadvise_willneed：这个函数是
输出文件名（relpath），OS block大小（os_page_size），对象占用系统page数（rel_os_pages）,OS剩余的page数（os_pages_free）。postgres=# select * from pgfadvise_willneed（"testmem1"）;
relpath | os_page_size | rel_os_pages | os_pages_free
------------------+--------------+--------------+---------------
base/13003/16384 | 4096 | 42554 | 167294
（1 row）

4、pgfadvise_dontneed：
这个函数对当前对象设置dontneed标记。dontneed标记的意思就是当操作系统需要释放内存时优先释放标记为dontneed的pages。输出的意义和上面一致，就不多做介绍了。postgres=# select * from pgfadvise_dontneed（"testmem1"）;
relpath | os_page_size | rel_os_pages | os_pages_free
------------------+--------------+--------------+---------------
base/13003/16384 | 4096 | 42554 | 332798
（1 row）

5、pgfadvise_NORMAL、pgfadvise_SEQUENTIAL、pgfadvise_RANDOM：
和pgfadvise_dontneed大同小异，分别将对象标记为normal、sequential、random，意思按照字面意思理解就可以了。
其实pgfadvise是一个单独的函数，参数是relname,fork,action,含义分别为对象名、进程名以及行为id，上面的3、4、5函数都是输入不同参数而设置的函数，其中willneed、dontneed、normal、sequential、random的值分别是10、20、30、40、50，且默认fork为main。
这里的pgfadvise主要调用了Linux下的函数posix_fadvise，标记值也是posix_fadvise所需要的。 6、pgfincore：
  这个函数是是提供对象在操作系统缓存中的信息的。
它分为三个函数，参数分别为（relname, fork, getdatabit），（relname, getdatabit），（relname），三个参数意思为对象名，进程名（这个地方默认是main），是否要显示databit（很长，注意显示），第一个函数需要全部输入，第二个函数默认fork为main，第三个函数默认fork为main，getdatabit为false。
它输出的是文件位置及名称（relpath），文件顺序（segment），OS page或block大小（os_page_size），对象占用系统缓存需要的页面个数（rel_os_pages），对象已经占用缓存页面个数（pages_mem），在缓存中连续的页面组的个数（group_mem），OS剩余的page数（os_pages_free），加载信息的位图（databit）。postgres=# select * from pgfincore（"testmem1", "main", false）;
-[ RECORD 1 ]-+-----------------
relpath | base/13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42554
pages_mem | 2
group_mem | 1
os_pages_free | 325475
databit |

postgres=# select * from pgfincore（"testmem1", false）;
-[ RECORD 1 ]-+-----------------
relpath | base/13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42554
pages_mem | 2
group_mem | 1
os_pages_free | 325475
databit |

postgres=# select * from pgfincore（"testmem1"）;
-[ RECORD 1 ]-+-----------------
relpath | base/13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42554
pages_mem | 2
group_mem | 1
os_pages_free | 325475
databit |7、pgfadvise_loader：这个函数可以对页面缓存直接进行两方面的作用。因此，它能通过页面的位图在缓存中来对页面进行加载或卸载。
它分为两个函数和上边的类似，就是设置缺省值，的输入参数是（relname, fork, segment, load, unload, databit）和（relname, segment, load, unload, databit），分别是对象名，进程名，文件序号，是否加载，是否卸载，位图信息。第二个函数默认fork为main。
它输出的是物理文件名及path（relpath），OS page或block大小（os_page_size）, OS中剩余的page数（os_pages_free），加载的page数（pages_load），卸载的page数（pages_unload）。postgres=# select * from pgfincore（"testmem1"）;
-[ RECORD 1 ]-+-----------------
relpath | base/13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42554
pages_mem | 0
group_mem | 0
os_pages_free | 331290
databit |

postgres=# select * from pgfadvise_loader（"testmem1", 0, true, true, B"1100"）;
-[ RECORD 1 ]--+-----------------
relpath | base/13003/16384
os_page_size | 4096
os_pages_free | 331294
pages_loaded | 2
pages_unloaded | 2

postgres=# select * from pgfincore（"testmem1"）;
-[ RECORD 1 ]-+-----------------
relpath | base/13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42554
pages_mem | 2
group_mem | 1
os_pages_free | 331290
databit |------------------------------------华丽丽的分割线------------------------------------Ubuntu Server 14.04 下安装 PostgreSQL 9.3.5 数据库 http://www.linuxidc.com/Linux/2015-12/126175.htmCentOS 6.3环境下yum安装PostgreSQL 9.3 http://www.linuxidc.com/Linux/2014-05/101787.htmPostgreSQL缓存详述 http://www.linuxidc.com/Linux/2013-07/87778.htmWindows平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htmUbuntu下LAPP（Linux+Apache+PostgreSQL+PHP）环境的配置与安装 http://www.linuxidc.com/Linux/2013-04/83564.htmUbuntu上的phppgAdmin安装及配置 http://www.linuxidc.com/Linux/2011-08/40520.htmCentOS平台下安装PostgreSQL9.3 http://www.linuxidc.com/Linux/2014-05/101723.htmPostgreSQL配置Streaming Replication集群 http://www.linuxidc.com/Linux/2014-05/101724.htm如何在CentOS 7/6.5/6.4 下安装PostgreSQL 9.3 与 phpPgAdmin http://www.linuxidc.com/Linux/2014-12/110108.htm------------------------------------华丽丽的分割线------------------------------------PostgreSQL 的详细介绍：请点这里
PostgreSQL 的下载地址：请点这里本文永久更新链接地址