Greenplum Database New Features - Improved Command Center Performance

Greenplum Database 4.3.2 includes a new Greenplum Database configuration parameter that improves the performance of Greenplum Command Center when monitoring a Greenplum database.

Note: For information about the Greenplum Database configuration parameter, see the Greenplum Database Reference Guide. For information about the Greenplum Command Center, see the Greenplum Command Center Administrator Guide.

Improved Command Center Performance (4.3.2 and 4.3.4.1)

The Greenplum Database configuration parameter controls which message levels are written to the gpperfmon log. The log is used by the Greenplum Command Center. Greenplum added 3 new configuration parameters which restrict message level written to gpperfmon log to improve the command center performance.

List of new parameters introduced in GPDB 4.3.2.0.

gp_workfile_limit_files_per_query: This parameter sets the maximum number of temporary spill files (also known as workfiles) allowed per query per segment. Spill files are created when executing a query that requires more memory than it is allocated. The current query is terminated when the limit is exceeded.

Setting this value to 0 will allow an unlimited number of spill files. Default is 100,000.

sachi=# show gp_workfile_limit_files_per_query;                                                                                                              gp_workfile_limit_files_per_query 

-----------------------------------

 100000

(1 row)

sachi=# 

gpperfmon_log_alert_level: This parameter controls which message levels are written to the gpperfmon log. Each level includes all the levels that follow it. The later the level, the fewer messages are sent to the log. If the Greenplum Command Center is installed and is monitoring the database, The default value is warning.

sachi=# show gpperfmon_log_alert_level;

 gpperfmon_log_alert_level 

---------------------------

 warning

(1 row)

sachi=# 

readable_external_table_timeout: This parameter limits the wait time to read the data from external table. When an SQL query reads from an external table, the parameter value specifies the amount of time in seconds that Greenplum Database waits before cancelling the query when data stops being returned from the external table.

sachi=# show readable_external_table_timeout;

 readable_external_table_timeout 

---------------------------------

 0

(1 row)

sachi=# 

The default value of 0, specifies no time out. Greenplum Database does not cancel the query.

If queries that use gpfdist run a long time and then return the error “intermittent network connectivity issues”, you can specify a value forreadable_external_table_timeout. If no data is returned by gpfdist for the specified length of time, Greenplum Database cancels the query.

In GB 4.3.4.1 Greenplum introduces another server configuration parameter to improve command center performance. This parameter name is ignore_qexec_packet. This parameter reduces memory consumption spikes that were caused by the gpmmon process.

This parameter ignore_qexec_packet is set in the gpperfmon.conf configuration file. 

The default setting is true and it reduces memory consumption spikes that can be caused by the gpmmon process.

ignore_qexec_packetSpecifies whether the gpsmon process ignores query execution data. The value is either true or false. 

If you specify ignore_qexec_packet = false, gpmmon memory usage could increase and an out of memory condition might occur due to a large number of UDP packets.

With the default setting, true, when a query is executed, the query plan iterators and their metrics are not added to the iterators_* tables in the gpperfmon database. When you view an explain plan for a query, the query plan iterators and their metrics are displayed in the query plan but are not stored in the iterators_* tables. 

To enable storing query execution data in the iterators_* tables, set the value of the parameter to false.