Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Working with DB Parameter Groups

You manage your DB engine configuration through the use of parameters in a DB parameter group. DB parameter groups act as a container for engine configuration values that are applied to one or more DB instances.

A default DB parameter group is created if you create a DB instance without specifying a customer-created DB parameter group. This default group contains database engine defaults and Amazon RDS system defaults based on the engine, compute class, and allocated storage of the instance. You cannot modify the parameter settings of a default DB parameter group; you must create your own DB parameter group to change parameter settings from their default value. Note that not all DB engine parameters can be changed in a customer-created DB parameter group.

If you want to use your own DB parameter group, you simply create a new DB parameter group, modify the desired parameters, and modify your DB instance to use the new DB parameter group. All DB instances that are associated with a particular DB parameter group get all parameter updates to that DB parameter group. You can also copy an existing parameter group with the AWS CLI copy-db-parameter-group command. Copying a parameter group is a convenient solution when you have already created a DB parameter group and you want to include most of the custom parameters and values from that group in a new DB parameter group.

Here are some important points you should know about working with parameters in a DB parameter group:

  • When you change a dynamic parameter and save the DB parameter group, the change is applied immediately regardless of the Apply Immediately setting. When you change a static parameter and save the DB parameter group, the parameter change will take effect after you manually reboot the DB instance. You can reboot a DB instance using the RDS console or explicitly calling the RebootDbInstance API action (without failover, if the DB instance is in a Multi-AZ deployment). The requirement to reboot the associated DB instance after a static parameter change helps mitigate the risk of a parameter misconfiguration affecting an API call, such as calling ModifyDBInstance to change DB instance class or scale storage.

  • When you change the DB parameter group associated with a DB instance, you must manually reboot the instance before the new DB parameter group is used by the DB instance.

  • The value for a DB parameter can be specified as an integer; an integer expression built from formulas, variables, functions, and operators; or as a log expression. For more information, see DB Parameter Values

  • Set any parameters that relate to the character set or collation of your database in your parameter group prior to creating the DB instance and before you create a database in your DB instance. This ensures that the default database and new databases in your DB instance use the character set and collation values that you specify. If you change character set or collation parameters for your DB instance, the parameter changes are not applied to existing databases.

    You can change character set or collation values for an existing database using the ALTER DATABASE command, for example:

    Copy
    ALTER DATABASE database_name CHARACTER SET character_set_name COLLATE collation;
  • Improperly setting parameters in a DB parameter group can have unintended adverse effects, including degraded performance and system instability. Always exercise caution when modifying database parameters and back up your data before modifying a DB parameter group. You should try out parameter group setting changes on a test DB instance before applying those parameter group changes to a production DB instance.

  • Amazon Aurora uses both DB parameter groups and DB cluster parameter groups. Parameters in a DB parameter group apply to a single DB instance in an Aurora DB cluster. Parameters in a DB cluster parameter group apply to every DB instance in a DB cluster. For more information, see DB Cluster and DB Instance Parameters.

Creating a DB Parameter Group

The following section shows you how to create a new DB parameter group.

AWS Management Console

To create a DB parameter group

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. Click Parameter Groups in the Navigation list on the left side of the window.

  3. Click the Create DB Parameter Group button.

    The Create DB Parameter Group window appears.

  4. Select a DB parameter group family in the DB Parameter Group Family drop-down list box.

  5. Type the name of the new DB parameter group in the DB Parameter Group text box.

  6. Type a description for the new DB parameter group in the Description text box.

  7. Click the Yes, Create button.

CLI

To create a DB parameter group, use the AWS CLI create-db-parameter-group command. The following example creates a DB parameter group named mydbparametergroup for MySQL version 5.6 with a description of "My new parameter group."

Include the following required parameters:

  • --db-parameter-group-name

  • --db-parameter-group-family

  • --description

Example

For Linux, OS X, or Unix:

Copy
aws rds create-db-parameter-group \ --db-parameter-group-name mydbparametergroup \ --db-parameter-group-family MySQL5.6 \ --description "My new parameter group"

