Amazon RDS InnoDB

4 Posts

Evolved Technologies Reply #1, 14 years ago

We are currently hosting an instance of Revo 2.2.4pl on Amazon EC2. Over the pass month our site has started receiving more traffic than our servers can support. We have decided to move our database to Amazon's RDS since will offer us the option of load balancing and auto scaling. Since RDS requires that all database engine be of InnoDB type to allow for point in time backups. We currently can't convert our current MyISAM database into InnoDB do to the Full Text Indexing on the site_content table. Has anyone else ran into this problem and if so what did you do to convert the database to InnoDB.

49 Posts

King'ori Reply #2, 13 years, 10 months ago

You've probably seen this already. I'm also looking for this solution, which led me here;
http://docs.amazonwebservices.com/AmazonRDS/latest/UserGuide/Overview.BackingUpUpAndRestoringAmazonRDSInstances.html

Automated Backups with Unsupported Storage Engines

Amazon RDS automated backups and DB Snapshots are currently supported for only the InnoDB storage engine. Use of these features with other MySQL storage engines, including MyISAM, may lead to unreliable behavior while restoring from backups. Specifically, since storage engines like MyISAM do not support reliable crash recovery, your tables can be corrupted in the event of a crash. For this reason, we encourage you to use the InnoDB storage engine.

If you choose to use MyISAM, you can attempt to manually repair tables that become damaged after a crash by using the REPAIR command ((see: http://dev.mysql.com/doc/refman/5.5/en/repair-table.html). However, as noted in the MySQL documentation, there is a good chance that you will not be able to recover all your data.

If you want to take DB snapshots with MyISAM tables, follow these steps:

Launch Process

Stop all activity to your MyISAM tables (that is, close all sessions)

Lock and flush each of your MyISAM tables

Issue a CreateDBSnapshot API call, or use the RDSCLI rds-create-db-snapshot command. When the snapshot has completed, release the locks and resume activity on the MyISAM tables. These steps force MyISAM to flush data stored in memory to disk thereby ensuring a clean start when you restore from a DB snapshot.

Finally, if you would like to convert existing MyISAM tables to InnoDB tables, you can use alter table command (for example, alter table TABLE_NAME engine=innodb;).

I have schema that had the default engine set to InnoDB.

<model package="xxxxxxxx" baseClass="xPDOObject" platform="mysql" defaultEngine="InnoDB" tablePrefix="cst_" version="1.1">

Use of createObjectContainer to make the tables still made a MyISAM table. So for now, your best bet is Amazon's advise above.

Wish xPDO/MODx would allow someone to use whatever DB engine. Or maybe there's a reason as to why MyISAM is preferred.

Other reads;
Is MyISAM the best table type for site content?
https://forums.modx.com/thread/32164/is-myisam-the-best-table-type-for-site-content

1 Posts

ggkurup Reply #3, 11 years, 5 months ago

Hi

I recently hit this scenario, where I have both Innodb & MyISAM tables running on AWS RDS.
Did the
1 Stop all activity to your MyISAM tables (that is, close all sessions)
2 Lock and flush each of your MyISAM tables
3 Issue a CreateDBSnapshot API call, or use the Amazon RDS CLI command rds-create-db-snapshot. When the snapshot has completed, release the locks and resume activity on the MyISAM tables. These steps force MyISAM to flush data stored in memory to disk thereby ensuring a clean start when you restore from a DB snapshot.

Workout for you? You are you automating this above process?
Would be great if you could share it.

Thank You
Ganesh G Kurup