最近从师傅那学习一种非常实用的数据库缓存机制,能够将数据库里表缓存到OS的cache里,注意不是数据库的BUFFER,是操作系统层面的cache, 众所周知,数据库从内存读要比从硬盘读数据快很多,虽然以前学sybase时有也有将少量经常查询的表放到数据库 ache里,Oracle里也有个Keep用来CACHE经常访问的小表,但今天要讲的机制不一样了,它是将数据库表,索引CACHE到OS层面的缓存里,只要内存足够大,可以将需要的数据都CACHE到OS 内存中,这极到的提高了应用的处理速度;采用这种方法测试了一套应用系统,速度要好快几十倍。这个工具叫 pgfincore,下面简单的介绍下它的用法。
Pgfincore安装
1.1 解压介质 (postgres), 并COPY 到 ostgresql的源代码目录/contrib1
2
3cd postgresql的源代码目录/contrib/pgfincore
1.2 instll (by root)1
2
3cd /home/postgres
source .bash_profile
USE_PGXS=1 make install
1.3 导入pgfincore.sql文件1
2
3
4su - 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
5mydb=> 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
4cedric=
block_size | block_free
------------+------------
4096 | 417534
2.3 pgfadv_willneed
将数据库对象(表,索引)载入OS CACHE。1
2
3
4
5cedric=# 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 CACHE1
2
3
4
5cedric=# 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
23mydb=> 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
12mydb=> 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 | [postgres@pg1](mailto:postgres@pg1) pgfincore]$ USE_PGXS=1 make clean |
解决方法
网上查了下,是因为时间问题,需要修改文操作系统时间和文件创建时间;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 {}