Pgfincore: cache data to OS cache

最近从师傅那学习一种非常实用的数据库缓存机制,能够将数据库里表缓存到OS的cache里,注意不是数据库的BUFFER,是操作系统层面的cache, 众所周知,数据库从内存读要比从硬盘读数据快很多,虽然以前学sybase时有也有将少量经常查询的表放到数据库 ache里,Oracle里也有个Keep用来CACHE经常访问的小表,但今天要讲的机制不一样了,它是将数据库表,索引CACHE到OS层面的缓存里,只要内存足够大,可以将需要的数据都CACHE到OS 内存中,这极到的提高了应用的处理速度;采用这种方法测试了一套应用系统,速度要好快几十倍。这个工具叫 pgfincore,下面简单的介绍下它的用法。

Pgfincore安装

1.1 解压介质 (postgres), 并COPY 到 ostgresql的源代码目录/contrib

1
2
3
cd postgresql的源代码目录/contrib/pgfincore  
$USE_PGXS=1 make clean
$USE_PGXS=1 make

1.2 instll (by root)

1
2
3
#cd /home/postgres  
#source .bash_profile
#USE_PGXS=1 make install

1.3 导入pgfincore.sql文件

1
2
3
4
su - postgres  
cd postgresql的安装目录/share/postgresql/contrib
chown -R postgres:postgres /database
psql mydb -f pgfincore.sql

主要函数介绍

  • pgsysconf –查看操作系统CACHE情况
  • pgmincore –查看对象(表,索引)CACHE情况
  • pgfadv_willneed –将数据库对象(表,索引)载入OS CACHE
  • pgfadv_dontneed –将数据库对象(表,索引)刷出OS CACHE
  • pgfadv_normal –This function set //NORMAL// flag on the current relation. ()
  • pgmincore_snapshot –保存对象块照情况
  • pgfadv_willneed_snapshot –还原对象快照情况

函数返回的列

relpath : the relation path
block_size : the size of one block disk?
block_disk : the total number of file system blocks of the relation
block_mem : the total number of file system blocks of the relation in buffer cache. (not the shared buffers from PostgreSQL but the OS cache)
group_mem : the number of groups of adjacent block_mem

2.1 pgmincore
查询表CACHE情况

1
2
3
4
5
mydb=> select * from pgmincore ('test_3');  
relpath | block_size | block_disk | block_mem | group_mem
------------------+------------+------------+-----------+-----------
base/16385/16394 | 4096 | 2 | 0 | 0
(1 row)

2.2 pgsysconf

查询当前操作系统的块大小,剩余多少可用的CACHE(块)。

1
2
3
4
cedric=# select * from pgsysconf();  
block_size | block_free
------------+------------
4096 | 417534

2.3 pgfadv_willneed
将数据库对象(表,索引)载入OS CACHE。

1
2
3
4
5
cedric=# select * from pgfadv_willneed('pgbench_accounts');  
relpath | block_size | block_disk | block_free
--------------------+------------+------------+------------
base/16384/16603 | 4096 | 262144 | 3744
base/16384/16603.1 | 4096 | 65726 | 4236

2.4 pgfadv_dontneed
将数据库对象(表,索引)刷出OS CACHE

1
2
3
4
5
cedric=# select * from pgfadv_dontneed('pgbench_accounts');  
relpath | block_size | block_disk | block_free
--------------------+------------+------------+------------
base/16384/24598 | 4096 | 262144 | 178743
base/16384/24598.1 | 4096 | 55318 | 234078

2.5 pgfadv_normal
使用普通内存方式,再pgfadv_dontneed方式刷出对象后,建议执行这个函数,将内存方式改为普通方式;

1
select * from pgfadv_normal('test_1');

2.6 pgmincore_snapshot and pgfadv_willneed_snapshot
当数据库刚启动的时候,查询会比较慢,应为PostgreSQL和OS级别的缓存都还没有缓存需要的BLOCK。我们可以用pgmincore_snapshot保存一个快照,在数据库起来时用 pgfadv_willneed_snapshot还原。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Snapshot  
cedric=# select * from pgmincore_snapshot('pgbench_accounts');
relpath | block_size | block_disk | block_mem | group_mem
--------------------+------------+------------+-----------+-----------
base/16384/24598 | 4096 | 262144 | 131745 | 1
base/16384/24598.1 | 4096 | 55318 | 55318 | 1
-- Restore
cedric=# select * from pgfadv_willneed_snapshot('pgbench_accounts');
relpath | block_size | block_disk | block_free
--------------------+------------+------------+------------
base/16384/24598 | 4096 | 262144 | 105335
base/16384/24598.1 | 4096 | 55318 | 50217
(2 rows)

测试下2.6步骤中的函数,先查询表OS CACHE情况, block_mem=0,表明没有缓存到OS cache中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mydb=> select * from pgmincore ('test_3');  
relpath | block_size | block_disk | block_mem | group_mem
------------------+------------+------------+-----------+-----------
base/16385/16394 | 4096 | 2 | 0 | 0
(1 row)
--保存表CACHE 情况快照
mydb=> select * from pgmincore_snapshot('test_3');
relpath | block_size | block_disk | block_mem | group_mem
------------------+------------+------------+-----------+-----------
base/16385/16394 | 4096 | 2 | 0 | 0
(1 row)
--查询表
mydb=> select * From test_3;
id
----
1
(1 row)
--再次观察CACHE情况,发现block_mem=2,表明表test_3 已经CACHE了
mydb=> select * from pgmincore ('test_3');
relpath | block_size | block_disk | block_mem | group_mem
------------------+------------+------------+-----------+-----------
base/16385/16394 | 4096 | 2 | 2 | 1
(1 row)

说明: PG数据库客户端执行查询后,数据除了会缓存到数据库CACHE 中还会缓存到OS CACHE中,不知道ORACLE是否会缓存到OS CACHE中,知道的大侠可以告诉我下,谢谢。

还原表TEST_3的CACHE情况到做快照时

1
2
3
4
5
6
7
8
9
10
11
12
mydb=> select * from pgfadv_willneed_snapshot('test_3');  
relpath | block_size | block_disk | block_free
------------------+------------+------------+------------
base/16385/16394 | 4096 | 2 | 909
(1 row)

--再次观察CACHE情况,和做快照时一样了
mydb=> select * from pgmincore ('test_3');
relpath | block_size | block_disk | block_mem | group_mem
------------------+------------+------------+-----------+-----------
base/16385/16394 | 4096 | 2 | 0 | 0
(1 row)

安装过程中遇到的错误

1
2
3
4
5
[postgres@pg1](mailto:postgres@pg1) pgfincore]$ USE_PGXS=1 make clean  
make: Warning: File `/database/pgdata/tbs1/pg_root/lib/postgresql/pgxs/src/makefiles/../../src/Makefile.global' has modification time 1.4e+08 s in the future
rm -f pgfincore.so pgfincore.o
rm -f pgfincore.sql uninstall_pgfincore.sql
make: 警告:检测到时钟错误。您的创建可能是不完整的。

解决方法

网上查了下,是因为时间问题,需要修改文操作系统时间和文件创建时间;

1
2
3
#date -s "2010-08-05 14:37:22"  
$cd /database/pgdata/tbs1/pg_root/lib/postgresql/pgxs/src/makefiles/../../src
find . -type f -exec touch {}

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%