Some Magento tables are not InnoDB, is it safe to convert all tables to InnoDB?

Question

I am using AWS RDS Read Replica. It constantly has issues with Magento's Memory engine tables. For backup and read replicas RDS loves InnoDB. Can I safely change all tables to InnoDB?

In addition I get the following warning from AWS:

DB Instance magento-monin-prod-db contains MyISAM tables that have not been migrated to InnoDB. These tables can impact your ability to perform point-in-time restores. Consider converting these tables to InnoDB. Please refer to http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html#MySQL.CommonDBATasks.Tables

Plausible Answer

Still interested in feedback. I will add this as an answer if I don't find any issues within the next 24 hours. The steps I took below appear to be safe, so far. My biggest concern was Magento's Memory Engine tables (tables ending in_tmp) and the impact it might have on indexing.

Here is what I did:

1. SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE (ENGINE = 'Memory' OR ENGINE='MyIsam') AND TABLE_SCHEMA='magento_db'

   • For me this returned mostly temporary index tables and magento module tables, so not a lot of critical core tables to be concerned about and few enough tables that I can easily execute another alter table if stuff hits the fan.

2. For each table returned I executed: Alter table {table-name} ENGINE=InnoDB;

I'd be nervous to try this if none of your tables are InnoDB. But, as I said before, there were only a few core tables on my instance that needed to be modified.

• Michael Parkin: Have you been running this in production for long? If so, how is it going - did it cause you any issues? Thinking primarily about the *_tmp index tables which are currently the MEMORY engine.
• Jim of the Stone Age: I haven't noticed anything unusual.
• Michael Parkin: Great, thanks for confirming - we will give this a go and report back too.
• Jim of the Stone Age: @michael parkin keep in mind that we are using Solr search. See the other answers that speak to how this could potentially impact search.
• Michael Parkin: We've been running this in most of our production sites (MariaDB 10.0), all tables (including Memory) running as InnoDB - works great

Answer 1

It is fine to change the data type to InnoDB, assuming one of the following is true:

1. You are using MySQL 5.6.4+ where the InnoDB storage engine supports fulltext search
2. You are not using the default Magento Search functionality which relies on underlying MyISAM fulltext search capabilities. That functionality is troublesome to begin with and the feature set provided in default Magento Search leaves a lot to be desired so I'd suggest using Lucene or Sphinx or best yet Algolia hosted search.

Personally i'd recommend doing this with the Magento DB Repair Tool to minimize risk and also check for any other DB configuration drift or problems. InnoDB is the ideal engine, it's fulltext limitations notwithstanding.

• Jim of the Stone Age: We're using Solar search. So, we should be in the clear as far as search is concerned.
• Tim Eckel: I wouldn't consider InnoDB the ideal engine at all. It's extremely slow compared to MyISAM if you have a lot of search queries. I often hear that InnoDB is faster doing updates and most queries are updates so it's faster. I see the total opposite. For every site I have, I have far more search queries that update/add queries. I tried switching all my tables to InnoDB and page load time went from basically no delay (maybe 0.1 second) to 10 seconds! I converted everything back to MyISAM because it's the ideal engine for speed (and it support fulltext search).
• Bryan 'BJ' Hoffpauir Jr.: Tim, I "KIND" of agree, mostly because I've seen time & again that @ben-lessani-sonassi is just flat-out proven correct & MySQL is rarely a REAL drag on overall perf w/ the # of OTHER systems that need optimization for sub-800ms responses even at load BUT InnoDB is key b/c Mage Core is writing to DB ~ 10X to log each user "view" action plus Solr is best for search perf & qualilty :)
• Fiasco Labs: So, how does converting what's supposed to be InnoDB handle all those foreign key relationships and how complete are deletions that depend on delete on cascade from those fkey relationships? In other words, how do you handle the litter that will be left over by not having the relationships in place?
• Bryan 'BJ' Hoffpauir Jr.: @FiascoLabs It's been a while since I have checked out this comment thread but the focus of the Q/A is to convert FROM MyISAM TO InnoDB. I'm guessing the ones that have the Relational features your mention probably already are InnoDB. MyISAM doesn't support FK's nor Transactions as of MySQL 5.7 though InnoDB does though it deviates from the SQL standard a little

Answer 2

Afaik you should not convert all tables to InnoDB.

catalogsearch_fulltext should stay MyISAM, because InnoDB has no fulltext search support, at least not until MySQL 5.6 (iirc).

For all other tables, though, it should be safe.

• Bryan 'BJ' Hoffpauir Jr.: It's supported in MySQL 5.6.4+

Answer 3

I just changed the MySQL default engine to InnoDB and most of my Magento tables just miraculously transformed themselves to InnoDB (a few are still MyISAM and some are Memory).

Just thought I'd share this...