Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to set up Workload Management in MySQL (in particular v5.5 on AWS RDS)?

By Workload Management I mean the functionality implemented in e.g. Teradata, RedShift that allows prioritization of queries. In particular, I want to give high priority to tactical, short running queries from applications and give low priority to longer running background queries.

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

Other than outdated techniques like obtaining exclusive locks on tables and tactics like SELECT HIGH_PRIORITY and INSERT DELAYED -- which are either deprecated or otherwise not useful (especially with InnoDB) -- MySQL (including RDS) does not have any mechanisms for prioritization like this.

Depending on the nature of your "longer running background queries" -- if they are reads, not writes, such as for reporting purposes -- the usual approach is to deploy a read replica. In RDS for MySQL 5.5, the replica server has to be another RDS instance. This is different than "Multi-A/Z" RDS, where you don't have access to the standby server, and switchover to the standby is automatic.

In addition to offloading heavy read loads, having a read replica is still a viable disaster recovery tool. If you were to have a catastrophic outage of your production RDS instance, an RDS read replica can be manually converted to a standalone (master) instance, and you can migrate your application to it -- it will have the most up to date version of your production data possible -- nothing that it received up until the original master failed should be lost.

Standard replication in MySQL is asynchronous -- the replica is not precisely in lock-step-sync with the master, but on a system that is sufficient for the workload offered, the delay is usually a matter of milliseconds. The master sends the changes to the replica almost instantaneously, and the replica -- which started out with an identical data set to the master -- immediately stores a copy of the raw change instructions to disk in the "relay log," and then applies those changes to its own working data set as fast as its disk, memory, and CPU will permit... in a 2-step process. An overloaded replica server's working data set may lag behind the master, but if the master has a catastrophic failure preceded by a period of normal operating conditions, then the replica will have already received the change events and once it catches up on playing back and applying the stream, its working data set will be identical or as near as possible to identical to what existed on the master at the moment the master failed.

New, in RDS for MySQL 5.6, you can actually deploy a read replica on a MySQL instance that is external to RDS, either in an EC2 instance or at your own data center, and have a live copy of your data that's independent of RDS. Officially, a non-RDS replica is documented as being intended for short term migration purposes, but it does not appear to be actually limited to this application. The availability of this feature was the main reason I dropped my resistance to allowing some of my corporate services to use RDS.

Operating in a production environment without at least one live replica as a standby in case of failure of the master is, to me, unthinkable, and the ability to run the reporting queries with zero impact on the master is, at minimum, a bonus. Every production server I have has at least one and sometimes more than one replica.

I don't know if this addresses your needs directly, but I would recommend having one at any rate.


ETA: A follow-up thought that occurred to me as something worth mentioning is that while a read replica generally has identical table definitions to those on the master, it is possible to declare the indexes on those tables as differently as your needs require. On one of my systems, I have a multi-million-row table where the master rarely does anything to more than one row at a time, but the replica does extensive sorting and ordering and group-by, so the replica has substantially more indexes on the same table than the master has. This gives me the luxury and flexibility of what are arguably almost excessive indexes, but without the master having to spend the necessary time maintaining all of those different indexes as it's processing live transactions. If these same indexes had to be defined on the master server, they could add up to unjustifiable overhead... not to mention that I can add additional indexes to an table on the replica at any time, without impacting the live system. In most cases, I do the majority of backups from replicas as well.

share|improve this answer
    
Thanks for the insightful answer. Really helpful. I'll look into replication. –  Enno Shioji Sep 19 '13 at 11:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.