文档章节

# postgresql-shared_buffers

o
 osc_fmg49rzg
发布于 2019/03/20 11:04
字数 2080
阅读 25
收藏 0

精选30+云产品,助力企业轻松上云!>>>


## 关于shared_buffers

### 什么是shred_buffer,我们为什么需要shared_buffers?

1.在数据库系统中,我们主要关注磁盘io,大多数oltp工作负载都是随机io,因此从磁盘获取非常慢。

2.为了解决这个问题,postgres将数据缓存在RAM中,以此来提高性能,即使ssd的情况下RAM也要快很多。

3.shared_buffers是一个8KB的数组,postgres在从磁盘中查询数据前,会先查找shared_buffers的页,如果命中,就直接返回,避免从磁盘查询。

### shared_buffers存储什么?

1.表数据

2.索引,索引也存储在8K块中。

3.执行计划,存储基于会话的执行计划,会话结束,缓存的计划也就被丢弃。

### 什么时候加载shared_buffers?

1.在访问数据时,数据会先加载到os缓存,然后再加载到shared_buffers,这个加载过程可能是一些查询,也可以使用pg_prewarm预热缓存。

2.当然也可能同时存在os和shared_buffers两份一样的缓存(双缓存)。

3.查找到的时候会先在shared_buffers查找是否有缓存,如果没有再到os缓存查找,最后再从磁盘获取。

4.os缓存使用简单的LRU(移除最近最久未使用的缓存),而数据库采用的优化的时钟扫描,即缓存使用频率高的会被保存,低的被移除。

### shared_buffers设置的合理范围

1.windows服务器有用范围是64MB到512MB,默认128MB

2.linux服务器建议设置为25%,亚马逊服务器设置为75%(避免双缓存,数据会存储在os和shared_buffers两份)

### os缓存的重要性

数据写入时,从内存到磁盘,这个页面就会被标记为脏页,一旦被标记为脏页,它就会被刷新到os缓存,然后写入磁盘。所以如果os高速缓存大小较小,则它不能重新排序写入并优化io,这对于繁重的写入来说非常致命,因此os的缓存大小也非常重要。给予shared_buffers太大或太小都会损害性能。

### 查看shared_buffers,os缓存

