Amazon RDS DB Parameter Group Deployment Guide

Minimizing Impact

One of the simplest ways to minimize the impact of unintended effects is to limit the number of DB Instances associated with the DB Parameter Group. Amazon RDS allows multiple DB Instances to use the same DB Parameter Group, which makes it easy to apply the same change to a group of DB Instances. This is desirable from an administrative viewpoint, but it also means that the impact of a single undesirable change is potentially greater.

If you do not want a single parameter change to impact multiple DB Instances, you can even use a different DB Parameter Group for each DB Instance. For example, if you have 10 DB Instances that will use non-default DB parameters, create 10 DB Parameter Groups: one for each DB Instance.

Minimizing Risk

In addition to minimizing the impact of an unintended effect, you can minimize the risk of introducing a detrimental database parameter change in the first place by testing changes in a non-critical environment. When you've completed testing, deploy the change to your DB Instances in stages, starting with the least critical first. The steps are summarized below.

1. Create a test environment
2. Test the changes
3. Deploy the changes

Create a Test Environment

Testing should be done in an environment that duplicates the target environment as closely as possible. Fortunately, Amazon RDS makes it easy to create test environments by duplicating existing DB Instances. We use the term "target environment" to refer to the DB Instances and DB Parameter Groups that will be affected by the planned DB Parameter Group changes.

The examples in this guide use a single DB Instance and DB Parameter Group for clarity, but in practice there may be multiple DB Instances and DB Parameter Groups in the target environment.

After completing this step, you will have a test DB Instance and test DB Parameter Group that matches the target environment, which means that the DB Parameter Group changes you'll be testing have not been applied to the test DB Parameter Group yet. You will apply and test those changes in Step 2.

First, create a new DB Instance for the test environment by duplicating the target DB Instance. In the examples, 'acme' is the target DB Instance, and 'twin' is the test DB Instance. Note that the DB Instance class for the test DB Instance is the same as the target DB Instance in order to match the target as closely as possible.

1. Create a snapshot of the target DB Instance using the rds-create-db-snapshot command.

   rds-create-db-snapshot acme --db-snapshot-identifier=testSnap
   

2. Create a test DB Instance from the DB Snapshot

   rds-restore-db-instance-from-db-snapshot twin \ 
    --db-snapshot-identifier=testSnap --db-instance-class=db.m1.large
   

Next, create a new DB Parameter Group for the test environment by duplicating the target DB Parameter Group. Start by creating a new DB Parameter Group, list the user-specified parameters in the target DB Parameter Group, and make the same changes to the test DB Parameter Group.

In the examples, 'acme-prod' is the target DB Parameter Group and 'twin-test' is the copy.

1. Create the test DB Parameter Group

   rds-create-db-parameter-group twin-test --description='Test group' --engine=MySQL5.1
   

2. List the user-specified parameter settings in the target group

   rds-describe-db-parameters acme-prod --source=User --show-long --headers
   
   DBPARAMETER,Parameter Name,Parameter Value,Description,Source,Data Type,Apply Type,Is Modifiable,Allowed Values
   DBPARAMETER,event_scheduler,ON,Indicates the status of the Event Scheduler,user,string,dynamic,true,"ON,OFF,DISABLED"
   DBPARAMETER,innodb_max_dirty_pages_pct,20,Maximum percentage of dirty pages in the buffer pool,user,integer,dynamic,true,0-100
   DBPARAMETER,innodb_thread_concurrency,10,The number of threads that can enter innodb concurrently,user,integer,dynamic,true,0-1000
   

3. Make the test DB Parameter Group settings match those of target group by modifying the parameters of the target group.

   rds-modify-db-parameter-group twin-test \
   --parameters="name=event_scheduler, value=on, method=immediate" \
   --parameters="name=innodb_thread_concurrency, value=10, method=immediate" \
   --parameters="name=innodb_max_dirty_pages_pct, value=20, method=immediate"
   

4. Modify the test DB Instance to use the test DB Parameter Group. Note that the changes being tested have not been made yet.

   rds-modify-db-instance twin --db-parameter-group-name=twin-test 
   

5. Restart the test DB Instance. When a DB Instance changes database parameter groups (i.e. it stops using its current DB Parameter Group and starts using another), it must be restarted.

   rds-reboot-db-instance twin
   

6. Wait for the DB Instance to restart. Use the rds-describe-db-instances command to check the DB Instance status. You can also use the rds-describe-db-events command to see if the DB Instance has been restarted.

   rds-describe-db-instances twin
   DBINSTANCE  twin  2009-08-25 01:10:12.234  db.m1.large MySQL5.1  20GB  XXXXX  available  twin.xxxxxxxxxxxxxx.rds.amazonaws.com  3306  us-east-1d  0 day(s)
         SECGROUP  default  active
         PARAMGRP  twin  in-sync
   

   rds-describe-events --source-identifier=twin --source-type=db-instance --duration=60
   db-instance  2009-08-25T01:18:49.036Z  twin  Database instance shutdown
   db-instance  2009-08-25T01:19:06.391Z  twin  Database instance restarted
   

Test the Changes

At this point you should have a test environment that duplicates the target environment. As noted earlier, the DB Parameter Group changes to be tested have not been made yet. You will make the DB Parameter Group changes to the test environment in this step.

