重庆思庄Oracle、Redhat认证学习论坛

标题: 使用 pgfincore 插件把表添加到操作系统缓存 [打印本页]

作者: 郑全 时间: 2024-10-7 13:28
标题: 使用 pgfincore 插件把表添加到操作系统缓存
pgfincore,把数据永久加载到OS的CACHE中（不是数据库的buffer）.在内存足够的情况下，被加载的数据不会被移出CACHE。

1. 源码安装
pgfincore下载地址：https://github.com/klando/pgfincore

make
make install

2.创建扩展
create extension pgfincore;

select * from pg_extension;

3.看一下有哪些与此有关的函数
select proname,prosrc
from pg_catalog.pg_namespace n join pg_catalog.pg_proc p on pronamespace=n.oid
where nspname='public';

         proname          |                               prosrc
-----------------------------+------------------------------------------------------------------------
pg_buffercache_pages       | pg_buffercache_pages
pg_buffercache_summary    | pg_buffercache_summary
pg_buffercache_usage_counts | pg_buffercache_usage_counts
pgsysconf                | pgsysconf
pgsysconf_pretty          |                                                                      +
                           | select pg_size_pretty(os_page_size)                as os_page_size, +
                           |       pg_size_pretty(os_pages_free * os_page_size)  as os_pages_free,+
                           |       pg_size_pretty(os_total_pages * os_page_size) as os_total_pages+
                           | from pgsysconf()
pgfadvise                | pgfadvise
pgfadvise_willneed       | SELECT pgfadvise($1, 'main', 10)
pgfadvise_dontneed       | SELECT pgfadvise($1, 'main', 20)
pgfadvise_normal          | SELECT pgfadvise($1, 'main', 30)
pgfadvise_sequential       | SELECT pgfadvise($1, 'main', 40)
pgfadvise_random          | SELECT pgfadvise($1, 'main', 50)
pgfadvise_loader          | pgfadvise_loader
pgfadvise_loader          | SELECT pgfadvise_loader($1, 'main', $2, $3, $4, $5)
pgfincore                | pgfincore
pgfincore                | SELECT * from pgfincore($1, 'main', $2)
pgfincore                | SELECT * from pgfincore($1, 'main', false)
pgfincore_drawer          | pgfincore_drawer
pg_prewarm                | pg_prewarm
autoprewarm_start_worker | autoprewarm_start_worker
autoprewarm_dump_now       | autoprewarm_dump_now
(20 rows)

4. pgfincore的使用
4.1 把某个表添加到内存中
select * from pgfadvise_willneed('表名');
比如把 ORDERS表添加到缓存

select * from pgfadvise_willneed('orders');

4.2 在内存中删除
select * from pgfadvise_dontneed('表名');
   比如把ORDERS表从内存中删除
   select * from pgfadvise_dontneed('orders');

4.3 检查是否缓存
查看状态
select relname,split_part(pgfincore(c.relname::text)::text,','::text,5) as "in_os_cache"
from pg_class c
where relname like 'orders';

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)