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.

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.

enter image description here

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

share|improve this question
2  
You do realise that "synchronising" will involve the same amount of writes to the "reading" database, thus making the separation pointless? –  Phil Jan 15 '13 at 15:12
    
Thank you Phil I will consider about it –  Ferid Mövsümov Jan 16 '13 at 7:11
add comment

2 Answers 2

up vote 4 down vote accepted

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 InniDB master with MyISAM slave.

Do you have foreign keys on your InnoDB tables? If so, you're in to 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 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.

share|improve this answer
    
Thank you for your answer I will consider about it +1 –  Ferid Mövsümov Jan 16 '13 at 7:11
    
+1 for this more comprehensive answer, especially laying out the disadvantages. –  RolandoMySQLDBA Jan 16 '13 at 12:22
add comment

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:

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)

share|improve this answer
    
Thank you for your answer. –  Ferid Mövsümov Jan 16 '13 at 7:10
    
You should accept @ShlomiNoach's answer instead of mine. –  RolandoMySQLDBA Jan 16 '13 at 12:26
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.