EmergencyEMERGENCY? Get 24/7 Help Now!

How to Configure Aurora RDS Parameters

 | August 9, 2017 |  Posted In: Aurora RDS, Cloud and MySQL, MySQL

PREVIOUS POST
NEXT POST

Aurora RDS ParametersIn 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:

Once you create the initial DB parameter group, configure the variables as follows:

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:

I hope you find this article useful, please make sure to share with the community!

PREVIOUS POST
NEXT POST
Nik Vyzas

Nik Vyzas joined the Percona Remote DBA Team in 2015 and is based out of Athens, Greece. Nik has 10+ years experience working with various SQL, NoSQL and Open-source databases. He has worked at a number of companies including Accenture and Pythian. When he is not working, Nik enjoys coding, gaming and cycling.

2 Comments

  • Useful article overall (as always on Percona Blog), but I’d argue with the choice of parameters/values. You’re starting with a parameter group named “percona-opt” (suggesting these are optimized settings) and then using:

    – max_connect_errors=999999 (one of the standard MySQL configuration mistakes, masking errors behind a greater limit)
    – max_connections=5000 (parameter that should be motivated by workload needs rather than set arbitrarily, which may contribute to memory issues the server e.g. if your customer has P_S enabled on a very small instance type; it’s worth noting that already RDS auto-tunes that parameter)
    – innodb_flush_log_at_trx_commit=2

    Yes, this is nit-picking, but people will generally trust Percona and might try to set these parameters assuming they’re endorsed by you.

    Other issues:


    aws rds modify-db-parameter-group
    –db-parameter-group-name

    Should it rather be a parameter group name instead of ?


    aws rds describe-db-parameters
    –db-parameter-group-name aurora-instance-1

    Again, it suggests you’re using an instance name in a call to describe a parameter group.

    Also:


    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.

    Why mention it in the context of parameter changes? I think I know where you’re going with this (failover vs reboot when applying parameter group changes; reboot applies the group, failover does not), but it’s not clear from the text and may be worth clarifying.

    • The goal of this article is to provide a “quick” guide on how to configure parameters on an Aurora instance vs. RDS (i.e. the differences in syntax). To elaborate, it focuses on the how to use the CLI tool rather than “silver bullet values for tuning an Aurora instance” or “recommended values”. Its a given that the values set depend on the needs of the applications using the database which is a very broad topic to cover, worthy of a book rather than a blog.

      Thanks for your feedback.

Leave a Reply to DanPerconaFan Cancel reply