Using MyISAM for reading and InnoDB for writing data

Question

I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data.

I want to use two structure like an image below. Two same (synchronized) databases but different table engines.

What is your suggestions about this structure? What are disadvantages of this design?

Answer 1

I recommend against using this. Consider:

This would only be of some advantage if on two separate servers (no point in mixing both on same MySQL server, naturally, since reads & writes will take place on both)

Which means you would follow RolandoMySQLDBA's advice, and set up an InnoDB master with MyISAM slave.

Do you have foreign keys on your InnoDB tables? If so, you're into integrity issues. If you happen to have ON DELETE CASCADE or ON DELETE SET NULL - prepare for bad news -- the cascading will not propagate on the slave. See: Impact of foreign keys absence on replicating slaves

OK, so maybe it's just this one table, and no foreign keys. But then you get to inherit all of MyISAM's other disadvantages, such as the likely possibility of not being able to recover from crash. Transactions won't work; the slave may find itself trying to execute queries on a table that hasn't really recovered from a crash; contains incorrect data. You may run into replication failures.

Phil's comment is also very valid: by synchronizing the tables you actually mean any write to your InnoDB table must propagate to your MyISAM table. This means same amount of writing.

If you are willing to relax some constraints, such as only batching (somehow?) the writes to the slave every once in a while, then that makes somewhat more sense.

So, in general, while the solution could work, you would have a constant headache to take care of.

Answer 2

This should pose no problem whatsoever provided you do the following:

Using ServerA and ServerB with no initial data

STEP 01) Setup MySQL Replication

• ServerA as Master
• ServerB as Slave

STEP 02) Add this line on ServerB

[mysqld]
skip-innodb

STEP 03) On ServerB, service mysql restart

This will automatically convert each InnoDB table into MyISAM as it replicates from ServerA

STEP 04) Load your production data (as a mysqldump) into ServerB

You will now have excellent read speed from ServerB

Let ServerA do all the heavy lifting of INSERTs, UPDATEs, and DELETEs.

I answered a question similar to this back on Aug 14, 2012 : Can I have an InnoDB master and MyISAM slaves with Full-Text for searching? . The person that implemented this was happy.

I have also suggested changing user MyISAM tables to Fixed in the past:

• Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes
• May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
• May 10, 2011 : Separate databases for front-end and back-end
• Jun 06, 2012 : MySQL table with 100,000 records queried often

CAVEAT #1

If you already have Data in the Master and Slave and you do not wish to reimport it from a mysqldump, you can do this

STEP 01) Setup MySQL Replication

• ServerA as Master
• ServerB as Slave

STEP 02) Add this line on ServerB

[mysqld]
skip-innodb

STEP 03) On ServerB, service mysql restart

STEP 04) Create this script

MYSQL_USER=username
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name', ROW_FORMAT=Fixed;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN ('information_schema','mysql')"
SQLSTMT="${SQLSTMT} ORDER BY data_length"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > MassConvertRowFormat.sql
less MassConvertRowFormat.sql

STEP 05) Login to MySQL on the Slave and run

source MassConvertRowFormat.sql

CAVEAT #2

While this topology makes it possible for your question to be answered, this is not "one size fits all." In terms of performance, this may be feasible for one application and horrible for another. This sync of data introduces something else: Replication Lag. MySQL Replication is asynchronous by nature. Make sure you effectively monitor replication lag (Seconds_Behind_Master from SHOW SLAVE STATUS\G)