For Windows:

Copy
aws rds create-db-parameter-group ^ --db-parameter-group-name mydbparametergroup ^ --db-parameter-group-family MySQL5.6 ^ --description "My new parameter group"

This command produces output similar to the following:

Copy
DBPARAMETERGROUP mydbparametergroup mysql5.6 My new parameter group

API

To create a DB parameter group, use the Amazon RDS API CreateDBParameterGroup action. The following example creates a DB parameter group named mydbparametergroup for MySQL version 5.6 with a description of "My new parameter group."

Include the following required parameters:

  • DBParameterGroupName = mydbparametergroup

  • DBParameterGroupFamily = MySQL5.6

  • Description = My new parameter group

Example

Copy
https://rds.amazonaws.com/ ?Action=CreateDBParameterGroup &DBParameterGroupName=mydbparametergroup &Description=My%20new%20parameter%20group &DBParameterGroupFamily=MySQL5.6 &Version=2012-01-15 &SignatureVersion=2 &SignatureMethod=HmacSHA256 &Timestamp=2012-01-15T22%3A06%3A23.624Z &AWSAccessKeyId=<AWS Access Key ID> &Signature=<Signature>

The command returns a response like the following:

Copy
<CreateDBParameterGroupResponse xmlns="http://rds.amazonaws.com/admin/2012-01-15/"> <CreateDBParameterGroupResult> <DBParameterGroup> <DBParameterGroupFamily>mysql5.6</DBParameterGroupFamily> <Description>My new parameter group</Description> <DBParameterGroupName>mydbparametergroup</DBParameterGroupName> </DBParameterGroup> </CreateDBParameterGroupResult> <ResponseMetadata> <RequestId>700a8afe-0b81-11df-85f9-eb5c71b54ddc</RequestId> </ResponseMetadata> </CreateDBParameterGroupResponse>

Modifying Parameters in a DB Parameter Group

You can modify parameter values in a customer-created DB parameter group; you cannot change the parameter values in a default DB parameter group. Changes to parameters in a customer-created DB parameter group are applied to all DB instances that are associated with the DB parameter group.

If you change a parameter value, when the change is applied is determined by the type of parameter. Changes to dynamic parameters are applied immediately. Changes to static parameters require that the DB instance associated with DB parameter group be rebooted before the change takes effect. To determine the type of a parameter, list the parameters in a parameter group using one of the procedures shown in the section Listing DB Parameter Groups.

The RDS console shows the status of the DB parameter group associated with a DB instance. For example, if the DB instance is not using the latest changes to its associated DB parameter group, the RDS console shows the DB parameter group with a status of pending-reboot. You would need to manually reboot the DB instance for the latest parameter changes to take effect for that DB instance.


				Parameter change pending reboot scenario

AWS Management Console

To modify a DB parameter group

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. Click Parameter Groups in the navigation pane on the left side of the window.

    The available DB parameter groups appear in a list.

  3. In the list, select the parameter group you want to modify.

  4. Select Edit Parameters.

  5. Change the values of the parameters you want to modify. You can scroll through the parameters using the arrow keys at the top right of the dialog box.

    Note that you cannot change values in a default parameter group.

  6. Click Save Changes.

CLI

To modify a DB parameter group, use the AWS CLI modify-db-parameter-group command with the following required parameters:

  • --db-parameter-group-name

  • --parameters

The following example modifies the max_connections and max_allowed_packet values in the DB parameter group named mydbparametergroup.

Note

Amazon RDS does not support passing multiple comma-delimited parameter values for a single parameter.

Example

For Linux, OS X, or Unix:

Copy
aws rds modify-db-parameter-group \ --db-parameter-group-name mydbparametergroup \ --parameters "name=max_connections,value=250,method=immediate" \ --parameters "name=max_allowed_packet,value=1024,method=immediate"

For Windows:

Copy
aws rds modify-db-parameter-group ^ --db-parameter-group-name mydbparametergroup ^ --parameters "name=max_connections,value=250,method=immediate" ^ --parameters "name=max_allowed_packet,value=1024,method=immediate"

