文档章节

PostgreSQL缓存

LianyouCQ
 LianyouCQ
发布于 2013/07/23 18:17
字数 2565
阅读 3.2K
收藏 32

缓存.... !!, 它很难在一篇文章中解释清楚。但是我会努力分享我从Heikki, Robert Haas, Bruce Momjian那里学到的知识。在PostgreSQL里有两层:PG共享缓冲和操作系统页面缓存,任何读写都会通过操作系统缓存(迄今为止还没有其它途径)。Postgres把数据写在操作系统页面缓存,用户觉得数据好像回写到了磁盘,之后操作系统缓存才会写到对应的物理磁盘位置。PG共享缓冲无法控制系统页面缓存,甚至连系统缓存是什么都不知道。所以,Postgres DBA或者专家给出的大多数建议都是更快的磁盘读写或者更好的缓存。

PostgreSQL的缓存/缓冲和其它数据库十分相像并且十分复杂。因为我有Oracle和mindset背景,所以我使用怎么样/什么时候/什么/为什么等提问方式,关于数据库的缓冲缓存,固定的缓冲,刷新数据库缓存,以及预加载数据库等方面,我都是从这种方式获得答案的,然而这种方式有点与众不同。尽管我的问题很烦人,但是他们总是耐心的回答,使我明白扩展我的知识,反过来,你才能阅读这篇博文... :) ..

在一些学习上,我画了一幅Postgres中数据在内存和磁盘之间传递的,以及一些重要的工具和Robert Hass提供的新补丁(pg_prewarm).

pg_buffercache

它是一个contrib模块,它会告诉你什么是PostgreSQL缓存。像下面安装:

postgres=# CREATE EXTENSION pg_buffercache

pgfincore

它有一个显示数据在操作系统页面缓存中信息的功能。Pgfincore和pg_buffercache联合会十分方便的。现在,它可以同时获得PG缓冲和操作系统页面缓存信息。感谢Cerdic Villemain。Pgfincore的主干是fadvise,fincore,它俩是linux ftools。你可以使用源码安装fincore/fadvise。你可以使用pgfincore contrib模块或者ftools,都会产生同样的结果。我试了两者,它们都十分简单优秀。

安装:

下载最新版本:

http://pgfoundry.org/frs/download.php/3186/pgfincore-v1.1.1.tar.gz

使用root用户:

export PATH=/usr/local/pgsql91/bin:$PATH     //设置执行pg_config的路径
tar -xvf pgfincore-v1.1.1.tar.gz
cd pgfincore-1.1.1
make clean
make 
make install
现在连接到PG,运行下面的命令:
postgres=# CREATE EXTENSION pgfincore;

pg_prewarm

预加载关系/索引到PG缓存中,在PostgreSQL中可能吗?当然可以了,感谢Robert Hass,他提交一些补丁到社区,期待它能够在PG 9.2或者PG 9.3中可行。然而,你可以使用这个补丁在PG 9.1做一些测试。
pg_prewarm
有三种模式:

  1. PREFETCH: 异步获取数据块到操作系统缓存中,而不是PG缓冲(提示只是操作系统缓存)
  2. READ:读取所有的数据块到虚拟缓存中,然后写到操作系统缓存中(提示只是操作系统缓存)
  3. BUFFER:读取所有的或者一些数据块到数据库缓冲中。
安装:
我把pg_prewarm补丁加入到PG源码安装中,你需要调整你的每步安装。
  1. 解压PG源码路径: /usr/local/src/postgresql-9.1.3
  2. PG安装路径: /usr/local/pgsql91
  3. 所有下载路径: /usr/local/src
注意:应用pg_prewarm补丁之前安装PG
1. 下载补丁,放在/usr/local/src
http://archives.postgresql.org/pgsql-hackers/2012-03/binRVNreQMnK4.bin
补丁附加邮件地址

http://archives.postgresql.org/message-id/CA+TgmobRrRxCO+t6gcQrw_dJw+Uf9ZEdwf9beJnu+RB5TEBjEw@mail.gmail.com

2. 在下载之后,到PG源码目录,然后执行下面几步。

# cd /usr/local/src/postgresql-9.1.3
# patch -p1 < ../pg_prewarm.bin         (在下载之后我重命名了pg_prewarm)
# make -C contrib/pg_prewarm
# make -C contrib/pg_prewarm instal

3. 上面的命令会在$PGPATH/contrib/extension目录里创建文件。现在准备添加contrib模块了。

postgres=# create EXTENSION pg_prewarm;
CREATE EXTENSION
postgres=# \dx
                          List of installed extensions
      Name      | Version |   Schema   |              Description
