Replication of MEMORY (HEAP) Tables

Some Applications need to store some transient data which is frequently regenerated and MEMORY table look like a very good match for this sort of tasks. Unfortunately this will bite when you will be looking to add Replication to your environment as MEMORY tables do not play well with replication.

The reason is very simple – both STATEMENT and ROW replication contain the changes to the data in binary logs. This requires the data to be same on Master and Slave. When you restart the slave you will lose contents of your MEMORY tables and replication will break. STATEMENT replication will often continue to run, with contents of the table just being
different as there is a little checks whenever statements produce the same results on the slave. ROW replication will
complain about ROW not exist for UPDATE or DELETE operation.

So what you can do ?

Use Innodb Table Instead Innodb is quite fast when it fits in memory so for most applications this performance will be enough and it will save you from all complexity of different workarounds.

Do not replicate MEMORY tables If you do not really need MEMORY table on the slaves you can skip replicating it specifying replicate-ignore-table=db.memory_table. Note you should not be using STATEMENT level replication with INSERT … SELECT into this memory table for this to work. Be careful using data
on the Slave in this case as table will be empty. Another nice trick sometimes is to make slave to generate its own
copy of the table, for example by running the same cron jobs MASTER runs to refresh this table periodically.

Restart Slaves Carefully I would not use this as long term solution as there are going to be the reasons when SLAVE will not restart normally – power goes down MySQL crashes etc. If you however are using MEMORY table in replication and just want to do a restart without replication breaking you can do the following:
Add skip-slave-start in your my.cnf; run SLAVE STOP; dump all your memory tables using MySQLDump; Restart the MySQL As planned; Load Dumped tables; run SLAVE START; Remove skip-slave-start from config file. Be careful using it with MASTER-MASTER or CHAIN/TREE replication. In this case you will need to disable binary logging while loading data from mysqldump as you may not want these changes to be replicated.

What could have done better ?

MySQL could have features to make it more convenient. It would be great to have MEMORY table option which would save table to on disk file on shutdown and load it back on startup. Of course you would lose the data on unclear start, but it is still handy for a lot of cases.

We could have the option similar to skip-slave-errors but specified on per-table basics. This would allow me to simply allow to avoid all replication errors for MEMORY table which would make things more robust if table is
regenerated periodically. It can be helpful in many other cases too.

Share this post

Comments (7)

  • Kostas

    In our setup we use memory tables as materialized views to speedup some queries, and we need to replicate them for load balancing. Our solution is to start the slave with skip-slave-start and in an init-file script we regenerate the tables and then do START SLAVE.

    This could work perfectly if it was guaranteed that the memory data are in sync with the original tables. But memory engine is non-transactional so we cannot guarantee that. But we can tolerate some differences (the view will be regenerated eventually) so the only issue is not to break replication. For this we use statement-based replication only for those tables and write queries in a way that they won’t throw errors if the slave data are different.

    What I’d like from MySQL:
    – memory-only innodb tables: with all the innodb goodies minus the disk overhead if you don’t care about persistence. (I wonder if this can be achieved by placing the tablespace on a ramdisk)

    – materialized views using such memory-only innodb tables, with automatic refresh on restart

    October 17, 2010 at 12:17 pm
  • Lucas Schirm

    Hi Pete,

    Congratulations for this great article they are very usefully for me and my team, but i want to write a translate for that in my blog, i think thei help more people in portuguease to, i waiting for you approvament to publish the translate!

    Thank you!

    October 17, 2010 at 5:05 pm
  • peter


    Sure, We welcome you to publish translation. Just make sure to provide credit and link to original

    October 17, 2010 at 10:15 pm
  • Daniël van Eeden

    You could replace MEMORY with NDBCLUSTER like Andrew Morgan describes here:
    The NDB engine also supports multiple replication links.

    October 18, 2010 at 1:05 am
  • Baron Schwartz

    Kostas, I assume you mean putting the .ibd file on a ramdisk. You can’t do that. If the server restarts and the .ibd file isn’t there, InnoDB will complain.

    October 19, 2010 at 4:39 am
  • Holger Thiel

    Corrado Pandiani has written a procedure with a federated table to get the data from the master.

    October 20, 2010 at 1:20 am
  • Jo Valerio

    Heap Tables are very useful specially on tables that are frequently read by application but it is a big challenge to use it in a MYSQL Replication setup because it will give you big headache if replication stopped without you knowing it. To utilize the HEAP engine in a Replication Environment, we have considered few things in our configuration and monitoring to ensure smooth transaction.

    September 6, 2012 at 2:39 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.