The command produces output like the following:

Copy
DBPARAMETERGROUP mydbparametergroup

API

To modify a DB parameter group, use the Amazon RDS API ModifyDBParameterGroup command with the following required parameters:

  • DBParameterGroupName

  • Parameters

The following example modifies the max_connections and max_allowed_packet values in the DB parameter group named mydbparametergroup.

Note

Amazon RDS does not support passing multiple comma-delimited parameter values for a single parameter.

Example

Copy
https://rds.amazonaws.com/ ?Action=ModifyDBParameterGroup &DBParameterGroupName=mydbparametergroup &Parameters.member.1.ParameterName=max_connections &Parameters.member.1.ParameterValue=250 &Parameters.member.1.ApplyMethod=immediate &Parameters.member.2.ParameterName=max_allowed_packet &Parameters.member.2.ParameterValue=1024 &Parameters.member.2.ApplyMethod=immediate &Version=2012-01-15 &SignatureVersion=2 &SignatureMethod=HmacSHA256 &Timestamp=2012-01-15T22%3A29%3A47.865Z

The command returns a response like the following:

Copy
<ModifyDBParameterGroupResponse xmlns="http://rds.amazonaws.com/admin/2012-01-15/"> <ModifyDBParameterGroupResult> <DBParameterGroupName>mydbparametergroup</DBParameterGroupName> </ModifyDBParameterGroupResult> <ResponseMetadata> <RequestId>3b824e10-0b87-11df-972f-21e99bc6881d</RequestId> </ResponseMetadata> </ModifyDBParameterGroupResponse>

Copying a DB Parameter Group

You can copy custom DB parameter groups that you create. Copying a parameter group is a convenient solution when you have already created a DB parameter group and you want to include most of the custom parameters and values from that group in a new DB parameter group. You can copy a DB parameter group by using the AWS CLI copy-db-parameter-group command or the Amazon RDS API CopyDBParameterGroup action.

After you copy a DB parameter group, you should wait at least 5 minutes before creating your first DB instance that uses that DB parameter group as the default parameter group. This allows Amazon RDS to fully complete the copy action before the parameter group is used as the default for a new DB instance. This is especially important for parameters that are critical when creating the default database for a DB instance, such as the character set for the default database defined by the character_set_database parameter. You can use the Parameter Groups option of the Amazon RDS console or the describe-db-parameters command to verify that your DB parameter group has been created.

CLI

To copy a DB parameter group, use the AWS CLI copy-db-parameter-group command with the following required parameters:

  • --source-db-parameter-group-identifier

  • --target-db-parameter-group-identifier

  • --target-db-parameter-group-description

The following example creates a new DB parameter group named mygroup2 that is a copy of the DB parameter group mygroup1.

Example

For Linux, OS X, or Unix:

Copy
aws rds copy-db-parameter-group \ --source-db-parameter-group-identifier mygroup1 \ --target-db-parameter-group-identifier mygroup2 \ --target-db-parameter-group-description "DB parameter group 2"

For Windows:

Copy
aws rds copy-db-parameter-group ^ --source-db-parameter-group-identifier mygroup1 ^ --target-db-parameter-group-identifier mygroup2 ^ --target-db-parameter-group-description "DB parameter group 2"

API

To copy a DB parameter group, use the RDS API CopyDBParameterGroup action with the following required parameters:

  • SourceDBPparameterGroupIdentifier = arn%3Aaws%3Ards%3Aus-west-2%3A123456789012%3Apg%3Amygroup1

  • TargetDBPparameterGroupIdentifier = mygroup2

  • TargetDBPparameterGroupDescription = DB%20parameter%20group%202

The following example creates a new DB parameter group named mygroup2 that is a copy of the DB parameter group mygroup1.

Example

