每日一博 | 利用系统缓存提高 PostgreSQL 操作效率

环境介绍：

下载安装：

在 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查看三种操作时间：

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：

6、pgfincore：

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       |