Amazon RDS with MySQL 5.6 – Configuration VariablesRyan Lowe
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:
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
- Thread Pool
Let’s look at the relevance of these individually:
The Audit log PlugIn is a commercial extension not available in the MySQL Community Edition offered by Amazon, so it’s not relevant.
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.
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.
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.
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.
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.
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.”