Copy
https://rds.us-east-1.amazonaws.com/ ?Action=CopyDBParameterGroup &SignatureMethod=HmacSHA256 &SignatureVersion=4 &SourceDBParameterGroupIdentifier=arn%3Aaws%3Ards%3Aus-west-2%3A123456789012%3Apg%3Amygroup1 &TargetDBParameterGroupIdentifier=mygroup2 &TargetDBParameterGroupDescription=DB%20parameter%20group%202 &Version=2014-09-01 &X-Amz-Algorithm=AWS4-HMAC-SHA256 &X-Amz-Credential=AKIADQKE4SARGYLE/20140922/us-east-1/rds/aws4_request &X-Amz-Date=20140922T175351Z &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date &X-Amz-Signature=5164017efa99caf850e874a1cb7ef62f3ddd29d0b448b9e0e7c53b288ddffed2

The command returns a response like the following:

Copy
<CopyDBParameterGroupResponse xmlns="http://rds.amazonaws.com/doc/2014-10-31/"> <CopyDBParameterGroupResult> <DBParameterGroup> <DBParameterGroupFamily>mysql5.6</DBParameterGroupFamily> <Description>DB parameter group 2</Description> <DBParameterGroupName>mygroup2</DBParameterGroupName> </DBParameterGroup> </CopyDBParameterGroupResult> <ResponseMetadata> <RequestId>3328d60e-beb6-11d3-8e5c-3ccda5460d76</RequestId> </ResponseMetadata> </CopyDBParameterGroupResponse>

Listing DB Parameter Groups

You can list the DB parameter groups you've created for your AWS account.

Note

Default parameter groups are automatically created from a default parameter template when you create a DB instance for a particular DB engine and version. These default parameter groups contain preferred parameter settings and cannot be modified. When you create a custom parameter group, you can modify parameter settings.

AWS Management Console

To list all DB parameter groups for an AWS account

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. Click Parameter Groups in the navigation pane on the left side of the window.

    The DB parameter groups appear in a list.

CLI

To list all DB parameter groups for an AWS account, use the AWS CLI describe-db-parameter-groups command.

Example

The following example lists all available DB parameter groups for an AWS account.

Copy
aws rds describe-db-parameter-groups

The command returns a response like the following:

Copy
DBPARAMETERGROUP default.mysql5.5 mysql5.5 Default parameter group for MySQL5.5 DBPARAMETERGROUP default.mysql5.6 mysql5.6 Default parameter group for MySQL5.6 DBPARAMETERGROUP mydbparametergroup mysql5.6 My new parameter group

The following example describes the mydbparamgroup1 parameter group.

For Linux, OS X, or Unix:

Copy
aws rds describe-db-parameter-groups \ --db-parameter-group-name mydbparamgroup1

For Windows:

Copy
aws rds describe-db-parameter-groups ^ --db-parameter-group-name mydbparamgroup1

The command returns a response like the following:

Copy
DBPARAMETERGROUP mydbparametergroup1 mysql5.5 My new parameter group

API

To list all DB parameter groups for an AWS account, use the RDS API DescribeDBParameterGroups action.

Example

The following example lists all available DB parameter groups for an AWS account.

Copy
https://rds.amazonaws.com/ ?Action=DescribeDBParameterGroups &MaxRecords=100 &Version=2012-01-15 &SignatureVersion=2 &SignatureMethod=HmacSHA256 &Timestamp=2009-10-22T19%3A31%3A42.262Z &AWSAccessKeyId=<AWS Access Key ID> &Signature=<Signature>

The command returns a response like the following:

Copy
<DescribeDBParameterGroupsResponse xmlns="http://rds.amazonaws.com/admin/2012-01-15/"> <DescribeDBParameterGroupsResult> <DBParameterGroups> <DBParameterGroup> <Engine>mysql5.6</Engine> <Description>Default parameter group for MySQL5.6</Description> <DBParameterGroupName>default.mysql5.6</DBParameterGroupName> </DBParameterGroup> <DBParameterGroup> <Engine>mysql5.6</Engine> <Description>My new parameter group</Description> <DBParameterGroupName>mydbparametergroup</DBParameterGroupName> </DBParameterGroup> </DBParameterGroups> </DescribeDBParameterGroupsResult> <ResponseMetadata> <RequestId>41731881-0b82-11df-9a9b-c1bd5894571c</RequestId> </ResponseMetadata> </DescribeDBParameterGroupsResponse>

