How to create and modify a Parameter Group for an RDS instance on AWS

A Parameter Group contains configuration to be used by the DB instances on AWS. Every DB instance we create has a default Parameter group attached to it with default values. This default parameter group can not be edited, hence it is necessary to create a new parameter group with the required configuration. So, if you want to change the values of parameters, you first create a new Parameter group and attach it to the DB instance.

Note that improperly configuring parameters can have adverse effects, and may result in degraded performance and system instability. So, you need to be very careful while changing the values of parameters on Production DB instances. You can also first try experimenting with changes on the test DB.

It is also to be noted that not all DB engine parameters can be changed in the parameter group you create. Changes to Dynamic parameters are applied immediately whereas you need to reboot the DB instance if changes are made to a static parameter.

In this article, we will see the steps to create a new Parameter group, change the default value of one of the parameters and assign it to the MySql DB instances. Before proceeding with this article, it is assumed that you have a basic understanding of RDS and already have a MySql DB Instance in your account.

Pre-requisites

1. AWS Account(Create if you don’t have one).
2. RDS MySql Instance.

What will we do?

1. Login to AWS.
2. Create a Parameter Group.
3. Attach the Parameter Group to the RDS MySql Instance.
4. Modify the Parameter Group.

Login to AWS

Click here to go to the login page where you can enter your credentials to get into the account.

You will see the main AWS management console as follows when you successfully login into your account. You can choose the region you want before you proceed, you can also change the region at a later point of time.

Create a Parameter Group

In the search box at the top of the screen search for RDS.

Here is the RDS dashboard, click on Parameter groups in the left panel.

Every RDS is attached with the default parameter group. Click on the “Create parameter group” button to create a new parameter group.

Give a name to the Parameter group to be created, choose the family as per your RDS engine and add a description to it. I will be attaching the Parameter group to the MySql instance, hence the family I have selected in “MySQL”.

Once the Parameter group is created, you can see it listed.

Attach the Parameter Group to the RDS MySql Instance

Now, go to the RDS instance, edit it and under the Database options choose the DB parameter group we created.

Check the summary modifications, select scheduling of modifications as “Apply immediately” so that the Parameter group gets attached to the DB instance immediately and click on the “Modify DB instance” button. This will reboot the DB instance and it will take some time to get started.

Modify the Parameter Group

Till this point, we have just attached the Parameter group with all its default values. Now, let’s change the value of one of the parameters. Select the Parameter group we created and click on “Parameter group actions — > Edit”.

Search for “lock_wait_timeout”, change the value to “1000” or any other allowed value and “Save changes”.

Now, if you go to the MySql instance and see its details, you will find that the Parameter group shows “pending-reboot”. Changes we made to the Parameter group are reflected in the DB instance after the instance is rebooted.

To verify this, connect to the DB instance using the following command.

mysql -h your-rds-endpoint-here -P 3306 -u admin -p

And execute the following query

show variables like ‘lock_wait_timeout’;

In the above screenshot, you will notice that the Parameter still has its older/default value.

Now, let’s reboot the instance. 

Confirm that you are sure about the reboot. This will take some time to get started. 

Again connect to the DB instance.

mysql -h your-rds-endpoint-here -P 3306 -u admin -p

And execute the same query. 

show variables like ‘lock_wait_timeout’;

This time you will see that the Parameter reflects the changed value.

Conclusion

In this article, we saw the steps to create a Parameter group and attach it to the MySql instance. We also so that making changes in the Parameter groups needs a reboot for the DB instance so that the changed values get reflected in the instance.