----------------+---------+------------+----------------------------------------
 pg_buffercache | 1.0     | public     | examine the shared buffer cache
 pg_prewarm     | 1.0     | public     | prewarm relation data
 pgfincore      | 1.1.1   | public     | examine and manage the os buffer cache
 plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)
文档:
/usr/local/src/postgres-9.1.3/doc/src/sqml
[root@localhost sgml]# ll pgpre*
-rw-r--r-- 1 root root 2481 Apr 10 10:15 pgprewarm.sgml

dstat

它是vmstat, netstat, top等工具的组合到一起成了一个"dstat"linux命令。当数据库表现不正常时,从操作系统级别了解语句,我们会打开好几个终端来显示进程,内存,磁盘读写,网络信息,但是在这些窗口切换是十分痛苦的。所以,dstat有几个选项来帮助显示所有的命令在一个输出窗口中。

安装:
Dstat下载连接:(RHEL 6)

wget http://pkgs.repoforge.org/dstat/dstat-0.7.2-1.el6.rfx.noarch.rpm

或者

yum install dstat
文档:http://dag.wieers.com/home-made/dstat/

Linux ftools

在被设计在现代的linux操作系统中用来调用像mincore, fallocate, fadivse等的工具。Ftools它会帮助你找出那些文件在操作系统缓存中。使用perl/python脚本,你可以获得操作系统页面缓存信息在pg_class.relfilenode对象中。pg_fincore就是建立在它之上的。你可以使用pgfincore或者ftools脚本。
安装:
从这个连接下载.tar.gz
https://github.com/david415/python-ftools

cd python-ftools
python setup.py build
export PYTHONPATH=build/lib.linux-x86_64-2.5
python setup.py install

注意:你应该在安装python-ftools之前就已经安装好了。

现在,我使用例子来检验这些工具。在这个例子中,有一个表,它有一个索引和序列(sequence),大小为100多MB。

postgres=# \d+ cache
Table "public.cache"
Column |  Type   |                Modifiers                | Storage  | Description
--------+---------+-----------------------------------------+----------+-------------
name   | text    |                                         | extended |
code   | integer |                                         | plain    |
id     | integer | default nextval('icache_seq'::regclass) | plain    |
Indexes:
"icache" btree (code)
Has OIDs: no
使用查询来了解这表,序列和它的索引所占的大小.
postgres=# SELECT c.relname AS object_name,
CASE when c.relkind='r' then 'table'
when c.relkind='i' then 'index'
when c.relkind='S' then 'sequence'
else 'others'
END AS type,pg_relation_size(c.relname::text) AS size, pg_size_pretty(pg_relation_size(c.relname::text)) AS pretty_size
FROM pg_class c
JOIN pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE (c.relkind = ANY (ARRAY['r'::"char", 'i'::"char", 'S'::"char",''::"char"])) AND n.nspname = 'public';

object_name |   type   |   size   | pretty_size
-------------+----------+----------+-------------
icache_seq  | sequence |     8192 | 8192 bytes
cache       | table    | 83492864 | 80 MB
icache      | index    | 35962880 | 34 MB
(3 rows)

Total object size 'cache'

postgres=# select pg_size_pretty(pg_total_relation_size('cache'));
pg_size_pretty
----------------
114 MB
(1 row)
我已经写了联合pgfincore和pg_buffercache的一个简单查询来获得PG缓冲和操作系统页面缓存的信息。我会在这个查询贯穿整个例子,仅仅复制这个查询就好了。
select rpad(c.relname,30,' ') as Object_Name,
case when c.relkind='r' then 'Table' when c.relkind='i' then 'Index' else 'Other' end as Object_Type, 
rpad(count(*)::text,5,' ') as "PG_Buffer_Cache_usage(8KB)",
split_part(pgfincore(c.relname::text)::text,','::text,5) as "OS_Cache_usage(4KB)"
from pg_class c inner join pg_buffercache b on b.relfilenode=c.relfilenode
     inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database() and c.relnamespace=(select oid from pg_namespace where nspname='public'))
group by c.relname,c.relkind
order by "PG_Buffer_Cache_usage(8KB)"
desc limit 10;

object_name | object_type | PG_Buffer_Cache_usage(8KB) | OS_Cache_usage(4KB)
-------------+-------------+----------------------------+---------------------
(0 rows)

注意: 我已经刷新PG缓冲和操作系统页面缓存。所以,缓存/缓冲没有任何数据.

使用pg_prewarm预加载关系/索引:

在之前,我刷新整个"Cache"表的顺序遍历查询和之前预加载关系/索引的时间。
postgres=# explain analyze select * from cache ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on cache  (cost=0.00..26192.00 rows=1600000 width=19) (actual time=0.033..354.691 rows=1600000 loops=1)
Total runtime: 427.769 ms
(2 rows)
现在让我们使用pg_prewarm来预加载关系/索引/序列,然后查看查询计划。
postgres=# select pg_prewarm('cache','main','buffer',null,null);
pg_prewarm
------------
10192
(1 row)
postgres=# select pg_prewarm('icache','main','buffer',null,null);
pg_prewarm
------------
4390
(1 row)

Output of combined buffers:
object_name | object_type | PG_Buffer_Cache_usage(8KB) | OS_Cache_usage(4KB)
-------------+-------------+----------------------------+---------------------
icache      | Index       | 4390                       | 8780
cache       | Table       | 10192                      | 20384
(2 rows)

pgfincore 输出: 

postgres=# select relname,split_part(pgfincore(c.relname::text)::text,','::text,5) as "In_OS_Cache" from pg_class c where relname ilike '%cache%';
relname   | In_OS_Cache
------------+-------------
icache_seq | 2
cache      | 20384
icache     | 8780
(3 rows)

or for each object.

postgres=# select * from pgfincore('cache');
relpath      | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit
------------------+---------+--------------+--------------+-----------+-----------+---------------+---------
base/12780/16790 |       0 |         4096 |        20384 |     20384 |         1 |        316451 |
(1 row)
To retrieve similar information using python-ftools script you need to know objects relfilenode number, check below.
使用python-ftools脚本也会获得相似的信息,你应该知道relfilenode对象的编号。像下面一样查看。
postgres=# select relfilenode,relname from pg_class where relname ilike '%cache%';
relfilenode |    relname
-------------+----------------
16787 | icache_seq       /// 你执行的序列
16790 | cache            /// 表
16796 | icache           /// 索引
(3 rows)
使用python-ftools脚本


有趣吧....!!!!.

现在比较一下预加载表到缓冲之后的explain plan

postgres=# explain analyze select * from cache ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on cache  (cost=0.00..26192.00 rows=1600000 width=19) (actual time=0.016..141.804 rows=1600000 loops=1)
Total runtime: 215.100 ms
(2 rows)

怎样刷新在操作系统缓存中的关系/索引 ?

使用pgfadvise,你可以预加载或者刷新关系到操作系统缓存。获得更多信息,在终端中使用\df pgfadvise*来显示所有与pgfadvise相关的函数。下面是刷新操作系统缓存的一个例子。
postgres=# select * from pgfadvise_dontneed('cache');
relpath      | os_page_size | rel_os_pages | os_pages_free
------------------+--------------+--------------+---------------
base/12780/16790 |         4096 |        20384 |        178145
(1 row)
postgres=# select * from pgfadvise_dontneed('icache');
relpath      | os_page_size | rel_os_pages | os_pages_free
------------------+--------------+--------------+---------------
base/12780/16796 |         4096 |         8780 |        187166
(1 row)
postgres=# select relname,split_part(pgfincore(c.relname::text)::text,','::text,5) as "In_OS_Cache" from pg_class c where relname ilike '%cache%';
relname   | In_OS_Cache
------------+-------------
icache_seq | 0
cache      | 0
icache     | 0
(3 rows)
通过使用dstat,这些信息显示在一个窗口中,如你可以查看读写比例。更多信息使用 dstat --list
dstat -s --top-io --top-bio --top-mem

使用pg_prewarm的range功能预加载随机块.

假设,因为一些原因,你想刷新服务器,但是有一个很大的表它在缓存中,而且执行的很好。在刷新中,缓存 中就没有了你的表了,要回到刷新前的状态,你不得不知道多少表块在缓存中以及使用pg_prewarm的range选项来预加载它。

我通过pg_buffercache来查询已创建的表,然后我使用pg_prewarm的range选项来发送块。通过这样,共享缓存就像先前加载到缓存中一样回来了。请看例子

select c.relname,count(*) as buffers from pg_class c 
inner join pg_buffercache b on b.relfilenode=c.relfilenode and c.relname ilike '%cache%' 
inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database()) 
group by c.relname 
order by buffers desc;
relname | buffers
---------+---------
cache   |   10192
icache  |    4390
(2 rows)
Note: These are the blocks in buffer.

postgres=# create table blocks_in_buff (relation, fork, block) as select c.oid::regclass::text, case b.relforknumber when 0 then 'main' when 1 then 'fsm' when 2 then 'vm' end, b.relblocknumber from pg_buffercache b, pg_class c, pg_database d where b.relfilenode = c.relfilenode and b.reldatabase = d.oid and d.datname = current_database() and b.relforknumber in (0, 1, 2);
SELECT 14716
刷新服务器以及从"blocks_in_buff"表中查看预加载和表相关的随机块的缓存。
postgres=# select sum(pg_prewarm(relation, fork, 'buffer', block, block)) from blocks_in_buff;
sum
-------
14716
(1 row)