Before making DB Parameter Group changes, establish a performance baseline and verify functionality in the test environment using application loads, benchmarks, and regression tests appropriate for your environment. After making DB Parameter Group changes, repeat the tests and compare the results against the baseline to determine their effects. If you encounter a problem after changing a parameter, roll the change back to its former value or reset the parameter to its default value using the rds-reset-db-parameter-group command.

In the following example, the innodb_thread_concurrency parameter is increased from its current value of 10 to 50.

1. Change the DB Parameter Group settings.

   rds-modify-db-parameter-group twin-test \ 
    --parameters="name=innodb_thread_concurrency, value=50, method=immediate"
   

   Amazon RDS will make the change to all DB Instances that use the DB Parameter Group. If the method specified is "immediate" and the parameter apply type is "dynamic", the change will take place without restarting the DB Instances. If the method specified is "pending-reboot" or the parameter apply type is "static", you will have to reboot the DB Instances using the rds-reboot-db-instance command.

   In this example, there is only one DB Instance associated with the DB Parameter Group. The method specified is "immediate" and the apply type is "dynamic", so the change should be applied within a few minutes. Check the status of the DB Instance using the rds-describe-db-instances command. When the PARAMGRP reports that the test DB Parameter Group is "in-sync", the changes have been applied to the DB Instance.

   rds-describe-db-instances twin
   DBINSTANCE  twin  2009-08-25 01:10:22.234  db.m1.large  MySQL5.1  20GB  XXXXX  available  twin.xxxxxxxxxxxxxx.rds.amazonaws.com  3306  us-east-1d  0 day(s)
         SECGROUP  default  active
         PARAMGRP  twin-test  in-sync
   

2. Connect to the DB Instance and verify that the changes have been made.

   mysql --user=username --password --host=twin.xxxxxxxx.rds.amazonaws.com --port=3306
   mysql> show global variables like 'innodb_thread_concurrency';
   +---------------------------+-------+
   | Variable_name             | Value |
   +---------------------------+-------+
   | innodb_thread_concurrency | 50    |
   +---------------------------+-------+
   1 row in set (0.00 sec)
   

3. As noted earlier, if the apply method specified is "pending-reboot" or the parameter apply type is "static", you will have to reboot the DB Instance using the rds-reboot-db-instance command. It is possible that the new parameter value will prevent the DB Instance from restarting. If this happens, change the parameter value back to a value that is known to work so that Amazon RDS can complete the reboot.

   This example shows how to determine that a DB Instance is unable to start after changing a static database parameter. We use the fictional parameter "crash" to demonstrate. Notice that the DB Instance status is "rebooting", the DB Parameter Group status is "pending-reboot" and that the last event is "Database detected to be down". These conditions indicate that the DB Instance is unable to start.

   rds-modify-db-parameter-group twin-test \ 
    --parameters="name=crash, value=true, method=pending-reboot"
   

   rds-reboot-db-instance twin
   

   rds-describe-db-instances twin
   DBINSTANCE  twin  2009-08-25 01:10:12.234  db.m1.large MySQL5.1  20GB  XXXXX  rebooting  twin.xxxxxxxxxxxxxx.rds.amazonaws.com  3306  us-east-1d  0 day(s)
         SECGROUP  default  active
         PARAMGRP  twin  pending-reboot
   

   rds-describe-events --source-identifier=twin --source-type=db-instance --duration=60
   db-instance  2009-08-25T01:18:49.036Z  twin  Database instance shutdown
   db-instance  2009-08-25T01:19:06.391Z  twin  Database detected to be down
   

   Change the parameter to a known good value to allow the DB Instance to start.

   rds-modify-db-parameter-group twin-test \ 
    --parameters="name=crash, value=false, method=pending-reboot"
   

   The DB Instance will start within a few minutes. If the DB Instance has been down for some time and there are multiple "Database detected to be down" events, allow additional time for the DB Instance to start.

Once you've identified the parameter values that work best for your environment, you're ready to proceed with deployment to the target environment.

Deployment

Before making the DB Parameter Group changes in the target environment, there are a few more steps that you can take to protect your data and reduce repair time in case, despite your rigorous testing, something unexpected occurs:

1. Enable automated backups for the DB Instances in the target environment that will be affected by the DB Parameter Group changes. Backups are enabled by default, but if they are currently disabled, re-enable them prior to deployment to protect your data. See the Amazon RDS Developer Guide for information on backups and recovery.

   In our example, the DB Instance in the target environment already has automated backups enabled, but here's an example of how to enable backups:

   rds-modify-db-instance acme --backup-retention-period=1 --apply-immediately
   

2. Take a DB Snapshot of each DB Instance in the target environment that will be affected by the DB Parameter Group changes. Taking DB Snapshots just prior to making changes to the target environment will reduce recovery time in case it becomes necessary to perform a point-in-time recovery.

   rds-create-db-snapshot acme --db-snapshot-identifier pre-deployment
   

3. Prepare an "undo" script that can be used to reverse the changes.

   rds-modify-db-parameter-group acme-prod \ 
    --parameters="name=innodb_thread_concurrency, value=10, method=immediate"
   

If the changes will be made to multiple business critical DB Instances, schedule the change in stages so that you have time to react and take corrective action if problems develop.

When you're ready, make the changes to the target DB Parameter Groups and verify that the changes have been made successfully as described in "Test the Changes".

Sometimes things just don't go as planned. If problems occur, use the "undo" script you prepared earlier to reverse the changes.