Creating read-replica in RDS of DB that contains both InnoDB and MyISAM tables.

2
votes

Can anyone please tell if it is possible to create a read replica from a database that contains both InnoDB and MyISAM tables in Amazon RDS service.

I know that Amazon doesn't backup MyISAM tables, but documentation seems abit arbitrary when it comes to read replica creation.

This bit of documentation seems abit controversial to me: "Read replicas require a transactional storage engine and are only supported for the InnoDB storage engine. Non-transactional engines such as MyISAM can prevent read replicas from working as intended. However, if you still choose to use MyISAM with read replicas, we advise you to watch the Amazon CloudWatch “Replica Lag” metric (available via the AWS Management Console or Amazon Cloud Watch APIs) carefully and recreate the read replica should it fall behind due to replication errors. "

On one hand it states that transactional engine is required but on the other hand following sentences do not prohibit usage of MyIsam tables. I mean, I am willing to take the risk on losing data concurrency to a degree.

Why do I need this? I have noticed I have a quite large queue on RDS and CPU utilization is < 20%. I have spent a week on optimization of queries thus the result of low CPU utilization. But, Queue is there since there are simply alot of traffic. I concluded that to reduce the size of queue I need to create a master slave solution and move error lenient queries to slave reducing the wait time.

2
mysqlmyisamamazon-rds

2 Answers

2
votes

According to AWS documentations:

Preparing MySQL DB Instances That Use MyISAM

If your MySQL DB instance uses a non-transactional engine such as MyISAM, you will need to perform the following steps to successfully set up your Read Replica. These steps are required to ensure that the Read Replica has a consistent copy of your data. Note that these steps are not required if all of your tables use a transactional engine such as InnoDB.

Stop all DML and DDL operations on non-transactional tables in the source DB instance and wait for them to complete. SELECT statements can continue running.

Flush and lock the tables in the source DB instance.

Create the Read Replica using one of the methods in the following sections.

Check the progress of the Read Replica creation using, for example, the DescribeDBInstances API operation. Once the Read Replica is available, unlock the tables of the source DB instance and resume normal database operations. http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html

0
votes

Basically, the documentation is saying that they recommend only using InnoDB tables. This assists both replication and crash recovery. Technically, you can still use MyISAM tables, but results may not be what you expect, and if something goes wrong, you'll be on your own.