Greenplum/Deepgreen ERROR: insufficient memory reserved for statement (memquota.c:228)

最新推荐文章于 2023-06-05 23:23:30 发布

转载最新推荐文章于 2023-06-05 23:23:30 发布 · 4.9k 阅读

3 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/98616

文章标签：

#数据库

本文介绍了解决 Greenplum 数据库中遇到的内存不足错误的方法。通过调整 statement_mem 参数的大小来避免 insufficentmemoryreservedforstatement 错误。

今天生产中遇到了内存不足的错误：insufficient memory reserved for statement (memquota.c:228)，这里记录一下问题原因及解决办法。

解决方法：

将statement_mem默认的125MB的配置提高，建议在500MB左右或者更高一些。

操作步骤：

1.查看statement_mem默认设置：

dgadmin@flash:~$ gpconfig -s statement_mem
Values on all segments are consistent
GUC          : statement_mem
Master  value: 125MB
Segment value: 125MB

2.在Master节点修改配置文件postgresql.conf，最后一行添加statement_mem：

chris@flash:~$ su - dgadmin
Password:
dgadmin@flash:~$ cd $MASTER_DATA_DIRECTORY
dgadmin@flash:/dgdata/master/dg-1$ ls
base     gpperfmon          pg_distributedlog     pg_ident.conf  pg_stat_tmp  pg_twophase            pg_xlog              postmaster.opts
global   pg_changetracking  pg_distributedxidmap  pg_log         pg_subtrans  pg_utilitymodedtmredo  postgresql.conf      postmaster.pid
gp_dbid  pg_clog            pg_hba.conf           pg_multixact   pg_tblspc    PG_VERSION             postgresql.conf.bak
dgadmin@flash:/dgdata/master/dg-1$

dgadmin@flash:/dgdata/master/dg-1$ vim postgresql.conf
dgadmin@flash:/dgdata/master/dg-1$ gpstop -u
20170611:10:40:34:003556 gpstop:flash:dgadmin-[INFO]:-Starting gpstop with args: -u
20170611:10:40:34:003556 gpstop:flash:dgadmin-[INFO]:-Gathering information and validating the environment...
20170611:10:40:34:003556 gpstop:flash:dgadmin-[INFO]:-Obtaining Greenplum Master catalog information
20170611:10:40:34:003556 gpstop:flash:dgadmin-[INFO]:-Obtaining Segment details from master...
20170611:10:40:34:003556 gpstop:flash:dgadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 4.3.99.00 build Deepgreen DB'
20170611:10:40:34:003556 gpstop:flash:dgadmin-[INFO]:-Signalling all postmaster processes to reload
................

3.使修改生效：

dgadmin@flash:/dgdata/master/dg-1$ gpconfig -s statement_mem
Values on all segments are consistent
GUC          : statement_mem
Master  value: 500MB
Segment value: 500MB

备注：需要注意的是，新版本的配置文件中，参数gp_resqueue_memory_policy通常会设置为：'eager_free'。如果要对statement_mem进行修改并让其发挥作用，参数应该配置为：