时间：2021-07-01 10:21:17 帮助过：6人阅读
缓存.... !!, 它很难在一篇文章中解释清楚。但是我会努力分享我从Heikki, Robert Haas, Bruce Momjian那里学到的知识。在PostgreSQL里有两层：PG共享缓冲和操作系统页面缓存,任何读写都会通过操作系统缓存（迄今为止还没有其它途径）。Postgres把数据写在操作系统页面缓存，用户觉得数据好像回写到了磁盘，之后操作系统缓存才会写到对应的物理磁盘位置。PG共享缓冲无法控制系统页面缓存，甚至连系统缓存是什么都不知道。所以，Postgres DBA或者专家给出的大多数建议都是更快的磁盘读写或者更好的缓存。
PostgreSQL的缓存/缓冲和其它数据库十分相像并且十分复杂。因为我有Oracle和mindset背景，所以我使用怎么样/什么时候/什么/为什么等提问方式，关于数据库的缓冲缓存，固定的缓冲，刷新数据库缓存，以及预加载数据库等方面，我都是从这种方式获得答案的，然而这种方式有点与众不同。尽管我的问题很烦人，但是他们总是耐心的回答，使我明白扩展我的知识，反过来，你才能阅读这篇博文... :) ..
在一些学习上，我画了一幅Postgres中数据在内存和磁盘之间传递的，以及一些重要的工具和Robert Hass提供的新补丁(pg_prewarm).
它是一个contrib模块，它会告诉你什么是PostgreSQL缓存。像下面安装：
?| 1 |
postgres=# CREATE EXTENSION pg_buffercache
|
它有一个显示数据在操作系统页面缓存中信息的功能。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用户：
?| 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
|
| 1 |
postgres=# CREATE EXTENSION pgfincore;
|
预加载关系/索引到PG缓存中，在PostgreSQL中可能吗？当然可以了，感谢Robert Hass,他提交一些补丁到社区，期待它能够在PG 9.2或者PG 9.3中可行。然而，你可以使用这个补丁在PG 9.1做一些测试。
pg_prewarm
有三种模式：
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模块了。
?| 1 2 3 4 5 6 7 8 9 10 11 |
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>
|
它是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/
从这个连接下载.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缓冲和操作系统页面缓存。所以，缓存/缓冲没有任何数据.
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)
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.
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)
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
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
PostgreSQL缓存
标签：