Cloud Zone is brought to you in partnership with:

I specialise MySQL Server performance as well as in performance of application stacks using MySQL, especially LAMP. Web sites handling millions of visitors a day dealing with terabytes of data and hundreds of servers is king of applications I love the most. Peter is a DZone MVB and is not an employee of DZone and has posted 263 posts at DZone. You can read more from them at their website. View Full User Profile

Amazon RDS with MySQL 5.6: Configuration Variables

08.22.2013
| 2139 views |
  • submit to reddit

This post comes from  at the MySQL Performance Blog.

One long-standing 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 same 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, and 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 in 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 way to go to be fully compatible with configuration variables, but by and large the important ones are available to customers, with minor exceptions (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.”



Published at DZone with permission of Peter Zaitsev, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)