postgres=# select c.relname,count(*) as buffers from pg_class c
inner join pg_buffercache b on b.relfilenode=c.relfilenode and c.relname ilike '%cache%'
inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database())
group by c.relname
order by buffers desc;
relname | buffers
---------+---------
cache   |   10192
icache  |    4390
(2 rows)
 看,我的共享缓存又回来工作了。

欢呼吧... ! 精彩继续。

via
caching in PostgreSQL

© 著作权归作者所有

LianyouCQ

LianyouCQ

粉丝 58
博文 92
码字总数 51766
作品 0
沙坪坝
程序员
私信 提问
加载中

评论(1)

大海中的水滴
大海中的水滴
你好,现在我的数据在缓存中了,现在问题是用什么sql语句能从缓存中提取数据呢,现在表的数据已经放在缓存里面了,像使用‘’SELECT * FROM 快运企业客户数据; ‘’这样的语句就无法访问缓存中该表的数据。因此速度并没有提升。
PostgreSQL DBA快速入门(四) - 体系架构

PostgreSQL在开源关系型数据库市场是最先进的数据库。他的第一个版本在1989年发布,从那时开始,他得到了很多扩展。根据db-enginers上的排名情况,PostgreSQL目前在数据库领域排名第四。 本篇...

闻术苑
2018/08/02
408
0
Oracle On the PL/SQL Function Result Cache

标签 PostgreSQL , Oracle , 函数结果缓存 , 函数三态 , immutable , stable , volatile 背景 Oracle 11g 支持的一个新特性,在创建PL/SQL函数时,可以指定这个存储过程是否需要对结果进行缓...

德哥
2019/04/27
0
0
Postgresql 数据库控制解析(一)

前面我们通过“探索postgresql数据库(一)”和“探索postgresql数据库(二)”基本了解了postgresql数据库。接下来本文开始介绍下数据库控制(server control)。。 一、手动开启数据库(S...

章郎虫
2013/10/23
245
0
PostgreSQL GPU 加速(HeteroDB pg_strom) (GPU计算, GPU-DIO-Nvme SSD, 列存, GPU内存缓存)

标签 PostgreSQL , GPU , heteroDB , pg_strom , CUDA , nvidia , DIO , Nvme , SSD , 列存 , GPU内存缓存 背景 HeteroDB pg_strom是PG的一个GPU加速插件,目前已支持到PG 11的版本。 在这些...

德哥
2018/07/28
0
0
php 通过 pdo 长连接 链接pgsql 但是pgsql连接数会不断增加 而不是复用一个链接

现在问题是,用pdo的做的长连接链接的pgsql,但是每次访问页面pgsql的链接数都会不断增加,而不是去用缓存好的连接,并且当一个用户操作时会涨到11个左右 就不会继续增加,然后再多一个用户访...

__都付笑谈中
2016/07/15
1.2K
4

没有更多内容

加载失败,请刷新页面

加载更多

检查字符串是否不为空且不为空

如何检查字符串是否不为null也不为空? public void doStuff(String str){ if (str != null && str != "**here I want to check the 'str' is empty or not**") { /* handl......

javail
2分钟前
7
0
No module named 'apscheduler.schedulers'

关于 ModuleNotFoundError: No module named 'apscheduler.schedulers' 和 ModuleNotFoundError: No module named 'apscheduler' 的问题,网上有不少解决。大多数都是因为,文件起名与模块名......

開援带碼
23分钟前
27
0
如何查看Android的系统版本?

有谁知道我怎么能检查系统版本(例如1.0 , 2.2 ,等)编程? #1楼 我无法对答案发表评论,但是Kaushik的答案存在一个严重错误:SDK_INT与系统版本不同,但实际上是指API级别。 if(Build.VER...

技术盛宴
49分钟前
35
0
引入AI变量,精准农业正在加速豹变?

  不久前,“江苏省脱贫率达 99.99%,尚未脱贫人数仅剩 6 户、17 人”这样一则新闻刷屏。“把这 17 个人迁出,江苏就全省脱贫”、“最后这 6 户人家拖了后腿”,在网民们的调侃和质疑声背后...

水果黄瓜
52分钟前
41
0
Elasticsearch系列---结构化搜索

概要 结构化搜索针对日期、时间、数字等结构化数据的搜索,它们有自己的格式,我们可以对它们进行范围,比较大小等逻辑操作,这些逻辑操作得到的结果非黑即白,要么符合条件在结果集里,要么...

清茶豆奶
今天
69
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部