In this blog post, we’ll look at some tips on how to configure Aurora RDS parameters.
I was recently deploying a few Aurora RDS instances, a process very similar to configuring a regular RDS instance. I noticed a few minor differences in the way you configure Aurora RDS parameters, and very few articles on how the commands should be structured (for RDS as well as Aurora). The only real literature available is the official Amazon RDS documentation.
This blog provides a concise “how-to” guide to quickly change Aurora RDS parameters using the AWS CLI. Aurora retains the parameter group model introduced with RDS, with new instances having the default read only parameter groups. For a new instance, you need to create and allocate a new parameter group (this requires a DB reboot). After that, you can apply changes to dynamic variables immediately. In other words, the first time you add the DB parameter group you’ll need to reboot even if the variable you are configuring is dynamic. It’s best to create a new DB parameter group when initializing your clusters. Nothing stops you from adding more than one host to the same DB Parameter Group rather than creating one per instance.
In addition to the DB Parameter Group, each instance is also allocated a DB Cluster Parameter Group. The DB Parameter Group is used for instance-level parameters, while the DB Cluster Parameter Group is used for cluster-level parameters (and applies to all instances in a cluster). You’ll find some of the MySQL engine variables can only be found in the DB Cluster Parameter Group. Here you will find a handy reference of all the DB cluster and DB instance parameters that are viewable or configurable for Aurora instances.
To run these commands, you’ll need to have the “aws” cli tool installed and configured. Note that the force-failover option used for RDS instances doesn’t apply to Aurora. You should perform either a controlled failover or let Aurora handle this. Also, the group family to use for Aurora is “oscar5.6”. The commands to set this up are as follows:
aws rds create-db-parameter-group
--db-parameter-group-name percona-opt
--db-parameter-group-family oscar5.6
--description "Percona Optimizations"
aws rds modify-db-parameter-group
--db-parameter-group-name percona-opt
--parameters "ParameterName=max_connections,ParameterValue=5000,ApplyMethod=immediate"
# For each instance-name:
aws rds modify-db-instance --db-instance-identifier <instance-name>
--db-parameter-group-name=percona-opt
aws rds reboot-db-instance
--db-instance-identifier <instance-name>
Once you create the initial DB parameter group, configure the variables as follows:
aws rds modify-db-parameter-group
--db-parameter-group-name <instance-name>
--parameters "ParameterName=max_connect_errors,ParameterValue=999999,ApplyMethod=immediate"
aws rds modify-db-parameter-group
--db-parameter-group-name <instance-name>
--parameters "ParameterName=max_connect_errors,ParameterValue=999999,ApplyMethod=immediate"
## Verifying change:
aws rds describe-db-parameters
--db-parameter-group-name aurora-instance-1
| grep -B7 -A2 'max_connect_errors'
Please keep in mind, it can take a few seconds to propagate changes to nodes. Give it a moment before checking the values with “show global variables”. You can configure the DB Cluster Parameter group similarly, for example:
# Create a new db cluster parameter group aws rds create-db-cluster-parameter-group --db-cluster-parameter-group-name percona-cluster --db-parameter-group-family oscar5.6 --description "new cluster group" # Tune a variable on the db cluster parameter group aws rds modify-db-cluster-parameter-group --db-cluster-parameter-group-name percona-cluster --parameters "ParameterName=innodb_flush_log_at_trx_commit,ParameterValue=2,ApplyMethod=immediate" # Allocate the new db cluster parameter to your cluster aws rds modify-db-cluster --db-cluster-identifier <cluster_identifier> --db-cluster-parameter-group-name=percona-cluster # And of course, for viewing the cluster parameters aws rds describe-db-cluster-parameters --db-cluster-parameter-group-name=percona-cluster
I hope you find this article useful, please make sure to share with the community!
via MySQL Performance Blog
How to Configure Aurora RDS Parameters