Mysql – Is MySQL/MariaDB getting a better IOPS deal than Postgres in RDS

Last month, I answered this question : Synchronizing a RDS instance with another RDS instance

From researching and answering that post, I recently learned that you can set read_only off on a MySQL RDS Slave. What can this mean for you ? A very unique opportunity. What opportunity ?

CONVERT ALL YOUR MyISAM TABLES TO InnoDB !!!

Once you have made the RDS Slave writable by setting read_only to 0, you can change the storage engine of all the tables in the RDS Slave to InnoDB. Don't worry, it is possible for a Master to have MyISAM tables and the Slave to have InnoDB. I have suggested Master and Slave having different storage engines before (Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?)

Once you have converted all that tables in the RDS Slave to InnoDB, you make read replicas of that new Slave without the MyISAM headaches of backing up data.

NOTE : Here is a sample script to convert all your MyISAM tables to InnoDB

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
CONVERT_SCRIPT=/tmp/ConvertMyISAMToInnoDB.sql
echo "SET SQL_LOG_BIN = 0;" > ${CONVERT_SCRIPT}
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${CONVERT_SCRIPT}
less ${CONVERT_SCRIPT}

GIVE IT A TRY !!!

I've managed to make it work with pointing slave to master IP instead of url, and putting slave ip to inbound rules of Master's security group