这里需要使用到两个插件,pg_bufferscache系统已经自带可以直接创建扩展,pgfincore需要安装详细的[步骤](http://fibrevillage.com/database/382-postgre9-4-database-buffer-cache-and-os-cache)

```sql
pg_buffered表占用缓存大小
pg_buffer_percent:表占用整个缓存的占比
percent_of_relation:缓存数据和该表数据占比
os_cache_mb:表占用os系统缓存大小
os_cache_percent_of_relation:os缓存和表占比
rel_size:整个表大小
pgbench=# select c.relname,pg_size_pretty(count(*) * 8192) as pg_buffered,
       round(100.0 * count(*) / (select setting from pg_settings  where name='shared_buffers')::integer,1) as pgbuffer_percent,
       round(100.0*count(*)*8192 / pg_table_size(c.oid),1) as percent_of_relation,
       (select round( sum(pages_mem) * 4 /1024,0 ) from pgfincore(c.relname::text) ) as os_cache_MB ,
         round(100 * (  select sum(pages_mem)*4096 from pgfincore(c.relname::text) )/ pg_table_size(c.oid),1) as os_cache_percent_of_relation,
         pg_size_pretty(pg_table_size(c.oid)) as rel_size
 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.oid,c.relname
 order by 3 desc limit 30;
        relname        | pg_buffered | pgbuffer_percent | percent_of_relation | os_cache_mb | os_cache_percent_of_relation | rel_size
-----------------------+-------------+------------------+---------------------+-------------+------------------------------+----------
 pgbench_accounts      | 471 MB      |              1.9 |                 7.3 |         495 |                          7.7 | 6416 MB
 pgbench_accounts_pkey | 139 MB      |              0.6 |                13.0 |         274 |                         25.6 | 1071 MB
 pgbench_history       | 2704 kB     |              0.0 |                86.9 |           3 |                         99.2 | 3112 kB
 pgbench_branches_pkey | 56 kB       |              0.0 |               100.0 |           0 |                        100.0 | 56 kB
 pgbench_tellers_pkey  | 240 kB      |              0.0 |               100.0 |           0 |                        100.0 | 240 kB
 pgbench_branches      | 2968 kB     |              0.0 |                70.7 |           4 |                         99.2 | 4200 kB
 pgbench_tellers       | 608 kB      |              0.0 |               100.0 |           1 |                         94.7 | 608 kB
(7 rows)

--表缓存预热
pgbench=# select pg_prewarm('pgbench_accounts', 'buffer', 'main');
 pg_prewarm
------------
     820956
(1 row)
--索引预热:
pgbench=#  select pg_prewarm('pgbench_accounts_pkey', 'buffer', 'main');
 pg_prewarm
------------
     137099
(1 row)

--预热后查看缓存
pgbench=# select c.relname,pg_size_pretty(count(*) * 8192) as pg_buffered,
       round(100.0 * count(*) / (select setting from pg_settings  where name='shared_buffers')::integer,1) as pgbuffer_percent,
       round(100.0*count(*)*8192 / pg_table_size(c.oid),1) as percent_of_relation,
       (select round( sum(pages_mem) * 4 /1024,0 ) from pgfincore(c.relname::text) ) as os_cache_MB ,
         round(100 * (  select sum(pages_mem)*4096 from pgfincore(c.relname::text) )/ pg_table_size(c.oid),1) as os_cache_percent_of_relation,
         pg_size_pretty(pg_table_size(c.oid)) as rel_size
 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.oid,c.relname
 order by 3 desc limit 30;
        relname        | pg_buffered | pgbuffer_percent | percent_of_relation | os_cache_mb | os_cache_percent_of_relation | rel_size
-----------------------+-------------+------------------+---------------------+-------------+------------------------------+----------
 pgbench_accounts      | 6414 MB     |             26.1 |               100.0 |        6414 |                        100.0 | 6416 MB
 pgbench_accounts_pkey | 139 MB      |              0.6 |                13.0 |         274 |                         25.6 | 1071 MB
 pgbench_history       | 2704 kB     |              0.0 |                86.9 |           3 |                         99.2 | 3112 kB
 pgbench_branches_pkey | 56 kB       |              0.0 |               100.0 |           0 |                        100.0 | 56 kB
 pgbench_tellers_pkey  | 240 kB      |              0.0 |               100.0 |           0 |                        100.0 | 240 kB
 pgbench_branches      | 2968 kB     |              0.0 |                70.7 |           4 |                         99.2 | 4200 kB
 pgbench_tellers       | 608 kB      |              0.0 |               100.0 |           1 |                         94.7 | 608 kB
(7 rows)

可以看到将数据加载至shared_buffers,并且os也缓存了一份。正常情况os不应该缓存这么多的数据。
```

## 如何设定shared_buffers?

使用pg_buffercache可查看缓存使用情况,以及命中次数和脏块

```sql
--1.缓存命中数
pgbench=# select usagecount,count(*),isdirty from pg_buffercache group by isdirty, usagecount order by isdirty, usagecount ;
 usagecount |  count  | isdirty
------------+---------+---------
          1 |    6651 | f
          2 |  762250 | f
          3 |   54684 | f
          4 |   12630 | f
          5 |    3940 | f
            | 2305573 |
(6 rows)
--2.数据在缓存中占比
pgbench=# SELECT                                                        
c.relname,pg_size_pretty(count(*) * 8192) as buffered,
round(100.0 * count(*) /(SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1)AS buffers_percent,
round(100.0 * count(*) * 8192 /pg_relation_size(c.oid),1)AS percent_of_relation
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.oid,c.relname
ORDER BY 3 DESC LIMIT 10;
        relname        |  buffered  | buffers_percent | percent_of_relation
-----------------------+------------+-----------------+---------------------
 pgbench_accounts      | 6414 MB    |            26.1 |               100.0
 pgbench_accounts_pkey | 1071 MB    |             4.4 |               100.0
 pg_amop               | 56 kB      |             0.0 |                87.5
 pg_cast               | 16 kB      |             0.0 |               100.0
 pg_constraint         | 8192 bytes |             0.0 |               100.0
 pg_index              | 32 kB      |             0.0 |               100.0
 pg_opclass            | 16 kB      |             0.0 |                66.7
 pg_namespace          | 8192 bytes |             0.0 |               100.0
 pg_operator           | 120 kB     |             0.0 |               100.0
 pg_amproc             | 40 kB      |             0.0 |               100.0
(10 rows)

缓存中存储了完整的表,和索引,占总缓存的30%,占比很低缓存剩余很多。
```

1.如果大量的usagecount都是4或者5,那表明shared_buffers不够,应该扩大shared_buffers;

2.如果大量的usagecount都是0或者1,那表明shared_buffers过大,应该减小shared_buffers;

每当共享内存中使用一个块时,他就会增加一次时钟扫描算法,范围从1-5。4和5标识极高的使用数据块,高使用可能会保留在shared_buffers中(有空间),如果需要更高使用率的空间,则低使用率的块将被移除,一般简单的插入或者更新会将使用次数设置为1。

缓存占比低。可以确定的是如果我们的数据集非常小,那么设置较大的shared_buffers,没什么区别。

## pgbench性能测试(shared_buffers 128MB,4GB,8GB,24GB)

PostgreSQL默认测试脚本,含UPDATE、INSERT还有SELECT等操作。通过修改shared_buffers大小来测试tps。

数据库版本:PostgreSQL 10.4 (ArteryBase 5.0.0, Thunisoft)

操作系统配置:CentOS Linux release 7 ,32GB内存,8 cpu

测试参数:初始化5000w数据:pgbench  -i   -s 500  -h  localhost -U sa   -d  pgbench

测试方法:pgbench  -c 500 -t 20 -n  -r  pgbench 模拟500客户端,每个客户端20个事务,每种配置参数执行三次,记录tps值。

数据库物理大小:数据库总大小7503 MB,其中表总大小pgbench_accounts:7487 MB,索引pgbench_accounts_pkey :1071 MB

测试脚本:

```sql
 - statement latencies in milliseconds:
         0.002  \set aid random(1, 100000 * :scale)
         0.001  \set bid random(1, 1 * :scale)
         0.001  \set tid random(1, 10 * :scale)
         0.001  \set delta random(-5000, 5000)
         9.478  BEGIN;
        14.575  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         6.758  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
       130.573  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
       786.933  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         5.355  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
      1242.835  END;
```



未预热缓存测试结果:

```sql
       序号                     |  第一次  | 第二次  | 第三次 | 平均值(tps)
-----------------------------+---------+---------+---------+---------
 shared_buffers=128MB(默认)| 249      | 126     |  145  |  173            
 shared_buffers=4GB            | 357         | 357     |  373  |  362           
 shared_buffers=8GB         | 362      | 363     |  415  |  380              
 shared_buffers=24GB          | 378      | 368     |  397  |  381            
```

shared_buffers设置为8GB(25%)和设置为24GB(75%)差别不大。

预热缓存测试结果:

```sql
       序号                     |  第一次  | 第二次  | 第三次 | 平均值(tps)
-----------------------------+---------+---------+---------+---------
 shared_buffers=128MB(默认)| 211      | 194     |  207   |  204            
 shared_buffers=4GB            | 1225     | 1288    |  1321  |  1278           
 shared_buffers=8GB         | 1176     | 1291    |  1144  |  1203              
 shared_buffers=24GB          | 1285     | 1250    |  1309  |  1281            
```

当shared_buffers=4GB时,数据6GB不能完全装下,所以优先预热索引,将索引加载到缓存,然后在加载数据。可以看到最终shared_buffers=4GB的tps和8GB,24GB表现差别不大。

## 内存结构

1.本地内存:work_mem,maintenance_work_mem,temp_buffer,进程分配

2.共享内存:shared_buffers,wal buffers,commitLog buffer

本地内存*max_connections+共享内存+服务器使用内存<=总内存

## 小结

1.大多数情况设置shared_buffers为内存的25%,当然为了最优可以根据命中,以及缓存占比调整。

2.设置shared_buffers为75%和25%相差不大,也和数据量一共只有7G+有关系。但是os系统缓存同样重要,而设置为75%,可能会超过总内存。

3.设置所有的缓存需要注意不要超过总内存大小。

4.在预热数据的过程中可以考虑先做索引的预热,因为要做索引的情况加载索引会比较慢。


o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
Understanding the PostgreSQL Architecture

PostgreSQL is probably the most advanced database in the open source relational database market. It was first released in 1989, and since then, there have been a lot of enhancem......

易野
02/06
18
0
数据库监控指标操作手册

数据库版本:9.3.1(不同版本数据库相关表列名可能略有不同) 数据库状态信息 数据库状态信息主要体现数据库的当前状态 1.目前客户端的连接数 postgres=# SELECT count(*) FROM pg_stat_acti...

一位不著名的普通群众
2018/08/20
0
0
数据库体系结构-进程和内存结构(Process and Memory Architecture)

1数据库体系结构-进程和内存结构(Process and Memory Architecture) PostgreSQL是一个client/server架构rdbms,一个服务器上运行多个进程。 1、进程结构 --pg的主进程,也是父进程,后端进...

Chrisworld
04/26
15
0
postgresql-shared_buffers调整

shared_buffers大小调整: http://www.rummandba.com/2011/02/sizing-sharedbuffer-of-postgresql.html SELECT usagecount,count(),isdirty, round((count()/ max(total_cache.cnt......

osc_62egr8or
2019/01/03
3
0
postgres的备机同步是占用很大的内存,导致查询非常慢,怎么解决?

postgres@hadoop3:/opt/postgres/data$ psql -version psql (9.4.1) postgres.cong主要配置如下 shared_buffers = 1638MB 主备同步时,备机的recover进程占用内存较大,导致查询时非常慢 to...

wangjiuong
2015/09/15
585
0

没有更多内容

加载失败,请刷新页面

加载更多

为什么从HBase的0.96版本开始,舍弃了-ROOT-文件?

HBase结构的读写流程 (1). HBase0.96版本之前: (2). HBase0.96开始: a. 当客户端获取到.meta文件的位置之后,会缓存.meta.文件的位置 b. 客户端还会缓存HRegion的位置 -ROOT-存在的意义: ...

其乐m
46分钟前
18
0
volatile关键字对 - What is the volatile keyword useful for

问题: At work today, I came across the volatile keyword in Java. 今天的工作中,我遇到了Java中的volatile关键字。 Not being very familiar with it, I found this explanation: 不太熟......

技术盛宴
51分钟前
25
0
golang 封装 mysql 和 redis 连接

Mysql封装 package dbimport ("fmt"_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx")var DB *sqlx.DBfunc init(){database, err := sqlx.Op......

开源中国最牛的人
52分钟前
21
0
pdfbox 读取文件报错 java.io.IOException: Page tree root must be a dictionary

pdfbox java.io.IOException: Page tree root must be a dictionary 示例代码 public static void main(String[] args) { try (InputStream sampleInputs = new ClassPathResource("s......

lemos
今天
28
0
整理 Linux下列出目录内容的命令

在 Linux 中,有非常多的命令可以让我们用来执行各种各样的任务。当我们想要像使用文件浏览器一样列出一个目录下的内容时,大家第一时间想到的是 ls 命令。但只有 ls 命令能实现这个目的吗?...

良许Linux
今天
17
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部