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

环境介绍:

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

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       |

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏安恒网络空间安全讲武堂

从零基础到解题之 Python is the best language

-目录- 前言 环境搭建 源码结构 题目分析 Python is the best language1 Python is the best langua...

29940
来自专栏PingCAP的专栏

TiDB 源码阅读系列文章(三)SQL 的一生

上一篇文章讲解了 TiDB 项目的结构以及三个核心部分,本篇文章从 SQL 处理流程出发,介绍哪里是入口,对 SQL 需要做哪些操作,知道一个 SQL 是从哪里...

443150
来自专栏技术小讲堂

Entity Framework4.3 Code-First基于代码的数据迁移讲解1.建立一个最初的模型和数据库   2.启动Migration(数据迁移)3.第一个数据迁移4.订制的数据迁移4.动态

前段时间一直在研究Entity Framework4,但是苦于没有找到我特别中意的教程,要么就是千篇一律的文章,而且写的特别简单,可以说,糟践了微软这么牛埃克斯...

35180
来自专栏抠抠空间

Flask之基本使用与配置

Flask是一个基于Python开发并且依赖jinja2模板和Werkzeug WSGI服务的一个微型框架,对于Werkzeug本质是Socket服务端,其用于...

10420
来自专栏程序员的SOD蜜

PDF.NET(PWMIS数据开发框架)之SQL-MAP目标和规范

SQL-MAP的目标: 集中管理SQL语句,所有SQL语句放在专门的配置文件中进行管理; 通过替换SQL配置文件,达到平滑切换数据库到另外一个数据库,比如从O...

27180
来自专栏王硕

原 PostgreSQL用C完成存储过程例子

354100
来自专栏Java学习网

Java开发之使用Java 8 Streams 对数据库进行 CRUD 操作

Speedment 是一个开放源代码的工具集,它可以被用来生成 Java 实体,并且能将我们同数据库的通信过程管理起来。你可以利用一个图形工具连接到数据库并生成...

12630
来自专栏腾讯Bugly的专栏

Android GC 原理探究

作者:陈昱全 知乎主页:https://www.zhihu.com/people/chen-yu-quan 前言 想写一篇关于android GC的想法来源于追...

43980
来自专栏Danny的专栏

机房收费系统(VB.NET)——存储过程实战

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/huyuyang6688/article/...

18150
来自专栏程序猿

带您理解SQLSERVER是如何执行一个查询的

带您理解SQLSERVER是如何执行一个查询的 连接方式和请求 如果你是一个开发者,并且你的程序使用SQLSERVER来做数据库的话 你会想知道当你用你的程序执...

54490

扫码关注云+社区

领取腾讯云代金券