简介
GreenPlum 6.26.2已发布，有3个新特性：
The new
optimizer_enable_right_outer_join
server configuration parameter allows you to control whether GPORCA generates right outer joins. In situations in which you are observing poor performance related to right outer joins you may choose to suppress their use. 新增 optimizer_enable_right_outer_join 服务器配置参数，允许控制 GPORCA 是否生成右外连接。在观察到与右外连接相关的性能问题的情况下，可以选择禁止使用它们。1[gpadmin@gpdb6262 ~]$ gpconfig -s optimizer_enable_right_outer_join
2Values on all segments are consistent
3GUC : optimizer_enable_right_outer_join
4Master value: on
5Segment value: onVMWware Greenplum includes a new extension -
pg_buffercache
-- which provides a new view to obtain clusterwide shared buffer metrics:gp_buffercache
. 新增了一个扩展 - pg_buffercache -- 提供了获取集群范围共享缓冲区指标的新视图：gp_buffercache。The PgBouncer distributed with Greenplum Database has been updated to version 1.21.0. 随 Greenplum Database 一起分发的 PgBouncer 已更新至版本 1.21.0。
6.26.2 测试环境
1docker rm -f gpdb6262
2docker run -itd --name gpdb6262 -h gpdb6261 \
3 -p 59431:5432 -p 28089:28080 \
4 -v /sys/fs/cgroup:/sys/fs/cgroup \
5 --privileged=true lhrbest/greenplum:6.26.2 \
6 /usr/sbin/init
7
8docker exec -it gpdb6262 bash
9su - gpadmin
10gpstart -a
11gpcc start
12
13gpcc status
14gpstate
pg_buffercache和gp_buffercache视图
The pg_buffercache
module provides a new view for obtaining cluster-wide shared buffer metrics: gp_buffercache
The gp_buffercache
view is a cluster-wide view that displays the pg_buffercache
information from the coordinator and every primary segment for each buffer in the shared cache.
1[gpadmin@gpdb6261 ~]$ psql
2psql (9.4.26)
3Type "help" for help.
4
5postgres=# select version();
6 version
7-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8 PostgreSQL 9.4.26 (Greenplum Database 6.26.2 build commit:609ff2bb9ccb7d393d772b29770e757dbd2ecc79) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Jan 20 2024 08:06:22
9(1 row)
10
11
12postgres=# create database db1;
13CREATE DATABASE
14postgres=# \c db1
15You are now connected to database "db1" as user "gpadmin".
16db1=# create extension pg_buffercache;
17CREATE EXTENSION
18db1=#
19db1=#
20db1=# select count(*) from gp_buffercache;
21 count
22-------
23 12000
24(1 row)
25
26db1=# select count(*) from pg_buffercache;
27 count
28-------
29 4000
30(1 row)
31
32db1=# select * from gp_buffercache limit 6;
33 gp_segment_id | bufferid | relfilenode | reltablespace | reldatabase | relforknumber | relblocknumber | isdirty | usagecount | pinning_backends
34---------------+----------+-------------+---------------+-------------+---------------+----------------+---------+------------+------------------
35 -1 | 1 | 12819 | 1664 | 0 | 0 | 0 | t | 5 | 0
36 -1 | 2 | 12563 | 1663 | 12818 | 0 | 0 | f | 5 | 0
37 -1 | 3 | 12554 | 1663 | 12818 | 0 | 0 | f | 5 | 0
38 -1 | 4 | 12554 | 1663 | 12818 | 0 | 1 | f | 5 | 0
39 -1 | 5 | 12554 | 1663 | 12818 | 0 | 2 | f | 5 | 0
40 -1 | 6 | 12554 | 1663 | 12818 | 0 | 3 | f | 5 | 0
41(6 rows)
42
43db1=#
44db1=# SELECT n.nspname, c.relname, count(*) AS buffers
45db1-# FROM pg_buffercache b JOIN pg_class c
46db1-# ON b.relfilenode = pg_relation_filenode(c.oid) AND
47db1-# b.reldatabase IN (0, (SELECT oid FROM pg_database
48db1(# WHERE datname = current_database()))
49db1-# JOIN pg_namespace n ON n.oid = c.relnamespace
50db1-# GROUP BY n.nspname, c.relname
51db1-# ORDER BY 3 DESC
52db1-# LIMIT 10;
53 nspname | relname | buffers
54------------+--------------------------------+---------
55 pg_catalog | pg_proc | 13
56 pg_catalog | pg_attribute | 11
57 pg_catalog | pg_depend_reference_index | 9
58 pg_catalog | pg_class | 8
59 pg_catalog | pg_depend | 8
60 pg_catalog | pg_proc_proname_args_nsp_index | 7
61 pg_catalog | pg_proc_oid_index | 5
62 pg_catalog | pg_type | 5
63 pg_catalog | pg_depend_depender_index | 5
64 pg_catalog | pg_extension | 5
65(10 rows)
66
67db1=#
68
pg_buffercache说明
Table F.15. pg_buffercache
Columns
| Name | Type | References | Description |
|---|---|---|---|
bufferid | integer | ID, in the range 1..shared_buffers | |
relfilenode | oid | pg_class.relfilenode | Filenode number of the relation |
reltablespace | oid | pg_tablespace.oid | Tablespace OID of the relation |
reldatabase | oid | pg_database.oid | Database OID of the relation |
relforknumber | smallint | Fork number within the relation; see include/common/relpath.h | |
relblocknumber | bigint | Page number within the relation | |
isdirty | boolean | Is the page dirty? | |
usagecount | smallint | Clock-sweep access count | |
pinning_backends | integer | Number of backends pinning this buffer |
参考
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/relnotes-release-notes.html?hWord=N4IghgNiBcIOYAcD6AjArgMwwUwE4GMx8ALbEAXyA
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/ref_guide-modules-pg_buffercache.html?hWord=N4IghgNiBcIOYAcD6AjArgMwwUwE4GMx8ALbEAXyA
https://www.postgresql.org/docs/12/pgbuffercache.html
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/install_guide-install_modules.html
