PostgreSQL缓存

最新推荐文章于 2023-04-11 23:45:00 发布

liguangxian2018

最新推荐文章于 2023-04-11 23:45:00 发布

阅读量1.2k

点赞数

分类专栏： postgresql 文章标签： postgresql 缓存 cache

postgresql 专栏收录该内容

41 篇文章 12 订阅

订阅专栏

目录[-]

pg_buffercache pgfincore pg_prewarm dstat Linux ftools 使用pg_prewarm预加载关系/索引: pgfincore 输出: 怎样刷新在操作系统缓存中的关系/索引 ? 使用pg_prewarm的range功能预加载随机块.

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

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

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

pg_buffercache

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

1	`postgres=#` `CREATE` `EXTENSION pg_buffercache`

pgfincore

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

安装：

下载最新版本：

 
    [cpp]  
    view plaincopy
http://pgfoundry.org/frs/download.php/3186/pgfincore-v1.1.1.tar.gz  

使用root用户：

1

2

3

4

5

6

 
         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，运行下面的命令：

1	`postgres=#` `CREATE` `EXTENSION pgfincore;`

pg_prewarm

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

PREFETCH: 异步获取数据块到操作系统缓存中，而不是PG缓冲(提示只是操作系统缓存)
READ：读取所有的数据块到虚拟缓存中，然后写到操作系统缓存中（提示只是操作系统缓存）
BUFFER：读取所有的或者一些数据块到数据库缓冲中。

安装：
我把pg_prewarm补丁加入到PG源码安装中，你需要调整你的每步安装。

解压PG源码路径： /usr/local/src/postgresql-9.1.3
PG安装路径： /usr/local/pgsql91
所有下载路径： /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源码目录，然后执行下面几步。

1

2

3

4

 
         # 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 
         )

文档：

1

2

3

 
         <span>/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</span>

dstat

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

安装：
Dstat下载连接：（RHEL 6）

 
    [cpp]  
    view plaincopy
wget http://pkgs.repoforge.org/dstat/dstat-0.7.2-1.el6.rfx.noarch.rpm  

或者

 
    [cpp]  
    view plaincopy
<span style="padding:0px; margin:0px">yum install dstat</span>  

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

Linux ftools

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

 
    [cpp]  
    view plaincopy
从这个连接下载.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。

 
    [cpp]  
    view plaincopy
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  

使用查询来了解这表，序列和它的索引所占的大小.

 
    [cpp]  
    view plaincopy
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缓冲和操作系统页面缓存的信息。我会在这个查询贯穿整个例子，仅仅复制这个查询就好了。

 
    [cpp]  
    view plaincopy
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"表的顺序遍历查询和之前预加载关系/索引的时间。

 
    [cpp]  
    view plaincopy
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来预加载关系/索引/序列，然后查看查询计划。

 
    [cpp]  
    view plaincopy
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 输出:

 
    [cpp]  
    view plaincopy
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对象的编号。像下面一样查看。

 
    [cpp]  
    view plaincopy
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

 
    [cpp]  
    view plaincopy
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相关的函数。下面是刷新操作系统缓存的一个例子。

 
    [cpp]  
    view plaincopy
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选项来发送块。通过这样，共享缓存就像先前加载到缓存中一样回来了。请看例子

 
    [cpp]  
    view plaincopy
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"表中查看预加载和表相关的随机块的缓存。

 
    [cpp]  
    view plaincopy
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)  

看，我的共享缓存又回来工作了。