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
- 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.”
What about backup solutions? You can’t use xtrabackup and mysqldump for large datasets is a big pain.
@Miguel Backups is a topic for another post 😀 Or in the meantime, I’ll be covering backups in my Webinar next week.
The big deal-breaker for me is that you cannot perform cross-region replication or attach your own slave to the RDS instance for a roll-your-own solution.
@Aaron There are ways around this. I’ll talk about them in my Webinar next week!
@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. 🙂
@bill “another platform” you mean another database solution, away from RDS, or something else?
@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.
It was helpful for me as I was starting a new job for a client with Amazon RDS and with MySQL 5.6, thanks for sharing.
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.