We have migrated to new Google sites!
Post date: Oct 20, 2014 12:25:39 AM
[gpadmin@sachi scripts]$ gpconfig --help
COMMAND NAME: gpconfig
Sets server configuration parameters on all segments within a Greenplum Database system.
*****************************************************
SYNOPSIS
*****************************************************
gpconfig -c <param_name> -v <value> [-m <master_value> | --masteronly]
| -r <param_name> [--masteronly]
| -l
[--skipvalidation] [--verbose] [--debug]
gpconfig -s <param_name> [--verbose] [--debug]
gpconfig --help
*****************************************************
DESCRIPTION
*****************************************************
The gpconfig utility allows you to set, unset, or view configuration parameters from the postgresql.conf files of all instances (master, segments, and mirrors) in your Greenplum Database system. When setting a parameter, you can also specify a different value for the master if necessary. For example, parameters such as max_connections require a different setting on the master than what is used for the segments. If you want to set or unset a global or master only parameter, use the --masteronly option.
gpconfig can only be used to manage certain parameters. For example, you cannot use it to set parameters such as port, which is required to be distinct for every segment instance. Use the -l (list) option to see a complete list of configuration parameters supported by gpconfig.
When gpconfig sets a configuration parameter in a segment postgresql.conf file, the new parameter setting always displays at the bottom of the file. When you use gpconfig to remove a configuration parameter setting, gpconfig comments out the parameter in all segment postgresql.conf files, thereby restoring the system default setting.
For example, if you use gpconfig to remove (comment out) a parameter and later add it back (set a new value), there will be two instances
of the parameter; one that is commented out, and one that is enabled and inserted at the bottom of the postgresql.conf file.
After setting a parameter, you must restart your Greenplum Database system or reload the postgresql.conf files in order for the change to take effect. Whether you require a restart or a reload depends on the parameter. See the Server Configuration Parameters reference for more information about the server configuration parameters.
To show the currently set values for a parameter across the system, use the -s option.
gpconfig uses the following environment variables to connect to the Greenplum Database master instance and obtain system configuration information:
* PGHOST
* PGPORT
* PGUSER
* PGPASSWORD
* PGDATABASE
*****************************************************
DESCRIPTION
*****************************************************
-c | --change <param_name>
Changes a configuration parameter setting by adding the new setting to the bottom of the postgresql.conf files.
-v | --value value
The value to use for the configuration parameter you specified with the -c option. By default, this value is applied to all segments, their mirrors, the master, and the standby master.
-m | --mastervalue master_value
The master value to use for the configuration parameter you specified with the -c option. If specified, this value only applies to the master and standby master. The option can only
be used with -v.
--masteronly
When specified, gpconfig only edits the master postgresql.conf file.
-r | --remove <param_name>
Specifies the configuration parameter name to unset or remove by commenting out the entry in the postgresql.conf files.
-l | --list
Lists all configuration parameters supported by the gpconfig utility.
-s | --show <param_name>
Shows the value for a configuration parameter used on all instances (master and segments) in the Greenplum Database system. If there is a discrepancy in a parameter value between segment instances, the gpconfig utility displays an error message.
Note that the gpconfig utility reads parameter values directly from the database, and not the postgresql.conf file. If you are using gpconfig to set configuration parameters across all segments, then running gpconfig -s to verify the changes, you might still see the previous (old) values. You must reload the configuration files (gpstop -u) or restart the system (gpstop -r) for changes to take effect.
--skipvalidation
Overrides the system validation checks of gpconfig and allows you to operate on any server configuration parameter, including hidden parameters and restricted parameters that cannot be changed by gpconfig. When used with the -l option (list), it shows the list of restricted parameters. This option should only be used to set parameters when directed by Greenplum Customer Support.
--verbose
Displays additional log information during gpconfig command execution.
--debug
Sets logging output to debug level.
-? | -h | --help
Displays the online help.
*****************************************************
EXAMPLES
*****************************************************
Set the work_mem setting to 120MB on the master only:
gpconfig -c work_mem -v 120MB --masteronly
Set the max_connections setting to 100 on all segments and 10 on the master:
gpconfig -c max_connections -v 100 -m 10
Comment out all instances of the default_statistics_target configuration parameter, and restore the system default:
gpconfig -r default_statistics_target
List all configuration parameters supported by gpconfig:
gpconfig -l
Show the values of a particular configuration parameter across the system:
gpconfig -s max_connections
*****************************************************
SEE ALSO
*****************************************************
[gpadmin@sachi scripts]$ gpconfig -s work_mem
Values on all segments are consistent
GUC : work_mem
Master value: 32MB
Segment value: 32MB
[gpadmin@sachi scripts]$ gpconfig -s gp_autostats_mode
Values on all segments are consistent
GUC : gp_autostats_mode
Master value: ON_NO_STATS
Segment value: ON_NO_STATS
[gpadmin@sachi scripts]$ gpconfig -c gp_autostats_mode -v none -m none
20141019:20:11:00:006170 gpconfig:sachi:gpadmin-[INFO]:-completed successfully
[gpadmin@sachi scripts]$
gp_autostats_mode
This specifies when an automatic ANALYZE should take place. The values for this configuration parameter can be:
none
on_change
on_no_stats
on_no_stats
The default is on_no_stats and the database will automatically perform an ANALYZE for you. If you INSERT data into an empty table (new table or a table you just did a TRUNCATE on), the database will automatically gather statistics with an ANALYZE.
Typically in an Analytics Data Warehouse, you will insert data once and then read it many times. So the default of on_no_stats will automatically give you statistics as needed with no additional work from you.
none
Self explanatory. You have to execute ANALYZE if you want statistics.
on_change
With this setting, if perform an INSERT, UPDATE, or DELETE that exceeds the gp_autostats_on_change_threshold value, then an automatic ANALYZE will happen. Note for HAWQ, you can only INSERT.
In Greenplum and in the rare case where you are doing a DELETE or UPDATE to a table, you will need to execute an ANALYZE statement. Or, you can set the database to use on_change and the ANALYZE will happen automatically.
gp_autostats_on_change_threshold
This is only relevant for on_change and it is the number of rows that need to change before an automatic ANALYZE will happen. The default is over 2 billion rows so if you really want to use on_change, then you will also need to reduce this configuration parameter to something more realistic.
SQL to get all GP related server configuration
select category,name,setting,unit,context,source,min_val,max_val,short_desc,extra_desc
from pg_settings
where name like 'gp%'
order by category;