The following example describes the mydbparamgroup1 parameter group.

Copy
https://rds.amazonaws.com/ ?Action=DescribeDBParameterGroups &DBParameterGroupName=mydbparamgroup1 &MaxRecords=100 &Version=2012-01-15 &SignatureVersion=2 &SignatureMethod=HmacSHA256 &Timestamp=2009-10-22T19%3A31%3A42.262Z &AWSAccessKeyId=<AWS Access Key ID> &Signature=<Signature>

The command returns a response like the following:

Copy
<DescribeDBParameterGroupsResponse xmlns="http://rds.amazonaws.com/admin/2012-01-15/"> <DescribeDBParameterGroupsResult> <DBParameterGroups> <DBParameterGroup> <Engine>mysql5.6</Engine> <Description>My new parameter group</Description> <DBParameterGroupName>mydbparamgroup1</DBParameterGroupName> </DBParameterGroup> </DBParameterGroups> </DescribeDBParameterGroupsResult> <ResponseMetadata> <RequestId>41731881-0b82-11df-9a9b-c1bd5894571c</RequestId> </ResponseMetadata> </DescribeDBParameterGroupsResponse>

Viewing Parameter Values for a DB Parameter Group

You can get a list of all parameters in a DB parameter group and their values.

AWS Management Console

To view the parameter values for a DB parameter group

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. Click Parameter Groups in the navigation pane on the left side of the window.

    The DB parameter groups appear in a list.

  3. Select a DB parameter group from the list. Click the Details page icon to see the list of parameters for the selected DB parameter group.

CLI

To view the parameter values for a DB parameter group, use the AWS CLI describe-db-parameters command with the following required parameter.

  • --db-parameter-group-name

Example

The following example lists the parameters and parameter values for a DB parameter group named mydbparametergroup.

Copy
aws rds describe-db-parameters --db-parameter-group-name mydbparametergroup

The command returns a response like the following:

Copy
DBPARAMETER Parameter Name Parameter Value Source Data Type Apply Type Is Modifiable DBPARAMETER allow-suspicious-udfs engine-default boolean static false DBPARAMETER auto_increment_increment engine-default integer dynamic true DBPARAMETER auto_increment_offset engine-default integer dynamic true DBPARAMETER binlog_cache_size 32768 system integer dynamic true DBPARAMETER socket /tmp/mysql.sock system string static false

API

To view the parameter values for a DB parameter group, use the Amazon RDS API DescribeDBParameters command with the following required parameter.

  • DBParameterGroupName = mydbparametergroup

Example

The following example lists the parameters and parameter values for a DB parameter group named mydbparametergroup.

Copy
https://rds.amazonaws.com/ ?Action=DescribeDBParameters &DBParameterGroupName=mydbparametergroup &MaxRecords=100 &Version=2012-01-15 &SignatureVersion=2 &SignatureMethod=HmacSHA256 &Timestamp=2009-10-22T19%3A31%3A42.262Z &AWSAccessKeyId=<AWS Access Key ID> &Signature=<Signature>

The command returns a response like the following:

Copy
<DescribeDBParametersResponse xmlns="http://rds.amazonaws.com/admin/2012-01-15/"> <DescribeDBParametersResult> <Marker>bWF4X3RtcF90YWJsZXM=</Marker> <Parameters> <Parameter> <DataType>boolean</DataType> <Source>engine-default</Source> <IsModifiable>false</IsModifiable> <Description>Controls whether user-defined functions that have only an xxx symbol for the main function can be loaded</Description> <ApplyType>static</ApplyType> <AllowedValues>0,1</AllowedValues> <ParameterName>allow-suspicious-udfs</ParameterName> </Parameter> <Parameter> <DataType>integer</DataType> <Source>engine-default</Source> <IsModifiable>true</IsModifiable> <Description>Intended for use with master-to-master replication, and can be used to control the operation of AUTO_INCREMENT columns</Description> <ApplyType>dynamic</ApplyType> <AllowedValues>1-65535</AllowedValues> <ParameterName>auto_increment_increment</ParameterName> </Parameter> <Parameter> <DataType>integer</DataType> <Source>engine-default</Source> <IsModifiable>true</IsModifiable> <Description>Determines the starting point for the AUTO_INCREMENT column value</Description> <ApplyType>dynamic</ApplyType> <AllowedValues>1-65535</AllowedValues> <ParameterName>auto_increment_offset</ParameterName> </Parameter> (... sample truncated...) </Parameters> </DescribeDBParametersResult> <ResponseMetadata> <RequestId>99c0937a-0b83-11df-85f9-eb5c71b54ddc</RequestId> </ResponseMetadata> </DescribeDBParametersResponse>

