PostgreSQL缓存

最近工作用到了PostgreSQL，主要是我们公司的产品需要访问数据库中的数据，但是一个表中160M的数据返回很慢，用户体验十分的差，虽然跟用户的带宽有关系，但是大佬发话，带宽有问题，那你就用其他方式给我解决这个问题，没办法，只能硬着头皮搞PostgreSQL了。

首先想到的就是加缓存
Postgresql本身自带着缓存，也就是说当一个数据被频繁的访问时，这个数据就会添加到缓存中
而我们也可以通过插件的形式将数据添加到缓存中

1）shared_buffers

PostgreSQL既使用自身的缓冲区，也使用内核缓冲IO。这意味着数据会在内存中存储两次，首先是存入PostgreSQL缓冲区，然后是内核缓冲区。这被称为双重缓冲区处理。对大多数操作系统来说，这个参数是最有效的用于调优的参数。此参数的作用是设置PostgreSQL中用于缓存的专用内存量。
shared_buffers的默认值设置得非常低，因为某些机器和操作系统不支持使用更高的值。但在大多数现代设备中，通常需要增大此参数的值才能获得最佳性能。
设置共享内存的大小 一般是设置为总内存的1/4到1/3
去postgresql.conf 中去设置：
注意： 单位是GB，别光写个G 。。。。。
还有，要重启数据库

可以到数据库中查看 shared_buffersde 大小
查看当前shared_buffers有两种方法

select name,setting,unit,current_setting(name) from pg_settings where name =‘shared_buffers’;

postgres=# show shared_buffers;

2）pg_buffercache 插件

查看缓存的方法：
1、导入pg_buffercache 插件

create extension pg_buffercache;

2、查看缓存

SELECT c.relname,count(*) AS buffers
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())
GROUP BY c.relname
ORDER BY 2 DESC LIMIT 10;

3） pg_prewarm

pg_prewarm模块可以方便的把相关的数据加载到系统的缓存或者是数据库的缓存中。在数据量大的情况下，内存中的数据可能会排出内存。

将数据预热处理
如果表没有在缓存中，那就需要手动添加：
查看缓存内的信息：
postgres=# select * from pg_buffercache where relfilenode=pg_relation_filenode(‘表名’);

打印的信息是不是有些乱
为了对表详细的信息更好的管理：
修改 \d+ pg_buffercache
这个表，用更人性化的方式去展示信息

create view pg_buffercache_v as
select bufferid,
(select c.relname from pg_class c where pg_relation_filenode(c.oid) = b.relfilenode) relname,
case relforknumber 
when 0 then 'main'
when 1 then 'fsm'
when 2 then 'vm'
end relfork,
relblocknumber,
isdirty,
usagecount
from pg_buffercache b 
where b.reldatabase in (0,(select oid from pg_database where datname=current_database()))
and b.usagecount is not null;

查看
select * from pg_buffercache_v;

bufferid : 缓存id编号
relname：表名
usagecount： 访问级数
isdirty： 脏页
relfork ： 在缓存中的缓存的种类 main表示缓冲关系表
fsm：空闲空间可映射
vm：可视化映射相关文件
relblocknumber： 缓冲块号

pg_prewarm 函数的使用语法：

pg_prewarm(
regclass,
mode text default ‘buffer’,
fork text default ‘main’,
first block int8 default null,
last_block int8 default null) RETURNS int8
)
第一个参数是预热的relation
第二个参数是要使用的预热方法
第三个参数是 relation fork 被预热
第四个参数是 预热的第一个块号
第五个参数是预热的最后一个块号
返回值是prewarm块的数量。

将表添加到缓存：

postgres=#select pg_prewarm(‘textdb’,‘main’,‘buffer’,null,null);

此时缓存中已经有了该表的信息，可以再查看一下缓存中的信息

4）pgfincore插件添加到缓存

简单介绍一下pgfincore,这是一个工具，它可以把数据永久加载到OS的CACHE中（注：不是数据库BUFFER）。在内存足够的情况下，被加载的数据不会被移出CACHE。
安装：
apt-cache search pgfincore 查看能够安装的pgfincore版本
apt-get install postgresql-9.5-pgfincore 安装pgfincore

切换postgres用户，进入PostgreSQL

create extension pgfincore;

查看插件列表：

select * from pg_extension;

此时插件已经创建成功，查看插件中的函数：

    SELECT  proname, prosrc
	FROM    pg_catalog.pg_namespace n
	JOIN    pg_catalog.pg_proc p
	ON      pronamespace = n.oid
	WHERE   nspname = 'public';

至于各个函数有什么作用，大家可以看看这篇文章：https://my.oschina.net/Suregogo/blog/546516

添加到内存中：
select * from pgfadvise_willneed(‘表名’);
查看状态：
select relname,split_part(pgfincore(c.relname::text)::text,’,’::text,5) as “In_OS_Cache” from pg_class c where relname ilike ‘表名’;
在内存中删除
select * from pgfadvise_dontneed(‘表名’);

以上就是缓存的总结： 下面是测试的小技巧

计算出每条sql语句的运行时间
\timing
查看能提供的postgresql的版本
apt-cache search postgresql
清空系统缓存：
step 1: 以最高权限同步所有的缓存到磁盘中
　　　　　　sync
　　　　　　sync
　step2: 执行以下命令指示内核对内存进行调整
　　　　　　echo 3 > /proc/sys/vm/drop_caches
　　　　　　解析:3表示清空所有缓存（pagecache、dentries 和 inodes）
　　　　　　　　 2表示清空 dentries 和 inodes
　　　　　　　　 1表示清空 pagecache
查看当前数据库连接人数：
select count(*), usename from pg_stat_activity group by usename;
查看所有表的大小并排序：
SELECT table_schema || ‘.’ || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size(’"’ || table_schema || ‘"."’ || table_name || ‘"’)) AS size FROM information_schema.tables ORDER BY pg_total_relation_size(’"’ || table_schema || ‘"."’ || table_name || ‘"’) DESC limit 20;
计算表在磁盘上占了多少页
select pg_total_relation_size(‘表名’)/8192;
查看缓存的页数
select * from pg_buffercache_v where relname =‘表名’;
checkpoint 将缓存写入磁盘