InnoDB -> MEMORY Master-Slaves configuration

MySQL & MariaDB Other MySQL® Questions
1

Dear all,

I really need any advice about this kind of architecture :

A Master → 4 slaves servers configuration with this particularity :
Most tables on the master are created with the InnoDB storage engine (others with MyISAM → InnoDB is not needed for them) and the same tables are created on each salve server BUT with MEMORY Storage Engine.

Database will be about 3GB, slave servers (3.2GHZ Xeon processor) have 12MB of memory (at least 8MB of free memory). The 4 biggest (and most accessed) tables contains about 2 million rows. Others are smaller (10k or 100k rows).

The main idea behind this is to provide four very high performance MySQL slaves for our 4 load balanced webapps running on each server.

I’ve already built a sample of this architecture, running on a single server. I’ve solved the slave crash issue (how to recover the full data loss because of the MEMORY engine) but before going further, I want you to give me your opinion.

Thank you and… happy new year )

2

Hm,

First if you use it mainly for reads and all data fits in memory Innodb tables normally perform already really well eliminating need for in memory tables.

And regarding memory tables and crash what would you expect ? You would need to reclone one way or around.

I would probably do physical clone ie via LVM and then run ALTER TABLE to convert tables to MEMORY before restoring replication.

3

Thank you for your answer. If I understand, InnoDB tables can be as fast as MEMORY tables for SELECT queries (if the memory is large enough to store all the data) ? At least if the innodb_buffer_pool_size is set correctly ?

So there is no good reason to use a different storage engine on the slave servers (even MyISAM) ? That can be a good news.

Regarding memory tables restoring process, i was thinking about that way :

  • export master’s data (dump) and save the binary log point right after this export (for instance each night).

When a slave crashes, the only thing to do is to modify the dump file to change the storage engine from Innodb to Memory, restore the dumped data and specify the new binary log point to the slave. Then start the slave (START SLAVE cmd).

Once again, because I’m quite new to all of this, I am still looking for the most robust and fastest solution.