http://200yi.com/ff_internal/ 2026-04-25T15:19:39+0800 http://200yi.com/ff_internal/ http://200yi.com/ff_internal/lib/tpl/mnml-blog/images/favicon.ico text/html 2023-06-17T21:25:18+0800 Anonymous (anonymous@undisclosed.example.com) http://200yi.com/ff_internal/wiki.php?id=wiki:Database:GreenPlum:%E8%B5%84%E6%BA%90%E7%AE%A1%E7%90%86:GPDB%E6%9F%A5%E8%AF%A2%E5%86%85%E5%AD%98%E8%A7%84%E5%88%92%E8%AF%A6%E8%A7%A3&rev=1687008318&do=diff GPDB内存规划是指，按照当前参数配置，在Coordinator上推算各个算子的配额。所谓“配额”是指单个算子期望的最大可用内存，并非实际使用内存量，实际使用内存： * 可能小于配额：实际数据量小于代价评估数据量。$$ \frac {queryMemKB - numNonMemIntensiveOperators * nonMemIntenseOpMemKB} {numMemIntensiveOperators} $$ text/html 2023-06-17T21:25:18+0800 Anonymous (anonymous@undisclosed.example.com) http://200yi.com/ff_internal/wiki.php?id=wiki:Database:GreenPlum:%E8%B5%84%E6%BA%90%E7%AE%A1%E7%90%86:GPDB%E5%8F%96%E6%B6%88%E7%94%A8%E6%88%B7%E4%B8%8E%E8%B5%84%E6%BA%90%E7%BB%84%E7%9A%84%E5%85%B3%E8%81%94&rev=1687008318&do=diff 一、问题 GPDB删除资源组时，可能会报以下错误： resource group is used by at least one role test=# DROP RESOURCE GROUP rgroup1; ERROR: resource group is used by at least one role 二、原因 被删除的资源组上关联了用户，需要将这些关联的用户全部移出。 text/html 2023-06-17T21:25:18+0800 Anonymous (anonymous@undisclosed.example.com) http://200yi.com/ff_internal/wiki.php?id=wiki:Database:GreenPlum:%E8%B5%84%E6%BA%90%E7%AE%A1%E7%90%86:Greenplum%206.9%20%E8%B5%84%E6%BA%90%E9%98%9F%E5%88%97%EF%BC%88queue%EF%BC%89%E7%A4%BA%E4%BE%8B&rev=1687008318&do=diff 本文在Redhat 7.6环境使用Greenplum 6.9.0（部署在一台物理机上，2个segment）测试通过。 因为资源队列管理方式下，超级管理员（SUPERUSER）不受资源队列限制，所以下文将创建一个非超级管理员用户进行测试。_______ text/html 2023-06-17T21:25:18+0800 Anonymous (anonymous@undisclosed.example.com) http://200yi.com/ff_internal/wiki.php?id=wiki:Database:GreenPlum:%E8%B5%84%E6%BA%90%E7%AE%A1%E7%90%86:Greenplum%206.9%20%E8%B5%84%E6%BA%90%E7%BB%84%EF%BC%88cgroups%EF%BC%89%E7%A4%BA%E4%BE%8B&rev=1687008318&do=diff 本文在Redhat 7.6环境使用Greenplum 6.9.0（部署在一台物理机上，2个segment）测试通过。 一、cgroups配置 安装cgroup工具 sudo yum install libcgroup-tools 创建配置文件 [gpadmin@bogon ~]$ sudo vi /etc/cgconfig.d/gpdb.conf group gpdb { perm { task { uid = gpadmin; gid = gpadmin; } admin { uid = gpadmin; gid = gpadmin; } } cpu { } cpuacct { } cpuset { } memory { } } ______________… text/html 2024-08-14T01:00:06+0800 Anonymous (anonymous@undisclosed.example.com) http://200yi.com/ff_internal/wiki.php?id=wiki:Database:GreenPlum:%E8%B5%84%E6%BA%90%E7%AE%A1%E7%90%86:Greenplum%206.9%20%E8%B5%84%E6%BA%90%E7%BB%84%E4%B8%AD%E6%96%87%E6%96%87%E6%A1%A3&rev=1723568406&do=diff 关于资源队列的中文文档请参考：用资源队列进行工作负载管理 一、资源管理 Greenplum Database（GPDB，下文简称GP）提供的特性可以帮助你根据业务需求按照优先顺序处理和分配资源，在没有可用资源的情况下不允许启动新的查询。____________________________________________________$$ \min (\frac {未分配的CPU核数} {所有CPU核数} * 100, gp\_resource\_group\_cpu\_limit) $$__________________$$ rg\_perseg\_mem = \frac {(RAM * \frac {vm.overcommit\_ratio} {100} + SWAP) * gp\_resource\_group\_memory\_limit} {num\_active\_primary\_segments} $$________________________________________________________________________________… text/html 2023-06-17T21:25:18+0800 Anonymous (anonymous@undisclosed.example.com) http://200yi.com/ff_internal/wiki.php?id=wiki:Database:GreenPlum:%E8%B5%84%E6%BA%90%E7%AE%A1%E7%90%86:Greenplum%206.X%20%E8%B5%84%E6%BA%90%E7%AE%A1%E7%90%86%E6%BA%90%E7%A0%81%E5%88%86%E6%9E%90&rev=1687008318&do=diff 本文适用Greenplum Database（GPDB）源码 6X_STABLE 分支。 一、源码路径 * src\backend\utils\resgroup * src\backend\utils\resource_manager * src\backend\utils\resowner * src\backend\utils\resscheduler 二、内存管理 初始化共享内存：$$ segmentsOnMaster = segment个数 + master个数 $$________ text/html 2023-06-17T21:25:18+0800 Anonymous (anonymous@undisclosed.example.com) http://200yi.com/ff_internal/wiki.php?id=wiki:Database:GreenPlum:%E8%B5%84%E6%BA%90%E7%AE%A1%E7%90%86:Greenplum%E5%8F%82%E6%95%B0%E8%B0%83%E4%BC%98&rev=1687008318&do=diff 本文适用于GPDB 6.X。 一、内存参数 GPDB推荐参数配置 * 详细推算方法见官方文档 * 官方推荐值计算器 statement_mem 每条查询单个segment可以使用的内存大小。 "Allocates segment host memory per query. The amount of memory allocated with this parameter cannot exceed max_statement_mem or the memory limit on the resource queue or resource group through which the query was submitted. If additional memory is required for a query, temporary spill files on disk are used.$$ \frac {gp\_vmem\_protect\_limit * 0.9} {期望的最大并发数} $$$$ \frac {\frac {RAM – 7.5GB} {… text/html 2023-06-17T21:25:18+0800 Anonymous (anonymous@undisclosed.example.com) http://200yi.com/ff_internal/wiki.php?id=wiki:Database:GreenPlum:%E8%B5%84%E6%BA%90%E7%AE%A1%E7%90%86:Greenplum%E5%8F%82%E6%95%B0%E8%AF%B4%E6%98%8E&rev=1687008318&do=diff 一、非公开参数 GP中有些参数不是公开的，代码中使用 GUC_NO_SHOW_ALL 参数进行控制。此类参数有以下特征： * 官方手册中查不到。 * “SHOW ALL”命令中不显示该参数。 * 参数键入过程中tab键词语联想时，不会自动补全。_____________$$ 0.7 + (0.116 * 2) = 0.932\% $$________$$ gp\_vmem = \frac {(SWAP + RAM) - (7.5GB + 0.05 * RAM)} {1.7} $$_________$$ gp\_vmem\_protect\_limit = \frac {gp\_vmem} {max\_acting\_primary\_segments} $$_$$ gp\_vmem = \frac {(SWAP + RAM) - (7.5GB + 0.05 * RAM - (300KB * total\_\#\_workfiles))} {1.7} $$__$$ vm.overcommit\_ratio = \frac {RAM - (0.026 * gp\_vmem)} {… text/html 2023-06-17T21:25:18+0800 Anonymous (anonymous@undisclosed.example.com) http://200yi.com/ff_internal/wiki.php?id=wiki:Database:GreenPlum:%E8%B5%84%E6%BA%90%E7%AE%A1%E7%90%86:%E4%B8%BAGreenplum%E6%B7%BB%E5%8A%A0%E5%B8%B8%E7%94%A8%E8%A7%86%E5%9B%BE&rev=1687008318&do=diff 查询资源队列配置视图 为GPDB添加查询资源队列配置的视图： -- ------------------------------------------------------------------------------ -- @view: -- gp_toolkit.gp_resqueue_config -- -- @doc: -- Resource queue configuration -- -- ------------------------------------------------------------------------------ CREATE VIEW gp_toolkit.gp_resqueue_config AS SELECT Q.oid AS queueoid , Q.rsqname AS queuename , Q.rsqcountlimit AS activ…