EmergencyEMERGENCY? Get 24/7 Help Now!

Amazon RDS with MySQL 5.6 – Configuration Variables

 | August 21, 2013 |  Posted In: Cloud and NoSQL, MySQL, Technical Webinars

PREVIOUS POST
NEXT POST

One longstanding complaint I have heard for the past several years, and still hear today, is that Amazon’s Relational Database Service (RDS) does not allow the configuration flexibility as running MySQL in an ec2 instance. While true, this ignores the consistent work that Amazon has done to provide access to the most important configuration variables needed to tune a MySQL instance (after all, how relevant is it for a customer to set bind_address in an RDS instance).

Let’s take a look visually:

Screen Shot 2013-08-18 at 11.39.50 AM

MySQL provides 523 options (35 of them NDB specific, so aren’t relevant to RDS), while RDS provides (via the web UI) 283, with 58 of those being immutable (things like basedir, datadir, and a variety of other variables).

So, what’s missing from the RDS configuration? The system variables can be roughly grouped into the following categories:

  • Audit Logs
  • Memcached Daemon
  • Binary Log Settings
  • Performance Schema
  • Relay Log Settings
  • Semi-Sync Replication
  • SSL
  • Thread Pool
  • Other

Let’s look at the relevance of these individually:

Audit Logs

The Audit log PlugIn is a commercial extension not available in the MySQL Community Edition offered by Amazon, so it’s not relevant.

Memcached Daemon

RDS is designed for relational database access, not key-value store access. If you need Memcached functionality, check out Amazon’s ElastiCache

Binary Log Settings

Binary logging is enabled by default on RDS, you just lose the ability to:

  • Use the old version of binary logging (pre-5.6.6)
  • Specify where the binlogs are saved or their base name
  • Control the maximum binary log size

The flexibility of controlling the maximum binary log size would be helpful in some workloads, but isn’t something that is generally tuned in the majority of engagements that I have been a part of.

Performance Schema

That these configuration parameters are not available via the Web UI is a bit of a misnomer. It is possible to enable/disable the Performance Schema and then control the collection via SQL as usual.

Relay Log Settings

Like the Binary Log settings, there is not much that we would want to tune here. The standard settings are appropriate for general workloads.

Semi-Sync Replication

Amazon RDS has a proprietary failover solution and block level replication across availability zones. It is not surprising that this functionality is not provided by default in the Web UI, but certainly something that could be useful for a small cross section of workloads.

SSL

For companies with strict security needs, the lack of SSL may be a deal breaker for using RDS. But, depending upon the security policies in place, can be worked around by using Amazon’s VPC with SSL. For many companies, though, this may not play a role in the decision process. I find it hard to believe that, with Amazon’s resources, providing this is an insurmountable technical challenge. Perhaps we’ll see this becoming available in future RDS releases.

Thread Pool

The Thread Pool PlugIn is a commercial extension not available in the Community Edition of MySQL, so is not relevant to what RDS provides. There are, however, solutions in both Percona Server and MariaDB that Amazon may choose to port in the future.

Conclusion

Amazon still has a ways to go to be fully compatible with configuration variables, but by and large the important ones are available to customers, with minor exception (I’m looking at you, innodb_log_file_size).

I’ll be talking about this topic in more detail, as well as a variety of other RDS 5.6-specific issues, in my upcoming Webinar on August 28 titled “Running MySQL 5.6 on Amazon RDS.”

PREVIOUS POST
NEXT POST
Ryan Lowe

Ryan was a principal consultant and team manager at Percona until July 2014. He has experience with many database technologies in industries such as health care, telecommunications, and social networking.

13 Comments

  • @Aaron

    Pre RDS 5.6 it was not possible, now it is. I am sure Ryan will provide the details.

    What is annoying is that RDS does not allow you to upgrade from RDS 5.5 to 5.6. You must (as per their documentation) do a mysqldump and reload. This means your application is down. This is another reason why vendor lockin is a mixed blessing.

  • Another variable that can be tuned on RDS, but last I checked was left at its default value, is innodb_io_capacity. It would be great if Amazon set that to a more appropriate value by default, since they know better than us what the underlying hardware is capable of.

    Regarding innodb_log_file_size, yes it’s a shame it’s not tunable, but on the other hand if you have an application that generates enough write load that you might want to increase the log file size, then it’s time to move to another platform anyway. 🙂

  • @Jacky, I mean away from RDS. For example, running MySQL or Percona Server on Amazon EC2. You can still be in the cloud, but you can have more control and flexibility over configuration and tuning and monitoring your database instance.

  • @Bill

    I have to disagree with you about innodb_io_capacity. You have a point, if they wanted to make the default more appropriate for non-provisioned iops that would be fine … but the reality is that most RDS users will be using provisioned IOPS so will need to tune it according to their selected size.

  • How do you disable the performance schema in RDS? I have set the “performance_schema” parameter = 0 in my parameter group and have restarted the database, yet the parameter is still ON…

    show global variables like ‘performance_schema’;
    +——————–+——-+
    | Variable_name | Value |
    +——————–+——-+
    | performance_schema | ON |
    +——————–+——-+

  • I am interested in good backup solutions for amazonrds, but I am surprised with the comment that is not possible to use mysqldump to dump large sets of data.
    Currently I have a 20Gb database (using three schemas) and I am doing backups automatically with mysqldump. The only problem is the database blocking while doing the backup, but it is not an amazon rds problem, as far I understand.

    There are better ways to backup? Please let me know!! 🙂

  • Another surprising omission: explicit_defaults_for_timestamp
    Without that a lot of 5.5 based apps will fail on 5.6 as they will write null to get current timestamp and that is no longer allowed!
    This caused us major headaches yesterday.

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.