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

近期,试用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)

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/ccf3ad895676a5e8e1c2debc10cdfc74.html