DB Parameter Values

The value for a DB parameter can be specified as:

  • An integer constant

  • A DB parameter formula

  • A DB parameter function

  • A character string constant

  • A log expression (the log function represents log base 2), such as value={log(DBInstanceClassMemory/8187281418)*1000}

DB Parameter Formulas

A DB parameter formula is an expression that resolves to an integer value, and is enclosed in braces: {}. Formulas can be specified for either a DB parameter value or as an argument to a DB parameter function.

Syntax

Copy
{FormulaVariable}
Copy
{FormulaVariable*Integer}
Copy
{FormulaVariable*Integer/Integer}
Copy
{FormulaVariable/Integer}

DB Parameter Formula Variables

Formula variables return integers. The names of the variables are case sensitive.

AllocatedStorage

Returns the size, in bytes, of the data volume.

DBInstanceClassMemory

Returns the number of bytes of memory allocated to the DB instance class associated with the current DB instance, less the memory used by the Amazon RDS processes that manage the instance.

EndPointPort

Returns the number of the port used when connecting to the DB instance.

DB Parameter Formula Operators

DB parameter formulas support two operators: division and multiplication.

Division Operator: /

Divides the dividend by the divisor, returning an integer quotient. Decimals in the quotient are truncated, not rounded.

Syntax

Copy
dividend / divisor

The dividend and divisor arguments must be integer expressions.

Multiplication Operator: *

Divides the dividend by the divisor, returning an integer quotient. Decimals in the quotient are truncated, not rounded.

Syntax

Copy
expression * expression

Both expressions must be integers.

DB Parameter Functions

The parameter arguments can be specified as either integers or formulas. Each function must have at least one argument. Multiple arguments can be specified as a comma-separated list. The list cannot have any empty members, such as argument1,,argument3. Function names are case insensitive.

Note

DB Parameter functions are not currently supported in CLI.

GREATEST()

Returns the largest value from a list of integers or parameter formulas.

Syntax

Copy
GREATEST(argument1, argument2,...argumentn)

Returns an integer.

LEAST()

Returns the smallest value from a list of integers or parameter formulas.

Syntax

Copy
LEAST(argument1, argument2,...argumentn)

Returns an integer.

SUM()

Adds the values of the specified integers or parameter formulas.

Syntax

Copy
SUM(argument1, argument2,...argumentn)

Returns an integer.

DB Parameter Value Examples

These examples show using formulas and functions in the values for DB parameters.

Caution

Improperly setting parameters in a DB parameter group can have unintended adverse effects, including degraded performance and system instability. Always exercise caution when modifying database parameters and back up your data before modifying your DB parameter group. You should try out parameter group changes on a test DB instances, created using point-in-time-restores, before applying those parameter group changes to your production DB instances.

You can specify the GREATEST function in an Oracle processes parameter to set the number of user processes to the larger of either 80 or DBInstanceClassMemory divided by 9868951.

Copy
GREATEST({DBInstanceClassMemory/9868951},80)

You can specify the LEAST() function in a MySQL max_binlog_cache_size parameter value to set the maximum cache size a transaction can use in a MySQL instance to the lesser of 1MB or DBInstanceClass/256:

Copy
LEAST({DBInstanceClassMemory/256},10485760)