Unable to make changes to statement_mem and max_statement_mem - changes will not take effect

Products

• VMware Tanzu Data Suite

Issue/Introduction

We are trying to change  max_statement_mem and  statement_mem to bigger value by running below command

gpconfig -c  max_statement_mem -v 6000
gpconfig -c  statement_mem -v 500

We see it changed in the postgresql.conf file:

max_statement_mem=6000   #gpconfig updated this parameter
statement_mem=500   #gpconfig updated this parameter

After the change, we also shutdown and restarted GP cluster, but the values will still not take effect. As show below, they are still showing the default values

[gpadmin@...]$ gpconfig -s  statement_mem
Values on all segments are consistent
GUC              : statement_mem
Coordinator value: 125MB
Segment     value: 125MB
[gpadmin@...]$ gpconfig -s  max_statement_mem
Values on all segments are consistent
GUC              : max_statement_mem
Coordinator value: 2000MB
Segment     value: 2000MB
[gpadmin@...]$

 

Query against pg_settings view show the same default values 

select * from pg_settings where name like '%statement_mem%'

max_statement_mem    2048000    kB
statement_mem    128000    kB

Cause

When an attempt is made to change max_statement_mem and statement_mem to the numeric values (6000 and 500 respectively in this example), postgresql.conf perceives the 6000 and 500 as Kilobyte values.

This can lead to unexpected result or potentially the Database may even fail to start on the subsequent attempt to reset.

 

Resolution

The value specified in gpconfig should contain the MB unit if the values desired are 6000MB and 500MB.

gpconfig -c  max_statement_mem -v 6000MB
gpconfig -c  statement_mem -v 500MB